Paging in RS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Paging in RS

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-05-09 : 10:47:08

Hi there

I've managed to make it query that return a dataset that have 2 views utilising the "Filter" in RS. I treat this as a single record with multiple views.

Now let say if I have a stored precedure that pass 2 parameters one is called state and year and accepting 'ALL' for every possibility of State and Year and construct that into single dataset with 2 views similar like above.

How do I breakdown this in the reporting services so it will have paging?

This is a simple dataset:

RECORDID, ReportViewType, State, Year, VALUE
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003
4, "VIEW1", "VIC", 2, null
4, "VIEW2", null, null, 11001

I would like to break down (paging) this per recordid. Each page obviosuly has 2 views using the same data set with different FILTER.

Do I need to put into a LIST then inside that list put 2 TABLES? Is this possible?!?!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 11:02:32

quote:
Originally posted by dewacorp.alliances

Hi there

I've managed to make it query that return a dataset that have 2 views utilising the "Filter" in RS. I treat this as a single record with multiple views.

Now let say if I have a stored precedure that pass 2 parameters one is called state and year and accepting 'ALL' for every possibility of State and Year and construct that into single dataset with 2 views similar like above.

How do I breakdown this in the reporting services so it will have paging?

This is a simple dataset:

RECORDID, ReportViewType, State, Year, VALUE
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003
3, "VIEW1", "VIC", 2, null
3, "VIEW2", null, null, 11001

I would like to break down (paging) this per recordid. Each page obviosuly has 2 views using the same data set with different FILTER.

Do I need to put into a LIST then inside that list put 2 TABLES? Is this possible?!?!

Thanks

paging? You mean you need to show only one RECORDID items in one page?

dewacorp.alliances

452 Posts

Posted - 2008-05-09 : 17:00:17

quote:

paging? You mean you need to show only one RECORDID items in one page?

What I mean that display whatever each recordid per page.

[PAGE 1:]
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000

[PAGE 2:]
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000

[PAGE 3:]
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003

[PAGE 4:]
4, "VIEW1", "VIC", 2, null
4, "VIEW2", null, null, 11001

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-09 : 17:05:58

Just group by the recordid (in RS not in stored proc) and select the option to page at the start or end of the group.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

dewacorp.alliances

452 Posts

Posted - 2008-05-09 : 20:48:50

Hi Tara

So you are saying that there is an option for grouping by page?!?! I don't know this ... I am investigating this.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 01:09:20

quote:
Originally posted by dewacorp.alliances

Hi Tara

So you are saying that there is an option for grouping by page?!?! I don't know this ... I am investigating this.

Thanks

Yup. That is in grouping properties. You have a PageBreakAtEnd property available for this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources