Using Dataset for Microsoft reporting

Question

When we use SSRS to create reports, we can use query box in Dataset window to create custom queries. But in local reports (.RDLC) when we want to create reports we should set a designed Dataset for report.

The problem is when we have 100 reports we should also have 100 Datasets.Currently we use a Dataset with these fields (F1,F2,F3,...,F100) and before binding our datatable to reports we change name of the datatable columns. but it's readability is low.

I want to ask that is there better way to solve this problem?

Thanks

Answer 1

Its the best solution is grouping the datasets. You can group dataset avoid by their business. For example POLICY.xsd , USER.xsd, INVOICE.xsd and add to other dataset into to the xsd file.

For example In visual studio you see 10 dataset file. But every item have different datatables.

Second upgrade is you will write better t-sql. When you general select your column for similar report you will use same datatables.

For example A report include 5 columsn A1,A2,A3,A4,A5 B report include 6 column A1,A2,A3,A4,A5,A6

for these report you will use B report's datatable

Answer 2

I may need more information in order to help - are you really saying that you have Datasets with fields labeled F1, F2, F3, ... and that you now need to bind to the actual tables with columns labeled (for example) Id, CustType, IsActive, ... ?

If this is the case then I would suggest using Views that alias each fieldname to be F1, F2,... This may sound like just as much work as your original problem but everything can be automated.

Creating these 100 views can be achieved via a script. This script can be produced by running some smart T-SQL that obtains the schema data stored in SQL Server and outputs the script required.

Have a look at How can I get column names from a table in SQL Server? to get started.

You will probably need to use cursors in your T-SQL to loop through the schema data and output the script to create the views.

Finally, if you need to modify each of your 100 SSRS reports in the same manner then don't forget that each is stored in Report Definition Language (XML) so you may be able to write a small utility that reads each XML file and makes the necessary changes. Don't forget to backup those files first before letting a new utility loose on them.

I hope all this helps and that the answer has not arrived too late.