Report studio value hint to SQL query via prompt macro function

Scenario: When we create a new table in the DW, but there is no model created in FM, and want to do a report directly for this table, then we need to query directly with SQL in Reportstudio, in order to pursue the query speed, we can pass the conditions of the page user selection directly to SQL.

For example: above is the province corresponding key value is 1-2-3-4-5, the following is the product type corresponding key value is 1-2, the province select 1,5 type Select 1 to appear the desired data

Implementation steps:

1: Drag a SQL from the toolbox, and each SQL binds a query that receives SQL query results, such as

2: Specify the data source for SQL Reference, the data source has been created in cognosconnection, to choose

3: Write the following in SQL

SELECT * from Testorder t where t.ordertype= #prompt (' OrderType ', ' Integer ') #

and T.orderprovince in (#promptmany (' Pkey ', ' Integer ') #)

Analytical:

#prompt (' OrderType ', ' Integer ') # The first parameter is the parameter name for the value hint, the second argument is a date type

#promptmany (' Pkey ', ' Integer ') #针对值提示可多选的时候, the first parameter is the parameter name, the second argument is the date type

PS: The macro function can also be used in the Model Designer FM, friends who have fun can do their own group to study.