Combine two columns using MDX

[Antny]

Hi

I am using a zen page to display dashboard. 

And i am using filters -  drop down box (zen components) to in Zen page. For the filter values am using Cube to fetch and return as Value list and Display list. Values for filters drop down are fetched from cube using MDX Query.

I need to combine two column values into one.

Example: 

I am using a MDX query as "select FName on 1  from [Emptab]" and assigning Fname result set to display list and value list. 

Table:

EId             FName              LName

1                Andre                Roswelt

2                Robert               Winsley

i want MDX code to combine FName and LName and produce a result as "Andre,Roswelt", "Robert,Winsley". 

kindly give me your suggestions.

thanks and regards,

Antony.

[Lexi]

The best way to do this is probably to use %CELL to refer to the other cells, and use + (the MDX concatenation operator). Here is an example that works in the SAMPLES namespace:

 WITH  MEMBER [Test].[Test] AS '%CELL(-2,0)+", "+%CELL(-1,0)' SELECT NON EMPTY {[HomeD].[H1].[City].CurrentMember.Properties("Population"),[HomeD].[H1].[City].CurrentMember.Properties("Principal Export"),[TEST].[Test]} ON 0,NON EMPTY [HomeD].[H1].[City].Members ON 1 FROM [Patients]

Output (as seen in MDX shell):
                        Population   Principal Export               Test
1 Cedar Falls                90,000               iron        90000, iron
2 Centerville                49,000        video games   49000, video games
3 Cypress                     3,000             gravel       3000, gravel
4 Elm Heights                33,194            lettuce     33194, lettuce
5 Juniper                    10,333              wheat       10333, wheat
6 Magnolia                    4,503         bundt cake   4503, bundt cake
7 Pine                       15,060          spaghetti   15060, spaghetti
8 Redwood                    29,192            peaches     29192, peaches
9 Spruce                      5,900                mud          5900, mud