system
1
I am trying to create a report, currently when I query on ‘Machine’, ‘Hours’, ‘Date’ I get this:
Machine1 01/02/2008 10,000
Machine1 01/03/2008 10,002
Machine1 01/04/2008 10,003
Machine2 01/02/2008 10,000
Machine2 01/03/2008 10,002
Machine2 01/03/2008 10,003
Machine3 01/02/2008 11,000
Machine3 01/03/2008 11,002
Machine3 01/03/2008 11,005
What I want is for the report to bring back the LATEST hours. i.e.
Machine1 01/04/2008 10,003
Machine2 01/03/2008 10,003
Machine3 01/03/2008 11,005
How do I do this?
Machine is a detail
Hours is a measure
Date is a detail
fghs_1987 
(BOB member since 2008-01-02)
system
2
Create a object in the universe using the following.
- Please Modify accordingly…
Say A = LEAD(Hours) OVER(
PARTITION BY machine
ORDER BY hours)
so your data should look somthing like…
Machine Date Hours A
Machine1 01/02/2008 10,000 10002
Machine1 01/03/2008 10,002 10003
Machine1 01/04/2008 10,003 0
Machine2 01/02/2008 10,000 10002
Machine2 01/03/2008 10,002 10003
Machine2 01/03/2008 10,003 0
Machine3 01/02/2008 11,000 11002
Machine3 01/03/2008 11,002 11005
Machine3 01/03/2008 11,005 0
Then in the report filter on A=0.
Hope this helps. 
Let me know.
chander165 
(BOB member since 2005-12-20)
system
3
I am unable to modify the universe…is there anyway that I can do this in the report?
fghs_1987 (BOB member since 2008-01-02)
system
4
Look at options with Ranking. Use a dimensional variable and the hours as your criteria. Should work.
chander165 (BOB member since 2005-12-20)
system
5
Sorry, could you detail how that rank would look?
i.e. hours = rank(machin,hours) ?
fghs_1987 (BOB member since 2008-01-02)
system
6
Apply a Break on Machine and Choose a Dimensional object in the report and Click on the Ranking Button. Rank it using Measure - Hours (Top 1).
chander165 (BOB member since 2005-12-20)
system
7
Well that certainly brings back only the last result! Many thanks, but is there anyway of tidying it up so that they all appear in just one big table?
it now looks like:
Machine 1 10001
Machine 1 10001 (IN BOLD)
Machine 2 10101
Machine 2 10101 (IN BOLD)
fghs_1987 (BOB member since 2008-01-02)
system
8
Select the empty line and Delete it.
Go to Format breaks and Fold it.
Select and Unbold it.
chander165 (BOB member since 2005-12-20)
system
9
Done!
Guys, many thanks!!!
fghs_1987 (BOB member since 2008-01-02)
system
10
You could also use a calculated condition to do this.
irish_stan 
(BOB member since 2003-05-13)
system
11
Yes. By dragging the objects in the condition panel and by selecting = as operator and then by selecting calculation as maximum.
Jansi 
(BOB member since 2008-05-12)
system
12
More like using a Subquery and choosing the max. Yes that should have been another Option. But, It would add to the complexity.
chander165 (BOB member since 2005-12-20)