SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services - Multiple Page Breaks Issue


Reporting Services - Multiple Page Breaks Issue

Author
Message
Gurpreet Atwal
Gurpreet Atwal
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 59
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
Attached is a report with this query:


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
            




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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Attachments
Nested Items.rdl.txt (42 views, 14.00 KB)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Gurpreet Atwal
Gurpreet Atwal
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 59
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
Attachments
Mutiple Dataset.rdl.txt (44 views, 41.00 KB)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
Can you provide the DDL for your source tables?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Gurpreet Atwal
Gurpreet Atwal
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 59
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.
Attachments
DDL.sql.txt (39 views, 2.00 KB)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44317 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Attachments
Mutiple2.rdl.txt (38 views, 38.00 KB)
Gurpreet Atwal
Gurpreet Atwal
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 59
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search