Customizing ‘Subtotal’ expression by using Inscope function in Matrix Reports (SSRS)

« SELECT list ‘alias’ in the WHERE or GROUP BY clause

Using/Creating an alias for SQL Server 2005 »

Customizing ‘Subtotal’ expression by using Inscope function in Matrix Reports (SSRS)

December 23, 2008 by Munish Bansal

In SSRS Matrix reports there is an inbuilt functionality to have the Subtotal of columns/row fields depending upon the scope of the group in which subtotal is defined. If it is defined in the scope of any row group it will sum up all the values across all the column groups but taking the specified row group as a whole; as defined by the group scope. Similarily if it is defined for any column group, it will sum up all the values acorss all the row groups but taking the column group as a whole; as defined by its scope.

Generally this Subtotal functionality is used to sum the values within a specified group/sub group. And thus by default for every Data field Sum function is there which will display sum of all the values for a specific row & column group. And by the effect of which, Subtotal will also display the sum of all these sum values.

But if we have requirement to display default sum values in the Data field but we want some other aggregations like AVG in place of Subtotal. There is no direct formula to be put in the Subtotal expressions as we don’t have any option to modify the subtoal value (though we can modify the lable for it).

Inscope function: SSRS provides a function ‘InScope()’ which returns boolean value as per the status of the field in the mentioned group scope. If it is in the scope (mentioned in the Inscope function), it will return 1 (true) else 0 (false).

We can make use of this function in the DataField’s expression box, to check whether it is in scope of a row group i.e. it is a Data value (Sum value) or it is not in the row group’s scope i.e. it is the value of Subtotal. And based on that we can use SUM or AVG functions to give the desired results.

=IIF(InScope(“matrix1_AccountNumber”), Sum(Fields!CustomerID.Value, “matrix1_AccountNumber”), AVG(Fields!CustomerID.Value, “matrix1_AccountNumber”))

: 11013 , 11014 so on showing sum of customerIDs for all the customers of type ‘I’, with account no# AW00011013, AW00011014 respectively and having TerritoryID = 1. But Average is showing the average value of all these sum values. Average here is subtotal functionality of the Matrix report.

In this same way, these expressions can be used to modify other properties like color.

Using Expression : =IIF(InScope(“matrix1_AccountNumber”), Sum(Fields!CustomerID.Value, “matrix1_AccountNumber”), IIF(InScope(“matrix1_CustomerType”), AVG(Fields!CustomerID.Value, “matrix1_CustomerType”), AVG(Fields!CustomerID.Value, “matrix1_AccountNumber”)))

1234

Like this:

Be the first to like this post.

Posted in SQL Server, SSRS | 79 Comments

79 Responses

on February 2, 2009 at 6:44 am Pramod Singh

Hi Munish,

Just saw the usability of Inscope function, it is quite impressive.

Though have a query regarding the addition of values in sub-total in a group based on condition:

For e.g lets say

Have a group called Type which have have values like “First “,”Second”,”Third”,”Fouth”

Now if sub total are created it would show values of the sub-total according to each value in group.

But i have requirement where Sub-total should be based on only two e.g “First” ,”Second”

Now Subtotal(“First”)=100

Subtotal(“Second”)=200
and value of the Sub-toal should like this

X- user defined
Sub-total (“X”)=Subtotal(“First”)+ Subtotal(“Second”)
=100+200=300

Have tried many ways using conditional formatting, filters but it doesn’t seem to work.

Can you help with this.

Thanks,

Rerards,

Pramod
on February 4, 2009 at 5:37 am Munish Bansal

Hi Pramod,

As far as I understand your requirement, you need to show the subtotal as a whole at last which should not be the total values for all the groups…but instead it should be total of values for first & second group only.
See, in SSRS we have two in built functions which can give you the First & Last group and using them in the final subtotal, you can have the subtotal…but as it seems that you need sum of some of the specific groups only, then you can get the sum of those required groups from the SQL data set only. And then you can use Inscope function to check where it is to be shown & then if it is at last subtotal you can use that value in the subtotal field.

Thanks,
Munish Bansal
on March 4, 2009 at 5:31 am Vijaya

Hi munish,
I just wanted to know how we can use any user defined functions (which are already defined in our report prperties)
in our reports in SSRS 2005.

Thanks,
Vijaya
on March 4, 2009 at 6:36 am Munish Bansal

Hi Vijaya,

If I understand your question, you want to use some custom function/sub routine defined using .net code in the code section of Report Properties.
like:
FUNCTION CustomFunc(Value as INTEGER) AS INTEGER
RETURN Value * Value
END FUNCTION

You can use this function anywhere in your report by using “Code” keyword like:
Code.CustomFunc(5)
It will call your function at runtime & will give the output returned by the function.

Also, if you are defining some class & inside that class there are some function to be used, then you need to specify the name of that class under “References” tab of the Report Properties in “Classes” section. And specify a name of the object (myObj) there under InstanceName. And then use that object to call the required function as:

Code.myObj.CustomFunc(5).
on March 4, 2009 at 7:19 am Vijaya

Thanks Munish
i just did b4 few min
its wrking
but nw i m facing new problem

where my code is like

Public Function GetDA(ByVal Ba As Long) As Long
Return Ba*0.1
End Function

Public Function GetPF(ByVal Ba As Long) As Long
Return Ba*0.005
End Function

Public Function GetGS(ByVal Ba As Long,ByVal DA As Long,ByVal PF As Long) As Long
Return Ba+DA+PF
End Function

n i want to call last fun ie GetGS function in my report
i tried so many ways for that bt each time it results in error

i dnt knw wht to do with this

i tried like
=code.GetGS(Fields!Basic.Value+Fields!DA.Value+Fields!PF.Value)
n
=code.GetGS(Fields!Basic.Value+code.getda(Fields!Basic.Value)+code.getpf(Fields!Basic.Value))

but it wont wrk

Fields!DA.Value n Fields!PF.Value these are my calculated value in above first two functions n that r wrking fine
but last one is nt wrking or my way of calling would be wrong

if u have a solution on this ,
plz

Thanks,
Vijaya
on March 4, 2009 at 9:48 am Munish Bansal

Hey Vijaya,

In the function, you are calling; you are passing values by ‘+’ separated….which should be passed ‘,’ (comma) separated.
Try this:
=code.GetGS(Fields!Basic.Value, code.getda(Fields!Basic.Value), code.getpf(Fields!Basic.Value))

This will work absolutely fine !
on March 4, 2009 at 11:02 am Vijaya

Hey its really worked.
Thnx Munish for d solution,nw i get wht was my mistake in this scenario.

Thanks,
Vijaya
on April 13, 2009 at 9:06 pm Andrew

Munish, I hope this question will get to you. I’ve been struggling with this for days and would really appreciate some help. I tried following your example, but no success.

Here is what I have.
I have 3 row groups. Let’s call them group1, group2, group3. I have the “hidden” and “toggle” properties set so that group3 rolls into group2, and group2 rolls into group1.

For the data region, I want to display some text indicating what group I am at.

So, I tried the following expression,

= iif (InScope(“group3″),
“on group3″,
iif (InScope(“group2″),
“on group2″,
iif (InScope(“group1″),
“on group1″,
nothing)
)
)

But when I try this all I get is “on group1″ for the data region regardless of what level I am at.
on April 16, 2009 at 5:27 am chaitanya

Hi Munish,
How to display ‘Average’ and ‘Total’ in a column group for each static column, in a matrix report….. for example…

Sales Profit
Jan feb mar apr Average jan feb mar apr Total
12 12 12 12 12 12 12 12 12 48
12 10 8 6 9 12 10 8 6 36
—————————————————————————
24 22 20 18 21 24 22 20 18 84
—————————————————————————
on April 17, 2009 at 3:05 am Munish Bansal

Hi chaitanya,

I think you can use expressions to check the name of the column you are currently in and also make use of inscope function. This way you can specify what should be there (SUM or AVG) based upon the column name, as it is static.

Munish Bansal
on April 17, 2009 at 8:44 am chaitanya

Hi Munish,
In the above I took ‘Sales’ and ‘Profit’ as Static Column in the matrix.
Jan,Feb,Mar… is in the [Column Group]
and the Subtotal column for [Column Group] is Total.

Now if I preview my Report, the Subtotal column should display ‘Average of Sales’ for “Sales” staticColumn and
display ‘Sum of Profit’ for “Profit” staticColumn.

Is there any way to change the behaviour of the Subtotal column for [Column Group], so that we can display/calculate ‘average’/”total’ depending….
I tried in vain, and I am new to SSRS.
I am using SSRS 2005
on May 5, 2009 at 8:42 am Kabelo

Hi Munish,

In my matrix I’m selecting MAX number per month and I want to add all the MAXs as a subtotal
on May 5, 2009 at 11:29 am Munish Bansal

Hi Kabelo,

Though explicitly, we cannot use nested aggregate functions but Irrespective of the logic/functions used in the individual cells of a matrix, we can add a subtotal (right click & add subtotal). This will operate on the cell values after being calculated based upon the functions used (like MAX function).

Munish Bansal
on May 5, 2009 at 11:36 am Kabelo

Hi Munish,

I’m using SSRS 2008, I don’t see any options to subtotals
on May 5, 2009 at 12:08 pm Munish Bansal

Hey Kabelo…

You can refer this below link to get a better idea over it.

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/3836e9e1-7940-4c5b-9652-ca64a8c9046b/
on May 27, 2009 at 6:19 pm kamal

Hi Munish:

I have a question.
I am trying to get the Max Value of field group by Month
=Max(Fields!Volume.Value, “Month”)
I could able to get this value.

Now I would like to see a Max Value Corresponding date also… Can u please let me know the way to display this date.

Thanks
Kamal
on May 27, 2009 at 9:00 pm Tanner Cortes

Hello Munish:
thank you so much for this valuable expertise.

My matrix report requires that I subtotal group value and average as well.

Category JAN FEB MARCH TOTAL AVG
A 1 0 1 2 0.66
B 2 2 2 6 2

