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

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

How to change crystal report document title dynamically?

I have ReportClass object of .Net CrystalReport
that uses .rpt file
ResCalc.SalesReport rpt = new ResCalc.SalesReport();
rpt.SetDataSource(dsSaleRep);
How to change crystal report document title dynamically?
Thanks,
Alex.Hi Alex,
i am new member of this forum...

you can try the following:

ReportDocument oRpt = new ReportDocument ();
oRpt.SummaryInfo.ReportTitle = yourinputcontrol.Text;

as in my case it was a checklist box so i did as follows

Reports.TIPSheetsSummary frmTIPSummaryReport = new TIPSheetsSummary();
frmTIPSummaryReport.oRpt.SummaryInfo.ReportTitle = "TIP Sheets Summary for Airplane Tab No. " + this.checkedListBox1.SelectedItem.ToString();

Regards,
Ashwini

How to build FROM clause dynamically

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.A SQL Server stored procedure is a poor option for giving users ad-hoc query capability. You would need to write a routine that parsed their input statement (very difficult considering that users have little understanding of relational databases) and then would create a logic execution plan from the statement given known relationships among tables.

Wait a minute...that's what Query Analyzer does! Why not just allow the user to submit adhoc query statements? (Make sure your security is tight and you have a query governor active!)

Either that, or check into some of the data-mining software packages such as DI Diver or Cognos.

Where do you live in Melbourne? I spent two years as a kid in Box Hill.|||Thaks for your reply,

but I am not writing the store procedure and I do not have an option using any packages, like Cognos, Crystal etc. I am writing the procedure in VB (it is not up to me). And the main idea, that uses SHOULD NOT HAVE any idea about relational database. Uses just say: I want SQL Server database(could be others - like Oracle, Sybase or MS Access), I supply them names of available servers, they choose the server, I supply names of availabe databases, then tables, then fields, they choose whatever the want, then they point at the related fields of the tables they chose before, and "magic" happened - they have a report. I've done almost everything, just bits and pieces left. AND a FROM clause! It works for simple queries, but for more complicated it works sometimes, which obviousy is not good enough. So I nee an algorithm and stuck with that.

I live at Moorabbin and I know Box Hill - very nice area. Where do you live now?|||*ack* the whole joining thing is the problem...

I have tried this before... basically unless you can query the db to find out what the foreign keys are you are kinda stuffed...

Otherwise the users have to know enough about the database to be able to define the relationships themseleves...|||it sounds to me that the best solution for your problem
which is:
dynamic sql statements
ad hoc queries
no sql knowledge at the end user

you are an excellent candidate for ENGLISH QUERY
There are sample apps available for this product
code samples and etc.

English Query (http://www.microsoft.com/sql/evaluation/features/english.asp)|||I think Ruprect's English Query suggestion is your best shot. The problem is that a user who does not know anything about relational database is more likely than not to get the WRONG ANSWER to a problem due to not understanding relational set manipulation. This is what DBAs and SQL developers are for.

Think about it. Basic SQL is not that complicated. If a user can't understand "Select columna, columnb from sometable where columnc = somevalue" they shouldn't be mucking about in a database anyway. I mean, the syntax is practically an English sentence anyway. Hey, how about a procedure that lets them submit it in Australian?

"Grab beer, prawns, lamington from cooler where label = 'Fosters'"

By the way, now I am back in the United States (Midwest), but I still remember my old address down under 25 years ago: 17 Simmons Street, Box Hill.|||if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think|||Depending on how many tables the users are going to want to access and how fluid the ad-hoc queries are, you could work this in two other ways - which aren't elegant but might be enough to serve your purpose:

Either, restrict the queries that the users can create by offering them a list of possible query options that you have already generated the SQL for, or create a table that holds the correct joining criteria for your tables so that you can select the required code.

These won't work if you do want it to be a completely open ended query tool - but I would have thought that if you're getting to the point of ad-hoc queries using OUTER JOINS, then your users will probably have the SQL knowledge already...

Just a thought...|||If you just want to give them slice, dice, and filter capability on defined recordsets. then consider a pivot table linked to a view from either a spreadsheet or a web page.|||Originally posted by r937
if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think

That is exactly what our company is trying to do and I am aware that is not easy task, but I have to do it somehow.|||in that case i would suggest investigating INFORMATION_SCHEMA views to see if you can create queries that can access the tables, column, and especially primary/foreign keys

i think with MS access you are up the creek, but i believe the other databases all support INFORMATION_SCHEMA

good luck and let us know how your project turns out