#COGNOS SQL PROMPT CODE#

To begin with, the structure of the prompt macro: You can, for instance, allow a user to which measure he wants to see in his graph, or create dynamic default values. Prompt macros give you an extremely fine grain of control. Using the macro functions timestampmask, you can easily select specific members, or query certain tables. Macros allow you to alter SQL/MDX before itвЂ™s send to the datasource.

When the macro resolves the function will be either topCount or bottomCount. You could also try to make a conditional block, but that would make the report needlessly complex. The problem with that is that it simply doesnвЂ™t work. Without a token prompt you might be tempted to use the following expression:Д¬ase #prompt('TopOrBottom','string',sq('top'))# The following examples will all be based on the Great Outdoor Sales (cube) package.ДЄ simple example would be a static prompt that would allow the user to see a list the top or bottom products by revenue.

#COGNOS SQL PROMPT CODE#

They allow authors to create extremely efficient code that would otherwise be difficult or impossible to make. Token prompts are an extremely powerful macro. This is also especially useful in Data Sets and Data Modules, where parameter maps simply donвЂ™t exist. Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt. Notice using a double quote to escape the single quote. We can add filters to the query like this: Cognos is actually making a query that runs maximum(), and returns that value to the macro вЂ“ exactly like a parameter map would. This behaves exactly like the parameter map, and the SQL is identical. Instead, letвЂ™s take a look at queryValue() What if we want the last date of data for a specific product line? Or product? It requires access to the framework model, and a new parameter map for each and every scenario that we need. This is much better but still way too clunky. Create a model query that has the value you want, add a data item with a static 1, and create a parameter map based on that. One way around would be to create a parameter map in the framework model. ItвЂ™s creating a window function, and actually hits every row in that table. 'TQ0_q_SelectedMonth1'.'Year0' AS 'Year0' 'GOSALESDW'.'gosalesdw'.'GO_TIME_DIM' 'GO_TIME_DIM' For example, my report might need to let the user select a year, but last date with data is unknown. There are many times where I want a way of pulling a default value from a database in a filter. On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. ThereвЂ™s a new function called вЂњqueryValueвЂќ. A very pleasant surprise when building reports.

Cognos sql prompt