SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Cross Tabs and Pivots, Part 2 - Dynamic Cross...
102 posts, Page 4 of 11
«««23456»»»

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jeff Moden
Posted Wednesday, December 03, 2008 9:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
rbarryyoung (12/3/2008)
Great article, Jeff. Like The Return of the King, it was well worth the wait! :)


Heh... that has a familiar "ring" to it. :P Thanks for the feedback, ol' friend!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613373
Jeff Moden
Posted Wednesday, December 03, 2008 9:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
LSAdvantage (12/3/2008)
I have been getting my daily SQLServerCentral.com emails and ashamedly skipping by them lately. I just had other stuff going on and hadn't lurked here in a while. But when I saw the subject, "Cross Tabs and Pivots, Part 2", I thought to myself..... that's gotta be something from Jeff. I HAVE to read that! As usual, pure poetry. It reconfirmed much of what I already do and taught me a few new tricks in the process. Thank you for taking the time to be so thorough.

Lisa


Awesome feedback and compliment, Lisa. What you said in your feedback is exactly what I was aiming for... Dynamic Cross-tabs are nothing new to folks like yourself... my goal was to do a little "Mo-dee-can Tweekin' " to maybe make them easier for some folks and it looks like I may have accomplished that.

Heh... Poetry? I had no idea that it would have the length of the Illiad and the Odessy when I starting writing this one. :P

Anyway... thanks for "stopping by". Haven't seen you around in a while.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613376
Jeff Moden
Posted Wednesday, December 03, 2008 9:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
Nicole Bowman (12/3/2008)
Jeff, thanks for another great article. Your articles are easy to understand and fun to read. I too would like an article on Analysis Services. I haven't used it and would like an article that I can understand. Pretty please would you write one?

Cheers!


Thanks for the great feedback, Nicole. It's funny... I never did well in High School English... the teachers kept telling me that I wrote like I talked and, for some reason, they thought that was just wrong. I spent a fair bit of time in the U.S. Navy and their training manuals were the epitome of boring technical rhetoric. I swore that I'd never write like that. Thank you for confirming that I haven't. :)

Hmmmm... an article on Analysis Services... not sure I'm qualified to do such a thing. Like my good friend, R. Barry Young, I'm a bit of a true-blue T-SQL wonk and I've never used Analysis Services because I've simply not needed it. Hah... At this point, I'm not sure I could even spell "MDX" correctly. :P Considering your awesome encouragement, though, it may be time to learn it and maybe write something about it if the people that already know it don't laugh me out of town. :D

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613384
ZA_Crafty
Posted Wednesday, December 03, 2008 9:56 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:31 AM
Points: 136, Visits: 146
Great Article.


And, I've not had the pleasure (displeasure according to rumor) of using Crystal Reports or Reporting Services and, thus, have no idea how long they would take to generate such a report.

Mostly we youngsters like to do the cross tabs in crystal. Yep, Crystal can be a pain with all it's bugs and "funny'isms", but a crosstab like this one, will take me 5 minutes or less in Crystal, and another 5 to make it pretty and do the report CI.
On 2 occations I had to manually create crosstabs in sql. I had quite a struggle, because I'm so used to do it in crystal. It's always a good idea to stay active on both playfields, and this article has done just that!
Thanks!
Post #613385
Jeff Moden
Posted Wednesday, December 03, 2008 10:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
ZA_Crafty (12/3/2008)
Great Article.
Mostly we youngsters like to do the cross tabs in crystal. Yep, Crystal can be a pain with all it's bugs and "funny'isms", but a crosstab like this one, will take me 5 minutes or less in Crystal, and another 5 to make it pretty and do the report CI.
On 2 occations I had to manually create crosstabs in sql. I had quite a struggle, because I'm so used to do it in crystal. It's always a good idea to stay active on both playfields, and this article has done just that!
Thanks!


As a mostly a high volume batch process/ETL programmer/tuner, I don't very often get tasked with making a report... I'm mostly a T-SQL wonk. But, I absolutely agree... if you have a reporting tool that can do the cross-tab for you (Access has a pretty good one, too!), you're probably way ahead of the game.

I've never used Crystal reports myself... is there a place or menu where you can figure out the correct indexes for some of the more complex reports? Or, do you have to copy the code to something like SMS and do it there? I also had some folks that got into Crystal Reports pretty heavily a couple of jobs back. One of their chief complaints was that it didn't cleanup all the temporary files (1 per generated page, if I recall correctly) that it made during report generation. Do you know if they fixed that problem in the later revs of CR?

