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

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

untitled112
untitled123
 : 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

Advertisements
This entry was posted in SQL Server, SSRS. Bookmark the permalink.

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

  1. Pramod Singh says:

    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

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

  3. Vijaya says:

    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

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

  5. Vijaya says:

    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

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

  7. Vijaya says:

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

    Thanks,
    Vijaya

  8. Andrew says:

    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.

  9. chaitanya says:

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

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

  11. chaitanya says:

    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

  12. Kabelo says:

    Hi Munish,

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

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

  14. Kabelo says:

    Hi Munish,

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

  15. kamal says:

    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

  16. Tanner Cortes says:

    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

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

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

  19. Kaladhar says:

    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

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

  21. Kamal says:

    Thanks Munish….

  22. Thato says:

    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.

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

  24. Thato says:

    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

  25. Venkat says:

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

  26. macin says:

    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?

  27. macin says:

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

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

  29. Joe says:

    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

  30. Harris says:

    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

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

  32. Thato says:

    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

  33. Frank says:

    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

  34. Karen Roslund says:

    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

  35. Sarah D'Mello says:

    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

  36. MVerma says:

    How did you get the Total Average row?

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

  38. @MVerma,

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

    Munish Bansal

  39. kim says:

    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

  40. Priya says:

    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.

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

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

  43. amitha says:

    Thanks a lot.My problem is solved.

  44. Cliff says:

    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.

  45. Muthu says:

    Thanks a Lot, Nice article..

  46. Bala says:

    Hi,

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

  47. Bala says:

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

  48. Hey Bala,

    You can directly type these header name there itself.

  49. Bala says:

    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.

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

  51. Jeff France says:

    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.

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

  53. Jeff France says:

    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?

  54. pavan says:

    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.

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

  56. pavan says:

    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.

  57. sri says:

    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

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

  59. Patti says:

    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

  60. Bunny says:

    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

  61. Hayden says:

    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

  62. Jatin says:

    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

  63. Jatin says:

    Good job mate

  64. Ida says:

    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!

  65. MB says:

    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

  66. Doug says:

    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

  67. zaid says:

    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

  68. Angie Rivera says:

    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

  69. Gary says:

    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

  70. cathy says:

    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

  71. teledebbie says:

    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!

  72. viji says:

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

  73. Amit Kumar says:

    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

  74. ehsan khosravi says:

    hi
    thanks for good topic but i have big problem.
    i make matrix report with right to left growth hirarchy.
    thanks.

  75. Karen says:

    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.

  76. Karen says:

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

  77. Srikanth says:

    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

  78. gpshiburaj says:

    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

  79. shruti says:

    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

  80. Deepa says:

    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?

  81. Anuj says:

    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

  82. Nitin says:

    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

  83. Corey says:

    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’

  84. Alka mittal says:

    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

  85. Susan says:

    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

  86. saran says:

    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…

  87. KARTIK DEWAN says:

    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

  88. florence says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s