Can this be done in BO?
I have put together a BO report which shows active reservations and how long they have been outstanding. This works great, but then a member of staff asked if I could include a 'date available' date on the report as well, so when viewing the list they could easily see which ones have not yet been published.
This 'date available' however comes from the orders universe (Normal Orders - Order Reports). So, I have created another report and linked the both on 'control number'.
So...
I have a report showing 'Control number', 'report date' and 'availability date'. This shows the data I need, but I can not then link this 'availability date' back to the reservations table because there are a number of 'report dates' with different 'availability' dates.
e.g.
CTRL NO,REPORT DATE,AVAILABILITY DATE
0007173555, 2007/02/28, 2007/06/04
0007173555, 2007/04/13, 2007/06/04
0007173555, 2007/05/14, 2007/06/04
0007173555, 2007/05/18, 2007/06/04
0007174276, 2007/02/06, 2004/03/01
0007189869, 2007/02/23, 2007/03/15
0007189869, 2007/03/09, 2007/05/15
etc
Can BO experts out there think of I way I can filter this list so, where there are control numbers with multiple report dates, it will only show the ones with the latest report date?
So I would end up with:
0007173555, 2007/05/18, 2007/06/04
0007174276, 2007/02/06, 2004/03/01
0007189869, 2007/03/09, 2007/05/15
Cheers
Mark
getting the maximum date
One approach would be to create the full report from both universes and then create two new variables "max report date" and "max availibility date".
The formulas for these variables would be
=Max(Report date)
and
=Max(Availability date)
The variable names will be bounded by < and > but this get treated as html tags in the posting so have havd to be removed from the text. However if you select the max function and object from the lists in the variable editor the right syntax will be used.
If these two variables are used instead of the original ones then you should get the right results. I have tried it with your just the data from the orders universe and it works. I haven't tried it with the data from the reservations universe and there could be complications but it would be worth a try.
Brian Crampton
Developer, Talis
Sorry Brian, I can't get my
Sorry Brian, I can't get my head around what you suggest.
If I just concentrate on the order reports. I need to get a list of Control numbers with just the latest dated 'report date' for each control number.
What I did was use the ORDERS Universe, and do a query showing:
CTRL_No, REPORT DATE, AVAILABILITY DATE, then I created a variable called maxReportDate and for the formula I used as you suggest =max(Report date)
However, I just get the Report Date repeated, I don't get the max one for each control number (where there are multiple control numbers)
e.g.
CTRL NO,REPORT DATE,AVAILABILITY DATE,maxReportDate
0007173555, 2007/02/28, 2007/06/04, 2007/02/28
0007173555, 2007/04/13, 2007/06/04, 2007/04/13
0007173555, 2007/05/14, 2007/06/04, 2007/05/14
0007173555, 2007/05/18, 2007/06/04, 2007/05/18
0007174276, 2007/02/06, 2004/03/01, 2007/02/06
0007189869, 2007/02/23, 2007/03/15, 2007/02/23
0007189869, 2007/03/09, 2007/05/15, 2007/03/09
How do I get it to look at the Control_number and THEN check for the max report date? So I would end up with a list like this, just showing one instance of the control number with the max report date?
0007173555, 2007/05/18, 2007/06/04
0007174276, 2007/02/06, 2004/03/01
0007189869, 2007/03/09, 2007/05/15
Cheers
Mark
http://www.wirral-libraries.net/
http://www.stembystem.co.uk/
getting the maximum date
As stated the variables are used instead of the original objects so remove the Report date object from the displayed report (but not from the query panel).
Thanks
Brian Crampton
Developer, Talis
Thanks Brian
Brian,
Doh! Thanks again. It works great now.
Cheers
Mark
http://www.wirral-libraries.net/
http://www.stembystem.co.uk/