Date prompts and timestamps

[ry1633]

Hi all I have a two-part question about dates and date prompts.

1.  The database environment I work in is Oracle.  And they have all the date fields with timestamps in them such as '7/22/2004 10:24:45 AM'.   Is it possible in Report Studio to filter out the time part of that field?   Or should I have my DBA guys try to cobble together something together?   I'm assuming it can't be done in Framework Mgr when making a package(?).

2.  Once I get #1 figured out :) - I'd like to create a date prompt that could filter for a single date or a range of dates.  Say I'm working some kind of permits as an example, and I have a field called ISSUE_DATE - can I do a date prompt that can do more than one thing?

[cognostechie]

Yes, it is possible to do it in FM but you should be able to use the datetime field in a date prompt even without stripping the time part from it. In Report Studio, create a date prompt (not a Datetime prompt) and then use the datetime field in that prompt. The filter can be the regular filter that would say :

[Database View].[Date Dimension].[Date] = ?Date_Prompt?


OR


[Database View].[Date Dimension].[Date] in_range ?Date_Prompt?

This would work because technically the Date prompt would only look at the date portion of it.

For doing it in FM, you can create a calculated column and strip out the time portion like this:

cast([Database View].[Date].[Date]  , date)

[ry1633]

I can clarify a bit....   This works just fine for me [ISSUE_DATE] in_range ?Date_Prompt? -- but when I run it still prompts for the data and time with a calendar and clock icon, which something I don't want.   I just want to select a date or range of dates.  Maybe I didn't do it right?

I chatted with one of my users and they wanted to add a few qualifiers.  I'll also have a 'status' and 'terminated date' fields.  So for the report say they entered "Jan 30 2012" - they'd want all 'Active' items before that date:
         

• issue_date would be less than or equal to 30 JAN 2012
           
• terminated date would be less than or equal to 30 JAN 2012 or null.

[bdbits]

I assume this is not a data warehouse? If it is and you never care about the time portion, you could handle this at ETL time and set column types to date instead of datetime/timestamp.

If it is not a data warehouse and you never care about the time, you could do the cast as cognostechie posted.

You can mess around with it in RS as cognostechie posted. But if you are never going to use the time portion then I would do one of the above. I would do this so authors (which could be users) don't ever have to even think about the time portion existing when creating reports. But this is only a valid approach if they never, ever care about the time portion.

[cognostechie]

I can clarify a bit....   This works just fine for me [ISSUE_DATE] in_range ?Date_Prompt? -- but when I run it still prompts for the data and time with a calendar and clock icon, which something I don't want.   I just want to select a date or range of dates.  Maybe I didn't do it right?

I chatted with one of my users and they wanted to add a few qualifiers.  I'll also have a 'status' and 'terminated date' fields.  So for the report say they entered "Jan 30 2012" - they'd want all 'Active' items before that date:
         

• issue_date would be less than or equal to 30 JAN 2012
           
• terminated date would be less than or equal to 30 JAN 2012 or null.

I got your PM too but I would rather post the answer here so that others can also benefit from this.

First of all, the reason you see calendar and clock is because you either used 'Date and Time prompt' or you didn't use a prompt at all and simply created a filter. For the latter, Cognos would have detected the field type in the DB and created a date and time prompt accordingly. Delete your filter and the prompt (if you created) and go to the toolbox (Red icon with a hammer in it on the left side of the page). You will find Date Prompt which is right under the Date and Time prompt. Drag the Date Prompt on the prompt page and follow the steps to point it to your Date & Time field. After creating it, run it and you will see only the calendar, not the clock. If you want to make it look better, click on the prompt and from the properties on the bottom left , change the property 'select UI' to 'Edit Box'.

Secondly, you can have multiple prompts in the report so it is possible to filter on status and any other field. Once you are done with the Date prompt, then go to the Toolbox again, drag a 'Value Prompt' and point that to the Status field. Do the same for other fields too. After creating the prompts, check the filters in the query, you will see separate filters for each prompt. These filters get combined when the report is run and together become part of the SQL so you can get the data for a specific date range and further filtered based on the status etc.

[ry1633]

I'm absolutely positive 100% that I chose "Date Prompt" from the Toolbox and not "Date and Time Prompt" - in fact I just tried it again just now and made sure - and I still get the calendar *and* the clock.

[BigChris]

Try using cast([ISSUE_DATE],date)...it looks like the data in your database is stored as a Datetime.

[ry1633]

I've got the prompts working ok on a new prompt page, but now there are a few new problems (or at least things I don't understand).

1.  The Date Prompts I made still run as Required fields, even though I've set "Required" to 'No' in the properties.   I don't want any of my date prompts on the prompt page for this report to be required.

2. Range of Dates:  I am still unable to select a range of dates in each Date Prompt, even though I have set "Range" to "Yes" and "Select UI" to "Edit Box".   When I change those, the mockup looks like it will give me a Range,  but when I Run Report I'm still only able to select one date

3.  Date Prompts show up in two places - 1. On the Prompt Page and 2. On the Report Page itself.  Can I change that?   And also.... the Date Prompt shows the Calendar and Prompt on the report page, and I'd rather not have that.  :)

4. I also don't understand the linkage and displays between prompts and pages.  For instance, when ever I create a brand new prompt,  Cognos automatically puts the prompt on a separate page at the beginning - regardless of whether I've created an actual 'Prompt Page' or not.      Conversely,  when I delete a prompt Cognos will remove it from the 1st page but not from the report page.  Very confusing and frustrating.

Is it possible to set an initial null value in a Date prompt - just in case a user didn't want to put anything in there?

[bdbits]

