Page 1 of 1
MDX cube views and string measures
Posted: Fri Jul 26, 2019 9:29 pm
by 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.
Re: MDX cube views and string measures
Posted: Mon Jul 29, 2019 1:48 pm
by PavoGa
Okay, WITH MEMBER resolves this problem...
@jimnaff helped with this.
Re: MDX cube views and string measures
Posted: Mon Jul 29, 2019 3:56 pm
by lotsaram
PavoGa wrote: ↑Mon Jul 29, 2019 1:48 pm
Okay, WITH MEMBER resolves this problem...
@jimnaff helped with this.
How exactly? Would be useful to post some example MDX.
Re: MDX cube views and string measures
Posted: Tue Jul 30, 2019 4:12 pm
by PavoGa
lotsaram wrote: ↑Mon Jul 29, 2019 3:56 pm
PavoGa wrote: ↑Mon Jul 29, 2019 1:48 pm
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])
Re: MDX cube views and string measures
Posted: Tue Jul 30, 2019 6:33 pm
by Wim Gielis
Thank you !
I reproduced this on a small test cube with numeric and string measures and I can see that it works.