3
votes

I have a Main Report within a Sub Report

Using reporting services, How to get the value from Sub report to the Main report?

I am calling sub report, based on main report's tablix data. So for example I have tablix on main report, that passes @Number value to sub report to fetch some child records and I want the sum of total values back to main report. How can I achieve this!

I tried by making a dataset that could not helped me. Getting 0 count every time.

I used below expression on main report by creating sub report dataset on main report.

=COUNTROWS("SubReportDataset")

Yet, I did not find any solution that worked in report, Please help.

1

1 Answers

3
votes

How to do it by referencing the subreport directly

Supposedly, you can access subreport items in the following way:

[Reports]![YourReportName]![YourSubReportName]![TheValueFromTheSubReportYouWantToReference]

I've seen references to this sort of thing in the past, but I've never been able to get reportItems references (or this sort of thing) to work consistently and in the way I imagined it would.

Keeping in mind of course that you're referring to a subreport that is presumably repeating multiple times, which instance of the given subreport would you be pulling with the reference above? I think you'll find this more trouble than it's worth.

How I recommend you approach it

As I've stated above, I think referencing the subreport directly is likely to be difficult and unreliable if not impossible. Instead, you're stating that you're passing @Number into your subreport. If you're utilizing a view or function within your subreport to pull the data based on @Number, then you could easily embed the same logic plus an aggregate function in your main report. This way, you can reference an aggregate value from a query in your main report that is utilizing the same calculations and data as your subreport.

If your subreport is not using views, functions, or shared datasets and you cannot (or choose not to use) views, functions, or shared datasets (notice I listed them twice for emphasis), then you'd be stuck reproducing your SQL query in the main report and calling the query logic and aggregating it in the main report. This is potentially a maintenance nightmare (and one I'm actively trying to minimize in my own organization) as you must maintain the same SQL logic in two places. That said, it can be done and done fairly easily depending on the complexity of your SQL query in the subreport.

If you have questions, leave them as a comment and I'll do my best to help.