Sorry for the vague title and for my lack of experience with business objects/web intelligence.
I'm part of a pathology lab that deals with thousands of samples every day and this data is stored in a data warehouse that we use BO to retrieve. We are especially interested in the turnaround times for samples and run queries to extract this data. We have managed to pull data but no matter how hard we try we are unable to remove addons and they are affecting the quality of our reports.
Clinicians often phone up hours or even days later to request addon tests. However when we retrieve the data from the warehouse it calculates the time taken from the time the specimen was booked in to the time the addon was completed. If this is days later it makes it look like it took much longer to complete than it did.
We use the test code 'ADD' when we modify the orders and so all samples with the test code ADD should be removed from the report.
The current report looks like the following
Test Code | Specimen Number | Receive Date | Receive Time | Date Current Status Achieved | Time Current Status Achieved | Test Turnaround Time (Decimal)
We calculate the test turnaround time with a simple calculation of current status achieved (aka results done) minus Receive date/time and report it in decimal hours.
The Report might look like the following
K | 000001 | 01/01/16 | 09:00:00 | 01/01/16 | 12:00:00 | 3
If a Test was added on to this sample we might have two lines
ADD | 000001 | 01/01/16 | 09:00:00 | 01/02/16 | 12:00:00 | 27
B | 000001 | 01/01/16 | 09:00:00 | 01/02/16 | 12:30:00 | 27.5
It now looks like test B took 27.5 hours to complete when it really only took 0.5.
We have tried doing a combined query to minus all the ADD test from the full report but as you can see that would only remove the ADD tests not the actual test added on.
Is there a way to basically do the following?
If [Test] = ADD then delete all [Test] that match [Specimen Number]?
Thanks in advance