#Cognos sql prompt how to

(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass. (select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD')) as delovne_ure, (select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure, (select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate')#,'YYYY-MM-DD') and to_date(#prompt('EndDate')#,'YYYY-MM-DD')). Thanks guys for all your help, much appreciate! All I needed to do was remove the data type 'date' that I had in my prompts and set the format pattern to YYYY-MM-DD and it's working like it should.

Build a Prompt page and create a Tree prompt for. I've solved the problem with the prompts. Create a List report with the following columns using DMR package 2. Adding parameters to SQL select and using them in Cognos Report Studio Version 10.2.1.

#Cognos sql prompt how to

(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')) as assignment_ure How to use a date range in a Cognos SQL Prompt Query. Prompt macros give you an extremely fine grain of control. (select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')) as delovne_ure, This behaves exactly like the parameter map, and the SQL is identical. (select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure, (select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'YYYY-MM-DD') and to_date(#prompt('EndDate','date')#,'YYYY-MM-DD')). Here is my SQL select that I'm using: select laborcode, I'm using to_date form, but I'm not sure if it is the correct one. I'm not really sure what's wrong.I tried to change the format but it didn't helped. IBM COGNOS Report Studio query based report with optional prompt syntax Many a times, we canвЂ™t wait till the metadata is created for every element that is there on the application, especially if your company is an extreme programming one. SQL20448N '1993' cannot be interpreted using format string 'YYYY-MM-DD' for the TIMESTAMP_FORMAT function. UDA-SQL-0144 An arithmetic exception was detected. I'm having trouble with an error that says: