Not too many releases ago, when relative date functionality as added to Cognos Analytics, the macro function queryValue was introduced. Macro expressions are evaluated at query planning time.
Here is an example using an FM model for the GOSALES database.
I added the following filter to the [gosales_8_2].[PRODUCT_TYPE] query subject.
[gosales_8_2].[PRODUCT_TYPE].[PRODUCT_LINE_CODE] =
# queryValue ('[gosales_8_2].[PRODUCT_LINE].[PRODUCT_LINE_CODE]' ,
'[gosales_8_2].[PRODUCT_LINE].[PRODUCT_LINE_EN] like ''Pers%''' )#
During planning the macro expression is evaluated. It runs a query that returns the PRODUCT_LINE_CODE of the product line that matches the 'Pers%' pattern.
(Please note the double single quotes around the Pers% pattern.)
When you now look at the generated SQL for the [PRODUCT_TYPE] query subject you will see a literal value as in:
WITH
PRODUCT_TYPE0 AS
(
SELECT
PRODUCT_TYPE.PRODUCT_TYPE_CODE AS PRODUCT_TYPE_CODE,
PRODUCT_TYPE.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE,
PRODUCT_TYPE.PRODUCT_TYPE_EN AS PRODUCT_TYPE_EN
FROM
gosales_8_2.gosales_8_2.gosl.PRODUCT_TYPE PRODUCT_TYPE
WHERE
PRODUCT_TYPE.PRODUCT_LINE_CODE = 3
)
SELECT
PRODUCT_TYPE0.PRODUCT_TYPE_CODE AS PRODUCT_TYPE_CODE,
PRODUCT_TYPE0.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE,
PRODUCT_TYPE0.PRODUCT_TYPE_EN AS PRODUCT_TYPE_EN
FROM
PRODUCT_TYPE0 FETCH FIRST 25 ROWS ONLY
You can also use this macro function in Data Modules and Reports.
// Henk
------------------------------
Kind regards,
HENK CAZEMIER
------------------------------
Original Message:
Sent: Thu August 12, 2021 04:54 PM
From: Elihu El
Subject: What can be passed to SQL queries?
Good Afternoon Richard,
I hope that you and yours are all doing well. Within Framework Manager, I recommend that you create a filter within your Business model using a Session Parameter as the filtering value.
Within filter's expression definition, reference the session variable like this - #sp($account.parameters.whatever)#
Example:
[Data].[Table].[Whatever] = #sp($account.parameters.whatever)#
If you need any further help, support should be able to assist from there.
Warm Regards,
Eli
------------------------------
Elihu El
Original Message:
Sent: Thu August 12, 2021 02:11 PM
From: Richard McAteer
Subject: What can be passed to SQL queries?
Hi there,
I found that I need to build some of my queries in SQL and use prompting to pull the data; I've had a lot of luck with using #prompt('PROMPTNAME')# in my queries, which is great.
In this instance, I want to use a query that I've built that would normally run using a prompt like this, but I want to pass it a piece of data from a query as the starting point (basically, pass something like a singleton in place of the prompt). Is this doable?
Are there ways to refer to a singleton in SQL in Cognos? To a column from a query (e.g., something in (<expression for output of a query>))?
I can just rewrite the SQL, but was hoping I could just repurpose it and replace the #prompt reference with some way to refer to something already generated by output from a normal Cognos query.
------------------------------
Richard McAteer
------------------------------
#CognosAnalyticswithWatson