Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 80
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
Sergio Adriano Blum
Sergio Adriano Blum
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 49
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!
Martin Cremer
Martin Cremer
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 80
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
Veronka
Veronka
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 1219
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.
aponduri
aponduri
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
dave-dj
dave-dj
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 1149
not tried it yet, but reading through it seems very interesting and useful.:-D

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

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22924 Visits: 18262
Interesting and helpful. Thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Martin Cremer
Martin Cremer
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 80
Hi dave-dj,

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

Take care
Martin
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