Thanks for the feedback, ZA...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613392
Jeff Moden
Posted Wednesday, December 03, 2008 10:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
As usual, one'n'all, I'm humbled by your great feedback and suggestions. The exchange of ideas in these article discussions is the thing I look forward to the most.

With that in mind, it's been suggested (a couple of times), that Analysis Services might be the "bee's knees" when it comes to doing the likes of cross-tabs and pivots. I'd really be interested in hearing your additional thoughts/opinions/experiences, pro or con, on the care and feeding of Analysis Services. In the process, try not to bash us T-SQL wonks too hard... :P heh... we've gotta eat, too!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613394
ZA_Crafty
Posted Wednesday, December 03, 2008 10:30 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:31 AM
Points: 136, Visits: 146

Well Indexes I still do in the procedures. Mostly our procs are just glorified selects, and we let sql decide which indexes to use, but on a few reports we had to specify the correct ones. These are ugly selects that takes up to 10 seconds.

And nope, that temp file bug is still there. I'm also scared to upgrade the service pack. we are still on 11.5, but have 4 applications using it. For one application the developers are in Japan, so a quick thing like upgrading the service pack can get very nasty if it goes wrong.
Post #613396
IntellectYog
Posted Wednesday, December 03, 2008 10:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 11, 2009 1:20 AM
Points: 28, Visits: 55
Jeff Moden (12/2/2008)
Comments posted to this topic are about the item


Hello Jeff,

Thansk for a nice articles on Cross-Tab.

I have one report format which is related to Dynamic Cross Tab report.

In which I am extracting data in row wise and designing it in fornt end like cross tab.

Can I directly desing that report in Cross-Tab in Query itself..

The for of report is as follows..


I have to show Sale of Item week wise...Which are group by their item group.

like..

ItemGroup ItemCode Sale_Week40 Sale_Week_41 Sale... Total

1001 101 50 45 95
1001 102 10 50 60
1001 103 5 2 7

SubTotal of Item Group 65 97 162
:
:
:
Grand Total

Can I do above format report in SQL with good performance..?

Please Suggest.

Thanks.

Best Regards
IntellectYog
Post #613402
IntellectYog
Posted Wednesday, December 03, 2008 10:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 11, 2009 1:20 AM
Points: 28, Visits: 55
Jeff Moden (12/2/2008)
Comments posted to this topic are about the item


Hello Jeff,

Thansk for a nice articles on Cross-Tab.

I have one report format which is related to Dynamic Cross Tab report.

In which I am extracting data in row wise and designing it in fornt end like cross tab.

Can I directly desing that report in Cross-Tab in Query itself..

The for of report is as follows..


I have to show Sale of Item week wise...Which are group by their item group.

like..

ItemGroup ItemCode Sale_Week40 Sale_Week_41 Sale... Total

1001 101 50 45 95
1001 102 10 50 60
1001 103 5 2 7

SubTotal of Item Group 65 97 162
:
:
:
Grand Total

Can I do above format report in SQL with good performance..?

Please Suggest.

Thanks.

Best Regards
IntellectYog
Post #613409
Jeff Moden
Posted Wednesday, December 03, 2008 10:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 20,183, Visits: 13,714
ZA_Crafty (12/3/2008)

Well Indexes I still do in the procedures. Mostly our procs are just glorified selects, and we let sql decide which indexes to use, but on a few reports we had to specify the correct ones. These are ugly selects that takes up to 10 seconds.

And nope, that temp file bug is still there. I'm also scared to upgrade the service pack. we are still on 11.5, but have 4 applications using it. For one application the developers are in Japan, so a quick thing like upgrading the service pack can get very nasty if it goes wrong.

Do I ever understand the upgrade dilema... it makes perfect sense for us to upgrade to SS 2008 right now because we're writing the 2.0 version of our infrastructure at work... but, most of the folks spend most of their time writing GUI code and there's only a couple of us that can bring full guns to bear on some of the more complicated back end processes, right now. We're in a training frenzy and trying to keep up, at the same time. We also know that some of the code will simply be copied... you just gotta know that some of that code is going to break in the migration from 2k to 2k8. It's not a matter of figuring out if something is going to hit the fan... the problem is figuring out how much is going to hit the fan. :P

Thanks for the feedback, ZA... always a pleasure to see how the "other side" lives.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #613414
« Prev Topic | Next Topic »

102 posts, Page 4 of 11
«««23456»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use