Sum LastNonEmpty measures in a chart within SSRS
Hi, I have a Reporting Services report that contains a chart using a dataset on a cube. In this chart, I want to show the quantity of employee at the end of the month per JobCategory, Departement and Quarter. The measure for the quantity of employee at the end of the month is defined as a LastNonEmpty measure and the time dimension is a periodic snapshot at month level with a hierarchy for Year/Quarter/Month. With the set of data below I should see 2 Managers in the Finances Department in Q1 of 2012, not 8. JobCategory Department Year Quarter Month Quantity Manager Finances 2012 Q1 January 3 Manager Finances 2012 Q1 February 3 Manager Finances 2012 Q1 March 2 I have no problem doing that with SSAS built-in navigator, PerformencePoint or Excel, and I can't find what's wrong with my report. Anyone have a clue? Thanks in advance! Dominic
June 20th, 2012 4:06pm

Hello Dominic, From your description, we can see that the expected result is only the last value of the data set instead of the sum of the values (3 + 3 + 2 = 8). In the report, I suggest you check the formulas of the cell to see whether it use the correct formula. For example, it used SUM() instead of Last(), or Last sum function is not write correctly. If it still have the problem, please post the settings on the error part, such as the Last sum function, so that we can investigate with you further. Regards, Edward Edward Zhu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2012 5:53am

Hi Edward, I had tried using the Last() function but it wasn't working as expected. Following your reply, I tried again and realized that something was wrong with the Last () function but only when using the All member of the hierarchy. Let me give you an example with a bigger data set : JobCategory Department Year Quarter Month Quantity Manager Finances 2012 Q1 January 3 Manager Finances 2012 Q1 February 3 Manager Finances 2012 Q1 March 2 Manager IT 2012 Q1 January 1 Manager IT 2012 Q1 February 1 Manager IT 2012 Q1 March 1 First you have to know that Department in the dataset is used has a parameter for my chart. In other words, it's not part of the chart itseft. But since it's part of the dataset, the Last() function considers it and provide the result base on the last row for the JobCategory, Department and year/quarter/month. So, when I filter on a specific Department (ex. IT), the Last() function return the last row in IT per JobCategory i.e. 1. This works fine and is the expected behavior. But, when I select the All Member or more than one Department the Last() function return the result base on the last row in Finances and IT per JobCategory i.e. 1. What I need is the SUM() of the Last() row per Department i.e. 3 (2 in the Finances dept + 1 the IT dept in the last month of Q1 i.e March). I did try using Sum(Last(Quantity)) in the expression field but it's not allowed. Is there a way to achieve what I want? Regards, Dominic
June 22nd, 2012 10:58am

Hi, I finally found the solution to the problem. With semi-additive measures from a cube, we need to use the Aggregate function! Dominic
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2012 2:27pm

I tried your solution and when I run the report it just spins... What data provider are you using?
September 10th, 2012 2:28pm

I tried your solution and when I run the report it just spins... What data provider are you using?
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2012 2:30pm

I'm using Microsoft SQL Server Analysis services Native provider with integrated security.
October 3rd, 2012 10:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics