on 06-18-2010 2:00 PM
I have a characteristic - say Profit Center - in one BEx query marked as a hierarchy variable. Works well thru universe & WebI in that it (1) is easy for users to navigate the hierarchy in a prompted query filter and (2) works well for query drill. Disconnect arises in that Profit Center is a dynamic hierarchy - leaf profit center can be at any level - and users want a single dimension to display the leaf instead of query/displaying all dimensions genned for max nbr of levels (as if the characteristic was defined as "display"). Can I have the best of both worlds? Can I configure the characteristic to act as a hierarchy for prompt filters and drill down, as well as a display characteristic genning a dimension object displaying the leaf profit center? My BW co-workers and I can't find it. Anybody have a solution? ... or a better work-around than querying all dims genned in a hierarchy? thx, ck
Hey Ingo -
I want an OLAP universe that can leverage the hierarchy for query filters, grouping/aggregation, & drill down. ... yet make it easier for users to display/report the "leaf" profit center assigned to transactions/documents. ... remember profit center hierarchy is dynamic,
As filter, want to leverage the hierarchy not the flattened list of nodes.
... So we've been able do one or the other. ... simply by toggling the characteristic property between hierarchy and display in BEx.
Defined as hierarchy, Designer genns 8 dims - L01 Profit Center thru L08 Profit Center. Great for query filters, grouping/aggregation, & drill down, but takes up report real-estate and hard to read the "leaf" node with all 8 dims on the report.
Defined as display, Designer genns 1 dim - L01 Profit Center - better suited to display/report the "leaf" profit center assigned to transactions/documents.
Does that help? thx, Clay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
does this first of all work when you run the query in RSRT ?
Yes. We are able to choose ' Profit center' from hierarchy on selection screen and have flat profit center (without hierarhcy) on report output.
does it create the correct Universe (without a hierarchy) ?
Yes. It does create correct universe when we turn off hierarchy in BEx. It also creates correct universe when we turn ON hierarchy and have hierarchy node variable in BEx. In this case on WebI it shows hierarchy on Prompts screen and all nodes on WebI
Is it possible to have flat display on WebI (just leaves) and hierarchy as prompt
ok - so what is the issue here then when you get the universe and the report working properly.
on the one entry you said it doesn't work but now you say that it does work.
you mentioned you want to have a flat display - that means not hierarchy but you also said that you want the hierarchy as a prompt - are you referring to a hierarchy node variable ? if so thats whay I described previously and you need to configure this in the BW query
ingo
so you have a BEX query which has
- no hierarchy activated
- no hierarchy variable
- a hierarchy node variable in the characteristic restrictions.
You create a brand new universe and you receive the hierarchy node variable ?
and which part does not work ? it would really help if you would provide more details
ingo
Hi Ingo, this is what we did
1. In BEx Query Filter, we have Profit center. Created a hierarchy node variable on it.
2. In Bex Query Rows added Profit center. Under 'Hierarchy' tab of profit center properties, 'Activate Hierarchy Display' is unchecked.
when we run this Query in BEx, we get the results we want. ie., We see hierarchy on selection screen and normal display on report output.
If we generate an Universe and build WebI on top of this query, when we run WebI it throws "A database error occured. The database error text is:. (WIS 10901)" error.
If I check 'Activate Hierarchy Display' on properties of profit center, WebI does NOT throw above error. but Universe generates 8 dims (L08) and it is hard to read the "leaf" node with all 8 dims on the report.
bottom line, users want normal display without hierarchy on WebI report and Hierarchy on selection screen. is this possible?
thanks,
Sam
Hi,
just to make sure we have the same understanding here:
You trying to have a report which on the one hand allows the user to leverage the hierarchy
but on the other hand you want the user to be able to just display the simple list of profit centers without the hierarchies ?
as filter you want to leverage the hierarchy or the list of hierarchy nodes ?
Ingo
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.