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 Thursday, July 24, 2008 12:42 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 02, 2011 12:11 AM
Points: 125, Visits: 77
Matteo (7/24/2008)
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)

...
Matteo


Matteo,

as a simplification of my approach your idea has the benefit of being easier to understand and maintain.
It has its limitations, however:
* When using groups you cannot decide to have a total on the first group only (in the example: on grp=01 but not on grp=02). Reporting Services will add a total column for both 01 and 02.
* You are limited to sums (Totals). My approach is more generic (and therefore more complex) and can be used for any arithmetic operation (average, multiply sales figures by price, ...).

Therefore it depends on the actual situation which way is the better way to go.

Regards
Martin
Post #540478
Posted Monday, April 13, 2009 4:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 11:59 AM
Points: 1, Visits: 41
Hello!

Great article. Really helpfull...
Do you know is there is any new feature on SQL Server 2008 that would add this functionality?
Any way, If you would be so kind to share the solution with SSAS.

Please, send me a briefing about it, if you would not write an article!
sergioblum@hotmail.com

Thank you very very much!
Post #696209
Posted Saturday, April 18, 2009 3:36 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 02, 2011 12:11 AM
Points: 125, Visits: 77
Hi Sergio,

I published the SSAS-article on SQLServerCentral a while ago. You should find it here: http://www.sqlservercentral.com/articles/Linked+Server/63867/

Concerning SQL Server 2008:
The Reporting Services 2008 has a new control called "tablix" ("table" and "matrix") which should behave much better than the matrix. However, I have not tried the example of my article within SQL Server 2008 yet.

Kind regards,
Martin
Post #700105
Posted Monday, July 27, 2009 1:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 05, 2013 8:43 AM
Points: 53, Visits: 1,207
Great article !

But how I would handle if I need to calculate subtotals for the noncalulated fields? For example if you would need to drill down to Customer Type for every month? Then if I add a subtotal, calculated fields will get duplicated.


Post #760317
Posted Monday, January 10, 2011 5:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 17, 2011 4:47 PM
Points: 4, Visits: 43
Hi Martin,

Thanks for your input. this is really helpful. I am stuck with an issue and unable to find any information. Hope you can provide me with some suggestions.

My issues is
Data Source :SSAS
Report Type :Matrix (Drill Down)

I have a report
Year :2010
Jan Feb March April Avg(last2 months)
Account
Sub Acc1 1 2 1 2 (2-1)=1
Sub Acc2 2 1 1 1 0
Account2
Sub Acc2 3 3 1 1 0
Sub Acc 1 4 4 1 3

I am able to create the report create totals , sub totals etc, my issue is the population or calculation of the last column “Avg” last column depends on the parameter (EndMonth), it is always EndDatevalue –EndDate-1.value.

Is there a way that I can get to that column
I tried to first display the last 2 columns but could not get to that. I tried
=IIF((Format(CDate(Fields!MonthFirst.Value), "yyyyMM"))=Parameters!ToDimDateYearMonth.Value,0,IIF(IsNothing((Fields!Orders.Value)), 0,(Fields!Orders.Value)))
Is there a way to get the last column.
Any suggestions are highly appreciated.

Thanks
Devi
Post #1045641
Posted Friday, June 24, 2011 4:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
not tried it yet, but reading through it seems very interesting and useful.

I'd be interested to know about the SSAS article when its done.,


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1131059
Posted Friday, June 24, 2011 10:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 20,475, Visits: 14,123
Interesting and helpful. Thanks



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 #1131321
Posted Saturday, June 25, 2011 5:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 02, 2011 12:11 AM
Points: 125, Visits: 77
Hi dave-dj,

the SSAS article has been published under http://www.sqlservercentral.com/articles/Linked+Server/63867/

Take care
Martin
Post #1131612
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse