Using two measures each with a prompt.. Gives wrong results

[shehul]

I have two measures.. 'Net sales monthwise' and 'net sales yearwise' mapping to the same database field
For each I have added a prompt for the month name and the year respectively.
Individually they work fine. However if I use both together in a report, they give the same result, which is wrong.

Any ideas on how to make them work properly together.

 

[Subhashab]

can only give you solution if you can tell us the table structure and the sql generated.

are u using decode or case in the measure object.

 

[shehul]

Yes I am using COALESCE (DB2 - Its similar to DCODE)
I have a one to many relationship between invoice and creditnote table and due to the fan trap i have an invoice alias

here's the sql generated in the report
SELECT
COALESCE(TTM_INVOICE.INV_INVOICE_AMT,0) - COALESCE(TTM_CREDIT_NOTE.CNT_CREDIT_NOTE_AMT,0),
COALESCE(TTM_INVOICE.INV_INVOICE_AMT,0) - COALESCE(TTM_CREDIT_NOTE.CNT_CREDIT_NOTE_AMT,0)
FROM
TTM_INVOICE LEFT OUTER JOIN TTM_CREDIT_NOTE ON TTM_INVOICE.INV_INVOICE_ID=TTM_CREDIT_NOTE.CNT_INV_INVOICE_ID
WHERE
( monthname(TTM_INVOICE.INV_INVOICE_DUE_DT)=@Prompt('Enter Month','A','Time Period\Month',MONO,CONSTRAINED) )
AND ( year(TTM_INVOICE.INV_INVOICE_DUE_DT)=@Prompt('Enter Year','N','Time Period\Year',MONO,CONSTRAINED) )

Need to somehow seperate the queries...

 

[blom0344]

First off all the coalesce function is not the counterpart of the decode, but rather the DB2 equivalent of the nvl function in ORACLE. It selects the first not null value from a list of input, reading from left to right.

Secondly, SQL does work differently than you expect. In the where clause you can not expect the first expression to work on the first measure and the second one to work on the second measure.
In your example the combined effect from the WHERE clause goes for every measure.

You might want to solve this using a UNION query, splitting the year from month set....



T. Blom
Information analyst
tbl@shimano-eu.com

 

[shehul]

Oops my mistake yeah COALESCE is a counterpart of NVL... I tried manually changing the query and make an union however it gave me some error.. Is there any way I can force BO to create to separate queries for the same...

 

[blom0344]

Copy / paste the union query and I'll have a look, a UNION query is best possible solution If it qualifies, because it will return everything in one dataset!!!!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

From the Query Panel you will find a button next to UserObjects. Click on that BO will do the Union and change the required objects and run. Remember the corresponding objects should be of same data type.

Good Luck
Sri

 

[shehul]

Thanks, did manage to get it right... the only grouse is i would need to manually change the sql whenever i need to use both the amounts together...thats almost all the reports ;-(

 

[blom0344]

With proper design and some tuning you will want to get a definitive solution. Manually changing the SQL is something one should do for ad-hoc situations , not for a series of existing reports....
If you have trouble digesting which data belongs to either monthly or yearly calculations you may introduce objects with a 'fixed' value to get a column to select on in the report.



T. Blom
Information analyst
tbl@shimano-eu.com

 

[shehul]

Sridharan, that doesnt work as BO puts the data in the same object, while I need two separate objects.

 

[Sridharan]

I don't think so. Why you want to change the SQL Manually???? You should try to avoid it as much as possible.

Sri

 

[blom0344]

Using a straight UNION query WILL put the data in one object, hence my suggestion to add a extra field to be able to create proper report variables.

This is a life example in ORACLE SQL:

-----------------------------------------------------------
(SELECT 'BIKE' AS TYPE,RTRIM(TO_CHAR(SYSDATE,'DAY')||TO_CHAR(SYSDATE,'WW'))AS STAMP,PAL_PRO_CODE,PAL_MAX_QTY,SUM(MOV_QTY_MOVED) FROM PRODUCT_ALLOCATION A ,STOCK_MOVEMENT B
WHERE A.PAL_PRO_CODE = B.MOV_PRO_CODE AND
MRA_TYPE = 'PIBP' AND PAL_ZON_ID = 'BPI' AND
MOV_DATE_ACTIONED > '19-JUN-03' AND MOV_DATE_ACTIONED < '27-JUN-03'
GROUP BY SYSDATE,PAL_PRO_CODE,PAL_MAX_QTY)
UNION
(SELECT 'FISHING' AS TYPE,RTRIM(TO_CHAR(SYSDATE,'DAY')||TO_CHAR(SYSDATE,'WW')) AS STAMP,PAL_PRO_CODE,PAL_MAX_QTY,SUM(MOV_QTY_MOVED) FROM PRODUCT_ALLOCATION A ,STOCK_MOVEMENT B
WHERE A.PAL_PRO_CODE = B.MOV_PRO_CODE AND
MRA_TYPE = 'PIFP' AND PAL_ZON_ID = 'FPI' AND
MOV_DATE_ACTIONED > '19-JUN-03' AND MOV_DATE_ACTIONED < '27-JUN-03'
GROUP BY SYSDATE,PAL_PRO_CODE,PAL_MAX_QTY)
----------------------------------------------------------

Observe that an additional object 'type' is added , which sets to either 'FISHING' OR 'BIKE' (i.e. YEAR / MONTH ) as in your case. With such an object you can create the proper report variables !!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[skrandel]

Easier answer is to create an alias of the fact table that handles the monthly stuff.

Then you can put your prompt in the join to that fact table.

Build 2 contexts and you're done.

BO will automatically create 2 queries.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

skrandel@baseconsulting.com