Help on TM1RptRow? (MDX)

sjgogo

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

ole2002ole

You will have to write a MDX statement specific to you requirement. Example is:

Code: Filter(
    TM1FilterByLevel(TM1SubSetAll([YourDim]) , 0)
    ,
    [CubeName].(
        [Dim1].[Element]
        , [Dim2].[Element]
        , [Dim3].[", $B$4 , "]
        , [Dim4].[Element]
        )
        <> 0
    )

socaca

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?  

Esuom

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,