Is there any approach to get access the Sub report variable or to get SubReport element in main report?

Question

I am writing an SSRS report in which I've a tablix that actually contain a subreport in one of its table cell. Now I want to understand to get this value as Sum on main report. For example, below is the student data:

Student Name | Total Subject | Obtained Marks (Sub Report)

XYZ          |  6            |  35

ABC          |  6            |  46

In above example, I am able to see the total marks of the first subject only. But I need to get the total form all 6 subjects from sub report. My tablix is already has Grouped by Student Name.

Below are the ways I tried to get it done:

Added another column in tablix and try to get the subreport as ReportItems! Subreport2, didn't work.
In the same column, I tried with Sum (Subreport2).

But since Subreport2 as report items are not accessible I'm not able to get it done.

UPDATE 1 I am using below expression to get sub-report value:

=ReportItems("Subreport2").value

Does SSRS use vbscript? Could you at least show some code to give us an idea? — user692942
I've not been using VBScript but relying only on Expressions. Sub Report retrieved as an object line by line in main report. But I need to get its value to make further calculations on it. — Mohsin A.
According to the SSRS documentation, it uses vb.net not vbscript which is why I asked, will edit the question and re-tag for you. — user692942
Here is the expression I am using to get the subreport value. But it returns with exception: =ReportItems("Subreport2").Value — Mohsin A.
Would help to edit the question and provide the exception you receive, that way others maybe able to give a clearer answer or point you in the right direction quicker. Would recommend reading How to Ask and if you haven't already take the tour to get a better idea of how Stack Overflow works and what is expected of those who participate in the community. — user692942

Steve-o169 Steve-o169 · Accepted Answer · 2019-03-28T12:30:13

The short answer is, no, you can't access fields from a subreport in the manner that you are attempting to. However, there is an easy way to get the values from that subreport that you may not have realized. The basic solution is to simply add another dataset to your report using the same query or stored procedure that is used in the subreport and getting the required data from that dataset. For example, you could then use a lookup function to match the data as needed. The lookup function requires four parameters detailed in the following expression.

= Lookup([SourceValue].Value, [DestinationValue].Value, [LookupValue].Value, "[DestinationDataset]")

The idea in your case would be to put your student name or ID field from the main report dataset in for source value, match that value in your subreport dataset for the destination value, get the value you need for lookup value, and the subreport dataset goes in parenthesis to indicate where to get the data from. So the following expression gives you an idea of what you need.

= Lookup(Fields!studentID.Value, Fields!studentIDSub.Value, Fields!ObtainedMarks.Value, "SubreportDataset")

Take a look at the documentation if you need any addition information.

Is there any approach to get access the Sub report variable or to get SubReport element in main report?

1 Answers