Hi,
I'm trying to create a RS report which displays counts by time. Users select a date and the need is to display the counts starting at 6 AM to every 12 hours for a week. The report has 2 columns one dimension and should be grouped and the other is count of and ID based on the created datetime. So the report will have 14 columns. Can anyone suggest a better way. I'm using oracle database.
You are describing the requirements , not the way you have designed the report. How can we then suggest a 'better way' for you? Can you be more specific?
Hi,
I'm trying to get counts by time when there is date crossover at midnight. When a date is selected the report should display counts for a week based on the date that is selected. All the counts should be grouped every 12 hours starting at 6 AM on the date that is selected. It's 8.4 cognos and oracle.
Let me know if you need more information.
What is the problem you are having? You are still describing your requirements and not really a specific issue you have in building the report.
Seems to me you need to filter for the selected date on or after 6 am through a week later up to 5:59 am. Then create a query item in your report that has the count query item and another that decides which bucket the count belongs in. A crosstab layout would be used to display the counts. A case statement might be the easiest way to derive the bucket values.
case
when [your datetime] between {6 am on selected day} and {5:59 pm on selected day} then 'Period 1'
when [your datetime] between {6 pm on selected day} and {5:59 am on selected day+1} then 'Period 2'
when [your datetime] between {6 am on selected day+1} and {5:59 pm on selected day+1} then 'Period 3'
when [your datetime] between {6 pm on selected day+1} and {5:59 pm on selected day+2} then 'Period 4'
...
etc
...
end
Otherwise create a query item for each bucket and use some case logic to count items that fall into it.