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

Pauwles

Are you okay with a solution which creates dynmaic datasets at runtime and set it as datasource for your reports? – Custos

I can't really udnerstand your question. Do you want to know if your GUI is too compilcated or if the readability of your code could need some boost? In either way it would help alot with some code snippets and or screenshots. – Selwyn

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

Anthropophagi

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.

Personality

Thanks but I know All what you said and I try some of them. So I search for a better solution – Pauwles

Answer 3

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).

Sherrill

Answer 4

Although it would be nice to see a Minimal, Complete, and Verifiable example I'll give it a shot as well.

What you can do is, creating a Table in your db, put the data you need related with each DataSet and loop through that data.

i.e.

ID.............Fields..........................................QueryType..............SqlQuery/StoredProcedure

1...............ProducID, ProductName............0..............................SP_GetBasicProductInfo

2...............OrderID, ProductId.....................0..............................SP_GetOrderIdsByProduct

3...............CustomerID, CustomerName....1..............................Select CustomerID,CustomerName From Northwind.Customers

etc.

This way, you can get the Fields to be mapped to and sql query to be executed. You can in the end pass this info to your Data Access Layer to execute query and fill in the DataSet that you need.

You can try converting everything to string or even use a delimiter for each type such as int|ProductID,string|ProductName

Shearin