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”)))

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

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

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

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).

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

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 !

Hey its really worked.

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

Thanks,

Vijaya

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.

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

—————————————————————————

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

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

Hi Munish,

In my matrix I’m selecting MAX number per month and I want to add all the MAXs as a subtotal

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

Hi Munish,

I’m using SSRS 2008, I don’t see any options to subtotals

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/

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

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

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

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

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

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

Thanks Munish….

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.

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

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

=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”))

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?

and how can i add another column for Ave.. i’m getting a hardtime.. i hope you can help me on this… thank you..

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

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

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

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

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

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

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

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

How did you get the Total Average row?

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

@MVerma,

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

Munish Bansal

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

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.

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

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

Thanks a lot.My problem is solved.

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.

Thanks a Lot, Nice article..

Hi,

How u brought that header names like Customer Type and Account Number in Matrix SSRS.

I need ur help regarding that header name. How can we give header name for row group.

Hey Bala,

You can directly type these header name there itself.

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.

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

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.

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

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?

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.

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

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.

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

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

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

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

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

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

Good job mate

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!

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

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

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

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

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

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

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!

I having a similar issue like Gary. I need to find percentage. If you have already done please let me know.

thanks

v

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

hi

thanks for good topic but i have big problem.

i make matrix report with right to left growth hirarchy.

thanks.

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.

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!!

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

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

Hi Munish,

I’m trying to modify a tablix report which has 1 row-group (casetype) and 1 column group (campaign). The cells contain the sum of gifts and a total value under the row group and column group. Each cell drills through a detail report that displays all the gifts that add up to that sum. All works fine except for the drill through report in the ‘Total’ cell . Technically, it has to display all the gifts that have the casetype and campaign as the ones that show up in the tablix report. However, it only displays gifts of the first casetype.

To design the drill through report, I have the ‘ACTION’ in the text property of the cell to go to the detail report and I pass giftcampaign and casetype as parameters. Is there a way to adjust the expression to pass multiple parameters? So when i click on the ‘Total’ it takes to a report that shows all the casetypes and not just the first one. I tried using Join but i get an error. My drill through report is set to allow multiple values for the parameters and works fine when run by itself.

Any tips/ideas is much appreciated.

Thank you

Shruti

Hi I need to create a report with regions,districts,stores and their maximum audit date. So for example when the user comes first he sees only all regions displayed and the audit date column shows the maximum audit date of all the districts within that region then when they open up the region then the list of districts show up and then the maximum audit date for all the stores show up.How can you acheive this?

Hello Munish,

I have matrix report and have two grouping level.one is row wise and another is column wise grouping has bene set to year,quarter and week wise.I need the Average of all weesks at querter level.But When value is coming as null then AVG function is ignoring the bull value.Suppose I Have 4 weeks and three of them have data and one of them is not.Then I need average is 40/4.But as average function is ignoring the null value its taking it as 40/3.

Please help me to tell how I can customize subtotal expression so Average can be calculated correctly.

I have tried isnothing but agai AVG function is ignoring that.

Name Workload

Hello Munish ,

I have tablix report. I want to Show group wise Total in my report. I use the expression =Sum(Field!MyColumn.Value) for this. But in report it is showing #Error instead of Sub Total.

Please Help me

SSRS 2008

Is there a way to sum a value for a multivalued parameter…example follows

@param = ‘jims cafe,dale auto body,kevin’s eatery’ If you just sum(premium) it give total of every premium not for each item in the multivalued parameter

sum(premium) for jims cafe

dale auto body

kevin’s eatery’

Hi Munish,

I have report SSRS 2008, I have parameter start date and end date, want to create sum expression to build this parameter to add the amount,

for example, if the user pick the dates in the parameter 10/1/2013 to 2/4/2014 all the depr for this dates sum up, can you please tell me what expression I can add for this , and same for before dates from 10/1/2013 can to go to previous year, how I can write that expression, can you please help,

Select AssetId, Transdate, Amountmst

From ASeetstrans

Hi, I have been looking for an answer to my report issue and found your article. While old we still work on 2005 and it have a matrix that I am trying to do what I think is something similar to what you have. My matrix has one column group which is based on month under which it have two separate columns – one for invoice number and the other for amount. The Row group is by day and when I select Subtotal I get a subtotal for the invoice numbers and the amount. I just need to hide the invoice numbers subtotal and I am struggling to find how to do this. Thanks Susan

hi munish, hope you are doing great. i have a scenario where i need to display row total in matrix in ssrs 2005. for instance i have 2 columns and four rows.for instance one such column having values like 1,2,3,4,5. i need to display the total 15 in row. i am new to matrix and how can i do this in 2005.can u pls guide me…

Hi Munish

I have grown through most of your forum and I found it very interesting and knowledgeable.

I also have one query. I am using ssrs 2012, report builder.

How to Divide row by group subtotal in ssrs:

Example below:-

Location Category col.1 %

Location A 1 1126 =1126/1249 –>90%

2 80 =80/1249 —->6%

3 43 =43/1249 —->3%

Total 1249

Location b 1 1889 Like wise

2 164

3 78

Total 2131

Hi Munish,

I have an issue where I’m not able to sum up a textbox.

For example:

val1 val2 val3(=val1/val2)

item1 100 2 50

item2 180 3 60

item3 150 0 150

item4 160 5 32

Brand 292

I need to sum up val3 column and get total as 292 at the brand level.

How to go about this?

If val2 is zero, val3 automatically takes val1 value

Hi Munish Bansal,

Super Article Buddy. I was wandering for one of the requirement where summing of amount column grouped by rows in SSRS drill down report. After 2 days of googling found your article.

“InScope” is the one i was exactly suited my requirement.

Thanks a lot buddy

Cheers.

Regards

Suresh