If there is a date prompt on the report page, you put it there - Cognos is not going to do that by itself. Just delete it.

If a prompt value is required somewhere in the report, and does not appear on the prompt page, you will get the dynamic/generated prompt page asking you to supply the missing value.

It sounds like maybe you have added/deleted/re-added prompts multiple times. My guess is you still have a required prompt name(s) referenced that no longer has a prompt control. If you go into the expression editor for some item in your report, the prompt names are on a separate tab. See if you have prompt names listed that do not have a corresponding control.

[ry1633]

yeah what I found out was that deleting the prompt from the page, still leaves the query parameter intact, so running the report makes it think it's still there.  I had to go into Query Explorer and delete it out of there too.

oh I noticed one more issue that has me a bit confused.   Sometimes when I drag in an create Value prompt into the main body of the report, the prompt actually runs on a page before the actual report.  Even though I never created a separate prompt page.  Why is that?

[MFGF]

oh I noticed one more issue that has me a bit confused.   Sometimes when I drag in an create Value prompt into the main body of the report, the prompt actually runs on a page before the actual report.  Even though I never created a separate prompt page.  Why is that?

Hi,

If you think about this methodically, you can see why it has to. If you define a parameter filter for a report object (eg crosstab), the page can't be displayed until the contents of the crosstab are resolved. The only way to resolve the contents is to prompt for the filter value. But if you put the prompt to do this on the report page, you introduce a conundrum for Cognos. The page can't be rendered until the contents of the crosstab are resolved. The crosstab contents can't be resolved until the page has been rendered with your prompt on it (and the prompt has been answered).

So - catch 22. What does Cognos do in this instance? It generates an automatic prompt in the first instance so it has the relevant data to render the page initially. This is what you are seeing. If you want to prevent this, you need to set a Default Selection value for the prompt on your report page, so that the page can initially be rendered using this value in the filter (without having to prompt for it).

Cheers!

MF.

[ry1633]

I do understand that - but I have other reports in RS where the exact same situation renders just fine - where the prompts are displayed on the same page as the report itself and I don't recall changing anything in the setup or placement?

oh forget that - as I'm typing this I figured it out.   I forgot to check "optional" in the wizard.  Sometimes I can't believe I make those mistakes. :)

[cognos810]

Hello ry1633,
Slightly off-topic, but given that your date time values are of the format '7/22/2004 10:24:45 AM' where you actually have some value in the time part. Beware that when you write a filter statement like [Date Time Column] <= '7/22/2004', you will miss the record. This is because at the database level, the filtering will be applied as  [Date Time Column] <= '7/22/2004 00:00:00 AM'. You can try this by writing a similar query directly against the DB.
Just wanted to warn you :). If you are ok with it, then please discard it.

-Cognos810

[cognostechie]

Hello ry1633,
Slightly off-topic, but given that your date time values are of the format '7/22/2004 10:24:45 AM' where you actually have some value in the time part. Beware that when you write a filter statement like [Date Time Column] <= '7/22/2004', you will miss the record. This is because at the database level, the filtering will be applied as  [Date Time Column] <= '7/22/2004 00:00:00 AM'. You can try this by writing a similar query directly against the DB.
Just wanted to warn you :). If you are ok with it, then please discard it.

-Cognos810

Excellent point ! Maybe add one day to the filter such as      [Date Time Column] <= _add_days(?DatePrompt?,1)

[ry1633]

if I have format '7/22/2004 10:24:45 AM' and I still use a straight Date prompt, for date only, will I still be OK?    I've done it this way, and it seems to be fine.  I haven't noticed any loss of records.

[cognos810]

I guess the sure shot way to make sure is that, extract the native SQL from the report query and see what the prompt is being translated into. Can you please post it here?

[ry1633]

how do I extract the Native SQL in Report Studio?   I've never had to do that before.

[bdbits]

In report studio, choose menu item Tools > Show Generated SQL/MDX. Pick the relevant query from the tree on the left, and make sure "Native" is selected in the dropdown above the query text.

It's not obvious, but you can select all or part of the text with your mouse, or press Ctrl-A to select it all, then Ctrl-C to copy the selected text to the clipboard.

[ry1633]

when I do that it only shows the SQL for the entire report layout but not the Parameters of the Date Prompt.  Here's the SQL - I had to heavily edit it because there is sensitive data in there I can't display:


select "T0"."C0" "TYPE", "T0"."C1" "CODE", "T0"."C2" "COMPANY", "T0"."C3" "STATUS", "T0"."C4" "FIRST_ISSUE_DATE", "T0"."C5" "CURRENT_ISSUE_DATE", "T0"."C6" "TERMINATION_DATE", sum("T0"."C7") over () "COUNT_Total", sum("T0"."C7") over (partition by "T0"."C0") "COUNT_TYPE"
from (
select "VW_PERM02"."TYPE" "C0", "VW_PERM02"."CODE" "C1", "VW_PERM02"."COMPANY" "C2", "VW_PERM02"."STATUS" "C3", "VW_PERM02"."FIRST_ISSUE_DATE" "C4", "VW_PERM02"."CURRENT_ISSUE_DATE" "C5", "VW_PERM02"."TERMINATION_DATE" "C6", count(*) "C7"
from "...DEV"."VW_PERM02" "VW_PERM02"
group by "V"."TYPE", "VW_PERM02"."CODE", "VW_PERM02"."COMPANY", "VW_PERM02"."STATUS", "VW_PERM02"."FIRST_ISSUE_DATE", "VW_PERM02"."CURRENT_ISSUE_DATE", "VW_PERM02"."TERMINATION_DATE") "T0"
order by "TYPE" asc nulls last