Refining results in Infoview
This is my first post so sorry if it's in the wrong catagory, if it is, let me know and i'll change it.
Basically I've come from a MS SQL Server background to a new job involving BO Infoview which I dont understand very well yet, I've tried researching it and doing tutorials but cant figure this problem out. I need to pull out reports on the available universes, but I'm not involved in any actual development work behind the universes.
I have many columns available, but the main 3 I need to consider are Account Number, Date, and Status.
I need to group by the account number, check for only the most recent date, and only return the record if the status matches a chosen one.
[Name] [Date] [Status]
Joe Bloggs 01.01.2011 Initiated
Joe Bloggs 02.01.2011 PreProcess
Joe Bloggs 03.01.2011 Processed
Joe Bloggs 04.01.2011 Closed
John Smith 11.01.2011 Initiated
John Smith 12.01.2011 PreProcess
John Smith 13.01.2011 Processed
So I want to return only the records where the most recent status is 'Processed', so only the final row would show in this example, because it's the msot recent status for John Smith and the status is Processed, Joe Bloggs however, has already been on Processed and is now Closed, so he is excluded from the results.
Hope that explanation is sufficient.
I've tried googling it and reading tutorials but can't figure it out, I'm new to this and I know how I'd do it in SQL Server, but not with Infoview, I tried exporting to Excel but only have 2003 and when I return the full dataset to then filter through with Excel, the row count exceeds 2003's 65536 row limit, so that options out. I can't output to CSV then import into SQL Server temp tables, because this needs to be a process that will carry forward and I cant manually do it every time due to various restrictions.
Any help would be appreciated.
Thanks in advance
Srivatsa Kondapalli replied
Assuming your objects are NAME, DATE and STATUS, create an object at your report level called [VAR MAX DATE] = max([DATE]) for each [NAME].
Now create another object at report level which is:
[VAR PROCESSED RECORDS]=if([VAR MAX DATE]=[DATE];if([STATUS]="Processed";1;0))
Use this variable as a filter and filter for "1" records. i.e. use [VAR PROCESSED RECORDS] as a filter at your report table level and say [VAR PROCESSED RECORDS]=1. Hopefully, it should return you the results you were looking for.