3

I am trying to add a calculated member to an MDX query which will create a set of "Age Bands" for people in my database. I have a measure which contains the age of each person.

I have started with this MDX

SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

I have another measure ([Measures].[Age]) which I would like to band up and return with the rows, so eg anyone aged 0-25, 26-50 etc

Here is what Ive tried

WITH SET [Age Band]
    AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes],
        [Age Band]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

However the error I get is

The function expects a tuple set expression for the argument. A string or numeric expression was used.

What am I doing wrong? Is there an easier/better way to do this without chaining IIF commands?

migrated from stackoverflow.com Jul 9 '14 at 13:04

This question came from our site for professional and enthusiast programmers.

5

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

4

Try

WITH Member [Age Band]

Instead of

WITH set[Age Band]

Since you are doing a calculation based on the [Measures].[Age]

Try the following and see if you can work with WITH MEMBER

with member [Age Band] 
AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))

SELECT 
   {[Age Band]  } DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS,
   {
    (
         [People].[Gender].[Gender]
    )
  }  DIMENSION PROPERTIES member_caption ON ROWS
FROM  [MyCube]
  • The Measures hierarchy already appears in the Axis0 axis. – Jamiec Jul 8 '14 at 10:52
  • If you do CREATE SET [Age Band] AS '1' instead of your IIF expression, and execute - do you get any errors from your rows configuration? – Stephen Borg Jul 8 '14 at 10:56
  • I dont have a CREATE SET I have a WITH SET - if I make it WITH SET [Age Band] AS "1" the error is "The function expects a tuple set expression for the argument. A string or numeric expression was used." – Jamiec Jul 8 '14 at 11:20
  • I just edited my answer. Does the 2nd query work for you? If it does, you would need to cross join the Gender and Other Attributes. – Stephen Borg Jul 8 '14 at 11:42
  • Btw, you can ignore the dimension properties in my answer. – Stephen Borg Jul 8 '14 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.