Prompt Macro inside own MDX

[goose]

Hi All

Anybody know if its possible to have a prompt macro in your own MDX in RS? I couldnt find anything in the docs and cannot get it working...?

Thanks

[platinne]

Hello Angus,

Here is an example for a data item in a RS query based on a PowerCube:

lastperiods(8,#'[MyPowerCube].[Time].[Time].[YearWeek]->:[PC].[@MEMBER].['+prompt('YearWeek','token')+']'#)

This also works for manyprompt but you should then use additional string functions to cope with the list of values and your MUNs.

I hope this helps you.

Kind regards,

Patrice

[teggno]

Hi Angus

Did you ever find a solution on this? I think the answer from Patrice doesn't do it because you were asking for macros in an MDX statement that you entered yourself (not one being generated by the query's data items). Currently I'm having the same problem when trying to create a "macrotized" query to a MS Analysis Services 2005 Cube. I think it's necessary to write my own query because the one generated by cognos, although returning the same results as mine, is 38 times slower then the hand written one.
I'd appreciate any information on using prompt macros in manually entered mdx.

Thanks

Christian

[goose]

Hi Christian, I never found a solution to this unfortunately. However there is an excellent article on the Cognos support site regarding improving performance of OLAP queries. The crux of this is that OLAP queries cross join members from different hierarchies.

So for example if you have a product, location & time hierarchy and you want to see sales for 2009, unless you are explicitly filtering null sales values your resultset will contain the product of product x location x time hierarchies which can easily be millions of records. Obviously you wont see this in your report because the data is getting aggregated, but it will be very slow.

There also some other interesting tips is a good read and has helped me.

http://support.cognos.com/supported/tti/public/docs/cognos_pp_reporting_writing_efficient_olap_queries.pdf?lid=cognos_pp_reporting_writing_efficient_olap_queries

If you are on 8.4 Im not sure if this is till necessary, I think Cognos is doing allot of this for you behind the scenes but I cannot confirm that.


Cheers
Angus