How to use prompt macros in the custom sql of congos

[xyz]

Hi Gurus,

I am using Cognos 10.2.1 and my data source is relational, I have a requirement to write custom sql, where one of the query shows me prior days ago data based on the selection of the parameter.

Without using prompt macro, I had a detail filter like below:

[Date Field] <= _add_days(current_date, ?p_Prir_Dys_Ago?)

where -30 is the default value. How will I achieve the above requirement using prompt macros in custom sql.
Can some one suggest?

Your help is much appreciated.

Thanks & Regards,
XYZ

[Lynn]

Hi Gurus,

I am using Cognos 10.2.1 and my data source is relational, I have a requirement to write custom sql, where one of the query shows me prior days ago data based on the selection of the parameter.

Without using prompt macro, I had a detail filter like below:

[Date Field] <= _add_days(current_date, ?p_Prir_Dys_Ago?)

where -30 is the default value. How will I achieve the above requirement using prompt macros in custom sql.
Can some one suggest?

Your help is much appreciated.

Thanks & Regards,
XYZ

Custom SQL tends to obliterate the point of Cognos, but I recognize there are companies out there who seem happy to purchase a top-of-the-line tool and then use it poorly. Somewhat like buying a fancy power saw and then dragging it back and forth across a piece of lumber without bothering to plug it into a power source. Oh well. </rant>

What did you try with regard to your prompt macro? Which part of the syntax are you struggling with?

[Michael75]

Custom SQL tends to obliterate the point of Cognos, but I recognize there are companies out there who seem happy to purchase a top-of-the-line tool and then use it poorly. Somewhat like buying a fancy power saw and then dragging it back and forth across a piece of lumber without bothering to plug it into a power source. Oh well. </rant>

@ Lynn Your rant had me creased up with laughter  ;D

@ xyz Pls don't take the above remark badly, even if Lynn is so, so right in what she says . . .

I've dug out a couple of examples from our portal where we do just this:

#1

Code Select Expand

select * from table where #prompt('SelectedSRGroup','varchar(5)')# = t1.SRGROUP

#2

Code Select Expand

select * from table WHERE OBJ.PRODUCT =#prompt('ParameterProduct')#

Not date selections, admittedly, but perhaps this will get you started.

[bdbits]

I so agree with Lynn, that said...

The samples from Michael75 show what you need, namely the prompt() macro. There is some information about prompt macros in the RS documentation, and it gets coverage here in the forums. Basically the macro gets resolved with what the user provides, and the result is inserted between the ## symbols. Prompt macros are very powerful, but if this is the first time you are using them it may take a bit of experimentation to get the macro syntax right. Specific syntax to work in a SQL query with what falls outside the prompt macro will depend on the database and your package design, and how you want the user to satisfy the prompt.

[xyz]

Thanks Lynn, Michael75 and bdbits,

My requirement was, when ever user select Prior Days Ago values prompt, query has to filtered on the date column with those many days back data. I am using the below filter in my report studio detail filter.

[Date Field] <= _add_days(current_date, ?p_Prir_Dys_Ago?)

I am using SQL server 2008 as my database. I want to replicate the same above condition in my custom sql using a prompt macro.
When I tried the below condition in my sql query is not getting validated, that is where I required help.

[Date Field] <= _add_days(current_date, #prompt('p_Prir_Dys_Ago')#)

When I used below condition my query is getting validated, but it is not my requirement. Based on number of days I pass from my value prompt, report goes back to those many days and display data.

[Date Field] = #prompt('p_Prir_Dys_Ago')#

Can you guys, please suggest some ideas.

As an alternate solution, I took the date field in the select clause and used the detail filter of Report Studio.

Your help is much appreciated.

Thanks & Regards,
XYZ

[cognostechie]

 Lynn - You are so good at those comments. Made my Friday and I am going to use your 'rants' sometime soon as I am in a  similar environment :)

xyz - Try this:

where calendar_date <= getdate() - #prompt('Prior Days ago','int','30')#

In this case 'calendar_date' is the actual name of the date field in a SQL Server table

[xyz]

Hi Cognostechie,

Thank you very much, you help is much appreciated :)





Thanks & Regards,
XYZ