Using Dataset for Microsoft reporting
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
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
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.
I must confess I don't fully understand the problem with the information provided so far, but here's a solution which might well be a huge benefit to you.
Have you tried creating your reports from stored procedures? By doing things this way, your RDLC file will not contain a query, it instead looks at the definition of the query within the stored procedure, and creates a dataset within the RDLC file. Then at run-time, you execute the stored procedure in code, and pass the returned data into the RDLC file for execution.
The advantages of doing it this way are that the queries are encapsulated as database objects, you don't end up with a million datasets in your project, and you can de-couple the query from the report, so if you had 6 versions of the same report which all used the same fields, but used different WHERE clauses for example, you could use just 1 single report (and call the relevant stored proc at run-time).
链接地址: http://www.djcxy.com/p/93846.html上一篇: 尝试格式化SQL查询结果
下一篇: 使用数据集进行Microsoft报告