ignoring Prompt values based on selection

[PM]

Hi Guys,

I just joined today, and looking forward to learn and knowledge sharing from here :)

My Question;

I have a 2 SQL reports  with same code, only difference is that one SQL ( in where clause) has date prompts and other with out date  prompt.
Any idea if we can develop one report for both purpose ? In other word, user can choose to execute with dates or with out dates?

Thanks, 
PM

[BigChris]

Hi PM - welcome to the forum. There's quite a few posts about the reasons why it's not such a great idea to have SQL queries, but that's not the question you asked  :D

To get what you're after, you can have just one query based on the SQL without the date prompts in the where clause. Then you can build a filter in your Cognos query that looks something like:

[SQL].[DateField] between ?pBegin? and ?pEnd?

That way when you run the report it'll prompt you for the start and end dates for your report. You could even build a prompt page for those dates so that it's a bit tidier.

Edit - forgot to mention, make the filter Optional so that you don't have to supply dates if you don't want to

[PM]

Hi BigChris,
Thanks for reply, I could not understand fully. so,
I have removed the Date filter from the SQL..

    where ro.location_id = #prompt('pm-location','numeric')#
    and  SP.SHIPMENT_DATE <= (#prompt('DATECOMBO')# and SP.SHIPMENT_DATE >= .....)

So now my where statement is something like :
    where ro.location_id = #prompt('pm-location','numeric')#

I have a date prompt page , how can I connect the prompt page values to SQL to get results?


Thanks again,

[BigChris]

Ahh...ok. I don't tend to use the prompt macros, but I know of them. From your code, it looks like you've got a prompt called pm-location. In your prompt page, can you create a prompt for location and point it at pm-location?

I don't know if you want to do this, but I would take out the prompt macro from the SQL (i.e. remove the WHERE clause altogether), and just have the prompts in the cognos query. You might get a performance hit though, it'll just depend on how your table and indexes are set up in the database. If you do it that way though it'll definitely be easy to tie up your prompt page to the prompts in your query.

[PM]

Hi,

In the code we have some conditions in where clause ( using different prompts values.) which is fine.
My question is still there that if I would like to skip Date prompt (one of the prompts condition in where clause ) can we do that ? as in Users may choose date to select or skip.

In the case if user skips the date prompts the SQL code should be executed with out Date filter.

Thanks,

[cognos810]

Hello PM,
Your prompt macro needs more arguments to achieve this. Have shown you a simple example below.

where ro.location_id = #prompt('pm-location','numeric')#
#prompt('DATECOMBO','date',' ','and SP.SHIPMENT_DATE<=''','','''')#

In essence, if the User supplies a value for DATECOMBO your where clause becomes as such...
where ro.location_id = #prompt('pm-location','numeric')#
and  SP.SHIPMENT_DATE <= '2015-05-15' //If the user selected May 15th 2015 as the date.

And if the user skips the prompt your where clause in the SQL becomes
where ro.location_id = #prompt('pm-location','numeric')#
skipping the date clause.

Similarly to implement the end date as well, your where clause would be...

where ro.location_id = #prompt('pm-location','numeric')#
#prompt('DATEEND','date',' ','and SP.SHIPMENT_DATE<=''','','''')#
#prompt('DATESTART','date',' ','and SP.SHIPMENT_DATE>=''','','''')#


Hope it helps,
Cognos810

[nsaxena]

Hi,

i Agree with Cognos810...you rock :)