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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics