Utilize _businessKey roleValue within #prompt# macro

[CogentNostra]

Hi,

I am struggling to pass a value from a prompt, which lives in a separate Prompt report on a dashboard.  The prompt affects multiple dashboard reports, and it utilizes Code selections to modify these dashboard report views.  For example, Account Manager Name will be displayed, and Account Manager Code will be used in the prompt and passed to the reports.

I need to pass these selected code(s) to a new Dimensional dashboard report.  The report needs to accept the Account Manager Code as the businesskey somehow.  The new report does not utilize Account Manager in any visual output - only used in slicers.

I have a slicer prompt that works when the report runs independently from the dashboard.  Run the report on its own, it works fine.  Run the report within the dashboard, the parameter pass does not work. 

My slicer looked like this:

Code Select Expand

#prompt('p_acct_mgr', 'memberuniquename', '[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]','','[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]')#

I tried to change the above with variants of '_businessKey', or 'businesskey' or 'businessKey' - replacing the 'memberuniquename' datatype in the prompt - but all of my efforts resulted in errors like this:

OP-ERR-0025

      The following OLAPPlanner internal error occurred: 'OPASSERT(pCalcMetaItem != NULL) in OPQueryType::CreateItemExpression() at OLAPPlannerCommonTransformations.cpp:2043'.

Research on the error tells me I need to add some kind of item in my report that utilizes the output.  But I don't want my view to have to display this member - I just need the slicer to control the output in my report.

Any ideas how I can get this to work? 

-CN

[Francis aka khayman]

If your diagnosis is correct that the error is about an item not in display but used only in filter, you might want to try this:

http://cognosm.fm3online.com/uncategorized/filter-not-in-display/

[CognosPaul]

The prompt is trying to pull a memberuniquename, but you're passing an integer to it.

You need to find a way to transform that integer into your member. The easiest way would be to simply construct the member from scratch.

Pull in an existing manager, let's say it looks like:
[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->[123]]]

The 123 would be the code of your manager. So we can embed the prompt into there like:
#'[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->[' + prompt('p_acct_mgr', 'integer') + ']]]'#

When you pass a value, like 321, it will embed it into the string creating your member:
[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->[321]]]

I notice that your prompt has a default of the Account Manager level. In general it's poor practice to put that into the slicer, as you're instructing the query engine to loop through every manager. It would be better to set the default to the all member. Since we're using a default member, we can't put the member fragments outside, otherwise we'd get something like:

[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->[[SBP - Commercial Cube].[Account Managers].[Account Managers].[All Level]:->[All]]]]]]

Which is just crazy. Instead, put the member fragments in the pre and post text parameters:


#prompt(
    'p_acct_mgr'
  , 'integer'
  ,'[SBP - Commercial Cube].[Account Managers].[Account Managers].[All Level]:->[All]]]'
  ,'[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->['
  ,''
  ,']]]'
)#

If nothing is selected it will slice by the all, otherwise it will wrap whatever is selected in the member fragments.

[CogentNostra]

Hi Paul,

Thank you very much for the suggestions and alternative approach to my slicer prompt!

I admit, though: I am a bit of a Dimensional report novice.  I am having a difficult time parsing your ideas and wonder if you would be willing to clear a few things up for me.

#prompt(
    'p_acct_mgr'
  , 'integer'
  ,'[SBP - Commercial Cube].[Account Managers].[Account Managers].[All Level]:->[All]]]'
  ,'[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->['
  ,''
  ,']]]'
)#

I don't have an [All] member anywhere in my cube (transformer).  An example of the top-level mun of the same hierarchy is: [SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Managers]->:[PC].[@MEMBER].[ACCT_MNGR_EMPLOYEE_CD], which seems to have a slightly different name format as the :->[All] format you suggested.  Since my format is a bit different, I am not sure how I could apply the general suggestion you made, which was to incorporate the prompt directly into my mun.  Can you make a suggestion?

#'[SBP - Commercial Cube].[Account Managers].[Account Managers].[Account Manager]:->[' + prompt('p_acct_mgr', 'integer') + ']]]'#

Secondly, I've never seen the concept of ']]]' before.  Why are these extra ]'s needed? 

Finally, since the only place that this prompt appears is in a slicer, I am not sure how to incorporate the {... [' + prompt('p_acct_mgr', 'integer') + ']]]#' } logic at all.  Maybe your help on the above point will clarify it for me, but if not, would you please provide a little more context to help me understand where to put this '+ prompt...' logic?

Sorry I am so limited.  I appreciate the time you spent putting together your original suggestions - I just don't quite know what to do with them, given my reporting circumstances.

Thanks again!!!

-CN

[CognosPaul]

By saying the "All" member, I was indeed referring to the top-level mun. There are several different OLAP technologies, each with their own way of constructing muns. In my example I simply took a mun from an SSAS cube, and converted it to what I thought your mun might appear. SSAS muns end in ']]]'

Remember, macros are processed before the SQL/MDX is generated. We can use the macros to generate the mun as needed. A string in the macro will be returned as a fragment in the expression. So #'Hello'# will return as Hello in the data item, leading to a parsing error. #sq('Hello')# will return 'Hello' (a valid string), because the sq function wraps the contents with single quotes.

In this case we're using the prompt function, which has the following parameters:
1. param name
2. data type
3. default value
4. pre-text
5. source for generated prompt
6. post-text

The pre-text is appended before any selected value, and the post after. If no value is selected, then it will use the default value.