Grouping issue in SSRS
HI,
I have a probelm with column grouping. I have to group certain set of elements into one group and the remaining into another.
The given below is the requirement of the grouping. All the Levels in AC,SSE, SE, SA will be grouped as Freshers. The same is the case for other groups.
I am designing the report uisng a tablix in SSRS(2008)
SBU
Level
Level Grouping
X
AC
Freshers
Y
AD
SM and Above
Z
C
C TO M
K
D
SM and Above
L
M
C TO M
M
SC
C TO M
N
SSE
Freshers
X
SE
Freshers
K
SA
Freshers
L
VP
SM and Above
Plesae let me know how can we acheive this.
Please revert if there is anything i havent explianed clearly.
Thanks,
Srihari
August 23rd, 2011 8:13am
The given below is the MDX i am using :
WITH
MEMBER
[BT Percentage]
as
(
[Measures].[Billable Hours]
/
[Measures].[Available Hours]
)*100
SELECT
NON
EMPTY {
[BT Percentage],
[Measures].[Billable Hours],
[Measures].[Available Hours]
}
ON 0,
NON
EMPTY {
[Business Units].[Sbu].[Sbu].
MEMBERS*
[Business Units].[Bu].[Bu].
MEMBERS*
[Business Units].[Sub Bu].[Sub Bu].
MEMBERS*
[Level].[Level Name].[Level Name]
}
ON
1
FROM
(SELECT [Business Units].[Sbu].&[AppsOne]
ON
COLUMNS
FROM
(SELECT [Business Units].[Bu].&[AppsOne AM Offshore]
ON
COLUMNS
FROM
(SELECT [Actual Period].[Month Name].&[3]
ON
COLUMNS
FROM
[ISG_BIM_FIN_UrveTime]
)))
I
have attached the requirement . Please let me know how can we acheive this
Thanks,
Srihari
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2011 9:13am
Hi Srihari,
Thanks for your post.
According to your description, you would like to split your data to two group based on Level, I would suggest you to specify Level as column Group, then take use of custom code to transform the level field value and group on it. Please refer to the steps below:
1.
In Design view, right-click the design surface outside the border of the report and click Report Properties.
2.
Click Code.
3.
In Custom code, type the code. Errors in the code produce warnings when the report runs. The following example creates a custom function named Find that verify whether
the value is contained in the limited string Array.
Public
Function Find(ByVal fa
As String,
ByRef StrArray() As
String) As Boolean
For Each Str
As String
In StrArray
If Str = fa Then
Return True
End If
Next
Return False
End
Function
4.
Right-click the Level item in the Column Groups, select
Group Properties.
5.
Click the fx button next to Group on drop down list, type in the expression below:
=IIF(Code.Find(Fields!Level.Value,new String(){"AC","SSE","SE","SA"}),"Freshers","Sr.Managers
& Above")
6.
Right-click the Level textbox in the tablix, select
Expression, type in the expression below:
=IIF(Code.Find(Fields!Level.Value,new String(){"AC","SSE","SE","SA"}),"Freshers","Sr.Managers
& Above")
After you complete the steps above, click preview to have a try.
Thanks,
Bill Lu
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
August 29th, 2011 4:58am
Hi Zilong,
Thanks for the reply and the details steps.
I will give a try on what you have suggested. I have created a column grouping at the table level for my grouping and my issue was resolved.
Its nice to hear these new tricks from much experienced fellows like you.
Thanks you and have a nice day.
Thanks,
Srihari Thakkelapati.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2011 5:43am