I need to show details in a report group using matrix.

Question

As shown in the image below, the group per initials is detailed per category. Is this possible in SSRS using matrix.

Answer 1

Hi Jess Banaga,

Yes it is possible.

I used following data,

SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 47 AS ColGroup, 
                      70.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 48 AS ColGroup, 
                      60.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 49 AS ColGroup, 
                      10.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 50 AS ColGroup, 
                      79.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 51 AS ColGroup, 
                      15.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 52 AS ColGroup, 
                      37.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 1 AS ColGroup, 
                      35.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 2 AS ColGroup, 
                      76.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 3 AS ColGroup, 
                      0.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 47 AS ColGroup, 
                      69.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 48 AS ColGroup, 
                      60.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 49 AS ColGroup, 
                      10.5 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 50 AS ColGroup, 
                      80.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 51 AS ColGroup, 
                      10.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 52 AS ColGroup, 
                      37.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 1 AS ColGroup, 
                      37.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 2 AS ColGroup, 
                      74.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 26904 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 3 AS ColGroup, 
                      0.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 47 AS ColGroup, 
                      25.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 48 AS ColGroup, 
                      50.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 49 AS ColGroup, 
                      55.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 50 AS ColGroup, 
                      0.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 51 AS ColGroup, 
                      0.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 52 AS ColGroup, 
                      100.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 1 AS ColGroup, 
                      88.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 2 AS ColGroup, 
                      85.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'AX' AS Type, 3 AS ColGroup, 
                      30.0 AS ColGroupValue, 1 AS Nfactor, 0 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 47 AS ColGroup, 
                      20.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 48 AS ColGroup, 
                      49.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 49 AS ColGroup, 
                      60.5 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 50 AS ColGroup, 
                      0.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 51 AS ColGroup, 
                      0.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 52 AS ColGroup, 
                      100.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 1 AS ColGroup, 
                      90.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 2 AS ColGroup, 
                      90.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor
UNION ALL
SELECT     'HDFN' AS Initials, 35678 AS ProjectId, 'asdfsafsd' AS ProfitCenterTeam, 'gkdflsng' AS CustomerName, 'dkghjdfag' AS ProjectName, 'RMA' AS Type, 3 AS ColGroup, 
                      50.0 AS ColGroupValue, - 1 AS Nfactor, 1 AS Dfactor

Columns NFactor and DFactor are written in SQL to reduce the complication of calculations.

Insert code in Report for calucating DEV%.

Public Function GetPercentage(ByVal partValue As Double, ByVal totalValue As Double) As Double
        If IsNothing(totalValue) Or totalValue = 0 Then
             If IsNothing(partValue) Or partValue = 0 Then
	Return 0
             Else
	Return 1
             End If
        Else
            If IsNothing(partValue) Or partValue = 0 Then
                Return 0
            Else
                Return partValue / totalValue
            End If
        End If
    End Function

First Insert Matrix -

• Changed the DetailsGroup properties "Group On" to Type
• Insert Parent Group "ProjectId" on ProjectId with Footer Row
• Insert Parent Group "Initials" on Intials with Footer Row
• Insert 3 columns to the right of project Id column, these 3 columns should fall under ProjectId group
• Edit the Col Detail Group and rename to "ColumnGroup" and Group On changed to ColGroup field

Now the matrix should be as shown in Part 1 of the picture below. Enter Expression

=Sum(Fields!ColGroupValue.Value) in cell A

=code.GetPercentage(sum(Fields!ColGroupValue.Value * Fields!Nfactor.Value), sum(Fields!ColGroupValue.Value * Fields!Dfactor.Value)) in cell B

In cell 1, insert Table. Remove the header row, change the detail group expression "Group On" to "Type". Add row below and out side the group.

Copy this table to Cell 2 also. Now the matrix will be as shown in section 2 of the picture.

The matrix with data :

Let me know if you need any more help. Good Luck.

-Srini

Answer 2



You need to add adjecent row group to achive the above.

---