How to change data Dynamically in report services

Hi,
Please help me. I created a report and works fine but I am wondering how to change data dynamically. For example I have different data base and want to run the same report in different places I can't go and change the report each time. Any suggestion please Thanks in advance.

Not sure what you mean with different data base. Is the underlying schema always identical and just the data base server / name is different?

If yes, then various approaches for dynamic database connections in RS 2000 are
available:

* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp

* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp

* Use the linked server functionality of SQL Server - but read the SQL Server documentation carefully about potential performance impacts.

* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")

* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:

<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value
&amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||Robert - How do I handle the design of the report if the underlying schema is not the same always . I posted a question today but I am repeating the same here

I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic

For ex the report looks like

Mon Tue Wed Thu Fri Sat Sun

-- - -- - --

2 2 4 2 4 5 6

0 7 6 7 9 4 2

The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...

Please advice how to program this dynamic nature of the report

|||Hi Kumar
have you found any solution yet ? as i need similar functionality and wondering if you have a solution.
Thanks .|||

You should change the query so that instead of returning one column per day you "denormalize" it to return one day per row:

Day Count
-- --
Mon 2
Tue 2
Wed 4
...

In the report, you could then just group by the Day field (in a matrix), or use conditional aggregation if you choose a table layout: e.g. for the Monday column in the table you would use =Sum(iif(Fields!Day.Value = "Mon", Fields!Count.Value, 0))

-- Robert