SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services: Adding extra columns / rows to a matrix


Reporting Services: Adding extra columns / rows to a matrix

Author
Message
Martin Cremer
Martin Cremer
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 80
Comments posted to this topic are about the item Reporting Services: Adding extra columns / rows to a matrix
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 597
Wow Martin, This is a great article. Totals columns, 13 week averages, % change -- all of these have been a critical part of reporting that I felt matrix tables would be well suited for if one could only add extra columns. This really pushes our reporting capabilities ahead by light years!

Mark
bteague
bteague
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 196
When do you think you'll have the SSAS article in a draft form?
rjv_rnjn
rjv_rnjn
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 430
Really a clever way. I've been using the addition of an extra table with similar grouping as the matrix one but then I always lost the ability to export and also on sorting capability (as is that is pretty limited in a matrix report). Thanks.
Matteo-317581
Matteo-317581
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 72
Another way to do this using more of the native function of the Matrix would be to create a grouping column, where one group value is tied to the Articles sold and another group value tied to the Target information.


Group 01 is the Articles sold.

Group 02 is the target.

The data set would look like:

grp Name Month #Sold
== ==== ===== ====
01 Franz 200801 10
01 Franz 200803 7
01 Franz 200804 12
02 Franz Target -20 (Trick:note negative Target value is need to make the "math" ok when sub totalling)

Then just set up totalling on your matrix report to provide sub totals per the group value.

Special Notes:
1) In order for the final total to come out correctly the Target data needs to go in as a negative number
2) You would have to have a special format option when displaying the "target" data to not show the negative
3) You would still need to use the techniques, described in the article, for proper sequencing and column headers and such on the final report.

Advantages:
- Your data set only has raw data in it, rather than mixing raw data and summary data.
- Much Simpler T-SQl statements
- Lets the matrix list do what it does best.

Regards All

Matteo
Martin Cremer
Martin Cremer
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 80
Hello bteague,

I hope to have that article written within the next week.
But I do not know how long it takes for the article to be published.

Martin
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8101 Visits: 1407
Great article ...



bteague
bteague
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 196
Excellent. If it looks like you'll have something ready and the "posting/publishing" is delayed, can you e-mail something in the interim. Smile
notquitexena
notquitexena
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1009 Visits: 313
This is a great article, and I must apologize -- this is the first time I went to rate an article, and I meant to give it 5 stars. Unfortunately my unfamiliarity with the process led to only crediting it with one star.Blush If the editor could change that vote I would appreciate it.Sad
Harit Gohel
Harit Gohel
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 232
Martin,

First of all Thank you for really good article with perfect example to show the functionality. I fully support your approch of stored functions/stored procedures to generate the result set with the calculation and just use basic functions of SSRS to display the data especially, as you wrote, while exporting report to excel.
Again it was a very good article....Keep it on.....Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search