For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)