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

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?

Answer 1

Hi,

Can you clarify what it means "to be re-usable for different datasources and datasets"?

---

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

Answer 2

Would like to have  on report and chart that can be used for multi-datasources and data sets

enduser would select the type of data they want report for example Product Sales, Sales by Region, Sales by Country, Sales by Agent  but it would be one dynamic table/matrix report and chart re-used for all these data sets.

Answer 3

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.

Answer 4

I don't have queries - I want to use stored procedure - so how will I do the same for stored procedures. I don't want to edit the store procedures result set.

Answer 5

Hi,

Try below links , hope it will help you :)

http://www.codeproject.com/Articles/56817/Dynamic-Reports-with-Reporting-Services

http://www.codeproject.com/Articles/42543/Setting-the-Data-Source-of-Reporting-Services-at-R

http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL

http://blogs.msdn.com/b/sqlforum/archive/2011/05/03/walkthrough-control-dataset-query-dynamically-based-on-ssrs-report-parameters.aspx

http://sqlserverrider.wordpress.com/2011/11/04/dynamic-data-source-for-ssrs-reports/

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 6

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