Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Post reply

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 15 posts - 31 through 45 (of 130 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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. 😛 Thanks for the feedback, ol' friend!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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. 😛

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.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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. 😛 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. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 4

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!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

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.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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... 😛 heh... we've gotta eat, too! :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 7

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.

Yogi30 SSC Enthusiast Points: 158 More actions · Answer 8

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

Yogi30 SSC Enthusiast Points: 158 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

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. 😛

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.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

IntellectYog (12/3/2008)
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

Depending on how you define what your sales weeks are and whether or not your tables/indexes are in good shape (normalized and all), I'd say your report is a nearly perfect candidate for the kind of dynamic cross-tab report that we built in the article. The column names would easily be generated by the Tally table code. The only concern would be, the output width of the report. Generally speaking, even in SS 2k5, you can only put out 8,060 characters to whatever output device you have in mind.

If you need to output more width than 8,060 characters, you may have to resort to something like reporting services, analysis services, or some black art trickery to write it to a very wide file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 12

Jeff Moden (12/3/2008)

ZA_Crafty (12/3/2008)
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?

I'm not sure if it's Business Object's best practice, but my best practices for Crystal Reports are to develop stored procedures to retrieve the data in exactly the format I want/need. I use Crystal simply for formatting. However, the Cross-tab tool in it allows for incredibly flexible cross-tabs and is as easy as using Excel's Pivot table stuff.

And as for the temp file buisness... I don't remember seeing that. The last time I saw that nightmare was when I was using CR 6 or 7 and somebody had used what it called Data Dictionaries back then to do a whole lot of weird stuff with a query I wrote... I had to spend quite a lot of time cleaning that up every time somebody ran the report. I finally asked the report writer "just what the hell are you doing?" Turns out he needed several values I had put into a single column for simple subtotalling to be broken out into multiple columns... I was like "why didn't you say so?"... rewrote the query and gave it back to him.. and told him to never ever use data dictionaries (what a dumb name for what it was) again.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

mtassin (12/4/2008)
However, the Cross-tab tool in it allows for incredibly flexible cross-tabs and is as easy as using Excel's Pivot table stuff.

Does that mean you're using the cross-tab tool in Crystal Reports?? And, if not, why not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 14

Jeff Moden (12/4/2008)
mtassin (12/4/2008)
However, the Cross-tab tool in it allows for incredibly flexible cross-tabs and is as easy as using Excel's Pivot table stuff.
Does that mean you're using the cross-tab tool in Crystal Reports?? And, if not, why not?

Always.. When I need to make a report... I'm at the point now where I try to figure out how to mangle it into a cross-tab... almost every time the requested data and the requested format lends itself to a cross-tab/pivot table/etc.

Since were I am now we don't have the huge BO infrastructure of my previous employer, if I can't setup a scheduled report from my own small time CR Server, I'll fall back on things like Excel's pivot table, or access for Cross-tabs... but CR is still my cross tab of choice.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

mtassin (12/4/2008)
I'm not sure if it's Business Object's best practice, but my best practices for Crystal Reports are to develop stored procedures to retrieve the data in exactly the format I want/need. I use Crystal simply for formatting.

Good lord, yes. Never let Crystal process data, it's awful at that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]