0

TL;DR

Is it possible to extract a value or parameter from a subreport within that subreport's parent (containing) report?

Description

I use a report with a subreport to display complex data about a bucket of countries. The subreport is inside a tablix which is associated with a dataset and receives a country code and some other parameters to display complex data about a single country. The parent report consolidates the individual country subreports and adds some static data like a cover sheet and table of contents.

Each subreport is several pages long and concerns a single country.

Problem

I'd like to implement a footer on the main report which contains the name of the country on each page which displays country data. However, this is proving quite difficult to implement.

I cannot directly reference the dataset from the footer because it contains multiple rows (one for each country), and I want to display the relevant country name for each page.

There does not seem to be a practical way to get the country name or country code from the subreport.

I've also experimented with placing hidden text boxes inside the tablix and referencing the textbox using ReportItem from the footer, but this has proven to be a problematic solution. Placing the text box above the subreport causes the country name to only be rendered on the first page of the subreport. Likewise, placing it below or to the right of the subreport causes the country name to only be rendered on the last page of the subreport.

I've also tried creating another column to the right of the subreport and placing the text box in there. This allows me to correctly render the country name on each page, however it causes a variety of formatting issues within the subreport, most seriously suppressing page breaks.

Question

Rightly or wrongly, I'm focusing on getting the country name (or country code) directly from the subreport, however I'm open to other solutions for this problem.

Sample Data

Sample data from the parent report is a list of countries (or country-level entities for the purposes of our report) and associated parameters which are fed to a tablix containing the subreport:

Master Report Data Sample

Sample data from the subreport is a list of line items pertaining to an individual country which are to be formatted and displayed:

Subreport Data Sample

Mark Micallef
  • 2,643
  • 7
  • 28
  • 36
  • Did you tried using the LookUp function. And this might help you http://stackoverflow.com/questions/19279412/ssrs-pull-variables-or-values-from-sub-report-into-main-report – Mahesh Jan 19 '15 at 08:46
  • @CoderofCode Thanks for the link, but I'm not sure how to use that. Lookup() function doesn't seem to get data from a subreport, so is there something I'm missing, or have I read it wrong? I'm trying to avoid replacing the subreport with inline data objects because it's complex and has its own subreports. – Mark Micallef Jan 20 '15 at 00:29
  • As you mention `I cannot directly reference the dataset from the footer because it contains multiple rows (one for each country), and I want to display the relevant country name for each page.` that means if you have country dataset access in main report then, You can use your `Lookup` function on that dataset – Mahesh Jan 20 '15 at 05:57
  • @CoderofCode Thanks, but how would I ensure that it displays the country being rendered by the subreport on that page? The whole problem goes back to determining which country from the dataset of countries is the one which is currently (on the current page) being passed in to the subreport as its parameter. – Mark Micallef Jan 20 '15 at 06:01
  • Can you post some dummy data from your main report dataset and sub report dataset and your desired output structure – Mahesh Jan 20 '15 at 06:03
  • @CoderofCode I've added some screenshots of the data we're using. Sorry for the delay. Is it possible to use the Lookup() function in the master report to determine the country being used in the subreport? I could add the country name as an additional column in the subreport data set if that would help. – Mark Micallef Feb 12 '15 at 04:06

0 Answers0