Home > Home, MSDN, SSRS > SSRS: Two Tablix page break with data parallely

SSRS: Two Tablix page break with data parallely

This question I got from MSDN, orginal question link here

Question Brief :-

I have 2 tablix in a report. Both the table has 30 records each in it. I want to show both the tablix reports one after another in the front page. Initially the report should display 10 records from the first table and 10 records from the second table in the first page. Then in the second page, next set of 10 records from the 1st & 2nd tables and third page should display last 10 records from the 1st & 2nd tables.

So I used Row Number function to split 10 records in both the tables. Now what happens is, In the 1st page it shows 1-10 records of the first table, 2nd page shows 11-20 records of the 1st table and 3rd page shows 21-30 records of the 1st table. Finally on the 4th page only I can able to see 1st set of 10 records from the 2nd table. But I want 10 records from both the tables should be shown in the 1st page itself, next set of 10 records from both the tables in the 2nd page and last set of 10 records from both the tables in the 3rd page. So All the records from both the tables will be shown in 3 pages itself.

For this issue, I created one work around , things we required:-

  1. Main report
  2. Sub report having table 1
  3. Sub report having table 2

Lets create two subreports first,

First subreport data set query:- This query is to generate 30 records, you can place your query also.

;With NumberSequence( rowrum, Tablename) as
(
Select 1 as rowrum , ‘table 1’ Tablename
union all
Select rowrum + 1, ‘table 1’ Tablename
from NumberSequence
where rowrum < 30
)
Select * From NumberSequence Option (MaxRecursion 1000)

Create data set using above query in the report.

Create parameter Groupnum  integer type in the report

Create calculated filed in the Data set as shown in below:-

Calculated Filed expression:- =Ceiling((Fields!rowrum.Value)/10)

1

After that add filter in the dataset based on parameter as shown in below screen:-

1

Add the table in the report design as shown in below screen:-

1

So First Sub report is created.

Like above create second subreport also.

I used the data set query for 2nd report is as follows:-

; WITH NumberSequence(rowrum, Tablename) AS (SELECT 1 AS rowrum, ‘table 2’ Tablename
UNION ALL
SELECT rowrum + 1, ‘table 2’ Tablename
FROM NumberSequence
WHERE rowrum < 30)
FROM NumberSequence OPTION (MaxRecursion 1000)

Only difference is in the Tablename value to identify which report data is showing the main report

Add the calculated field as we did in sub reports dataset , same in main query dataset properties and calculated field  with this expression  =Ceiling((Fields!rowrum.Value)/10)

No Filter needed as we did in subreports dataset

once both the sub reports are ready, then start creating main report.

Main report data set query:-

;With
NumberSequence1( rowrum)as
(
Select 1 as rowrum
union all
Select rowrum + 1
from NumberSequence1
where rowrum < 60
)
Select * From NumberSequence1
Option (MaxRecursion 1000)

In your main data set query , just produce total number of records your reports has.

like 30 +30 = 60 rows

Drag the list from tool bar into report body

Create group as add parent group with expression:- =ceiling((Fields!rowrum.Value)/10)

1

Delete the sorting expression

Delete the column of the group just created, but not delete the group.

Add pagebreak in the group as shown in below screen:-

1

Now insert two subreports control into the list and add both the reports, your design will look like below:-

1

 

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

1

Please do the same thing in second subreport as well.

Once this step done, execute the report.

Report will look like below:-

1

In second page navigated then :-

1

in last page:-

1

 

Any issue, please contact me. Video on this as given below:-

If you want to see my YouTube channel on different BI tools click on below link:-

Thanks

Prasad

My YouTube Channel : https://www.youtube.com/channel/UC_uH3Dloc4I86PtQVrHVlcg

 

 

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. Supriya Raman
    May 9, 2017 at 5:57 pm

    Hi, I have a similar requirement. I have explained it here http://stackoverflow.com/questions/43867676/display-tables-vertically-in-a-report-grouped-by-itemnumber. Could you please help?. Many Thanks!

    • May 9, 2017 at 7:22 pm

      Hi Supriya,
      I haven’t checked this workaround on report builder. But i dont think there should be any problem if all the steps followed as I mentioned . However if u forward screenshots of report design and the error message after u execute. You can email me on knowbitools@gmail.com
      Thanks
      Prasad

  1. May 9, 2017 at 4:16 pm

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: