creating a date prompt for a query based report

[skbeginner]

I am doing a report in cognos reportstudio, based on a query in query explorer. in the query i am filtering based on date (where closeDate between '06/24/2011' and '06/28/2011').

i want to prompt this date to the user when the report is run.

I tried closeDate IN_RANGE ?closeDate? but this gives error.

can someone help me with the syntax please?

[saumil287]

Hi,
You can use the date prompt where in the user can select the start and end date.
I have done the same in report studio.
I am having no idea in report net.

[Lynn]

Code Select Expand

[closeDate] between ?DateStart? and ?DateEnd?

Or use whatever parameter names you like instead of DateStart/DateEnd

[skbeginner]

close date is in where clause of my query. do i need to do anything in prompt page first? thanks.

[Lynn]

You mention ReportNet but I assume you are using Cognos 8 since you posted on this board?

The prompt page allows you to define a prompt control for the user to select the various run options. If you don't have a prompt page then Cognos will generate one for you which isn't generally as attractive.

Do you mean that [closeDate] is referenced in a filter in your query? Are you using hard-coded SQL in your report or are you building it in the query explorer? I can't tell if I'm making incorrect assumptions about your situation and giving you incorrect advice as a result. I am referring to the typical approach of using query explorer.

You can create a prompt page first and define the parameter names there, then reference them in your query. Or you can create the query first and define parameter names there, then later associate those parameters to prompt controls on a prompt page. A matter of preference really.

[skbeginner]

Sorry, yes it is Cognos 8 report studio.

Yes, i am using a hard-coded SQL. so first i wrote the SQL, validated it in SQL portion of query explorer page. Once validated I built the report page by using the data items from insertable objects pane->query 1

I hardcoded the dates in my sql where clause (where closeDate between '06/24/2011' and '06/28/2011'). But I would like to create a prompt so user can enter different dates...

you suggested  "create the query first and define parameter names there, then later associate those parameters to prompt controls on a prompt page" sounds like this is what i should be doing....can you kindly elaborate on the second step.

First, did i do the query part correctly. to define the parameter i wrote closeDate between ?Parameter1? and ?Parameter2?

second, what do i need to do in prompt page? and how do i associate from prompt page to query.

thanks much for your time! i really appreciate it!

[Lynn]

Oh dear, hard-coded SQL......heavy sigh.

Why?? The whole power of this product is the ability to produce reports without writing SQL! Is a package published that you can use instead? I would consider hard-coded SQL only as a desperate, last ditch effort when any and every other possible avenue has been explored. There are a lot of reasons why, but I'll spare you the rant....

I don't recall ever trying to use hard-coded SQL with prompts in Report Studio, but my suspicion is that the syntax would need to be that which the database expects. For example, in Oracle I think an ampersand is used. Not sure what Cognos will do with that.

You could leave the condition out of your SQL statement and just create the detail filter in the query referenced by the SQL statement, but this means (I think) the filtering would be happening locally on the Cognos server and not in the database. Probably a poorly performing option to consider.

If you really (really, really, really) don't have a data package to use as the basis for reporting then you might consider having a stored procedure created in the database with the prompting handled there and then have that modeled in Framework Manager.

Sorry I can't be of more help.