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.
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]=).
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?
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?
Thank you Paul.
It works when I change the data type to string.
Thank you.