Skip to content

Cognos 8 BI Measure Prompt

Using Dimensionally Modeled Relational (or DMR’s) for reports in recent projects, I’ve come over a requirement for selecting a measure for display via value box prompt fo crosstabs.
It’s very simple with dimensions (let’s forget MUN’s and etc)  but not that straight-forward with measures.
I did the first thing that came to my mind, maybe you know a better solution.

Suppose we have a DMR with 2 measures (A and B).  What to do to have a measure prompt.
1 Create a value-box prompt with parameter ?measure?, no filter, no query source, just 2 static choices ‘A’ and ‘B’. Set autosubmit and default choice of ‘A’.
2 Create a new data item in a query, called ‘Selective_Measure’, with following formula:
case ?measure?
when ‘A’ then [DMR].[Measures].[A],
when ‘B’ then [DMR].[Measures].[B]
end
3 Drop this new ‘Selective_Measure’ into cross-tab, and there you go.

I don’t like this variant due to extensibility problems (adding new cases), so I’ll be thinking of smth more ‘dynamic’.
Maybe attaching measure selections to a query and trying to convert them into measures in a formula…

  • Branislav

    Hi Ykud,

    I had to do the same, and actually I come out with the same solution.

    But I was getting problems with aggregations. Let’s say you have countries on rows and months on cols. If you implement your solution on choosing measures, you won’t get correct results on all countries / all months. My measures are calculated (a/b) …

    I didn’t come out with workaround yet …

    Brano

  • http://ykud.com ykud

    Hi Branislav,

    Can you describe, why the problem happens? I can’t see a reason, since you just do measure substitution in this case.

    You can do conditional formating, hiding the unnecessary measures, based on prompt. But that’s just the slowest variant.

  • Branislav

    Hi,

    I upgradet from 8.1 to 8.2 and I got an error :(

    QE-DEF-0459 CCLException
    GEN-ERR-0015 Initially, in data source type(s) ‘pattern’, function ‘providerQuery’ is not supported in ‘OlapQueryProvider’. After decomposition, in data source type(s) ‘PC’, function ‘ces_when’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0011 Pattern ‘manyDimensions’ is not supported in ‘RelationalQueryProvider’.
    GEN-ERR-0011 Pattern ‘manyDimensions’ is not supported in ‘RelationalQueryProvider’.
    GEN-ERR-0009 Data source type(s) ‘PC’ – Function ‘ces_when’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0009 Data source type(s) ‘PC’ – Function ‘ces_when’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0011 Pattern ‘providerQuery’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0009 Data source type(s) ‘PC’ – Function ‘ces_when’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0009 Data source type(s) ‘PC’ – Function ‘ces_when’ is not supported in ‘OlapQueryProvider’.
    GEN-ERR-0011 Pattern ‘providerQuery’ is not supported in ‘OlapQueryProvider’.

    Brano

  • http://ykud.com ykud

    Hi Brano,
    Are you using a PowerCube as DataSource? Datasource tests work?
    I think you’ve got a problem with datasource identification ;)
    You shouldn’t see ‘RelationalQueryProvider’ connecting to PowerCube.