and in CubeA_measure dimension has a numeric element of IsActive, which can be 0 or 1.

I have another cube called CubeB with the following dimensions:

- Period
- Region
- Product
- Customer
- Account
- CubeB_measure

In CubeB_measure dimension, I have a numeric element of Amount.

I need to create a report out of CubeB to display total amount based on a filter in CubeA for its measure IsActive.
For example, display sum of Amount from CubeB for each period where Region-Product combination is Active as per CubeA data.

I do not want to use rules and feeders to bring data from CubeA to CubeB or vise versa as I have lets say 20 million populated cells in CubeA and 1billion populated cells in CubeB.

How do I write an MDX query to give me this type of a filtered result from CubeB based on filtered values as per CubeA?

Thanks for the comment. I know it is easy when exporting data with a process. But I want to create a report, rather than an extract. I prefer having an MDX that I can parametrize and use in a websheet report.

Indeed, I have a lot of measure elements in CubeA for which I want to filter cubeB data depending on ad-hoc reporting requirements. Hence I do not want to create seperate C level measures in cubeB (with C level rules). Moreover, some of the measures in CubeA are string measures rather than numeric measures. Hence I cannot apply the logic of having C level measures with C-level rules in CubeB for those.

1) Create a zero-suppressed active form report on Cube A. This would give you the rows where the IsActive value is 1. Add a VIEW statement for Cube B. For the rest of the report use DBRW formulas that point to Cube B and the new VIEW statement. The caveat is this is really only viable when the choice for Period, Customer and Account are title dimensions, or are fixed in columns.

Thanks for the comment. Yes, this could be done. But remember that my cubeA has 20million populated cells for IsActive measure. In my real case, I will have thousands of elements in each dimension. Hence performing a zero suppression on both dimension in Excel will not work. In my ideal report on CubeB, I do not have CubeA dimensions in rows or columns. I will have other dimensions of Cube B in rows and columns. But even if I have those cubeA dimensions nested in Rows, I have to filter my context based on other dimensions of CubeB to reduce the size of the report rows to focus my analysis.

1) Create a zero-suppressed active form report on Cube A. This would give you the rows where the IsActive value is 1. Add a VIEW statement for Cube B. For the rest of the report use DBRW formulas that point to Cube B and the new VIEW statement. The caveat is this is really only viable when the choice for Period, Customer and Account are title dimensions, or are fixed in columns.

I didn't know until I saw this post that it was possible to do an MDX using values of a cube as variables so I can't really help more than that sorry.

Okamasu

I am aware of those filter functions of MDX. In a subset of Region dimension, I can filter my Region list based on data in CubeA. In a subset of Product dimension, I can filter my Product list based on data in CubeA. Then I can use these subsets in CubeB report. If I use one of these subsets in a view of CubeB, it works ok. So far so good. But when I want to use both subsets in rows or both in columns, then it does not work. TM1 cube viewer gives an error message. It does not filter list in one dimension based on the filtered list in the other dimension, when the filtering is based on CubeA data.