I use BO6.5 SP4 , “freehand SQL” to build a report and users use prompt to input the start time and stop time to get the measurement data as line type chart ( X axis : time , Y axis: Measurement )
In our lab , every quater ( 0,15,30,45 min ) we will receive a measurement data . It works OK if all the data is loaded into the database. ( sybase IQ )
The problem I have now is that the chart cannot show the missing data, ie. the line is always continous , I would like to see at certain time (for example 01:15) when no measurement data is received , the line should be broken at that time , so it will be easy to see when the data is unavailable.
I can think of having a list of intervals like 00:00 , 00:15… 23:45 , and link this with the original timestamp in the table and in the chart , show the interval I defined as X axis instead.
But if the user needs to input the start time and stop time , then I don’t know how to get those expected time intervals ?
Please forgive me if it is a easy question , I searched the forum and cannot find one answer .
I would appreciate if anyone could help .
Thank you in advance !
Best regards,
Arthur
The technique how to include data for all possible dimension values is described in this Reporter’s FAQ:
In your case quarter dimension is the same as month dimension in the scenario linked above.
The second data provider that you create and that will contain all values of quarter dimension will also have a condition with the same start time and stop time prompts.
So I need to create the second provider which has all the quarters from the start time to the end time and use this as X axis instead .
Is there any SQL function or "stored procedures " which can be used for this ?
For example , when user input:
start time 2008-04-20 23:00
End time 2008-04-21 1:00
and the output will be
20080420 23:00 , 23:15, 23:30,23:45
20080421 00:00 00:15 0030 0045
P.S ,in the data provioder 1 , the datatime entry is like
'2008-04-20 01:15".
Or there is any other ways to do that ?
I am not very good at SQL , thank you for any ideas or recomendations.
Hi Arthur,
This all depends on which database you use as the syntax may very from one DB to another.
However, the best solution would be to have a separate table (dimension) with all values of quarters and in the format that you need. Then you query just this table in the second data provider.
Select
tablename.datecolumn,
tablename.measurecolumn
from
tablename
where
tablename.datecolumn between .... and ....
union
select
tablename.datecolumn,
0 as measurecolumn
from
tablename
where
tablename.datecolumn between ..... and ....
This would put a “zero” in place of any missing data. Your line would not be “broken”, but it would spike down to zero - equally as obvious.
for Jac’s reading , I suppose you refer to the following in the table.
00:00 100
00:15 101
00:30 NULL
00:45 104
in our database , if the measurement at specific time is missing , there will be no time info , for example , as you can see , we miss the time stamp 00:30 . So your solution does not work here .
…
00:00 100
00:15 101
00:45 104
…
So your solution does not work here .
I will post the SQL question in the universe design on how to get the quarters dynamically.
Thank you all for your great support !!
best regards,
Yu
(BOB member since 2003-11-27)
(BOB member since 2005-05-10)