Topic
1 reply Latest Post - ‏2010-01-22T00:13:52Z by SystemAdmin
gks13mbp
gks13mbp
1 Post
ACCEPTED ANSWER

Pinned topic Date Prompt Macro Function - Cognos 8.4

‏2010-01-22T00:01:14Z |
Hi All,
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..

WHERE
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')))#)
else (1=1)
end
Thanks
gk
Updated on 2010-01-22T00:13:52Z at 2010-01-22T00:13:52Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Date Prompt Macro Function - Cognos 8.4

    ‏2010-01-22T00:13:52Z  in response to gks13mbp
    You can use a prompt macro to insert values or tokens anywhere you wish in your custom SQL statement.

    http://www.ibm.com/developerworks/data/library/cognos/page232.html

    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.