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

Display Complex Hierarchical Data with Server-Side Pagination Expand / Collapse
Author
Message
Posted Wednesday, December 21, 2011 9:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:04 AM
Points: 9, Visits: 97
Comments posted to this topic are about the item Display Complex Hierarchical Data with Server-Side Pagination
Post #1225536
Posted Thursday, December 22, 2011 1:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
Very nice work, thank you.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1225609
Posted Thursday, December 22, 2011 11:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Nice work thanks. I added this to my briefcase for future reference.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1226085
Posted Friday, December 23, 2011 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:46 AM
Points: 7, Visits: 45
Yes, a great post: very useful to know how the paging works. This is great for the single column recursive hierachial data for which it was intended.

On the other hand, on a related topic, is there any similiar thinking concerning how to deal with the more general case of non-recursive hierchial data which is found in multiple columns, for example, Country, State, and City, with visibility toggles for State and City.

Unlike the article EmployeeID example, in my case, I don't see a way to predetermine and then set page numbers for the numerous State and City rows and my reports with large recordset do indeed suffer the repaging performance hit described in the article. Any advice?
Post #1226290
Posted Friday, December 23, 2011 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:04 AM
Points: 9, Visits: 97
Thanks for your comment.

SQL Kiwi (12/22/2011)
Very nice work, thank you.
Post #1226313
Posted Friday, December 23, 2011 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:04 AM
Points: 9, Visits: 97
Thanks for the feedback. I hope this article can be helpful someday.




SQLRNNR (12/22/2011)
Nice work thanks. I added this to my briefcase for future reference.
Post #1226323
Posted Friday, December 23, 2011 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:04 AM
Points: 9, Visits: 97
I'd suggest to maintain data at the leaf-levels of the HIERARCHYID column and create artificial nodes for the roll ups. Let's say the HIERARCHYID column is called LocationId, and all the sales records have their LocationId set to cities. If state roll ups are needed, you can insert 50 artificial nodes into the hierarchy and point sales data's parent to the newly created state nodes; similarly, if country roll ups are needed, add country nodes to the hierarchy and then point state nodes to country nodes. Essentially, you maintain real data at the leaves and build the tree by inserting roll up nodes. I hope this helps. Please let me know if you have any questions. Thanks,

george 86905 (12/23/2011)
Yes, a great post: very useful to know how the paging works. This is great for the single column recursive hierachial data for which it was intended.

On the other hand, on a related topic, is there any similiar thinking concerning how to deal with the more general case of non-recursive hierchial data which is found in multiple columns, for example, Country, State, and City, with visibility toggles for State and City.

Unlike the article EmployeeID example, in my case, I don't see a way to predetermine and then set page numbers for the numerous State and City rows and my reports with large recordset do indeed suffer the repaging performance hit described in the article. Any advice?
Post #1226332
Posted Friday, December 23, 2011 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:46 AM
Points: 7, Visits: 45
Thanks a lot for the advice. I'll have to digest your idea before I can comment on it, but at first glance, I can say that I will need a flexible solution that allows for an ever-changing groups of Countries and Cities.
Post #1226339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse