Home > Home, MSDN, SSRS > Main and Sub report

Main and Sub report

I go this question from MSDN, original question here

Brief:-

I have a salesperson matrix report which I have added a  matrix sub report. I am using multiple parameters.  If I select two salespeople say 1 and 2 I need the output to be this way:

SalesPerson 1 Main Report

SalesPerson 1 Sub Report

PageBreak

SalesPerson 2 Main Report

SalesPerson 2 Sub Report

The report is running the main report for both salespeople before running the subreport.  Is there a way to gourp it so the output is like above

Create main report using below sample query:-

select * from

(

select ‘Salesperson1’ Salesperson, 100000 SalesTarget, 50000 Sales

UNION

select ‘Salesperson2’ Salesperson, 200000 SalesTarget, 100000 Sales

UNION

select ‘Salesperson3’ Salesperson, 500000 SalesTarget, 400000 Sales

UNION

select ‘Salesperson4’ Salesperson, 400000 SalesTarget, 300000 Sales

UNION

select ‘Salesperson5’ Salesperson, 600000 SalesTarget, 600000 Sales

)T

Where Salesperson in (@Salesperson)

Use Matrix control in main report to show data

Create sub report using below sample query:-

select * from

(

select ‘Salesperson1′ Salesperson, 100000 SalesTarget, 50000 Sales,’India’ Region

UNION

select ‘Salesperson2′ Salesperson, 200000 SalesTarget, 100000 Sales,’US’ Region

UNION

select ‘Salesperson3′ Salesperson, 500000 SalesTarget, 400000 Sales,’ASIA’ Region

UNION

select ‘Salesperson4′ Salesperson, 400000 SalesTarget, 300000 Sales,’JAPAN’ Region

UNION

select ‘Salesperson5′ Salesperson, 600000 SalesTarget, 600000 Sales,’CHINA’ Region

)T

Where Salesperson in (@Salesperson)

Come to main report and add list item.

Drag main report matrix into the list

Insert subreport control into the list.

Your design will look like below:-

1

Assign dataset to list as shown in below screen:-

2

Assign parameter value to subreport as shown in below screen:-

3

Go to the detail group and add group on expression as shown in below screen:-

4

After adding group expression enable page break as shown in below screen:-

5

 

Run the report, you will see first page output like:-

6

Next page output will look like below:-

7

 

So we can group the main report and subreport data.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: