SSRS parameters dynamically passed to other reports

Question

I'm using SSRS 2008 R2.

This one is hard to describe. I have a report that has a main report on top and 10 sub reports below it. The main report ranks the top 10 industries by revenue. The sub reports are detailed reports within the ranked industries. But they need to be dynamic. So for August, if the #1 industry is AUTO, the sub-report for AUTO appears in position 1. Then in September, if the #1 industry is RETAIL, the sub-report for RETAIL needs to appear in position 1. For example ...

August --> 
MAIN REPORT:   1. AUTO
        2. RETAIL
        3. FOOD

SUB REPORTS: POS 1=AUTO, POS 2=RETAIL, POS 3=FOOD

September --> 
MAIN REPORT:   1. RETAIL
        2. FOOD
        3. CONSUMER

SUB REPORTS: POS 1=RETAIL, POS 2=FOOD, POS 3=CONSUMER

Note that the sub reports order changes depending on the main report. The sub report accepts the Industry name parameter but I don't know how to dynamically change the parameter.

I tried the CHOOSE function as the value of the Industry parameter and it works if I pass it an explicit string of values. I'd love to be able to pass it an array. I tried passing a dataset name but it just thinks its another string value.

  =CHOOSE(1,"AUTO","TRAVEL","FINANCIAL",...)

LOOKUP fails because I can't use a Field in the parameter statement.

Any help is appreciated! Thanks, all!

-- Since I can't upload a picture, I'm going to attempt to build a diagram to show you how this would look. Imagine the SSRS work area. There is a main report and several sub reports. I'm using the dots to simulate spaces. Notice that the order of the sub reports change to match the order of the rank in the main report. So what I need to do is pass the industry parameter from the main to the sub reports dynamically.

Main Report - August
Rank...Industry...Revenue
1........AUTO.........$333
2........RETAIL......$222
3........FOOD........$111

Sub Reports - August
.........AUTO
Rank...COMPANY...Revenue
1........TOYOTA........$555
2........HONDA.........$111
3........LEXUS..........$99

.........RETAIL
Rank...COMPANY...Revenue
1........WALMART........$45
2........TARGET.........$35
3........COSTCO..........$25

.........FOOD
Rank...COMPANY...Revenue
1........KROGER........$888
2........MOMS............$277
3........FOOD CITY....$150

Main Report - September
Rank...Industry...Revenue
1........RETAIL.........$333
2........FOOD..........$222
3........CONSUMER........$111

Sub Reports - September
.........RETAIL
Rank...COMPANY...Revenue
1........TARGET........$555
2........JC PENNY.........$111
3........DILLARDS..........$99

.........FOOD
Rank...COMPANY...Revenue
1........FOOD CITY........$45
2........KROGER.........$35
3........RALPHS..........$25

.........CONSUMER
Rank...COMPANY...Revenue
1........P&G...........$888
2........KIMBERLY............$277
3........J&J..........$150

I attempted too but got an error that I have to be a level 10 to upload images. I pretty new to Stack Overflow so don't have the privileges yet. — BradD
Well I am not sure what you want but i have feeling that i can help you but without correct understanding its difficutlt — Sanjay Goswami
I've added a mock up of the reports. Hopefully that will make things clearer. — BradD

Sanjay Goswami Sanjay Goswami · Accepted Answer · 2014-11-06T21:29:50

Create 2 report 1) Main Report 2) Sub Report

Note : Sample Image attached -- Output Output Parameter for Sub Report

ParameterSelect for SubReport Main Report Layout

MainReport Layout Subreport Layout

SubReport Layout 2 Parameter created for Sub Report

Create 2 Parameter in SubReport

Datasource

---------- Data Source for Main Report
IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Main'))
BEGIN
    CREATE TABLE #Main
    (
        [Rank] INT,
        Indusry VARCHAR(500),
        Revenue INT,
        [MONTH] int
    )

    INSERT INTO #Main VALUES(1,'AUTO',333,8)
    INSERT INTO #Main VALUES(2,'RETAIL',222,8)
END
SELECT * FROM #Main
------------------ Data source for Sub Report
DECLARE @Industry VARCHAR(50)
SET @Industry ='Auto'

DECLARE @Month Int  
SET @Month =8


IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#AUTO')) 
BEGIN
CREATE TABLE #AUTO
(
    [Rank] INT,
    Indusry VARCHAR(500),
    Revenue INT,
    [MONTH] int
)
INSERT INTO #AUTO VALUES(1,'TOYOTA',34,8)
INSERT INTO #AUTO VALUES(2,'HONDA',44,8)
END


IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#RETAIL')) 
BEGIN
CREATE TABLE #RETAIL
(
    [Rank] INT,
    Indusry VARCHAR(500),
    Revenue INT,
    [MONTH] int
)

INSERT INTO #RETAIL VALUES(1,'WALMART',55,8)
INSERT INTO #RETAIL VALUES(2,'TARGET',44,8)
END 



IF @Industry ='Auto'
BEGIN
    SELECT * FROM #AUTO WHERE [MONTH] = @Month
END
ELSE IF    @Industry ='Auto'
BEGIN
    SELECT * FROM #RETAIL WHERE [MONTH] = @Month
END

Note : Base on Condition data will display into sub report.. parameter passed from parent report

SSRS parameters dynamically passed to other reports

3 Answers