different sql instructions for each measure

[saramaia]

Hello everyone!

I started using BO 2 months ago and I have this problem: i´m defining two measures (example: one that counts the clients with the age between 10 and 30, and other between that counts the ages between 30 and 50). The "where" clause of one measure excludes the other...
My question is: is there a way i can generate a sql instruction for each measure? This way, i wouldn´t have the problem i described above...

Can anyone help me? I´m running out of time here...

Thank you for your help

P.S. If there is anyone who didn´t understand my question please say so, and i will try to explain better.

 

[PAULSGS]

You don't mention what database you are using so I'll assume Oracle, otherwise I hope you can work out an equivalent.

Firstly you must redefine your ranges as in your example 30 occurs in both groups. So I'll assume your ranges are 10 -30 and 31 - 50

You can then define the objects as

decode(sign(Age -31),-1,1,0) ... any age under 31 returns 1
decode(sign(Age -31),-1,0,1) ... any age 31 & over returns 1


In the first case the SIGN function is giving '-1' only if 'Age - 31' is a negative amount, The DECODE function examines the output from SIGN & converts '-1' into '1, and anything else to '0' (zero).

In the second case the decode converts '-1' into '0 and '0 & '1' into '1'.


I hope I've got all '0's & '1's in the right position and that the principle can be applied to your problem. The essence of it is that you can dispense with the "where" clause in your two objects as the decode will discard the unrequired results.

BTW you might want to wrap a COUNT function around the objects as defined above

 

[Guest_imported]

Yes, sometimes i used that solution.. And it works.
Probably i dindn´t give the right example because it can be solved using the way you described... But what if it can´t be resolved that way? Because i´ve tried and tried and it can´t be solved that way. So, my question is still the same: is there a way i can run different sql instructions for different measures??

Thanks again

SaraMaia

 

[PAULSGS]

What goes wrong with my suggested solution?
Have you got another example?

 

[saramaia]

Yes Paul, i have another example.
Look at this:

you have two tables:

- the first
agreement | delay
------------------------
1 | 4
7 | 32
8 | 43
9 | 11
22 | 0

- the second

agreement | number | value | payed
---------------------------------------------
1 | 1 | 300 | N
1 | 2 | 100 | N
7 | 1 | 200 | N
7 | 2 | 150 | N
8 | 1 | 100 | N
8 | 2 | 100 | N
9 | 1 | 100 | N
9 | 2 | 200 | N
22 | 1 | 100 | N
22 | 2 | 100 | N



the first measure i want is the number os agreements with the delay between 0 and 30.
Easy : in the select clause i have "
(case when ( ICS_V_AGREEMENTS.MAXIMUM_DELAY >= 1 and ICS_V_AGREEMENTS.MAXIMUM_DELAY < 31) then
1 else 0 END) &quot; -> and sum in the end

the second measure is the total value of what is not payed:
I wrote this: &quot;
(case when ( ICS_V_AGREEMENTS.MAXIMUM_DELAY >= 1 and ICS_V_AGREEMENTS.MAXIMUM_DELAY < 31) then
(case when ( ICS_V_AGREEMENTS_DETAIL.payed_date is null) then
ICS_V_AGREEMENTS_DETAIL.instalment_amount else 0 END)
else 0 end) -> sum in the end
&quot;

I want to know how many agreements have a delay between 1 and 30 and the total value.
From the table analysis i have the agreement 1 and 9 and for each one (from the table two) i have two values...
The result is something like

1 | 300
1 | 100 ( these first twho corresponding to agreement 1)
1 | 100
1 | 200 ( these last twho corresponding to agreement 9)

In the end i have the righr value but not the right number of agreements. Should be two and not 4...

Did i make myself clear? Hope so

 

[PAULSGS]

I have had a look at your example and suspect you might need 2 data providers which you can link together see the response to your other thread thread393-329795

 

[saramaia]

Hello PAULSGS,

thanks for your explanation.
Along with the answer to my other thread i know now what to do.

SaraMaia