Quickie: How to use the new macro function queryValue

A very pleasant surprise when building reports. There’s a new function called “queryValue”.

On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. There are many times where I want a way of pulling a default value from a database in a filter. For example, my report might need to let the user select a year, but last date with data is unknown. Normally we can do something like:

[Sales (query)].[Time].[Year] = #prompt('Year','integer',
'maximum([Sales (query)].[Time].[Year] for report)'
)#

WITH 
"TQ0_q_SelectedMonth1" AS 
    (
    SELECT
        "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0", 
        MAX("GO_TIME_DIM"."CURRENT_YEAR")
            OVER(
            ) AS "Max1"
    FROM
        "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM"
    )
SELECT
    "TQ0_q_SelectedMonth1"."Year0" AS "Year0"
FROM
    "TQ0_q_SelectedMonth1" 
WHERE 
    "TQ0_q_SelectedMonth1"."Year0" = "TQ0_q_SelectedMonth1"."Max1" 
GROUP BY 
    "TQ0_q_SelectedMonth1"."Year0"

It works, but I don’t like it. It’s creating a window function, and actually hits every row in that table. One way around would be to create a parameter map in the framework model. 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. You could then do:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
sq($lastYearWithData{1})
)#

Then the SQL will look like:

SELECT
    "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0"
FROM
    "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM" 
WHERE 
    "GO_TIME_DIM"."CURRENT_YEAR" = 2013 
GROUP BY 
    "GO_TIME_DIM"."CURRENT_YEAR"

This is much better but still way too clunky. It requires access to the framework model, and a new parameter map for each and every scenario that we need. What if we want the last date of data for a specific product line? Or product?

Instead, let’s take a look at queryValue()

[Fact Business].[Dates].[Year] = #prompt('Year','integer'
, queryValue('maximum([Sales (query)].[Time].[Year])')
)#

This behaves exactly like the parameter map, and the SQL is identical. Cognos is actually making a query that runs maximum([Year]), and returns that value to the macro – exactly like a parameter map would. We can add filters to the query like this:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
queryValue('maximum([Sales (query)].[Time].[Year])','[Sales (query)].[Sales].[Quantity]>0 and [Sales (query)].[Products].[Product]=''Trail Star''')
)#

Notice using a double quote to escape the single quote. Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt.

This is also especially useful in Data Sets and Data Modules, where parameter maps simply don’t exist.

Prompt parameter name as a prompt

Recently I was presented with an interesting problem. The developer made a chart report which allowed the user to define the x-axis through a radio button. In order to do this the developer dragged all the fields he needed into the query, and used the following expression to change the axis:
#sb(prompt('Category','token'))#

This means if Month was selected in the radio, the x-Axis would be [Month].

Next he set up a drill through to a list report. The user would click on one of the bars, and it would filter the detail report based on the category selected, and the bar clicked. The optional filter is:
#sb(prompt('Category','token'))# = #prompt('Value','string')#
Resolving to [Month] = ‘Jan/2010’ or maybe [Product] = ‘Glowsticks’

The target report is also designed as a standalone, with a dozen prompts on the page. And now we get to the problem. If the user drilled on Country = UK, and then selects France and Belgium, the report will return no results. This makes sense as the country filter is [Country] in (‘France’,’Belgium’) and the Category filter is [Country] = ‘UK’. The expected behaviour is slightly different. The user expects to override the category filter with whatever is selected in the regular prompts. It also has to be accomplished without JavaScript.

I’d welcome ideas on different ways to accomplish this, as the solution I’m about to present is difficult to understand and maintain.

First I changed all of the prompts on the page to match the values the categories were passing. So the filter [Country] = ?p_dest_country? was changed to [Country] = ?Country? and [Month] = ?p_Month? was changed to [Month]=?Month?

The categories filter was changed to:
#sb(prompt(‘Category’,’token’,’1′))# in (#promptmany(prompt(‘Category’,’token’),’string’,sq(prompt(‘Value’,’string’,’1′)))#)

Assuming the user drilled on Country = UK, this will resolve to:

[Country] in (#prompt(‘Country’,’string’,sq(‘UK’))#)

Since the Country parameter in the report is null, it will default to UK, ultimately resolving to:
[Country] in (‘UK’)

Since the drilled value is only in the default parameter of the macro, selecting any value from the prompt will always take precedence.

Please feel free to leave a comment if you can think of another way of handling this.

Token prompts

Token prompts are an extremely powerful macro. They allow authors to create extremely efficient code that would otherwise be difficult or impossible to make.

The following examples will all be based on the Great Outdoor Sales (cube) package.

A simple example would be a static prompt that would allow the user to see a list the top or bottom products by revenue. Without a token prompt you might be tempted to use the following expression:

case #prompt('TopOrBottom','string',sq('top'))#
when 'top' then topCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
when 'bottom' then bottomCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
end

The problem with that is that it simply doesn’t work. You could also try to make a conditional block, but that would make the report needlessly complex.

Instead you could use a token prompt:
#prompt('TopOrBottom','token','top')#Count([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])

When the macro resolves the function will be either topCount or bottomCount.