CODEWITHSUNDEEP
sql-servervb.netvariablesreporting-servicessubreport
Mohsin A.
Mohsin A.

Reputation: 163

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

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:

  1. Added another column in tablix and try to get the subreport as ReportItems! Subreport2, didn't work.

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

Upvotes: 0

Views: 428

Answers (1)

Steve-o169
Steve-o169

Reputation: 2146

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.

Upvotes: 0

Related Questions