Simple but tricky requirement

[mitwa]

Hi everyone,

I have one requirement..

I have one prompt page with two options..they are 1. yesterday 2. other

1. if i select yesterday then i have to get revenue upto yesterday

2. if i select other then other 2 prompts will have to open for selecting date of FROM & TO

[BigChris]

On your prompt page put a value prompt with two static values, "Yesterday" and "Date Range", and call the prompt "pDateSelection". you could make the prompt a radio group if you want to...
Also put on two date prompt, one for "From" and one for "To". Make the From and To prompts optional.

In your query have a filter that runs something along the lines of:

if (?pDateSelection? = "Yesterday") then ([Query].[Date] = cast(_add_days(getdate(),-1),date) else (cast([Query].[Date],date) between ?From? and ?To?)

Obviously I've made some assumptions about date and times - you might need to alter the cast functions that I've put in there.

[Lynn]

I think getdate() is a vendor specific function, so may or may not be the right thing depending on the OP's data source, which isn't specified. Also not specified is if the source is relational or dimensional. The answer will vary dramatically between those two types of sources.

When filtering in a relational situation, I would opt to create a boolean condition rather than case or if/then/else syntax. I don't think any casting is required if the database element is date datatype and if the from and to prompt controls are date pickers:

Code Select Expand


(
   ?pDateSelection? = "Yesterday"
   and [Query].[Date] = _add_days(current_date,-1)
)
or
(
   ?pDateSelection? = "Date Range"
    and [Query].[Date] between ?From? and ?To?
)


If you have a dimensional source then post back that information since the method is entirely different in that situation.

[navissar]

The answers offered by Lynn and Chris are solid. Nonetheless, I think I would take a different approach:
So we start with a prompt page that has three prompts: dateSelection (Yesterday or Other), fromDate and toDate.
We use HTML to hide the two date prompts by default (Something along the lines of

Code Select Expand

<span id="dateprompts" style="display:none"></span>)
Now, we create an onclick event (Or, if using Cognos 10.2+, a validation function via prompts API) for the dateSelection prompt. If yesterday is selected, we fill in 1/1/1900 (I'm boldly assuming your company doesn't have data prior to January 1st 1900...) in the from date prompt, and getDate() - 1 in the toDate prompt.
If other is selected, the date prompts become visible and the user can insert whatever dates the like.

The query then gets but one filter: [Query].[Date] between ?from? and ?to?

I like this solution because it uses JS in the prompt page to help create easier to read, easier to parse, better to perform SQL in the query itself....

I'm attaching a rough sketch in 10.1 samples.

[BigChris]

I like that Nimrod, but I just get a bit twitchy around JavaScript. Wherever possible I try to stick to the options out of the box, just because I figure that means there's less expert knowledge needed beyond what you can get from IBM help info, Cognos courses etc.

As an aside, if I were to start looking into JavaScript to customise some of the functionality in Cognos where would you suggest I start? Do you know of any online sources for example where I could start to get an understanding of what's involved?

Cheers,
Chris

[navissar]

Hey Chris,
You'll perhaps be surprised to know I share your sentiment regarding JS usage. My condition for using JS (Which I require any of my developers coming in with a "Code me this" request to answer before I even start) is this: The solution must be essentially better than an out-of-box alternative in either shorter dev times or better performance/significantly better user experience.
In this case, JS is already necessary according to requirements (Because it would be confusing to display the from-to prompts when "yesterday" is picked, and we'll need JS to hide them). Since we're already using JS, we may as well throw in some other simple functionality - in this case, it meets my criteria because it simplifies the query, ultimately having a positive effect on performance.

As to your question, I cannot direct you to a specific place to learn JS beyond what a simple Google search would - I sort of picked it up along the way myself.

[mitwa]

cool guys...

i like ur replies...

and thank u all