I have a query where I need to pull back the latest risk assessment rating for a record, but need to do this for all records from the query. I have MS
SSRS Report Builder (V3) as the only way to access this data and no access to modify the underlying cubes which are provided by a supplier.

My basic MDX which pulls out the data needed is the following:

SELECT

NON EMPTY {
[Measures].[No of Investigations]
} ON COLUMNS,

NON EMPTY { (
[Investigation Indicators and Text Fields].[Investigation Reference Number].[Investigation Reference Number].ALLMEMBERS *
[Risk Assessment Rating].[Risk Rating].[Risk Rating].ALLMEMBERS *
[Risk Assessment Completed Date].[Date].[Date].ALLMEMBERS *
[Risk Assessment Completed Time].[Time HHMM].[Time HHMM].ALLMEMBERS
) } ON ROWS

FROM [Investigation]
which leaves my Date and Time needing to be concatenated. I have carried this out as a calculated field but am unable to then test for a max value as it gives me an error around using an aggregate function.

Is anyone able to provide me with any ideas on how to carry this out, either via the MDX statement or subsequently. Ideally I would like to be able to create the results in two ways - one with all records and a marker showing which is the latest assessment, and one which just pulls back the subset via MDX.

For clarity the source system holds Investigation records as the core item of interest, and these can have multiple risk assessments attached to them (of different types). I need to return the latest risk assessment against each investigation from a single query. If at all possible it would be good to return all investigations, with or without risk assessments, as the missing records need to be quality assured, but I could approach this with a look up.


Thanks for any help, ideas or pointers on this.