reportviewer with multiple database and multiple optional parameters

Question

User-958411764 posted

I have 3 databases, db1/db2/db3, every db with 3 tables - customer master, sales record master and item master. In the web page, I got 5 selections for users to choose,

• From which db - combo box
• From what age to what age combo box # get value from customer master
• Area - combo box # get value from customer master
• total purchase value - textbox # get value from sales record master
• item type - combo box # get value from item master

User can generate the report by fill in all the required information or they can just fill in some of them.

Example user can fill in age and item type to generate the report. System will search the record in all db's customer master table and item master table

or user can fill in db2, area, total purchase value and item type to generate the report. System will search record in db2's customer master table, sales record table and item master table.

i am using VB to code. I able to generate the report with single db and multiple fixed parameters (means user must fill in all required information).

It is highly appreciated if anyone can give me some idea how to generate this kind of report in asp.net 4.0 (VB).

Thanking you in advance.

Answer 1

User-67082742 posted

Please refer to my previous post : http://forums.asp.net/t/1903368.aspx/1?Multitenancy+reporting

This post mentions that you can use a dynamic connection string for your report to get the desired database. All other parameters can remain as they are.

 

Please mark as answer if this helps!

AB

Answer 2

User-958411764 posted
It is possible to provide step by step how to do it? I already read your previous post, but have no idea how to do it. Thanking you in advance.

Answer 3

User-67082742 posted

The connection string is available in Report datasource. Click on Report Datsource --> got o daatsource properties and in expression write :

="DataSource =" & Parameters!RServer.Value & ";InitialCatalog=" & Parameters!Rdatabase.value

Now got reportParameters-->right click  and select addParameter. Name the parameter RServer . Now add second parameter and name it Rdatabase. Thereafter in your VB code add these paarmeters to receive values from user selected dropdown values and post them to Report url wherever the report is calle din your code.

Sorry but its not possible to give more detailed steps here.

Answer 4

User-958411764 posted
Hi Abhi.ssrs, First of all thanks for your reply. Our users may select data from db1/db2/db3, or either one of them. When they select data from more than 1 db, how do I generate multiple database records into one report, since dataset accept 1db only. Correct me if i'm wrong. I got few searching criteria, user may fill in all to searching criteria or just filter in some field to search and generate report. Due to those field extract data from different table. How to filter record based on user searching criteria? Example if user search for customer information and sales record, then system just search inside customer master and sales record master. If user search for customer information and item record, then system just arch inside customer master and item master. I mean can system intelligent enough to detect which database to search and which table to search. Thank you!!

Answer 5

User-67082742 posted

Hi, In that case, you will need to do that in dataset using a stored proc. I dont think we will be able to achive that in SSRS . You might send the database names selected by user to stored proc as paarmeters and in stored proc use the fully qualified table name with database names and then join the results. In case the Database are on different servers, I am not sure how to query them. you might need to search for that on net.

 

Answer 6

User-67082742 posted

These links may help :

http://www.techrepublic.com/article/work-in-multiple-databases-using-these-methods/6038771

 

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141343

 

http://dbaspot.com/ms-sqlserver/433234-can-i-use-sql-reporting-multiple-databases.html

Answer 7

User-958411764 posted
Thanks for your link and information. I will try to find more information about store procedure, never use it before. The link is useful for me to settle multiple database problem. But still no idea how to make this kind of complicated report.

Answer 8

User-149030355 posted

you CAN USE lookUPS in order to see information from other dataset.....