Help on TM1RptRow? (MDX)

[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

[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
    )

[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?

[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,