cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple rows when using reference path variables

Former Member
0 Kudos

Hi all,

Im new to using BEx query designer, this is my first post, a very simple query hope u gurus will help me out so let me explain the problem im facing;I have three charactersitcs product , complaint receive date and complaint resolve date; and there can be multiple complaints recvd on different dates against one product. Now i have to form a report in the following format:

1 to 5 days 6 to 10 days

Product

In the 1 to 5/6 to 10 days columns i have to give the count of the number of complaints resolved in that period; im using reference path variable to calculate the diff' b/w the dates but the problem is for doing that i have to take the recvd and reslvd date fields in the column section (i have kept them hidden) b/c of which i get multiple rows against a product and if i remove these dates from the selection it gives me x as the result; i just need single row for each product indicating the number of complaints which were resolved in the mentioned timeframe.

Hope im able to clearly explain my query

Please advice.

Thanx in advance

FS

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

As I understood you have two dates Complaint received and complaint resolved dates.

So first create replacemenmt path variables on these two and get the dates in number format. I gues you have done that already. Let say Var1 and Var2

Now create a CKF1--> Var1 - Var2

Now create a CKF2 for 1 to 5 days --> (CKF1<=5)1 + NOT (CKF<=5)0

In CKF 2 we have put if condition and it is giving the count as 1 for 1to5 days else 0

Now create a CKF3 for 6 to 10 days --> (CKF>5 AND CKF<=10)1 + NOT (CKF>5 AND CKF<=10)0

In CKF 3 we have put if condition and it is giving the count as 1 for 6to10 days else 0

Now display Prodcut, CKF2 and CKF3 in report. It will give you the total numbers by adding the counters.

I hope it will help.

Thanks,

S

Former Member
0 Kudos

Thanx S but my problem remains; i have created two replacement path variables dt1 and dt2 one for rcvd date and the other for reslvd date then i created a calc. key figure in wich i took their difference; then i created two formulas in the column one for each time frame

now if i display only the three fields i.e. the product and the two formulas as columns i get garbage as a result but if i add the original date fields (rcvd / rslvd dates) to the selection it gives me correct data but i get multiple rows against a product due to differing dates

hope i've made it clearer now

Former Member
0 Kudos

Hi,

You need to pull all the base key Figure/ RKF/ CKF in the report with the received date/ issue date and then you have to set the prperty as "Hide but can be shown"for those. You need to set prperty as "Calculation befor execution" for uqery and used object.

I hope it will help.

Thanks,

S

Former Member
0 Kudos

Thanx everyone i guess i'll have to create a separate KF in the cube in order to do this thanx for all your support and advice

Cheers

Fahad

Answers (2)

Answers (2)

Former Member
0 Kudos

HI Fahad,

I have tried out your scenaior and it has worked out. I have followed a lengthy procedure. This is what i have done.

I have created four key figures, TOTAL, (0-5days), (6-10 days) and (above 10). For TOTAL I have created a formula (End date - Start Date). And in the transformations, I wrote the following code in END ROUTINE

data: wa_itab type tys_TG_1.

loop at RESULT_PACKAGE into wa_itab.

if wa_itab-/BIC/ZZ_PTOT GT 10.

wa_itab-/BIC/ZZ_P3 = wa_itab-/BIC/ZZ_P3 + 1.

endif.

if wa_itab-/BIC/ZZ_PTOT BETWEEN 0 and 5.

wa_itab-/BIC/ZZ_P1 = wa_itab-/BIC/ZZ_P1 + 1.

endif.

if wa_itab-/BIC/ZZ_PTOT BETWEEN 6 and 10.

wa_itab-/BIC/ZZ_P2 = wa_itab-/BIC/ZZ_P2 + 1.

endif.

modify RESULT_PACKAGE from wa_itab.

endloop.

Then in the report level, I have dragged product in Rows and (0-5days), (6-10 days) and (above 10) in Columns. I am sure there may be a better ways of doing this. But it works.

Regards.

Prasad

Former Member
0 Kudos

Thanx Prasad but i want to do this on the query designer level

Former Member
0 Kudos

The problem is you need the chars in the row for the formulas to work even if you hide them you still need them!

You will always get the multiple rows - unless somebody can come up with a clever solution

Normally I would create a kf in the cube

You could start playing with before aggregation or exception aggregation (same thing) - to try and retrieve the detailed dataset for the formulas to work and then aggregate on the presentation layer...

Actually it isn;t a simple query.. it is quite a complicated one! it's asking to do two OLAP calculations which you can;t really do)

There is another way around it - but you aren;t going to like it! It;s a virtual cube calling the query then aggregating the lines up inside a virtual cube bespoke function module

Watching this with interest !!

(same problem like this exists for weighted averages)

former_member189638
Active Contributor
0 Kudos

One product has many complaints. So it is not able to understand which dates to be used for calculating the differene.

Have you created a Formula Key Figure for calculating the Date Difference. Try creating a Calculated Key Figure with an exception aggregation Count with reference characteristic as Product.

Not sure if this will work but give it a try.

Former Member
0 Kudos

thanx Rakesh already done that buddy