Query Filter on Dimension Level

[Rushikesh_Yeole]

HI,

In info provider, I have 2 dimensions of similar type [material]. I want to display query output only when values of both the dimensions will be same {material 1= Material 2 }.

I have tried replacement path of query but I can not select reference characteristic while creating variable.

I can not add flag on cube level because I need to reload data.

Is there any user exit/ BADI or can be managed at query level? Any thoughts?

[former_member214415 · Accepted solution]

Hi Rushikesh,

This can be done at Query level with help of Formula variable with replacement path and condition .

Let Say we are having following value in Dimension tables:

Sr no.	Mat1	Mat2
1	100	100
2	100	200
3	200	200
4	300	300
5	300	400

As per your scenario only row no 1, 3 and 4 should come in output.

Now in Query designer create formula let name this as 'Formula to compare'

In this formula create one formula variable with replacement path with mat1 char. let say zfv_mat1 and second formula variable with replacement path mat2 let say zfv_mat2 .

Now in formula bar keep them as zfv_mat1 EQ zfv_mat2.

Now output of your query will be like below

Sr no.	Mat1	Mat2	Formula to compare
1	100	100	1
2	100	200	0
3	200	200	1
4	300	300	1
5	300	400	0

Now create condition based on formula to display only 1 .

now you get your desired output as required :

Sr no.	Mat1	Mat2	Formula to compare
1	100	100	1
3	200	200	1
4	300	300	1

Hope you understand to complete your requirement .

Thanks,

Swapna

[ccc_ccc]

Hi Rushikesh,

After understanding your requirement correctly.

Dim1	Dim2	Kf1	Kf2	Result
MT123	MT123	100	200	This record should display
MT234	MT545	300	100	This record should not,

There are two ways for above solution.

1 If report output in excel sheet , please use VB Macros

if cell.value = MT123 and cell.next.value = MT123.

BW-BEX-ET-WB-7X - 7.x Analyzer Frontend - SAP NetWeaver Business Warehouse - SCN Wiki

2 If report output is web based, use JAVA code.

3 If report output excel or web, for better way use virtual characteristics.

Thank you,

Nanda

[sachin_kulshrestha2]

Dear Rushikesh,

Can you elaborate further on you requirement or scenario to help us comment in a better way.

[Rushikesh_Yeole]

HI,

Info provider contains 3 cubes. There are 2 dimensions which are of type material and it contains data in combinations.

Each cube has different dimensions and key figures. I want to display output  where values of both dimensions are same. These dimensions are common in each cube.

Query output only when:

DIM1 DIM2           KF1      KF2

X          X