none
ssrs 2008 Matrix report how to caliculate row group totals from column group totals

    Question

  • Hi,

    I have a an issue in caliculating the rowgroup totals. There are 3 student who are appreaed for the exams for different subjects. i have displayed teh same in matrix table like below

     

        Exam Group1 The current Overal Pass Status is Overal Pass Status Should be
    Java (Marks) Pass Status SQL
    (Marks)
    Pass Status    
    slno Student Name            
    1            A 20       1        10       0         0 0
    2            B 10        0        20       1        0 0
        3            C 30        1       30       1        1 1
    Total Passed   2         2        4 1
    Total Failed   1        1        2 2

     

     

      

     The problem is at totals last column i.e The total overal passed students should be 1 instead it is  showing as 4 (2+2). The Tablix is doing the total caliculations like, the column group total is done on the row group totals instead of the same colun group values. Please help me out.

     

    Total Passed   2   2          4  1
    Total Failed   1   1          2  2
     

     

    Thanks in advance,

    Konatham

    • Edited by Konatham Monday, July 19, 2010 12:55 PM added text
    Monday, July 19, 2010 12:29 PM

Answers

  • If the picture I posted is correct, then you can try to add a assembly to your report. The sample code I created:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace ClassLibrary1
    {
        public class Class1
        {
            public static Dictionary<string, int> Dics = new Dictionary<string, int>();

            public static int AddGrop(string gname, int val)
            {
                if (Dics.ContainsKey(gname) == false) Dics.Add(gname, 0);
                Dics[gname] += val;
                return val;
            }

            public static int GetName(string gname)
            {
                if (Dics.ContainsKey(gname))
                {
                    return Dics[gname];
                }
                else
                {
                    return 0;
                }
            }
        }
    }

    In your report, you can simply call the fution:

    =ClassLibrary1.Class1.AddGrop(Fields!ID_Group.Value,min(Fields!IsPass.Value))

    =ClassLibrary1.Class1.GetName(Fields!ID_Group.Value)

    If you don't konw how add/use Assembly in report, I will post the detailed steps.

    Happy reporting:)

     


    Raymond Li - MSFT
    Friday, July 23, 2010 3:44 PM
    Moderator
  • Thanks Raymond for the solution, it is working perfectly. the only alteration i have made is the code you have written assebly i had placed it in the ssrs code.

    other wise if you put it in the assembly, each time when I refresh the report the totals again adding up and values are getting doubled.

    Thanks a lot Raymond for the work arround you had provided.

    I am still not happy with SSRS flexibility/ features available for nested aggegations etc, in ssrs  we should be able to do whatever we can do it in Excel Application other wise developers/sloution architects may propose other tools where they can acheive this kind of caliculations easily.

    It would be nice if some who can post this issues to  Microsoft  Development Team. 

     

    Regards,

    Konatham

     

     

    • Marked as answer by Konatham Monday, July 26, 2010 5:49 AM
    Monday, July 26, 2010 5:47 AM

All replies

  • Hi,

    I have a an issue in caliculating the rowgroup totals. There are 3 student who are appreaed for the exams for different subjects. i have displayed teh same in matrix table like below

     

        Exam Group1 The current Overal Pass Status is Overal Pass Status Should be
    Java (Marks) Pass Status SQL
    (Marks)
    Pass Status    
    slno Student Name            
    1            A 20       1        10       0         0? 0 ?
    2            B 10        0        20       1        0? 0?
        3            C 30        1       30       1        1? 1?
    Total Passed   2         2        4 1
    Total Failed   1        1        2 2

    What are the columns ‘The current Overal Pass Status is’ and ‘Overal Pass Status Should be’? Another two subtotals for the column group? If so what’s the logic behind these two columns?

     

    Thanks,

    Raymond


    Raymond Li - MSFT
    Wednesday, July 21, 2010 9:16 AM
    Moderator
  • Raymond, First of all thanks a lot for attending my issue. I am sorry for not able to make the question easy to understand.

    The SSRS report should show me  how many students passed at each subject and each examination level.

    In the Tablix, I have row group on Student ID and column group on Examination Group (ex: Unit test). and I have the subjects like Java, SQL. The data base table is

    Student
    ID
    Student
     Name
    Exam
    Group
    Subject Marks
    Scored
    IsPass
    1 A Exam Group1 Java 20 1
    2 B Exam Group1 Java 10 0
    3 C Exam Group1 Java 30 1
    1 A Exam Group1 SQL 10 0
    2 B Exam Group1 SQL 20 1
    3 C Exam Group1 SQL 30 1

    1) The 1's and 0's are the  student pass status ( 1=Pass, 0=Fail. database field name is isPass)

     2)  "The current Overal Pass Status is" = are the values I am able to get it from Tablix Report.

     "Overal Pass Status Should be" = is the result I am supposed to get or  the result i am seeking for.

    The report should show me  how many students passed at each subject and each examination level.

    if we look at the above table data 'A' and 'B' are failed at overal "Exam group 1",  and 'C' is the only person Passed at overal level.

     I) For more details :

       Let's take Student name 'A' as an example,

                     > he scored  20 marks in Java and his pass status in java is "1" (i.e passed)

                     > The same student 'A' scored "10" in SQL and his pass status is "0" (failed)

                    > so his overal pass status is  '0' (Failed) , this value i could get it by applying aggregate function  Min(isPass) in the column grouptotal, so if he fail (0) in any one subject I'll get '0'

         similarly for Student B, C the results were shown.

     II) Now if we look at the Total Passed, Total Failed rows (row group)

    > Under each subject I could get the no.of Passed and no.of failed students by applying sum and iif

            No.of Passed Students = Sum(iif(Fields!ispass=1,1,0))

           No.of Failed Students = Sum(iif(Fields!ispass=0,1,0))

     > When it comes Overal Status at 'Exam Group 1' -> I need to check  at the column group total, for  each Student this value i could get it by applying Min(isPass).  this values also comming correct, Please look at  column "The current Overal Pass Status is".

     But when it comes to Row Group Grand  total, i.e Total Students Passed and Total Students Failed for 'Exam Group 1', the values are comming as 4 and 2. but the correct values should be 1 , 2 . it means the The column grand Totals are summed up at each row  level values.

    Please guide me in getting the final result as explained .

    Many Thanks in advance,

    Konatham.

     

    • Edited by Konatham Thursday, July 22, 2010 1:09 AM spell correction
    Thursday, July 22, 2010 1:01 AM
  • Wow, thank you Konatham for your detailed explanation! Except the column ‘Overal Pass Status Should be’, but I think it’s just a Goal, right? So I don’t pay attention to it. If there is trouble to you, let me know.

     

    For the cell across ‘Total Passed’ and ‘the current Overal Pass Status is’:

    1)    If you are using SSRS 2008R2, then you can simply use the expression: =Sum(min(Fields!IsPass.Value,"Group name of ID"))

    I mean you can simply use nested aggregation in R2 (If possible, try the 2008R2, you will like it).

    2)    But since you are using SSRS 2008, this could be a challenge. For the workaround, you can try this first:

    a)      Copy the following code to the custom code area: 

    Dim Aggregation as Decimal

    Function AddValue(ByVal NewValue As Decimal) as Decimal

         Aggregation  = Aggregation  + NewValue 

         Return Aggregation 

    End Function 

    Function GetAggregation () as object  

    GetAggregation  = Aggregation 

    Aggregation  = 0 'Reset the group 

    Return  GetAggregation 

     

    End Function  

    b)      Use below expression in the red area:

    =code.AddValue(min(Fields!IsPass.Value))

     

     

    Exam Group1

    The current Overal Pass Status is

    Overal Pass Status Should be

       

    Java (Marks)

    Pass Status

    SQL
    (Marks)

    Pass Status

     

     

    slno

    Student Name

     

     

     

     

     

     

    1           

    A

    20      

    1       

    10      

    0    

      0 0?

    0 ?

    2           

    B

    10       

    0       

    20      

    1    

       0?

    0?

        3           

    C

    30       

    1      

    30      

    1    

       1?

    1?

    Total Passed

     

    2      

     

    2    

       4

    1

    Total Failed

     

    1     

     

    1    

       2

    2

     

     

    c)      Use below expression for the cell across ‘Total Passed’ and ‘the current Overal Pass Status is’:

    =code.GetAggregation()

    another article you may also want to take a look:

    http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6f10d0cf-2af3-4832-9d8c-842759a4eec2

     

    For the cell across ‘Total Failed’ and ‘the current Overal Pass Status is’:

    =CountDistinct(Fields!ID.Value)-code.GetAggregation()  (In 2008)

    =CountDistinct(Fields!ID.Value)- Sum(min(Fields!IsPass.Value,"Group name of ID"))  (In 2008R2)

     

    Any update, please let me know. I can send you a demo base on your dataset.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Thursday, July 22, 2010 5:27 AM
    Moderator
  • Thanks Raymond for your time and your assumptions are correct.

    The solution you have suggested is working fine when there is only one Exam Group. if there are multiple exam groups then values are not comming as expected.

    Kindly add the below table data in access/sqlserver data base and try to develop the report.

    ID Student
    Name
    ExamGroup Subject Marks isPass
    1 A Exam1 ENG 10 0
    2 B Exam1 ENG 20 1
    3 C Exam1 ENG 30 1
    1 A Exam1 MATH 20 1
    2 B Exam1 MATH 10 0
    3 C Exam1 MATH 30 1
    1 A Exam2 MATH 20 1
    2 B Exam2 MATH 10 0
    3 C Exam2 MATH 30 1

    The SSRS 2008 rdl (tablix) developement is done as below

    1) rowgroup is on Student ID

    2) Column Group :  a) Column Group 1 is on Subject  b) Column Group 2 is On Exam

    Then the result should look like

      col1 col2 col3 col4 col5 col6 col7
      Exam 1 Total Exam 1 Pass Status Should be Exam 2 Total Exam 2  Pass Status Should be At all exams
    level        
    Subject1 Subject2 Subject1 Subject2
    Student 1 Pass Fail Fail Fail Fail Fail Fail
    Student 2 Fail Pass Fail Fail Pass Fail Fail
    Student 3 Pass Pass Pass Pass Pass Pass Pass
    Total Pass 2 2 1
     (How to sum up the col3 pass Status to get the correct result)
    1 2 1 1
    Total Fail 1 1 2
     (How to sum up the col3 Fail Status)
    2 1 2 2

    Thanks & Regrads,

    Konatham

     

    Thursday, July 22, 2010 11:03 AM
  • Hi,

    I have checked it in Report builder 3.0 also, but I have not found any thing which we can use for totaling the column totals. Below is the excel grid I am placing for easy understand.

    The issue in the below example is at Col 5 Grand total (value 13). The SRRS is not allowing us to add up the grand total of Col5, but it is adding up the row totals which is not I am looking for.

      Col1 Col2 Col3 Col4 Col5  
        Grand Total  
    r1 a  7  2  5   7 Max of r1
    r2 b  1  4  6  6 Max of r2
    r3 8 6 11 13  
        sum of col2 sum of col3 sum of col4 sum of col5  
     

    Please suggest me the solution if we have  it in ssrs, if not how do we intimate it to Microsoft development team to add this feature in SSRS.

    Please mail me your sugestions to my email address kvn_murali@ yahoo.com

    Thanks & Regards,

    Konatham

    • Edited by Konatham Friday, July 23, 2010 6:23 AM formating
    Friday, July 23, 2010 6:20 AM
  • Let me give another try:

    I modify the code:

    Dim Aggregation as Decimal

    Function AddValue(ByVal NewValue As Decimal) as Decimal

         Aggregation  = Aggregation  + NewValue 

         Return NewValue 

    End Function 

    Function GetAggregation () as object  

    GetAggregation  = Aggregation 

    Aggregation  = 0 'Reset the group 

    Return  GetAggregation 

     

    End Function  

    Here is the datasouce:

     

    CREATE TABLE [dbo].[students](

        [ID] [int] NULL,

        [Name] [char](2) NULL,

        [_Group] [varchar](10) NULL,

        [_Subject] [varchar](10) NULL,

        [Marks] [int] NULL,

        [IsPass] [int] NULL

    ) ON [PRIMARY]

     

    ID  Name    _Group  _Subject    Marks   IsPass

    1   a   Group1  Java    20  1

    2   b   Group1  Java    10  0

    3   c   Group1  Java    30  1

    1   a   Group1  SQL 10  0

    2   b   Group1  SQL 20  1

    3   c   Group1  SQL 30  1

    1   a   Group2  SQL 20  1

    2   b   Group2  SQL 10  0

    3   c   Group2  SQL 30  1

     

    And then I got this:

     

     

    And this is the demo report:

     http://cid-75f1f331db7892bf.office.live.com/self.aspx/.Public/20100616/Report5.rdl

    Note, you can try this in SSRS 2008r2, but in 2008, you have to remove the expression =Sum(min(Fields!IsPass.Value,"matrix1_ID"))

    =CountDistinct(Fields!ID.Value)-Sum(min(Fields!IsPass.Value,"matrix1_ID"))

     

    Is that what you are looking for?

     

    Hope this helps,

    Raymond

     


    Raymond Li - MSFT
    Friday, July 23, 2010 7:30 AM
    Moderator
  • No Raymond.

    If you look at  the 

    a) "Total Passed" value   under "Group 1 -> Total-> isPass column it shows  as "4"  (Java 2+ Sql 2) , it should be "1" (this should come from Total -> isPass column)

    b) "Total Failed" value   under "Group 2 -> Total-> isPass column it shows  as "2"  (Java 1+ Sql 1)  and value is correct , but the computation logic is wrong, this should come from Total -> isPass column, otherwise value will go wrong when the isPass status and no.of students varies.

    if you add some more subjects under Group 2 then we can notice the count errors under that group 2.

    The fundamental issue is at aggrigation of each inner and outer group totals grand totals. if you open an excel document and place the below data, we can achieve the results as shown below. the same results I am not able to bring it in SSRS 2008.

    The issue in the below example is at Col 5 Grand total (value 13). The SRRS is not allowing us to add up the grand total of Col5 (subtotal i.e 7+6), instead it is adding up the row totals which is not I am looking for (i.e 8+6+11).

      Col1 Col2 Col3 Col4 Col5
    Grand Total
    row 1 a  7  2  5   7   ------> Max of row1
    row 2 b  1  4  6  6    -------> Max of row 2
    row 3 8 6 11 13

    sum of col2

    sum of col3 sum of col4 sum of col5  

     Regards,

    Konatham

    Friday, July 23, 2010 9:57 AM
  • The issue in the below example is at Col 5 Grand total (value 13). The SRRS is not allowing us to add up the grand total of Col5 (subtotal i.e 7+6), instead it is adding up the row totals which is not I am looking for (i.e 8+6+11).

      Col1 Col2 Col3 Col4 Col5  
        Grand Total
    row 1 a  7  2  5   7   ------> Max of row1
    row 2 b  1  4  6  6    -------> Max of row 2
    row 3   8 6 11 13

    sum of col2

    sum of col3 sum of col4 sum of col5  

     Regards,

    Konatham

    This is not the issue. I know you will see the value 25, but 25 != 8+6+11 in matrix. The value 25= sum(fields!isPass.value) = sum(fields!isPass.value, “current scope”) = sum(fields!isPass.value, “data set”) = 7+2+5+1+4+6 = 25

     

    The difficulty is how to do such nested aggregation in multiple groups. To be honest, I don’t know of any workaround so far, but I will think about it this weekend. If there is any update, I will let you know. If anyone has solution for this, please share with us, thank you!

     

    BTW, could you check this picture, is it correct?

     

     

    Happy weekend,

    Raymond



    Raymond Li - MSFT
    Friday, July 23, 2010 2:10 PM
    Moderator
  • If the picture I posted is correct, then you can try to add a assembly to your report. The sample code I created:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace ClassLibrary1
    {
        public class Class1
        {
            public static Dictionary<string, int> Dics = new Dictionary<string, int>();

            public static int AddGrop(string gname, int val)
            {
                if (Dics.ContainsKey(gname) == false) Dics.Add(gname, 0);
                Dics[gname] += val;
                return val;
            }

            public static int GetName(string gname)
            {
                if (Dics.ContainsKey(gname))
                {
                    return Dics[gname];
                }
                else
                {
                    return 0;
                }
            }
        }
    }

    In your report, you can simply call the fution:

    =ClassLibrary1.Class1.AddGrop(Fields!ID_Group.Value,min(Fields!IsPass.Value))

    =ClassLibrary1.Class1.GetName(Fields!ID_Group.Value)

    If you don't konw how add/use Assembly in report, I will post the detailed steps.

    Happy reporting:)

     


    Raymond Li - MSFT
    Friday, July 23, 2010 3:44 PM
    Moderator
  • Thanks Raymond for the explanation and the solution.

    The picture you have displayed is correct. let me try to add the assembly you have provided ( I'll will check how to add it to report).

    Hope this should work. Thanks once again for your effort. Happy weekend !

    Regards,

    Konatham

    Saturday, July 24, 2010 2:48 AM
  • Thanks Raymond for the solution, it is working perfectly. the only alteration i have made is the code you have written assebly i had placed it in the ssrs code.

    other wise if you put it in the assembly, each time when I refresh the report the totals again adding up and values are getting doubled.

    Thanks a lot Raymond for the work arround you had provided.

    I am still not happy with SSRS flexibility/ features available for nested aggegations etc, in ssrs  we should be able to do whatever we can do it in Excel Application other wise developers/sloution architects may propose other tools where they can acheive this kind of caliculations easily.

    It would be nice if some who can post this issues to  Microsoft  Development Team. 

     

    Regards,

    Konatham

     

     

    • Marked as answer by Konatham Monday, July 26, 2010 5:49 AM
    Monday, July 26, 2010 5:47 AM
  • I am still not happy with SSRS flexibility/ features available for nested aggegations etc, in ssrs  we should be able to do whatever we can do it in Excel Application other wise developers/sloution architects may propose other tools where they can acheive this kind of caliculations easily.

     


    Yes, I agree with you. As you can see, we are try to improving such capability. In SSRS 2008R2, you can do some simple nested aggregations now. But for this scenario - multiple groups across matrix, our improvement is not enough:) Good sample to explain such issue. I will escalate this to product team.

    In addition, thanks for your  solution, I forgot to reset the value to 0 after return. So could you post the code you are using? That would be helpful for these kinds for issue.

    - Raymond


    Raymond Li - MSFT
    Monday, July 26, 2010 6:36 AM
    Moderator
  • Hi Raymond ,

    The below VB.NET code has been placed in the SSRS Code window

    Dim ldictionary As New System.Collections.Generic.Dictionary(Of String, Integer)

         public function lAddGrop(gname as string,  val as integer) As Integer
           
                if not (ldictionary.ContainsKey(gname) ) Then ldictionary .Add(gname, 0)
       
                ldictionary.Item(gname) += val
                return val
            end function


     public Function GetDicTotal(gname as string) as Integer
           
                if (ldictionary.ContainsKey(gname)) Then
               
                    return ldictionary.Item(gname)
              
                else
              
                    return 0
                End if

           End Function

    '''---------------------

    Infact I have 4 levels of  nested groups I have, Implementing  the above logic is tricky like we need pass dynamic unique group name while calling the above function. 

    Regards,

    Konatham

     

    Tuesday, July 27, 2010 4:53 PM
  • Hi,

    In the same report,  When there is more data, i.e. having more than 1 page. lets say i have data of 2 pages and then when I move to 2nd page, the custome code variables are getting reset and  the first page row values are not considered in the 2nd page. The Totals are caliculated based on the values in that 2nd page only.

    One more  instance is of this kind is , I have written a  custome code function for displaying the Serial Number. the seriral number is reset to 1 for the first record of second page.

    The custome code for Serial  number is

    ========================

    Dim iTotal as Integer=0

    Public Function GetSlNo As Integer
            i+=1
            Return i
    End Function

    ==================

     

    This SSRS report I have called from ASP.NET page. is some thing I need to set it some where ?

    Please help me out.

     

    Regards,

    Konatham

    Tuesday, August 03, 2010 4:22 AM