Im having a situation where i have to conditionally change the where clause of the Pass Through SQL in the Frame Work manager.
I have a Static Value Prompt set on the prompt page of my report and based on that i have write a case where condition in the SQL using prompts and its throwing an systax error...
what i want to know is can i use a prompt macro on the Left side of my equation for selection..
case when (#sq(prompt('p_date','string','Date_1'))# = 'Date_1')
then (A.DATE_1 between #sq(prompt('StartDate', 'date','2007-01-01'))# AND #sq(prompt('EndDate', 'date', timestampMask($current_timestamp,'yyyy-mm-dd')))#)
case when (#sq(prompt('p_date','string','Date_1'))# = 'Date_2')
then (A.Date_2 between #sq(prompt('StartDate', 'date','2007-01-01'))# AND #sq(prompt('EndDate', 'date', timestampMask($current_timestamp,'yyyy-mm-dd')))#)
Pinned topic Date Prompt Macro Function - Cognos 8.4
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2010-01-22T00:13:52Z at 2010-01-22T00:13:52Z by SystemAdmin
SystemAdmin 110000D4XK15496 Posts
Re: Date Prompt Macro Function - Cognos 8.42010-01-22T00:13:52ZThis is the accepted answer. This is the accepted answer.You can use a prompt macro to insert values or tokens anywhere you wish in your custom SQL statement.
However, you may wish to reconsider your current approach. Pass Through SQL is useful in some scenarios but it comes with some serious drawbacks.
1. Joining the Pass Through SQL query subject to anything else will require local processing of the data.
2. Performing calculations and filters in the studios on top of Pass Through SQL will often require local processing of the data
3. The Pass Through query subject will likely only be useful in a small subset of reporting scenarios. If you choose this as your overall design approach in FM then you will likely end up developing individual queries in your model to satisfy individual reports. This is not a solution that will be easy to maintain or develop/change in any timely fashion.
I would recommend that you revisit the modelling recommendations supplied in the FM user guide and see if your reporting requirements can be satisfied using the standard modelling approaches rather than using Pass Through SQL.