I would like some ideas/suggestion about solving the following issue:
I have a cube with the following dimensions: A,B,C,D, M (Measure)
The Measure M has fields called: Amount, Dispute Flag, Dispute Status
I would like to create a Cube view that show only data when M. Dispute Flag > 0
Any help will be appreciated.
Thanks
Cube View Filter Using MDX
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Cube View Filter Using MDX
Hello,
What is your view layout / how do you select elements ?
Do you have several elements in the rows / columns ?
Or for example, 1 dimension in the rows and the other dimensions in the titles ?
What is your view layout / how do you select elements ?
Do you have several elements in the rows / columns ?
Or for example, 1 dimension in the rows and the other dimensions in the titles ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 15
- Joined: Tue Apr 06, 2021 8:44 pm
- OLAP Product: TM1
- Version: 2.8
- Excel Version: 365
Re: Cube View Filter Using MDX
This is the type of filter I am using:
TM1SORT(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , [Product Cube].([Versions].[Actual],[Period Daily].CURRENTMEMBER,[Prod Types].CURRENTMEMBER,[Invoice Dates].CURRENTMEMBER,[Product Category].CURRENTMEMBER,[Product Cube Measures].[Open Invoice Flag]) = 1) , ASC)
When this MDX statement is applied to the cube view It still show products that have [Open Invoice Flag] 1 and 0.
Cube has 5 Dimensions on Rows and 3 on Column, Product is on a row
Thanks
TM1SORT(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , [Product Cube].([Versions].[Actual],[Period Daily].CURRENTMEMBER,[Prod Types].CURRENTMEMBER,[Invoice Dates].CURRENTMEMBER,[Product Category].CURRENTMEMBER,[Product Cube Measures].[Open Invoice Flag]) = 1) , ASC)
When this MDX statement is applied to the cube view It still show products that have [Open Invoice Flag] 1 and 0.
Cube has 5 Dimensions on Rows and 3 on Column, Product is on a row
Thanks
- PavoGa
- MVP
- Posts: 616
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Cube View Filter Using MDX
Which dimensions are used in context (title dimensions) and which are used in rows/columns?
There are a couple of approaches. The easiest, by far, is with one row dimension and one column dimension, one of which being the measure dimension. That MDX is pretty straightfoward and does not require any reference to the title dimensions as those are automatically taken into account as context. If your business need can be fitted within this framework, this is by far the fastest performing option.
Assuming the Measure dimension is the column, it gets a wee bit more difficult if two dimensions are used on the row. In this case, you'd need two MDX statements, one for each dimension which joins the other row dimension, using GENERATE and FILTER. Depending on the size of the dimensions, this could be a bit slow.
In this case, if you want three or four dimensions on the rows, then an MDX view is going to be the ticket, although you could continue to wrap GENERATE around the queries described above, and it, too, may be a bit slow at times. I've pretty much quit using MDX views in TM1 except in special circumstances (like this!). I'll get an example in a bit if someone else does not provide one.
There are a couple of approaches. The easiest, by far, is with one row dimension and one column dimension, one of which being the measure dimension. That MDX is pretty straightfoward and does not require any reference to the title dimensions as those are automatically taken into account as context. If your business need can be fitted within this framework, this is by far the fastest performing option.
Assuming the Measure dimension is the column, it gets a wee bit more difficult if two dimensions are used on the row. In this case, you'd need two MDX statements, one for each dimension which joins the other row dimension, using GENERATE and FILTER. Depending on the size of the dimensions, this could be a bit slow.
Code: Select all
# assume A and B are title dimensions, C & D are row dimensions, M the column dimension.
# subset for [B]
GENERATE( TM1FILTERBYLEVEL( TM1SUBSETALL( [A] ), 0),
FILTER( TM1FILTERBYLEVEL ( TM1SUBSETALL( [B] ), 0),
[cube].([A].currentmember, [B].currentment, [M].[Dispute Flag]) > 0))
# Need the same subset as above for [A] just reverse the dimensions in the query.
Ty
Cleveland, TN
Cleveland, TN
- PavoGa
- MVP
- Posts: 616
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Cube View Filter Using MDX
MDX example View as promised:
Code: Select all
SELECT
[M].members ON COLUMNS,
FILTER( [A].members *
[B].members *
[C].members *
[D].members,
[M].[Dispute Flag] > 0)
ON ROWS
FROM [cubename]
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 15
- Joined: Tue Apr 06, 2021 8:44 pm
- OLAP Product: TM1
- Version: 2.8
- Excel Version: 365
Re: Cube View Filter Using MDX
Thanks a lot "PavoGA" I was able to follow your suggestions and I created a view based on you MDX query
SELECT
[M].members ON COLUMNS,
FILTER( [A].members *
.members *
[C].members *
[D].members,
[M].[Dispute Flag] > 0)
ON ROWS
FROM [cubename]
The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.
Thanks
SELECT
[M].members ON COLUMNS,
FILTER( [A].members *
.members *
[C].members *
[D].members,
[M].[Dispute Flag] > 0)
ON ROWS
FROM [cubename]
The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.
Thanks
- PavoGa
- MVP
- Posts: 616
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Cube View Filter Using MDX
This should be a fairly straightforward MDX view, but there are some differences in how the various user interfaces handle MDX in TM1. I do not have one readily available, but you might search the forum for some of the discussions on it.
Ty
Cleveland, TN
Cleveland, TN