Advanced Drill Down Behavior-Calculation based on diff column base on hierarchy

[anthus]

Hi all.  I'm working in Cognos 8.4.  My FM model is DRM.  I created a list report that has 2 hierarchies (Location and Product) and the rest of the columns are measures.  Some of the measures are calculations created in FM (like percentage of on hand qty to allocation).  The problem I am having is that one of the calculated measures (columns on my report) needs to be dependent on the level of hierarchy you are on.  For example, the report by default is on the Region level and the column for "percentage of on hand qty" is based "on hand qty / region allocation".  However, when you drill down on the Location hierarchy from Region to State, then the calculation needs to change to on "hand qty / state allocation".  How do I change the default behavior (or maybe it's a change to my query calculation) to look at the Location hierarchy on the report to see if it is at the Regional level and then apply a calculation but if it's at a State level (because the user drilled down on it) to use a different calculation for that column of the report? State allocations do not always add up to Regional allocations so I cannot sum the State allocations as part of my calculation.  Only the main state will have a value in the Region Allocation column in the database. Hopefully that makes sense if not, then please let me know.

Sample data is as follows:

Region                State        On Hand           Region Allocation        State Allocation
West                  NV             2667                    3700                         3000
West                  CA               100                        0                           100
West                  WA              501                         0                          500     
Summary for West                 3268                    3700                       3600                             


Thanks.....Anthus

[Arsenal]

Interesting
The calculation you mention will have to be at the report level I would imagine.
One way is a "brute" force way....where you add all the required claculations as columns into the report but display only the one required by the use of render variables and prompt macros. Check out the thread below. It's off a cube but something similar (though maybe you don't need the static choice prompt) to your requirement.
You will only need part of Paul's solution. See if it helps.

http://www.cognoise.com/community/index.php/topic,10171.0.html

[anthus]

Thanks for your reply!  I was kind thinking along that lines -- have 2 calculated columns - one for the % based on the Regional Alloaction and one for the % based on the State Allocation and then use conditional layout to display either one or the other depending on which level in the Location hierarchy the user is at.  So, by default the user opens the report and is at the Regional level and sees the column for the % based on Regional Allocation (% based on State is hidden).  Then the user clicks on a Region (say West) and the report drills down displaying all the States (as rows) that belong to that Region and displays the % based on State Allocation column (hiding the % based on Region column).  I just don't know how to tell Cognos which level (Region or State) the report is on.

[Arsenal]

hmm, I am not sure I understand but I think you're saying that initially the user will land on the region report and then he is free to click on a region and you will have drill up and down behavior set so that the user can click region and then see the states. On this click, the column must change to now show state % as opposed to region %

if so, I doubt conditional displays will be able to achieve that.  For conditional display to work, there should be a prompt and the user will need to reprompt the report so that the layout can change appropriately to display one or the other column. The reprompt can happen from the report page itself where you can reuse the variable from the prompt page (if you have one)

but if what I think you're saying is right, then it would be very hard to do and possibly involve javascripts or something. Maybe one of the experts here can assist you

[anthus]

Hi Arsenal.  Thanks for your reply.  Yes you are correct about what I'm attempting to do.  A user will run the report and already be at the Region level showing the Region %.  The user can then drill up to the Country level or down to the State level.  The state level would then show all the states for that Region that was clicked on.  There is no prompt for the level.  When the user drills up to Country level then the Regional % gets calculated correctly.  But when the user drills down to State, the % column calculation needs to change to use a different query subject because state allocations are different than regional allocations.  I have no control over the db so I have to make this work in the report.  I see what you mean, there will be no re-prompting.  If the render style is invoked by the reprompting (and not a drill down) then I think I may have a problem using render styles to achieve this.  I didn't realize that a render style wouldn't be invoked by a drill down.  I see that under Drill Behavior / Advanced tab, you can set a drill down item to have a behavior of replace expression, but that only replaces the item that is being drilled -- not another column in that list.  I need it to change another column in that list. 

What I was orgionally thinking was that if I could tell what level a user was currently after drilling then I could use a render style to hide the appropriate column.  I was looking into the ordinal() and the level() functions to key off of for level but I cannot get them to work.  If I create a new data query with ordinal (Regional level) then all I get is the value of 2 even when the user drills down to the State level (which should show as level 3).  I'm wondering if this is just impossible or if I'm using these functions improperly. 

Thanks so much for your help!

[anthus]

I had a thought while driving my almost 1 hr commute to work this morning.  If I can figure out the ordinal() or level() to display the current level of the hierarchy that the user had drilled to, then I can create a column with an IF THEN ELSE statement that calculates the correct % based on which level the user had drilled to.  Can someone please help me to get the level number to display correctly in the report.  The hierarchy is as follows:

Location
   Location
       Members
       Location (All)
       Country
       Region
       State

When I add a column to the report with a definition of ordinal([REGION]) which is the field on my report that the users click on to drill Location, all it ever displays is 2 which is the level number for Region.  When I try it with Location, I get an error. 

Thanks......Anthus

[anthus]

I was able to create a query calculation that will determine what level the user has drilled to but now I'm getting more rows in my report than I need.  It seems to be brining back every member from the Location hierarchy.  Here is the calculation I created

if (ordinal(level(currentMember ([package].[Locations].[Location]))) = 3)
then
    ([OH] / [State Allocation])

else ([OH] / [Region Allocation])

When I try to use this formula in a Set Expression I get a QE-DEF-0478 invalid coercion from "value" to "memberset" for "1" in "union(set(defaultMembe([package].[Locations].[Location)).....

Any help would be greatly appreciated.

Thanks.....Anthus

[MFGF]

When I try to use this formula in a Set Expression I get a QE-DEF-0478 invalid coercion from "value" to "memberset" for "1" in "union(set(defaultMembe([package].[Locations].[Location)).....

That is because the result of your calculation (above) is a value, not a member.  The set() function requires the component parts within to be members.  One option is to convert your value to a member using the member() function - this will then allow you to use it in a set.

Regards,

MF.

[anthus]

Thanks MF.