Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reporting Services - Multiple Page Breaks Issue Expand / Collapse
Author
Message
Posted Monday, January 5, 2009 4:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 102, 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
Post #630200
Posted Monday, January 5, 2009 4:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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
Post #630211
Posted Friday, January 9, 2009 11:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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
Post #633731
Posted Saturday, January 10, 2009 7:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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


  Post Attachments 
Nested Items.rdl.txt (23 views, 14.55 KB)
Post #634268
Posted Sunday, January 11, 2009 6:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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
Post #634443
Posted Monday, January 12, 2009 7:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 102, 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


  Post Attachments 
Mutiple Dataset.rdl.txt (25 views, 41.69 KB)
Post #634662
Posted Monday, January 12, 2009 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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
Post #634737
Posted Monday, January 12, 2009 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 102, 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.


  Post Attachments 
Mutiple Dataset From Tables.rdl.txt (19 views, 38.59 KB)
DDL.sql.txt (19 views, 2.04 KB)
Post #634761
Posted Monday, January 12, 2009 10:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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


  Post Attachments 
Mutiple2.rdl.txt (25 views, 38.60 KB)
Post #634856
Posted Monday, January 12, 2009 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 102, 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?
Post #634881
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse