I am not sure if I am doing something wrong to be honest.
I have a simple Master - Detail type report. The report is displaying the correct data. However, when I run SQL Profiler to review the queries that are being executed, I see the master query run as expected. The detail query however has no filter on it.
I expected to see the parameter value from the master report being passed to the subreport so that it filters the query.
The result of this is a slow rendering of the report. It appears that for each record in the master report, it executes the sub reports query in an unfiltered state. My sub query could have 100,000+++ records. So it grabs that entire dataset, passes it back to the master report which then picks the correct record(s) that it wants. It then moves on to the next parent record.
Is that correct? I must be doing something wrong.
Think of it this way.I have customers table and invoices table. Each customer could have numerous invoices. I want a customer listing report that shows all the invoices for those customers. The sub-report query would be something like "Select Customer, Invoice from Invoice". I would have thought it would be something like "Select Customer, Invoice from Invoice where Customer= @Customer".
Please let me know what I have done wrong.
Brian
1 Answer, 1 is accepted
The subreport item has its own SqlDataSource that indeed can be filtered server-side based on a Report Parameter (Filter DataSource records), which value can be passed from the main report.
The value of the filtering parameter is provided as part of the ReportSource property of the corresponding SubReport item in the main report (check How to: Set ReportSource for SubReport).
Note that the filtering Report Parameter should be linked to the SqlDataSource parameter used in the 'Where' clause of the SQL Query - Using Parameters with the SqlDataSource component.
I have attached a sample report definition using a subreport (also attached), which SqlDataSource is filtered server-side by the parameter value passed from the main report. The SQL Profiler indicated that the correct 'Where' clause is applied in the queries from the subreport.
My suggestion is to take a look at the set up of the sample report definitions and try to make the appropriate adjustments in the detail report that currently does not get filtered as required.
The sample reports use the AdventureWorks database that should be available on the local machine in order the samples to work correctly. It may be necessary to update also the connection string to the database according to your local settings. Note that the AdventureWorks database gets installed with Telerik Reporting examples by default, as most of our demo reports use it as data provider.
I hope the provided information and the example would be helpful.
If the problem persists, please, send us the main and the detail reports for local investigation.
Regards,
Todor
Progress Telerik