This project has moved and is read-only. For the latest updates, please go here.

Adding non-values to a Pivot Table

Topics: Resolved
Nov 18, 2010 at 11:01 PM

I'm trying to use Pivot Table Extensiosn to add a column of text - not values.  Is this even possible?  Specifically, our setup has the following dimension to define a Work Breakdown Structure:

[Program Hierarchy].[Project_Task - All Task Levels].[Task Level 1]

Within this dimension, there is a property for Task 1 Name which you can add to the table itself.  However, we have up to 9 different task and I don't want a column per task name.  I was going to use Extensions to consolidate these but I don't know how to access this property from within Extensions.  Is it even possible to add a non-value?

Any help would be appreciated.

Nov 20, 2010 at 2:42 AM

Maybe something like this:

Calculation Name: [Measures].[Task Level]
Calculation:
Max(
 [Program Hierarchy].[Project_Task - All Task Levels].[Task Level 1].Members
 ,IIf(
  IsEmpty([Measures].[Your Primary Measure Here])
  ,Null
  ,[Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Name
 )
)

If that's not quite right, then capture the MDX query using OLAP PivotTable Extensions and ask your question on the SSAS forums, as that's a better place for general MDX questions: http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/

Hope that helps

Marked as answer by furmangg on 6/10/2014 at 4:19 AM
Nov 22, 2010 at 7:01 PM

Thanks, this was extremely helpful!  It worked - to an extent.  Enough to prove to me that you can put query for text, and not just values.  It didn't quite give me the right Name though.  I wasn't involved in setting up our Cube so I don't know all of the details but using CurrentMember.Name returns the 'name' of the task.  In my example, this literally returns the text of the task number, not the associated task name.

The distinction isn't too important, but now that I know I can put non-values in here, it's a good start.  From here, how do I go about accessing the Property associated with [Program Hierarchy].[Project_Task - All Task Levels].[Task Level 1]?  I don't know if our internal terminology is different but when I say Property, I mean the same as right clicking on a row in my table and going to Show Properties in Report.  I can get to the Task Name that way but I need to do it from Extensions.

Ideas?

Nov 22, 2010 at 7:17 PM

Instead of:

[Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Name

You probably want:

[Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task Name")

That should work if there's a Task Name member property on that particular level. Hope that helps.

Nov 22, 2010 at 7:29 PM

That is exactly what I need!  You nailed it, thanks!

I'll still need to do a little modifying so that it pulls the correct Property but this is a great start.  For some reason, our Cube is setup such that there is a different Property per different Task Level.  It's a bit of a pain but I can go through each and get only the valid ones.

Thanks!

Nov 22, 2010 at 8:26 PM
IIf(IsEmpty([Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 1 Name")),
   IIf(IsEmpty([Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 2 Name")),
      0,
      [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 2 Name")
   ),
   [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 1 Name")
)

Running into further issues.  I'm trying to use an IIf statement to see if the data is valid, if not, check the next level but it's not working.  I'm doing this because if I call for Task 1 Name on Task 2, I get #VALUE!.  I tried to check IfEmpty and if ="#VALUE!" but to no avail.  Ideas on how to do this?  My code is above.

Nov 22, 2010 at 9:48 PM

How about this?

case
 when [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Level is [Program Hierarchy].[Project_Task - All Task Levels].[Task Level 1]
  then [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 1 Name")
 when [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Level is [Program Hierarchy].[Project_Task - All Task Levels].[Task Level 2]
  then [Program Hierarchy].[Project_Task - All Task Levels].CurrentMember.Properties("Task 2 Name")
end

Hope that does the trick. If not, it's probably best to continue this question on the Analysis Services forum link I provided above, because this question is just about MDX, not about OLAP PivotTable Extensions.

Marked as answer by furmangg on 6/10/2014 at 4:19 AM
Nov 22, 2010 at 9:53 PM

Bingo!  Thanks!