Issues with tree prompt

[oscarca]

Has anyone here had performance issues with tree prompts. When I am choosing "All" in the hierarchy the report runs fast, when I select one member in the hierarchy the report runs fast but when I select two members the report takes extremely long time. For exemple if I want to filter all shoes it is good, when I want to filter on nike it is good but if I want to choose both nike and adidas in the tree prompt no good... The report just runs for a really long time..

Best regards,
Oscar

[MFGF]

Has anyone here had performance issues with tree prompts. When I am choosing "All" in the hierarchy the report runs fast, when I select one member in the hierarchy the report runs fast but when I select two members the report takes extremely long time. For exemple if I want to filter all shoes it is good, when I want to filter on nike it is good but if I want to choose both nike and adidas in the tree prompt no good... The report just runs for a really long time..

Best regards,
Oscar

Hi,

Can you share how you are "filtering" the data in your report? Is this a slicer? Are you using a dimensional expression in the crosstab/chart ie <your hierarchy> -> ?Your Parameter?

Cheers!

MF.

[oscarca]

it looks like this now
[CBI General Ledger].[Profit Center].[Function] in ?pFunction?
since when I used [CBI General Ledger].[Profit Center].[Function] -> ?pFunction? I couldnt multi select.
You would recommend using slicer ? and with so creating a data item with [CBI General Ledger].[Profit Center].[Function] in ?pFunction? and dragging it into the slicer box ? how do I get multi select option then ?

[oscarca]

and also the default prompt page pops up sistead of prompt page when using slicer ..

[MFGF]

it looks like this now
[CBI General Ledger].[Profit Center].[Function] in ?pFunction?
since when I used [CBI General Ledger].[Profit Center].[Function] -> ?pFunction? I couldnt multi select.
You would recommend using slicer ? and with so creating a data item with [CBI General Ledger].[Profit Center].[Function] in ?pFunction? and dragging it into the slicer box ? how do I get multi select option then ?

Is the member or set of members you are filtering displayed in the crosstab/chart? If so, you can replace the current expression in your chart/crosstab with the relevant dimensional expression. If you need multi-select ability, then use a set() function in the expression eg set([CBI General Ledger].[Profit Center].[Function] -> ?pFunction?)

If the member or set isn't displayed in the chart/crosstab, then a slicer filter is the way to go. Use the same dimensional expression as above.

Whatever you do, don't use a detail filter. That's a relational construct and could give you incorrect results in your dimensional report.

and also the default prompt page pops up sistead of prompt page when using slicer ..

That indicates to me that the report is not resolving the expression you used in the slicer. It needs to be a dimensional expression as above, not detail filter syntax (as you posted)

Cheers!

MF.

[oscarca]

1. so I created an data item "Function" with expression:
set([CBI General Ledger].[Profit Center].[Function] ->?pFunction?)
2.I  dragged in into the slicer.
3.I clicked on the tree prompt on the report page and clicked the paramter under propteries setting it to "pFunction".
4. When I run the report the default prompt page comes up wanting me to select something from the hierachy before it takes me to the prompt. I dont want it to override the prompt..

Thanks for you help, very much appriciated.

[oscarca]

So after setting default selection it works. But still having major performance issues whenever choosing two functions. As I said before when I choose all it works great or when I select one it works good.

[oscarca]

This one works fine:
[CBI General Ledger].[Profit Center].[Function].[(All)]->:[M16].[Profit Center].[Function].[All]
but combining for example:
[CBI General Ledger].[Profit Center].[Function].[Function]->:[M16].[[Profit Center]].[Function]].&[B2B & FS]]]
and
[CBI General Ledger].[Profit Center].[Function].[Function]->:[M16].[[Profit Center]].[Function]].&[Category]]]
just makes the remport run forever

[MFGF]

1. so I created an data item "Function" with expression:
set([CBI General Ledger].[Profit Center].[Function] ->?pFunction?)
2.I  dragged in into the slicer.
3.I clicked on the tree prompt on the report page and clicked the paramter under propteries setting it to "pFunction".
4. When I run the report the default prompt page comes up wanting me to select something from the hierachy before it takes me to the prompt. I dont want it to override the prompt..

Thanks for you help, very much appriciated.

Try just adding a slicer member set object from the toolbox into the slicer, and putting the expression into the slicer member set. Also make sure that there's nothing else in the report that is referring to this parameter, eg a detail filter or a query calculation.

Where specifically in the report does the tree prompt sit? Have you put it on a prompt page? If it's on the report page, the page won't render without first prompting for the desired member or set. The way to fix this is to specify a default selection and put in the MUN of the member you want to use by default.

Cheers!

MF.

[oscarca]

So after setting default selection it works. But still having major performance issues whenever choosing two functions. As I said before when I choose all it works great or when I select one it works good.

[CognosPaul]

Can you describe the data container being used? Is profit center function, or the descendants, being displayed or are you just filtering the measures?

When you use a simple set expression in the slicer or detail filter Cognos will attempt to convert the expression into a calculated member. You could try explicitly defining it:

member(total(currentMeasure within set set(#promptmany('pFunction','mun','drag default member here')#)),'profitcenters','profitcenters',[CBI General Ledger].[Profit Center].[Function])

If you are showing members from the profit center hierarchy you need to put the set expression directly into that data item, using a slicer to filter those members will be incredibly slow.

[oscarca]

Hey Paul,

The report is showing Profit center ([CBI General Ledger].[Profit Center].[Profit Center Functional].[Profit Center Functional Level 1]) but filtering on function ([CBI General Ledger].[Profit Center].[Profit Center Attribute].[Function].[Function]
). They are in the same dimension but two different hierachies. Profit center consist of for example Albertsson, CVS, wallmart etc (different stores) while function consist of for example IT, Fincance, Export, B2C etc. I am going to try what you are suggesting, using #promptmany macro. I tried using promptmany before but didnt get the syntax right but i will give it another shot.

Thanks for the reply and I am an avid fan of your blog posts and videos. Great inspiration.

[oscarca]

I tried this:
member(total(currentMeasure within set set(#promptmany('pFunction','memberuniquename','[All]')#)),[CBI General Ledger].[Profit Center].[Function]) but didnt get it to filter but not error occurd. " 'profitcenters','profitcenters' " is that the hiearchy name or data item name ?

When I try to define the paramter for the tree prompt it only says [object object] like it doesnt sense the pFunction in the pormptmany macro.

[oscarca]

When I put the promptmany dataitem in the slicer it senses the paramter and the prompt is actually way faster now, Thank you so much Paul. But still little confused what 'profitcenters','profitcenters' or what i wrote 'func 'func' is that the data item name ?

this works:   
member(total(currentMeasure within set set(#promptmany('pFunction','memberuniquename','[All]')#)),'func','func',[CBI General Ledger].[Profit Center].[Function])

Best regards,
Oscar

[CognosPaul]

A member has a couple of intrinsic properties, caption and code. When you make a calculated member, you can define the code and caption.


member(
    expression
  , code
  , caption
  , hierarchy
)


In this case func is now both the code and caption. If you were to call roleValue('_businessKey',[data item]) you should get 'func' as the key.

It's important that it's unique, you'd either get an error or crazy results. It's also important to drag in the correct hierarchy. I've had so many cases where Cognos puts the member into the wrong hierarchy and causes really unexpected results.

[oscarca]

Thank you Paul, truely great help !