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 12»»

Reporting Services: Adding extra columns / rows to a matrix Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2008 10:52 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 2, 2011 12:11 AM
Points: 125, Visits: 77
Comments posted to this topic are about the item Reporting Services: Adding extra columns / rows to a matrix
Post #539847
Posted Thursday, July 24, 2008 6:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:43 PM
Points: 17, Visits: 574
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
Post #540054
Posted Thursday, July 24, 2008 8:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:39 AM
Points: 14, Visits: 144
When do you think you'll have the SSAS article in a draft form?
Post #540204
Posted Thursday, July 24, 2008 8:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513, 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.
Post #540209
Posted Thursday, July 24, 2008 8:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:18 PM
Points: 14, Visits: 69
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
Post #540216
Posted Thursday, July 24, 2008 8:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 2, 2011 12:11 AM
Points: 125, Visits: 77
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
Post #540221
Posted Thursday, July 24, 2008 8:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,404, Visits: 1,400
Great article ...


Post #540237
Posted Thursday, July 24, 2008 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:39 AM
Points: 14, Visits: 144
Excellent. If it looks like you'll have something ready and the "posting/publishing" is delayed, can you e-mail something in the interim. :)
Post #540264
Posted Thursday, July 24, 2008 9:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 2, 2009 8:46 AM
Points: 875, 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. If the editor could change that vote I would appreciate it.
Post #540281
Posted Thursday, July 24, 2008 10:46 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:40 PM
Points: 612, Visits: 212
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.....:)
Post #540372
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse