Question: Can't use "Show Generated SQL/MDX" when having Macro Prompts?

[lookingforK]

Hi,

I am using Cognos Report Studio 8.4.1.

I want to use "Show Generated SQL/MDX" to generate a SQL query.

But this can't work when I add some Macro Prompts as filters, e.g.
* [Sales Detail Fact].[Deal Dimension].[Deal Year Code]=#prompt('prmYr', 'memberuniquename','','','[Sales Detail Fact].[Deal Dimension].[Deal Year Code]')#

Why? Do I need some kind of permission from the IT administrator?

Thank you in advance.

[CognosPaul]

What type of model is this based on? OLAP, DMR, or a DMR/Relational hybrid?

Because the default value parameter in your macro is empty, and as you're referencing what appears to be a hierarchy in the source parameter, a tree prompt should open when you attempt to view the SQL. Looking at the problem again, is it possible you're not on a dimensional source at all? If [Deal Year Code] is a numeric field, a memberuniquename prompt won't work at all. Try changing that to integer.

If it is a dimensional model, and [Deal Year Code] is the name of your hierarchy, try putting the macro expression into the slicer (just the macro expression, without the [Deal Year Code]=).

[lookingforK]

Thank you Paul.

How can I identify what type of model this is based on?

[Deal Year Code] is a Character (String) field in this case. If I give a default value (e.g. 2013) to it, I can generate the SQL query. However, in the query, all "Deal_Dimension"."DEAL_YR_CD" items related to [Deal Year Code] in WHERE clauses are equal to 2013. How can I keep the Prompt working?

[CognosPaul]

The only reason your prompt wasn't working is that you set the datatype to memberuniquename. What happens when you change the data type to string?

[lookingforK]

Thank you Paul.

It works when I change the data type to string.

Thank you.