Reporting Services - Multiple Page Breaks Issue

Question

Post reply

Reporting Services - Multiple Page Breaks Issue

Gurpreet Atwal

SSC-Addicted

Points: 498
More actions
January 5, 2009 at 4:03 pm

#195706

Hi All,
I have a unique kind of requirement explained as under.
I have two table controls on report design.
1) The table one is say main details of metric
2) The table two is the trended values of the metric over time.
I have added a group on metric type to each of the tables, and added a page break at beginning of the group.
Now the requirement is that when table one shows details of metric "A" the table two should show trended data for metric "A"
The way I have implemented it, shows all the main details first one metric per page then all the trended data one metric per page.
I have 16 metrics so I have 32 pages in the output (first 16 with main details and then another 16 for trended data).
Is there any way that we can use the same group on both tables and the output will have 16 pages with two tables on each page (one for main details and the second for trended data)
Thanks
Gurpreet

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think you can do this a couple of ways.

1. Nest the trended table in the details table.

2. Put both tables inside a list control.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

It may be awhile before I get to an example as I currently don't have SQL Server 2005 easily available. I will post an example on this thread later though.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Attached is a report with this query:

[font="Courier New"]SELECT

S.schema_id,

S.name AS schema_name,

COUNT(O.OBJECT_ID) AS objects_in_schema

FROM

sys.schemas S JOIN

sys.all_objects O ON

S.schema_id = O.schema_id

GROUP BY

S.schema_id,

S.name

[/font]

The report has a list control with a group on schema_id and schema_name which has page break at end set. Then a table and a chart control within the list.

Just remove the ".txt" from the file.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Gupreet,

Please continue to post on this thread, I am subscribed to it so I will be notified when you post on it. In order to give accurate help the SQL and the rdl would be helpful.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gurpreet Atwal SSC-Addicted Points: 498 More actions · Answer 5

Jack, thanks for all your help.

I have attached a sample report to this post. There are two controls on the report, the first one is a table with the Seg and Metric, the second one is a matrix with the metric value trended for each segment over time.

I want to do a page break in a way that if the first table shows seg 1 the second control the matrix only shows trended data for seg 1.

So the attached report would give you 5 pages for seg 1, 2, 3, 4, 5 with two controls on each page.

Please let me know if you need more inputs.

Regards,

Gurpreet

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Can you provide the DDL for your source tables?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gurpreet Atwal SSC-Addicted Points: 498 More actions · Answer 7

Jack, I am attaching the DDL to this post. It will create two tables SegmentMetrics and TrendedSegmentMetrics, the first one has definitions and is for the first table on the report, the second table has trended data for the segments and metric combination.

I have also added the insert statements to the DDL to create a sample dataset.

I have also modified the rdl to read from these tables and attached that also.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Okay, here's how I'd do it. I use one dataset, which I think you need to do in order to do it anyway. It also reduces the roundtrips and reduces load on the SQL Server.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gurpreet Atwal SSC-Addicted Points: 498 More actions · Answer 9

I had thought about that option.

But I decided not to go with it because it is not an elegant solution.

I had simplified the example posted. But in the actual report the table one has like 13 columns and the table two is something similar.

Do you think there is any other way of achieving this?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I don'[t understand what you mean about it not being an elegant solution. It does what you desire, it limits round trips, and it takes advantage of the abilities of the platform.

I think your only other option may be to use a subreport for the matrix and put it in the table. Of course this would mean your dataset for the matrix would be called once for each row in the table.

I really think the solution I have suggested will perform best. If there are reasons it will not work because of the added information you need returned, post the complete DDL and samples and we'll see if there is a way to do what you need. I can only provide solutions based on the information that is provided.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mloew Newbie Points: 2 More actions · Answer 11

Thanks for all the information in this thread guys.

I am having a similar issue and was wondering what the best method was to fix my problem. This should do the trick thanks for posting here.

Mr. Data Newbie Points: 1 More actions · Answer 12

I know this post is pretty old, but I wanted to thank Jack for his response. It was helpful.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

Steve,

Thanks for the post. Responses like yours are what makes people continue to post solutions.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Duncan Bossle Valued Member Points: 50 More actions · Answer 14

Duncan Bossle

Valued Member

Points: 50

June 2, 2010 at 6:56 am

#1175255

Accidentally posted here. moved to new thread.