locked
Can you create a dynamic report utilizing multiple data sources and data sets? RRS feed

  • Question

  • I have project to create a One dynamic report with a One dynamic chart that must have the capability to be re-usable for different datasources and datasets.   

    I was thinking that I need a fake dataset which has columns as: column1, column2, column3, etc and delegate the real name of the column from other data sets.

    I am not sure where to begin - could someone provide a real sample rdl  using Northwind database?


    Wednesday, October 2, 2013 1:10 PM

Answers

  • This can be accomplished in SQL.

    Here is an example:

    You have an integer parameter named @source. When @source=1 then the data for the report should come from SalesByRegion table and when @source = 2 then the data for the report should come from SalesByCountry table. 

    The sample SQL below should do the job.

    Select Field1, Field2 From SalesByCountry
    Where @source=1
    UNION 
    Select Field1, Field2 From SalesByRegion
    Where @source=2
    


    Remember to mark as an answer if this post has helped you.

    • Proposed as answer by Charlie Liao Tuesday, October 8, 2013 7:47 AM
    • Marked as answer by Charlie Liao Sunday, October 13, 2013 3:19 PM
    Wednesday, October 2, 2013 4:43 PM
  • Hi Jazlady,

    If you want to use stored procedure to create the dataset, then you can create a stored procedure using the query which provided by Igor.
    create procedure sp_dynamicquery
    @Table nvarchar(50)
    as begin
    select Name as column1,Subject as column2, Score as column3 from table1 where @Table='table1'
    union
    select Name as column1,Week as column2,Amt1 as column3 from table2 where @Table='table2'
    end

    And then create a dataset using this stored procedure.

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Friday, October 11, 2013 1:17 AM
    • Marked as answer by Charlie Liao Sunday, October 13, 2013 3:19 PM
    Tuesday, October 8, 2013 7:47 AM

All replies