hardcode SQL for prompt page

[ajen7118]

Hi everyone,

I created a report with 6 time periods and there's a prompt page to choose one of these 6 time periods (only one at a time)in order to show the data. 

In my query explorer, i had a query linked with the SQL i wrote as below also i put the filter for these time periods('PROP', 'ADOPT', 'FINAL', 'FINALCHG', 'CY_BUDG','CY_EST')

select 
       CASE
              WHEN c.lev4='GF' AND e.lev5 like '%FUNDED%' THEN 'CAPITAL PROJ'
              WHEN c.lev4='GF' AND e.lev4='CH' THEN e.lev4name
              WHEN c.lev4='GF' AND e.lev4='CS' THEN e.lev4name
              WHEN c.lev4='GF' AND e.lev4='SS' THEN e.lev4name
...
...
...
from   bf_data a, bf_bdob_rlup_tbl b, bf_fund_cnsl_tbl c, bf_bdob d, bf_orgn_cnsl_tbl e, bf_tm_perd_cnsl_tbl f
where  f.bf_tm_perd_select_cd in ('PROP' )   --> since i hardcoded this time period in SQL, i don't know what to do to retrieve the others.

and       a.bf_bdob_cd=b.bf_bdob_chld_cd
and       c.bf_fund_cnsl_slcn_cd='01-FUND'
and       a.bf_fund_cd=c.bf_fund_cd
and       a.bf_bdob_cd=d.cd
and       e.bf_orgn_cnsl_slcn_cd='01-ORG'
and       a.bf_orgn_cd=e.bf_orgn_cd
and       a.bf_tm_perd_cd=f.bf_tm_perd_chld_cd
and       a.bf_bdob_cd<>'B_3301_CP'
and       a.data<>0
...
...

what can I do in Cognos report to include these 6 time periods so that i don't have to redesign the logic in my SQL?

thank you.

[MFGF]

Hi everyone,

I created a report with 6 time periods and there's a prompt page to choose one of these 6 time periods (only one at a time)in order to show the data. 

In my query explorer, i had a query linked with the SQL i wrote as below also i put the filter for these time periods('PROP', 'ADOPT', 'FINAL', 'FINALCHG', 'CY_BUDG','CY_EST')

select 
       CASE
              WHEN c.lev4='GF' AND e.lev5 like '%FUNDED%' THEN 'CAPITAL PROJ'
              WHEN c.lev4='GF' AND e.lev4='CH' THEN e.lev4name
              WHEN c.lev4='GF' AND e.lev4='CS' THEN e.lev4name
              WHEN c.lev4='GF' AND e.lev4='SS' THEN e.lev4name
...
...
...
from   bf_data a, bf_bdob_rlup_tbl b, bf_fund_cnsl_tbl c, bf_bdob d, bf_orgn_cnsl_tbl e, bf_tm_perd_cnsl_tbl f
where  f.bf_tm_perd_select_cd in ('PROP' )   --> since i hardcoded this time period in SQL, i don't know what to do to retrieve the others.

and       a.bf_bdob_cd=b.bf_bdob_chld_cd
and       c.bf_fund_cnsl_slcn_cd='01-FUND'
and       a.bf_fund_cd=c.bf_fund_cd
and       a.bf_bdob_cd=d.cd
and       e.bf_orgn_cnsl_slcn_cd='01-ORG'
and       a.bf_orgn_cd=e.bf_orgn_cd
and       a.bf_tm_perd_cd=f.bf_tm_perd_chld_cd
and       a.bf_bdob_cd<>'B_3301_CP'
and       a.data<>0
...
...

what can I do in Cognos report to include these 6 time periods so that i don't have to redesign the logic in my SQL?

thank you.

Hi,

There seem to be quite a few posts on here recently asking for help in reports using hard-coded SQL queries in the report. Sorry - I have to ask - why (why why why why) would you do this? Why?

Imagine you regularly posted on a car-oriented forum, and I posted a message saying I had just bought a shiny new Ferrarri, and I was having problems steering it, because when I was pushing it along the road (with one hand), and steering it with the other (using pieces of string tied to the steering wheel) the string was snagging and getting caught on the door handle. If I asked for help in how to route the string to the back of the car so it doesn't get trapped, you would (rightly) wonder why I would opt to move my new car in this way. You'd probably ask why I was pushing the car and steering using pieces of string, rather than getting into the car, switching on the engine, driving the car and steering with my hands on the steering wheel (as the designers intended).

It's the same paradigm using hand-cranked SQL in Cognos reports. Why do this when you can (and should) create a metadata framework and a reporting package to generate queries against the database?

My frank and honest advice is that you look carefully at the development choices you are making, and gain a better understanding of the benefits of using the Cognos software in the way the designers intended. You can (of course) continue to use hand-cranked SQL, but you are making life HUGELY difficult for yourself and for anyone else who has to maintain your reports later on.

Anyhow, since you are referring to having a prompt, your prompt must be setting a parameter (to one of the six values you stated) I assume? If so, replace the 'PROP' literal in your SQL with a prompt macro that references your parameter

eg

in (#sq(prompt('Your parameter','token','PROP'))#)

Oh, did I mention that I think hard-coded SQL is a bad idea? ;)

MF.

[bdbits]

I am picturing poor Beaker pushing a red Ferrari down the freeway, with Dr. Bunsen Honeydew manipulating the string. Or would it be the other way around? But definitely string, not wire, because we know what happens when the muppet gets a hold of wires.  8)

[ajen7118]

Hi,

I tried and it worked.

where tm_perd_select_cd = #prompt( 'PhasePrompt', 'VARCHAR')# --> I don't really know what this function for?

At beginning, when I chose the value from drop down menu, it wanted me to prompt a parameter.
Afterwards, seems I don't need to do it anymore.

I am pretty new for Cognos and SQL. Just trying to learn and finish my project.

Thank you for your help.

Sincerely,

[MFGF]

where tm_perd_select_cd = #prompt( 'PhasePrompt', 'VARCHAR')# --> I don't really know what this function for?

This is a prompt macro. A mecro is a way of embedding code into a query or expression in order to alter how the query or expression is formed. The hash symbols ## mark the beginning and end of the macro. Within this, you are calling a prompt() function (which is fulfilled by means of a prompt in the report). It is setting the response to the prompt into the first argument. The second argument is the data type - I suggested 'token' for this. The third argument is a default value to be used in the event that the parameter is empty.

Cheers!

MF.