Is there a way to method to achieve this?
thank you
Tanner
on May 28, 2009 at 3:12 am Munish Bansal

Hey Kamal,

I don’t think you can get that date directly in the report field using inbuilt functions. I would suggest to write a custom function in the Code area available in the report. Pass that max value of the volumn or so to that function & get the date to display.

Munish Bansal
on May 28, 2009 at 3:17 am Munish Bansal

Hey Tanner.
Thanks for your comments.

As far as your requirement is concerned; yes, in SSRS matrix we cann’t have these two Sum & Avg together at the same level of the column groups.
So I would suggest you to get one of them in the data set itself while querying the report data. Other column you can add in the matrix explicitly.
Hope this works for you.

Munish Bansal
on May 28, 2009 at 3:21 am Kaladhar

Need help in SQL server reporting Services.
I have input parameters like

BachNumFrom, BatchNumTo, StartDate and Enddate

I have Dropdownloist which has values PaymentDate and BatchNumber

When I select PaymentDate in dropdown list I need to Enable Start Date and End date parameters and disable the BatchNumFrom and BatchNumTo parameters.

When I select BatchNumber in dropdown list I need to Enable BatchNumFrom and BatchNumTo parameters and disable the Start Date and End date parameters .
Can anybody please let me know how to do in SSRS report

Thanks in Advance.
Kaladhar
on May 28, 2009 at 3:22 am Munish Bansal

Hi Kaladhar,

You can somehow achieve it using dependent parameters. I mean you can make data set for ‘Start Date and End date’ dependent upon the value from PaymentDate. And thus until & unless some value is selected from PaymentDate, Start Date & End Date parameters would be disabled.
Similiarly you can do for other set of parameters.

This all is required if you are using Report Manager to display your reports, but if you have liberty to use Report Viewer or so, you can easily implement these kind of functionality using custom drop down lists etc.

Munish Bansal
on May 28, 2009 at 12:35 pm Kamal

Thanks Munish….
on June 3, 2009 at 11:38 am Thato

Hi Munish

I am struggling with a matrix drill through on totals. Manged to create a drill through from which I can click on any cell and are able to drill to the detail report but when I click on total it picks a random cell and retun that as a value.

I wrote my inscope like:-
=IIF(Inscope(“Matrix1_HC_CAT1″), Fields!cntStaff.Value, Sum(Fields!cntStaff.Value))

My navigation parameters are :
EndPeriod = Parameters!EndPeriod.Value
Consolidate = Fields!Consolidate.Value
HCCAT1 = Fields!HC_CAT1.Value

—Consolidate is Row and HCCAT1 is column
My detail query is:
SELECT *
From DTIHeadcountReport
WHERE (Period = @EndPeriod)
AND (Consolidate =@Consolidate OR @Consolidate IS NULL)
AND ([HC Cat1] = @HCCAT1 OR @HCCAT1 IS NULL)
ORDER BY [HC CAT1], Period

May you please assist.
on June 10, 2009 at 10:25 am Munish Bansal

Hey Thato,

I really don’t understand why you are using InScope function here. This should be used when we want some custom aggregations or so other than normal SUM.
But, when we add ‘SubTotal’ in matrix, it automatically SUMs up all the column field values and displays it in the Subtotal row. Though it shows SUM function there for all respective rows fields for a column too, but it will display only the field value not any sum there.

Thanks
on June 18, 2009 at 12:15 pm Thato

Hi Munish

Thanks for the feedback. I have managed to resolve that problem and came to another on in that same report.

The report looks like:

Perm Temp Payroll-Vac NonPayroll-Vac Total
Marketing 10 4 2 1 17
Accounting 3 2 2 3 10
Total 13 6 4 4 27

I now need to add subtotals like:

Totalheadcount = perm + temp
TotalVacancies = Payroll-Vac + NonPayroll-Vac
Grand Total = TotalHeadcount + TotalVacancies

May you please point me in the right direction

Kind Regards
on June 26, 2009 at 11:04 am Venkat

=Iif(InScope(“matrix1_ColumnGroup1″),

Iif(InScope(“matrix1_RowGroup1″) and InScope(“matrix1_RowGroup2″),

“In Cell”,

“In Subtotal of RowGroup1″),

Iif(InScope (“matrix1_RowGroup1″),

“In Subtotal of ColumnGroup1″,

“In Subtotal of entire matrix”))
on July 8, 2009 at 7:27 am macin

Hi Munish,

i have a problem on dynamic column.. on my matrix, i have a dynamic column.. i need to get the average of each row and place it on another column.. can you help me on this? thanks in advance…

(ex)

(–A,B,C,D,E,F are dynamic columns)

A B C D E F Ave
70% 71% 80% 80% 90% 91% <–how to get Ave?
on July 8, 2009 at 7:50 am macin

and how can i add another column for Ave.. i’m getting a hardtime.. i hope you can help me on this… thank you..
on July 9, 2009 at 3:39 am Munish Bansal

Hi macin,

We can only add a group column which gets added to a next level of the top group column.
So to add ‘Ave’ in your case, you can get this column along with the other column (A,B,C,D….) and then it will automatically be shown at the same level as that of A,B,C,D… columns.

