Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS