How to display data in terms of user input intervals (e.g., 15 minute interval )

[sandesh2012]

The database has a timestamp as a column , according to the value entered by the user the rows have to be
grouped as per the interval
e.g., the prompt will be given for entering a value ... 15 minutes , 30 minutes, 45 minutes, 1 hr, 6 hr, 12 hr etc

and according to the value entered the rows have to be grouped and shown as below

8:00 - 8:15 x 12 30

8:15 - 8:30 y 15 45

8:30 - 8:45 v 67 78

8:45 - 9:00 y 70 76

the problem is how to group this data because the value interval value is not static and the user can enter any
of the n values which is allowed

My requirement is like the data should be displayed according to the user input interval value

if user gives 15 minute interval it should display the data in this manner
2011-11-22  0:00  -  0:15
2011-11-22  7:45  -  8:00
2011-11-22  8:00  -  8:15
2011-11-22  8:30  -  8:45

if user gives 30 minute interval

2011-11-22  0:00  -  0:30
2011-11-22  7:30  -  8:00
2011-11-22  8:00  -  8:30
2011-11-22  8:30  -  9:00

similarly for the other intervals ...

2011-11-22  0:00  -  1:00
2011-11-22  7:00  -  8:00
2011-11-22  8:00  -  9:00
2011-11-22  9:00  -  10:00

this I found difficult to handle ..
similarly the user can input time interval value in hours like 1 hour, 6 hours, 12 hours 1 day.
Thanks for help ....

[blom0344]

It should be possible to dynamically use the calculated dataitem (that stores a certain grouping level) based on the parameter value of a prompt:

CASE
WHEN
?parameter_level? = '15min'
THEN [calc_level_15_min]
WHEN
?parameter_level? = '30min'
THEN [calc_level_30_min]
....
....
ELSE NULL
END

[sandesh2012]

Thanks for the reply ...
and how about representing it in the format i have posted above , in the intervals ..

I tried using CASE statements

CASE #prompt('Enter the interval value','integer')# WHEN 15 THEN (....do this....) WHEN 30 THEN (........do this.....) END

but it gave a error message like

This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0219 The function "TRUNC" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported


when i put the part inside the then part in 2 different QSs both work fine , but when i combine in a single case the above error came up ...

[blom0344]

Sorry, I imagined you already had a solution for calculating the right groups based on the timestamp.

DB2 has the mighty midnight_second function, but you can wrap your own within Cognos:

Code Select Expand


extract(hour,[sometimestamp])*3600+extract(minute,[sometimestamp])*60+extract(second,[sometimestamp])


Calculating minutes:

Code Select Expand


extract(hour,[sometimestamp])*60+extract(minute,[sometimestamp])


Calculating set of 15 minute:

Code Select Expand


_round((extract(hour,[sometimestamp])*60+extract(minute,[sometimestamp])/15),0)


And so on..

Capice?

[sandesh2012]

Thanks Blom0344
I am already doing this ... but my problem is with representation part
I need one column where i have to represent this timestamp as a timeinterval in

hh:mm -hh:mm

e.g., if user had entered 15 minutes as interval

l it should display the data in this manner
2011-11-22  0:00  -  0:15
2011-11-22  7:45  -  8:00
2011-11-22  8:00  -  8:15
2011-11-22  8:30  -  8:45

if user gives 30 minute interval

2011-11-22  0:00  -  0:30
2011-11-22  7:30  -  8:00
2011-11-22  8:00  -  8:30
2011-11-22  8:30  -  9:00

similarly for the other intervals ...

2011-11-22  0:00  -  1:00
2011-11-22  7:00  -  8:00
2011-11-22  8:00  -  9:00
2011-11-22  9:00  -  10:00

this I found difficult to handle ..
similarly the user can input time interval value in hours like 1 hour, 6 hours, 12 hours 1 day.

and i am using prompt to accept the value in place of 15 in my query ...

Thanks for your help ...

[blom0344]

Okay, but I tried to answer that question in my first reaction.  You can dynamically set the proper 'level' by using a case against the prompt parameter to make sure the chosen calculated dataitem is used within the report layout.

So, if the user selects  '15 minutes' in the prompt   :

CASE
WHEN
?parameter_level? = '15minutes'
THEN [calc_level_15_min]
WHEN
?parameter_level? = '30minutes'
THEN [calc_level_30_min]
....
....
ELSE NULL
END

then [calc_level_15_min] is used in the layout of the report object (list etc)