Page 1 of 1
Help on TM1RptRow? (MDX)
Posted: Fri Jun 15, 2012 5:28 pm
by schlemiel29
I found this statement to solve my problem:
TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove,MDXStatement, Indentations, ConsolidationDrilling)
Does somebody know, what to fill in MDXStatement for filtering on a condition, e.g. Elementname = $B$4?
Thanks in advance
Dirk
Re: Help on TM1RptRow? (MDX)
Posted: Fri Jun 15, 2012 6:30 pm
by rkaif
You will have to write a MDX statement specific to you requirement. Example is:
Code: Select all
Filter(
TM1FilterByLevel(TM1SubSetAll([YourDim]) , 0)
,
[CubeName].(
[Dim1].[Element]
, [Dim2].[Element]
, [Dim3].[", $B$4 , "]
, [Dim4].[Element]
)
<> 0
)
Re: Help on TM1RptRow? (MDX)
Posted: Fri Jun 15, 2012 8:07 pm
by schlemiel29
I did't get.
How it is used?
I tried:
"{Filter( TM1FilterByLevel(TM1SubSetAll([Country]), 0) , [Sales].([Country].[China]) <> 0)}"
generated by this formula in Excel:
="{Filter( TM1FilterByLevel(TM1SubSetAll([" & B10 &"]), 0) , [" & Cube & "].([" & B10 & "].[" & B18 & "]) <> 0)}"
This Value I linked into
=TM1RPTROW($B$12;"Country";"Rep_1";"";"Name";0;B$11)
as the last paramter ($B$11)
But the reports stays empty. Where is my error?
Re: Help on TM1RptRow? (MDX)
Posted: Sat Jun 23, 2012 8:04 pm
by Jaspe
Hi !
It depends if you want to filter on an element of a dimension or a value in the cube...
For examle, if you want to filter on "China" in dimension "Country", you wan write {[Country].[China]} on cell B11.
And in your fomula TM1RPTROW, you can write :
=TM1RPTROW($B$12;"yourserver:Country";"";"";"";0;$B$11)
If you want to filter your active form with a condition of value in a cube, you can use the method explained by schlemiel29.
But be sure referencing all dimensions (except country) in the filter...
For example :
If you have a cube "Cube_Sales" with 4 dimensions : Version, country, Product, and Indicator
If you want to filter you active form with sales > 0, you must write :
Filter(
TM1FilterByLevel(TM1SubSetAll([Country]) , 0)
,
[Cube_Sales].(
[Version].[Actual 2012]
, [Product].[Product1]
, [Indicator].[Sales]
)
> 0
)
You can't write :
Filter(
TM1FilterByLevel(TM1SubSetAll([Country]) , 0)
,
[Cube_Sales].(
[Indicator].[Sales]
)
> 0
)
Regards,