Help please - maximum xxx witthin set xxxx

[Una_R]

Hi

I am struggling with the syntax for "within set"

I have a table of test cases, each test case can have multiple results.  Each result also has a run date.

For each test case, I want to select ONLY the most recent result

Test Case   Result   Run Date
TC1            pass     1 Jan 2014
TC1            fail     2 Jan 2014
TC1            pass     11 Jan 2014
TC2            fail         1 Jan 2014
TC2            pass       9 Jan 2014

should give
Test Case   Result   Run Date
TC1            pass     11 Jan 2014
TC2            pass       9 Jan 2014

I have been trying

maximum( [TestCasePerspectiveSummary].[End Date] within set  [TestCasePerspectiveSummary].[Test Case Name] )

but get OP-ERR-0061

      Filter expressions must evaluate to Boolean. Error processing filter 'maximum( [__ns_0].[__detail_dim].[__detail_dim].[__detail_level].[End Date] within set [__ns_0].[__detail_dim].[__detail_dim].[__detail_level].[Test Case Name] )'.


Is the correct approach but something swry with the syntax or is there an easier way to do this?

Thanks

[CognosPaul]

There are a few ways of doing this, but it depends on what type of data source you're using. The within set method is generally only used against dimensional sources. In your case I suspect this is a relational source.

Try the filter: [Run Date] = maximum([Run Date] for [Test Case])

Quick edit: A data warehouse should be built to the needs and specifications of the analyses needed. It looks like you're referencing a slowly changing dimension. I strongly recommend adding a new field that flags the most recent row for each test case. Once you have that the filter would be an easier, and far more efficient, [Most Recent] = 'Yes'.

[Una_R]

Hi Paul

Thanks so much! This works perfectly.

Una ;D