Thanks,
Munish Bansal
on July 10, 2009 at 6:06 pm Joe

I have a similar problem with a report i am working on. I have a matrix report that includes running values as part of the data source (I did this because the report has pages and it uses a subreport for the matrix so that it can freeze headers properly.

Once i did that the normal runningvalue function no longer acted properly when multiple pages were displayed (the running total did not reset each page). I then modified my data source to include both a count and a running total which the report then groups on 1 column group with the 2 fields and 4 row groups. Of the 4 row groups the outermost is used to control the pages in the parent report (the same field is used for the pages in the parent). The second is used for grouping of the following 2 which are equivalent (a date and an integer calculated from that date).

This fixed the issue of the running values not resetting but now the row and column totals do not calculate properly. The intention is to have the running totals calculate for the column group using max so that the value displayed at the bottom is the last value in the column above. But when the expression is evaluated on the row totals it returns the max value in all of the columns. I was able to partially address this by using =IIf(InScope(“matrix1_ColumnGroup1″),Max(Fields!RunningValue.Value),Sum(Fields!RunningValue.Value)) but for the totals of the subtotals it should still do a max otherwise the numbers are not accurate.

What expression should i use so that if it is in the column totals (the rightmost column) then it will do a max if it is also in the subtotal or grand total of the row groups but otherwise do a sum if it is inside of the inner 3 row groups (The row totals are on the 2nd from left row group not the outermost which is hidden).

Sorry if that was a little convoluted but i have been fighting with this report for several weeks now and this is one of the few things left that i have not been able to fix.

Thanks,
Joe
on July 10, 2009 at 10:25 pm Harris

HI Munish

My question is little bit off topic.
Is it possible to make parameter tree structure in one drop down list, I can do it by dependent parameters but I want them in one drop down box. e.g.

I have time dimension with three level hierarchy i.e. date, month and year.

now i want one drop down box showing years, where user can select multiple years and for each year one or more month means after selecting one year all the nodes of that year appear for selection.

your response will be highly appreciated

Harris
on July 13, 2009 at 11:01 am Munish Bansal

Hi Harris,

I dont think it’s possible in the SSRS reports, being deployed on the report server. If you go for Report Viewer & all, there you can customize the drop down lists etc using ASP.Net or the environment you are working in.

Thanks for your comment !

-Munish Bansal
on July 22, 2009 at 9:10 am Thato

Hi Guys

Is it possible to have the values of a matrix row change based on parameter values? Where your default value would be all regions but when a user select a particular region you display all areas under that region.

I wrote something like this on the expression area of the matrix row but it’s not working:

=iif(Parameters!Region.Value = “ALL Regions”, Fields!Region.Value, Fields!Area.Value)

“ALL regions” is catered for in the query. Please help

Thato
on August 4, 2009 at 8:05 pm Frank

I am definitely having an issue with Matrix Averages.

My situation:
CG3
CG2
CG1
RG1 RG2 RG3 RG4 365 52 50 51

The number of days are dynamic, the row groups are fixed. I have been reading about this function not only here but on other sites and I cannot figure is out. Can anyone assist? I may need more detail than most. Thank you for your time.

SS(RS) Newbie
on August 6, 2009 at 1:44 pm Karen Roslund

Hi Munish
I have a problem with my subtotals too..

in the detail cell of my matrix i have to display the value that coming from the dataset so its going to be Fields!somevalue.Value but when i create the subtotal it is just giving the first(fields!somevalue)

but now if i put sum(fields!somevalue.value) in the subtotal it adds all the fields for that particular group…

The problem is the field!somevalue is the value that i want to show and it is going to be the same for that group
so i want it to look

Plan1 Plan 2 Total
service 5 10 30 10 35 45 135
instead of

Plan1 Plan 2 Total
service 15 20 30 15 35 45 160
on September 23, 2009 at 10:53 am Sarah D'Mello

Hi Munish,
I’m not a regular user of SSRS n so do not have complete knowledge of how to go about it. Could you please guide me in finding out the row totals in a similar way as you’ve found the average, because i get incorrect values results when i am doing the same
on September 23, 2009 at 3:33 pm MVerma

How did you get the Total Average row?
on September 24, 2009 at 3:29 am Munish Bansal

Hi Sarah,

It depends upon whether you are using a table or a matrix for your report.
In case you are having martix, then you can use the Subtotal feature which you can select by Right Clicking the column group. It will add up a new column at the end of the row for Row total. It will add up all the values of column groups for each row.
Now if you want to modify/customize it, you can modify the expression for the column group as mentioned in the article.

But in case you are having table for your report, then you would not get Subtotal option there. In that case you can add one more column and in the that you can put the expression for summing up (or any aggregate function) all the required column fields like:

=Fields!Col1.Value + Fields!Col2.Value…..etc

Thanks for your comments!

-Munish Bansal
on September 24, 2009 at 3:30 am Munish Bansal

@MVerma,

You can get it by right clicking the first column (Customer Type) and selecting ‘Subtotal’ option there.

Munish Bansal
on September 29, 2009 at 2:41 pm kim

Sep (group1) Oct Total
A(group2) B A B
8 11 12 11 42
12 2 1 5 20
14 2 1 0 17
15 2 18 3 38

I am using SSRS 2005 and I am new to matrix. Can you please guide me with the steps to find the total in a matrix for all 12 months for both A and B columns?

When I right click on group 1 and add sub total it give me the total column
The total displays
The first column values as 8, 12, 14…
and not the actual total
on September 29, 2009 at 7:03 pm Priya

Hi Munish,

I have similar question here with matrix subtotals.
I have two column groups and one row group, first column group is by A1,A2,A3 and second column group is by Month. A1,A2,A3 are group by Month.

I need show subtotal of only A3 at last column in report.But If I create subtotal it shows Totals of A1,A2,A3 for all months.How can I acheive Total of only A3 for all months excluding A1 and A2 totals.

Priya.
on October 1, 2009 at 8:27 am Munish Bansal

Hey Kim,

If u right click the column group1 and add ‘subtotal’ it should give you the total for all the months displayed and adding both A as well as B values.
I can also see the same in the resultset you have put.

If it is not the case, please eloborate the problem in detaills.

Munish Bansal
on October 1, 2009 at 8:41 am Munish Bansal

Hi Priya,
To handle such scenarios, you can use the expressions for displaying/hiding the sub total based upon your value of the column. Set the expression for ‘Hidden’ property of the subtotal column.

-Munish Bansal
on October 1, 2009 at 7:24 pm amitha

Thanks a lot.My problem is solved.
on October 6, 2009 at 8:57 pm Cliff

Hi Priya,
I hope you can help with a issue I have been having with SSRS 2005
I am developing a matrix report in with the following layout

US Canada Japan

Sales %Growth Sales %Growth Sales %Growth

Product A xxx xxx xxx xxx xxx xxx

Product B xxx xxx xxx xxx xxx xxx

Product C xxx xxx xxx xxx xxx xxx
______________________________________________
Total xxx xxx xxx xxx xxx xxx

For my %growth column its obvioulsly not correct to to simply sum the percentages what I really need is to apply a formula like such (Sales – Previous Year Sales) / Previous Year Sales. (PY Sales is in my dataset). I tried using the inscope function but continue to get an “Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope” message in reference to the PY Sales field.

Any thoughts or suggestions

Thanks
Cliff.
on November 10, 2009 at 11:39 am Muthu

Thanks a Lot, Nice article..
on December 17, 2009 at 9:37 am Bala

Hi,

How u brought that header names like Customer Type and Account Number in Matrix SSRS.
on December 17, 2009 at 9:43 am Bala

I need ur help regarding that header name. How can we give header name for row group.
on December 17, 2009 at 9:48 am Munish Bansal

Hey Bala,

You can directly type these header name there itself.
on December 17, 2009 at 10:04 am Bala

sorry Munish. I cant give the header names. Because I have single textbox for Column group. So I can give single header. But I have 3 groups so I need to give 3 header names. Do u have any Idea.
on December 18, 2009 at 4:16 am Munish Bansal

Hey Bala,
That you can do by putting one rectangle control into the header textbox and inside that put 3 textboxes (as many you required), adjust them as per the size of the below group size.

Hope this will resolve your problem.

Thanks,
Munish Bansal
on January 5, 2010 at 11:30 pm Jeff France

Munish – I have a matrix report requiring customized %’s. There are 2 Column Groups and 1 Row Group in my Matrix. Here is what it looks like:

Division #1 Division #2
Year Cat 1 Cat 2 Total Cat 1 Cat 2 Total Grand
2006 50 50% 25 25% 75 75% 20 20% 5 5% 25 25% 100
2007 50 50% 25 25% 75 75% 20 20% 5 5% 25 25% 100
2008 50 50% 25 25% 75 75% 20 20% 5 5% 25 25% 100

This is what I’d like:
Division #1 Division #2
Year Cat 1 Cat 2 Total Cat 1 Cat 2 Total Grand
2006 50 71% 25 83% 75 75% 20 29% 5 17% 25 25% 100
2007 50 71% 25 83% 75 75% 20 29% 5 17% 25 25% 100
2008 50 71% 25 83% 75 75% 20 29% 5 17% 25 25% 100

Notice that the Cat 1 and Cat 2 %’s desired are the Cat number divided by the sum of Cat 1′s in Divisions #1 & #2 (in the case of Cat 1 it is 50 / (50 + 20) = 71%.

Please assist if this is an SSRS Matrix capability.

Many thanks.
on January 6, 2010 at 11:48 am Munish Bansal

Hi Jeff,

I might not have understood your requirement exactly, but as far as I understand; I think you can use expressions to have the desired values in cells.

Thanks,
Munish Bansal
on January 6, 2010 at 1:31 pm Jeff France

Munish – thanks for replying. I have been trying several different combinations for several days to no avail. Any ideas on how to start? Would you be better able to assist if I put the desired results in a spreadsheet? If so, how do I get that spreadsheet to you?
on March 18, 2010 at 1:40 pm pavan

Hi Munish,
I customised the expression for grand total as avg, now for the remaining data cells I am using the navigation, can you tell me how I can deactivate the navigation for the data cells of the average, so that only the data cells can have this option instead of all the cells present in the matrix.
on March 18, 2010 at 1:45 pm Munish Bansal

Hi Pavan,

There also you can set the expression where you have set the navigation path/property. You can impose the condition saying that if it’s true then navigation (reportname) else FALSE.

=IIF(Parameters!SelectedValue.Count < 2, "SubReport", False)

Hope this will resolve your issue.

Thanks,
Munish Bansal
on March 19, 2010 at 6:41 am pavan

Hi Munish,
I tried editing the expression, it didnt work.
what i want is:
The data of grand total which is customised as avg should not have the hyperlink which is present in the remaining data cells.
on May 4, 2010 at 8:54 pm sri

Hi Munish,

I am new to sSRS and I have

customer, fin year, monthly sales.

Customer Fin year Jan Feb Mar …. Dec Total Sales
abc 2009 10 10 10 2000
2010 20 20 20 3000

Now I want to add another column YTM sales.

if current month is May, it should total Jan, feb, Mar, Apr for 2009 and 2010.
I added a new column, but i tried with expressions, but nothing works. Basically i need selected column as YTM total depending on current month.

Hope i explained the problem. please let meknow if not.

can you suggest.
thanks in advance
Sri
on May 4, 2010 at 9:02 pm Munish Bansal

Hi Sri,

As explained in the above article..u can make use of Inscope function.
If still not able to resolve, let me know the expression u r using & exact requirement.

Thanks,
Munish Bansal
on June 17, 2010 at 7:57 pm Patti

Can you help? I am trying to use your InScope technique for the firstime, but it is not working. I am attempting to test the expression by putting a 0 in the cell if is is the subtotal row for the column.

My matrix column group is

“M1_Col_Bu”

The expression in my data cell is

=iif(InScope(“M1_Col_Bu”),

iif( Parameters!Rpt1000.Value = “N”,
code.ChkZero(sum( Fields!ACTAMTCM.Value ),sum( Fields!HCAMOUNT.Value )),
code.ChkZero(sum( Fields!ACTAMTCM.Value ),sum( Fields!HCAMOUNT.Value ))/1000)

, 0)

My subtotal line does not return the value of 0.

Thank you for any help ou can provide. I don’t work with the Matrix very much. Also, I am using Visual Studio 2003.

Kind Regards, Patti
on June 27, 2010 at 1:41 pm Bunny

Hi Munish

Iam using a matrix report which has dynamic columns that grow horizantally.( depending on the report parameters) . What I need to acheive is- I need to get fixed no.of columns per page .Say 6 columns on the 1st page next 6 columns on 2nd page .. I tried to follow this link to do this but this giving fixed no.of rows as well as fixed columns.
But I don’t want fixed rows.

http://blogs.msdn.com/b/chrishays/archive/2004/07/23/horizontaltables.aspx

Please help me with this.. I need to get this done very badly.

Thanks
on August 17, 2010 at 2:13 pm Hayden

Hi Munish,

Is there a way that we can remove the hyperlink in subtotal since it does not drill down to the right total in the detail report? Or make the subtotal drill down correctly?

Many Thanks,

Hayden
on August 27, 2010 at 3:27 am Jatin

Hellllloooo Munish

Thanks for such wonderful maintaining blog.

I do have problem with matrix sub total, I have one of the two column group is Item Type which got two values 1. budget 2. invoice.

And ya first column group is Financial Year.

Now at the far right end when I create subtotal on Financial year it adds both but I just want the total of budget.

So its got 12 major months columns and every month got two columns budget and invoice.

subtotal is adding all the 24 columns but I just want total of 12 budget columns(“Where Item Type = budget”) only.

I hope it makes sense.

Thanks

Jatin
on August 27, 2010 at 3:44 am Jatin

Good job mate
on November 24, 2010 at 12:50 pm Ida

hi,

i found your solution fine!
i have difficult to calculate the subtotal of a diffrenz.
Hope you can read my table well.
I have 1 row group G1 and the subgroup is G2. In each subgroup i calculate the value diffrenz. Now i want the subtotal of this differenz but i have only wrong values. I have no idea. Please can you help me?
I use the code: subtotal = sum(differenz.value, “G2″)

Y N
G1 G2

A A1 50 100

differenz A1 (Value N – Value Y = 50)

Subtotal of differenz here the wrong value(should give me 50!)
B B2 30 50
differenz B2 20
B3 60 100
differenz B3 40
Subtotal of differenz should give 20+40!
on December 13, 2010 at 11:13 am MB

Hi Munish,

I want to customise the subtotal fields. below is described my matrix structure:

I have added Column group on Score field value in matrix and Row Group on City field value. In data cell I am showing the count of items for each city falling in a particular score.Ex:
Score 1 2 3 4
City ————————–
Mumbai 2 0 0 0
Delhi 0 1 1 0
Chennai 3 1 0 2

Now for this matrix I want to show row total at the end for each score but this total is based on some other columns value instead of the itemCount value that is shown here.

Can you please help me out for this? how can i add/show subtotals based on the columns that are not shown or included on matrix groups.

please suggest

thanks
on January 21, 2011 at 12:05 am Doug

Hi Munish,

I am having a problem trying to figure out at what level in my matrix that I am clicking on.
We have a report that has 3 row group levels.
Project
Status
Trade
Both the Status and Trade levels are hidden when the report runs and the user must click on the + sign to open each level. We have one column level called Priority (ranges from 1 to 6). What happens is each row levels show the totals for each Priority range.
This Prority column level when clicked on will link to an report and show the detail information for that cell. I pass all of the necessary parameter values (8 in all) to the report and it works ok and returns the correct information when selected at the Trade row level. If you click on a cell in the Project or Status row level the report returns only the information one trade level.
What we need to do is to be able to see what level we are at and be able to pass that infromation in a parameter to the linked report.

Thanks,

Doug
on March 14, 2011 at 7:18 pm zaid

Hi,

I have a report which in turn has a matrix with one row grouping Matrix_ItemRow1. I believe there is one coloumn group as well when we look into the GROUPS of the matrix object itself.

The problem I am facing is that the subtotal for the ItemRow gives me a table footer like row which has no cell in it except for the “Total” cell itself. And when the report renders all the totals are rendered. The issues is that one of the coloumns in the matrix is item Descirption and the subtotal footer shows some random Item description in this feild.

I would like to hide this and I think the only solution is related to InScope operator. I tried =IIF(InScope(“Matrix1_ItemRow”),TRUE,FALSE) on the TOTAL and it hides the cell which it has been applied to and not the one which I want.

I cannot even reference the cell which is Item Description so that I can try and hide it. I also tried placing a textbox in place of the empty cell and hide it but does not work.

My matrix looks something like this..

ITEM# COL A COL B COL C COL D COL E item Descp

TOTAL (And no cells in the row appear except for this one)

Please advise as to how do I refer to the Item Description field specifically and hide it all times.

Thanks,
Zaid
on March 16, 2011 at 7:05 pm Angie Rivera

I created a matrix, in the query I am using DATEPART(q, DateRecd) I get Q1, Q2, Q3, Q4, but this is base on calendar quarter year. My Quarters start like this:

Q1 = 7/1/2010 through 9/30/2010; in a Calendar year my Quarter Q1 belongs to Q3

Q2 = 10/1/2010 through 12/31/2010, in a Calendar year my Quarter Q2 belongs to Q4

Q3 = 1/1/2011 through 3/31/2011, in a Calendar year my Quarter Q1 belongs to Q1

Q4 = 4/1/2011 through 6/30/2011, in a Calendar year my Quarter Q1 belongs to Q2

How can I modify my query DATEPART(q, DateRecd) to create my custom business quarters. Please help,
Thanks,
Angie
on April 1, 2011 at 5:50 pm Gary

Hi,

I am having an issue with calculating percentages in a matrix. I have a column group (CG1) and a row group (RG1). For Example let’s say the column group is departments A, B, C and D and the row group is districts Noth, South and East. Lets say the results of the matrix look something like this:

A B C D
North 15 20 30 50
South 20 30 40 55
East 10 20 30 40

What I want to do next is add a percentage column where each percentage is based on the sum of Department A (45 = 15 + 20 + 10).

So in essense, the percentage columns for the above matrix should have the formula:

15/45 20/45 30/45 50/45
20/45 30/45 40/45 55/45
10/45 20/45 30/45 40/45

My resulting report would look like this:

A B C D
North 15 33.3% 20 44.4% 30 66.7% 50 111.1%
South 20 44.4% 30 66.7% 40 88.8% 55 122.2%
East 10 22.2% 20 44.4% 30 66.7% 40 88.8%

How do I grab the sum where the department = A throughout the entire matrix?

Regards,
Gary
on May 19, 2011 at 5:29 pm cathy

Hi,

I have a matrix report in which I need to calculate the average of datacells. The issue I face here is I have some empty cells in the data region and need to include this also while calculating the average. Could you please help me?

Thanks,
cathy
on June 22, 2011 at 8:34 pm teledebbie

This article was a huge help! I won’t go into the details of what I was trying to accomplish but this resource gave me the aha! moment I needed. Bookmarking now.

Thanks!
on July 14, 2011 at 2:03 pm viji

I having a similar issue like Gary. I need to find percentage. If you have already done please let me know.
thanks
v
on November 4, 2011 at 7:17 am Amit Kumar

Dear Munish,

Thanks for posting your question & answer ……….

It helps me too much to explore about customization of subtotal of row Group as well as column Group.

Again thanks……..

Regads
amit
on November 30, 2011 at 9:02 am ehsan khosravi

hi
thanks for good topic but i have big problem.
i make matrix report with right to left growth hirarchy.
thanks.
on January 19, 2012 at 2:33 pm Karen

Hi Munish

I have a matrix that has 2 columns group, centre and year and one row group organisation. I have added a total column group at the end of each row grouped by year. Is it possible to calulate a % of the centre/total organisation for each year.
on January 19, 2012 at 2:41 pm Karen

Centre1 Centre2 Centre3 Total
Y1 Y2 Y1 Y2 Y1 Y2 Y1 Y2
App % App % App % App % App % App %

Org 1 3 21% 4 44% 5 36% 3 33% 6 43% 2 22% 14 9

I have tried to provide an example for my previous question of what I mean, hope it makes sense!!
on February 3, 2012 at 4:10 pm Srikanth

Hi Munish,
My report is simple, i have two data sets, one for Header and one for detail section, i had to create separate section for header because the values in the header are dynamic.

in the detail section, which is a table, i have columns 1 to 12 , the 12th column is a expression which is the sum of column 1 to 11,

expression for column 11 is

=Fields!COLUMNONE.Value + Fields!COLUMNTWO.Value + Fields!COLUMNTHREE.Value + Fields!COLUMNFOUR.Value + Fields!COLUMNFIVE.Value + Fields!COLUMNSIX.Value + Fields!COLUMNSEVEN.Value + Fields!COLUMNEIGHT.Value + Fields!COLUMNNINE.Value + Fields!COLUMNTEN.Value + Fields!COLUMNELEVEN.Value,

then i have a totals for each column , for column 1 to 11 , i did a sum (that value in the column), but i have no idea as how do we calculate a sum of a column which has a calculated value in it.

Thanks in advance

Srikanth
on February 27, 2012 at 5:19 am gpshiburaj

Good Day,
I am not able to identify, the cell (row,column) in a matrix report.
If I could identify the cell
Ex.
Cell (rowgroup01,colgroup01,item no: 01) then I could say
Cell (rowgroup01,colgroup01,item no: 03) = Cell (rowgroup01,colgroup01,item no: 01)/ Cell (rowgroup01,colgroup01,item no: 02) * 100
My simplified table is
Project, Year, AccountNum, Amount, AccBit
ABC, 2010, 10xy, 1000, 10 Rev
ABC, 2011, 10xy, 1000, 10 Rev
ABC, 2012, 10xy, 250, 10 Rev
DEF, 2010, 10xy, 1000, 10 Rev
DEF, 2011, 10xy, 1000, 10 Rev
DEF, 2012, 10xy, 250, 10 Rev

ABC, 2010, 20xy, 900, 20 D.Exp
ABC, 2011, 20xy, 800, 20 D.Exp
ABC, 2012, 20xy, 200, 20 D.Exp
DEF 2010, 20xy, 900, 20 D.Exp
DEF, 2011, 20xy, 800, 20 D.Exp
DEF, 2012, 20xy, 200, 20 D.Exp

ABC, 2010, 40xy, 50, 50 I.Exp
ABC, 2011, 40xy, 60, 50 I.Exp
ABC, 2012, 40xy, 10, 50 I.Exp
DEF, 2010, 40xy, 50, 50 I.Exp
DEF, 2011, 40xy, 60, 50 I.Exp
DEF, 2012, 40xy, 10, 50 I.Exp

In my matrix report I get
|10 Rev |20 D.Exp |30 GM |40 GM% |50 I.Exp |60 EBIT |70 EBIT%
ABC
2010 |1000 |900 |100 |10% |50 |50 |5%
2011 |1000 |800 |200 |20% |60 |140 |14%
2012 | 250 |200 |50 |20% |10 |40 |16%
Total ABC |2250 |1900 |350 |50% |120 |230 |35%
DEF
2010 |1000 |900 |100 |10% |50 |50 |5%
2011 |1000 |800 |200 |20% |60 |140 |14%
2012 | 250 |200 |50 |20% |10 |40 |16%
Total DEF |2250 |1900 |350 |50% |120 |230 |35%

The Calculation for
30 GM = 10 Rev – 20 D.Exp
40 GM% = 30 GM / 10 Rev *100
60 EBIT = 30 GM – 50 I.Exp
70 EBIT% = 60 EBIT/ 10 Rev*100
The error is in
1, the calculation of the 40 GM% & 70 EBIT%
Project ABC may use account 1010 & 1020 to record the revenue but project DEF will use 1020 to record their revenue
2, the major error is in the totalling of percentages
In the example above the correct figures are
|40 GM% |70 EBIT%
Total ABC |15.5% |10.2%

Total 40 GM % = Total 30 GM/ Total 10 Rev*100
Total 70 EBIT%= Total 60 EBIT / Total 10 Rev*100

Friends I need help with the calculation of the percentages
Thanks in advance.

Best Regards
Shibu.P
Email: gpshiburaj@hotmail.com
 Please consider the environment before printing this e-mail

Comments RSS

	shimon on Extending SQL Server Reporting…
	How to get 5000 face… on rrdsc00295
	Emil on Dynamic columns mapping – Scri…
	anki on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Pranav Kapadia on How to remove duplicate record…
	diablo 3 beta on Disclaimer
	Shirley Kobie on Disclaimer
	Venice Zapata on Disclaimer
	外遇抓姦 on Disclaimer
	keerti on Dynamic columns mapping – Scri…

Tech Updates

…keep in pace with technology !