Solved

SSRS Adding Two Fields from Two Separate Subreports onto the Main Report

Posted on 2014-02-21
4
2,496 Views
Last Modified: 2014-02-24
Hello:

Attached is my main report called "Contract Commitment Report".  Also attached are two of its subreports.

From the report containing "Past Due" in its name, the main report shows a total Past Due amount (column).  From the other attached subreport, the main report shows a "Total" amount (column) on the far right-hand side.

I need for the main report to add these two amounts together.  How do I take a value from one subreport and add it to the value of a second subreport?

Thanks!

TBSupport
Contract-Commitment-Report.rdl
POOR-RCG-PastDue-Detail-Subrepor.rdl
POOR-RCG-Detail-Subreport2.rdl
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Assisted Solution

by:planocz
planocz earned 250 total points
ID: 39879886
The sub-report parameters have to be in the main report parameters for them to be passed back to the main report. Then in the main report you just add the 2 parameters togather.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39879927
Thanks, planocz, for the quick response!

Do you have any general steps that I might follow, on how to add the two parameters together?

TBSupport
0
 
LVL 27

Expert Comment

by:planocz
ID: 39880042
Sample:
Main report has 2 place holders like Sub1 and Sub2 in the parameters.

Sub1 report has its parameters and also has a Sub1 parameter.

Sub2 report has its parameters and also has a Sub2 parameter.

When the parameters match (by name) in each report the reports know that parameter can be returned to the main report.

Now that the Sub1 and 2 have data in its parameters it can be seen by the main report.
In the main report just add the two Sub1 and 2 parameters.

Like Totals: (Sub1.value + Sub2.value)

Hope that helps you.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 250 total points
ID: 39881865
I always thought that passing a value from a subreport to the main report was not possible with SSRS. So I would very much like to get Planocz's technique to work. But I'm afraid I'm missing something. I've added a parameter to the main and sub report (in my test I only use 1 subreport) with the same name: "Sub1". When I run the main report it asks for the value of the parameter "Sub1". When I supply a value for this parameter the report runs but instead of the subreport I get an error message "One or more parameters were not specified for the subreport, ....". So I set a default value for the Sub1 parameter on the subreport. Now the subreport runs, but the main and sub report have a different value for the Sub1 parameter. I tried tried to link the parameters in the subreport and tried different combination of default values, but I'm only able to pass a value from the main to the sub report and not the other way around.

The only solution to TBSupport's problem I know is just adding two extra datasets to the main report that calculate the total. You can use the same query as on the subreport and let the report sum the values or you can change the query a bit so it only returns the total.
You can add a text field to the main report and refer to a value in a specific dataset like this:
=sum(Fields!PastDue.Value,"RCGPastDue")

Open in new window

For this to work the Dataset RCGPastDue must be added to the main report first.

I've attached a small sample where I do something similar with one subreport and the max function instead of the sum. But the technique is exactly the same. Enter 1, 2 or 3 as the value for the parameter on the main report.
Main.rdlMainSub.rdl
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question