Date Prompts and Subreports

[sstengler]

Here's what I need to do:
- I have a main report, with several subreports, mostly text.
- I want the user to enter a single date prompt (as a date)
- I want to then use the date prompts in the subreports, either directly as displayed data (or manipulated as a string, maybe), or as a filter.

I've created the Date (not datetime) prompt on the main report, and named it.
I've tried using it as a filter in some of my subreports, but it doesn't appear to like it, as my results are NULL, whereas if I replace the prompt name (?dateprompt?) with the function NOW(), it brings back the correct data set.
Also, I cannot seem to get a text frame subreport to simply display the date in the prompt, except as a 'prompt display', which I can't use since I can't manipulate it...(I need just the Month name and the year)

Anyone got any ideas?

Sam Stengler

 

[griffindm]

Sam,

Just a check on procedure: Are you creating a calculated column in the main query and making it equal to the main query prompt? This is the proper way to use a main query prompt in a sub-report. If not, how are you using the main query prompt in the sub-report?

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[sstengler]

In the Data tab of the Main Query, i clicked on the Calculated button in the middle to bring up the Calculation Definition screen. In the Expression box I've entered ()actually, piked from the list on the left) the prompt name (ie. ?dateprompt?), and then entered a name in the Name box. Saved it.

 

[griffindm]

Sam,

Sounds like you're doing it right. What SQL are you getting in the sub-report? (Check menu option Report | Query | Profile - then select the SQL radio button).

If the main query prompt is not being used as a data filter there, it may be a question of the date format of your database. Date-based filters can be tricky, based on the database platform, as the filter often does not make it into the SQL and is processed locally. If it does make it into the SQL the date format has to agree with with what your database expects in order to be effective. If you are getting no rows returned, I would investigate this possibility.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[sstengler]

When I use this as a filter, (Perioddate >= first-of-month( Main Query.promptdate) and Perioddate <= last-of-month(Main Query.promptdate)) - (where Perioddate is a data source column)

I get the following error when I try to look at the SQL:

&quot;A general exception has occurred during operation 'prepare request with options'. DMS-EE-SS_SYNTAX A syntac error was detected near 'TIMESTAMP'.&quot;

 

[griffindm]

Sam,

Here is the Cognos Knowledgebase article on this topic:

Title: IMPROMPTU Error Message DMS_ESS_SYNTAX
Product: Impromptu
Version: 6.0
Machine: PC
Document ID: 119705

Description:

When running existing reports that contain date fields, the following error is returned:

DMS-E-SS_SYNTAX, a syntax error was detected near 'TIMESTAMP'.

Solution:

If the date being stored on the database is a value other than Date, possibly packed decimal or integer, check to make sure that the data type has not been changed. Decimals or Integers will need to be converted. A change in the data type via a truncation or other means will cause the calculation to fail. Either the data type will need to be changed back, or the calculation will need to be changed to accommodate the new data type.

If the date field is a prompt, ensure that the dates are entered in the right format. For example, if the date field is formatted as mm/dd/yyyy, when entering the date in the prompt, it must also be mm/dd/yyyy.

end of article

What I would do first is to try to verify the table and column in the catalog (with no report open, but the catalog open and connected to the database - go to Catalog | Tables then select the table with this column in it on the right side of the screen and press the 'Verify' button). This should tell whether the actual database column is of the type that Impromptu expects.

You can also create a simple report that just returns the column, and then check the returned data types in the Report | Query | Profile screen.

Oh, I would also change the filter statement to use the between clause instead of two separate <= => statements.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[sstengler]

Ok, checked the data source column, and it was returning a date-time. so converted it to a date (datetime_to_date(Perioddate), but it still didn't work.

The only way I can display the prompt data is to Insert, Prompt variable - that shows the prompt variable name, then the variable (&quot;currentdate: 4/8/03&quot, but I can discover no other way to dsiplay the prompt data...I'm wondering if the prompt data is even showing up in the filter expression.

However, I did try this: datetime_to_date(Perioddate) >= first-of-month( Main Query.promptdate) and datetime_to_date(Perioddate) <= last-of-month(Main Query.promptdate)

- got a similar message to the above, except for the last line (&quot;A syntax error was detected near 'DATE'&quot

 

[griffindm]

Sam,

What is your database (type and version), access method (native or ODBC), and version of Impromptu?

Also, could you include the SQL statement that Impromptu generated when you did the report successfully using the NOW() function.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[sstengler]

Oracle 8i - native
Impromptu 7.0.702

select sum(T1.&quot;TOTALCALLS&quot &quot;c1&quot;
from &quot;UHGDASHBOARD&quot;.&quot;AAC_COG_TELEPHONE_TOTAL_CALLS&quot; T1
where T1.&quot;PERIODDATE&quot;>=TRUNC(SYSDATE ,'MONTH') and T1.&quot;PERIODDATE&quot;<=LAST_DAY(SYSDATE) and T1.&quot;TIER&quot;='UHG Tier 1'

filter:
Perioddate >= first-of-month(now()) and Perioddate <= last-of-month(now()) and Tier = 'UHG Tier 1'

 

[griffindm]

Sam,

Doing the same type of query on Oracle 8i and Impromptu 6, I get the following SQL:

select T1.&quot;VEND_HRS&quot; &quot;c1&quot;, T1.&quot;TIME_STAMP&quot; &quot;c2&quot;
from &quot;DELTEK&quot;.&quot;VCHR_LAB_VEND_HS&quot; T1
where T1.&quot;TIME_STAMP&quot; between to_date('2003-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2003-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

This is from a filter of :

time_stamp between ?sdate? and ?edate? ...

Time_stamp is a date-time (and is reported as such under Reports | Query | Profile - Query column button). The two prompts are type-in dates. The conversion of the prompt dates to Oracle date-times occurs automatically.

Not sure what is going on with your setup.

Let me know what you think.

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com