MDX cube views and string measures

[PavoGa]

Looked, but did not find anything on this particular topic. Apologize in advance if one exists.

Looks like a TI that uses ViewCreateByMDX is unable to read string measures. SValue is not populated (well, it is populated with 0) and looks like the only way to retrieve a cell string value is by using CELLGETS.

Is it possible to construct the MDX in a way to force/convert a return of a string value?

Have also tried explicitly defining the data variable as string with the same result.

[PavoGa]

Okay, WITH MEMBER resolves this problem...

@jimnaff helped with this.

[lotsaram]

Okay, WITH MEMBER resolves this problem...

@jimnaff helped with this.

How exactly? Would be useful to post some example MDX.

[PavoGa]

Okay, WITH MEMBER resolves this problem...

@jimnaff helped with this.

How exactly? Would be useful to post some example MDX.

This code works in PAX and returns values as desired:

Code: Select all

SELECT 
    {    
    [lookup.Measure].[lookup.Measure].[Rollup Account Primary Name]
    }
    ON COLUMNS,
    NON EMPTY
    TM1SUBSETTOSET( [Accounts Master], "Distinct Accounts")  
    ON ROWS  
FROM [lookup.FPRP]
WHERE ([Version].[Active_01],
    [sys.Websheet Control].[Effective])

This code works in a TI. The TI will return the same values as the previous code, but in PAX does not return any values.

Code: Select all

WITH MEMBER [lookup.Measure].[lookup.Measure].[Rollup Account Test] AS
         [lookup.Measure].[lookup.Measure].[Rollup Account Primary Name]
SELECT
    {    [lookup.Measure].[lookup.Measure].[Rollup Account Test]    }
    ON COLUMNS,
    NON EMPTY
    TM1SUBSETTOSET( [Accounts Master], "Distinct Accounts") 
    ON ROWS  
FROM [lookup.FPRP]
WHERE ([Version].[Active_01],
    [sys.Websheet Control].[Effective])
    

This version returns blank values in PAX. Did not test, but expect this to work in TI returning values AND records with blank values.

Code: Select all

WITH MEMBER [lookup.Measure].[lookup.Measure].[Rollup Account Test] AS
         [lookup.Measure].[lookup.Measure].[Rollup Account Primary Name]
SELECT 
    {    
    [lookup.Measure].[lookup.Measure].[Rollup Account Test]
    }
    ON COLUMNS,
    TM1SUBSETTOSET( [Accounts Master], "Distinct Accounts") 
    ON ROWS  
FROM [lookup.FPRP]
WHERE ([Version].[Active_01],
    [sys.Websheet Control].[Effective])
    

[Wim Gielis]

Thank you !
I reproduced this on a small test cube with numeric and string measures and I can see that it works.