3

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.

Pedram
  • 6,256
  • 10
  • 65
  • 87

1 Answers1

3

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.

Christopher Brown
  • 2,170
  • 1
  • 13
  • 19
  • Yes, I have a grouping on Main Report with `Number` column, Also, I can have all the Number in a format like `102,103,104` (By using LookupSet and Join) but again I'm stuck! How can I pass this value to pass in Dataset of Main Report. For example: I've set '102,103,104' in one textbox of Main Report and created a new dataset that will fetch total count. But I don't have any idea to pass this value as Parameter to new Dataset. Please help and thanks a lot for your answer! – Pedram Apr 05 '16 at 06:12
  • You cannot pass values from a textbox into a dataset. Datasets are run before the body of the report is rendered. What you can do, however, is this: Let's say dataset A contains the number. Add a CTE or temporary table to Dataset A that contains the same data in your subreport related to all the number values retrieved in Dataset A. Then, you can join to that CTE or temporary table within Dataset A and run an aggregate on that CTE or temporary table at the record level (which then can be grouped and totaled within the tablix as needed). Does that make sense? – Christopher Brown Apr 05 '16 at 21:03
  • yeah I got it, I did the same yesterday, what you mentioned today. I made different dataset and mixed my logic of subreport dataset into newly created dataset and returned only aggregate value that was I looked for. But Reporting service should provide some way to get sub report value on main report... Anyways I really thankful to you... – Pedram Apr 06 '16 at 04:25