With our previous BI reporting tool (Hyperion), there was a concept called a “Grouping Column”. This allowed us to create a user-defined field that would take each record in a report and assign it particular label. In the example below we have looked at Sales Group and defined its “Group” using the following If statement:
Sales Group Grouping Column
No Sales Group
02 FC North America
03 FC North America
BW FC North America
C2 FC China
C3 FC China
CC FC North America
CZ FC North America
EA FC MEIA
EB FC Latin America
EC FC MEIA
EE FC Latin America
EF FC Latin America
EG FC MEIA
EH FC MEIA
EN FC MEIA
EO FC Latin America
ER FC Latin America
=If[Sales Group] InList ("C1";"C2";"C3";"C4";"C6";"C8";"C9";"D1";"PG") Then "FC China" ElseIf([Sales Group] InList ("M1";"M2";"PZ")) Then "FC Europe" ElseIf([Sales Group] InList ("EB";"EE";"EF";"EO";"ER";"EU")) Then "FC Latin America" ElseIf([Sales Group] InList ("DE";"EA";"EC";"EG";"EH";"EN";"ET";"EV";"EZ";"JA";"JC";"JO")) Then "FC MEIA" ElseIf([Sales Group] InList ("C5";"E7";"E8";"E9";"EX";"PB";"PC";"PH";"PJ";"PP";"PW")) Then "FC Rest of Asia" ElseIf [Sales Group]="#" Then "No Sales Group" Else "FC North America"
Question is this - is there an easier, less error-prone, way of achieving this?
Yes - at universe level. The ideal would be to define the grouping required to your universe admin and to get them to create an object in your universe that returns the group heading - if they make it a higher hierarchical object than [Sales Group] you can drill to it as well.
Otherwise, if that’s not an option - and from experience, sales areas are subject to frequent change… then the only alternative in webi is the IF statement option since Group dimensions are no longer ‘available’ - unless they’re in the full client, but I doubt it.
You’re correct on the universe approach not being doable. This is only one example of something my users want to group by and I don’t think we could define all their possibilities through the universe. Combined with the volatility of the criteria, it would probably become a nightmare.
Group dimensions sound almost like what I would like to do. Has that been obsoleted or something? I’m still pretty new to WebI development and don’t have experience prior to XI 3.1…it just surprised me that Hyperion offered such a nice graphical interface to do this and WebI reduced users to writing code…
Grouped dimensions don’t appear in webi I’m afraid. As mentioned, the way to do it at report level would have to be an if statement, but then every time the requirement changes - the statement would have to change to… Better than doing it at universe level, yes. Ideal, no.
(BOB member since 2010-04-22)
(BOB member since 2009-11-02)
(BOB member since 2009-11-02)