I have a report in which i am getting data based on the userresponse. However i need data for year to date on the same calculated value on that report as well.
e.g
Manager : Emp count (user response date timeframe) : Emp count (YTD)
tom : 6 : 12
jerry : 12: 14
I know that I can make a second query and add the YTD value into the same report. I was just wondering if there was a way to it using one query. :?
honkeyhindu (BOB member since 2007-03-29)
In the query panel:
You can get in one query… if your WHERE condition is something looking for <= @Prompt plus you also need date object.
In Report:
– You have to create few local variables and calculate your “Emp count (user response date timeframe)” from your query.
BO_Chief 
(BOB member since 2004-06-06)
hmm i am trying this but its not really working.
The user is prompted for the following
trunc(KOL_ACTIVITY.START_DATE) BETWEEN @Prompt('Activities From Date','D',,MONO,FREE) AND @Prompt('Activities To Date','D',,MONO,FREE))This above code appears in the FROM clause. The person who initially created the universe used it in a join and since there are mutiple reports that run off this report changing that join would become a pain.
in the query panel if i create this in the WHERE clause
trunc(KOL_ACTIVITY.START_DATE)<= @Prompt('Activities To Date','D',,MONO,FREE))I wont get the data that I want. I am guessing this is due to the FROM clause. Any ideas !!!
honkeyhindu (BOB member since 2007-03-29)
Am i right in assuming that the following are my choices.
Edit the join . Go into each report that uses that join and add the prompt in the WHERE clause for the activity date between prompt then use BO_chief’s idea of getting YTD data.
Make a ALIAS tables for all the tables necessary and objects for the YTD part of the report.
If anyone has a different idea please feel free. 
honkeyhindu (BOB member since 2007-03-29)