If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Column Calculation in ReportNet

Started by kbasha2004, 30 Jun 2008 09:59:54 AM

Previous topic - Next topic

kbasha2004

Hi All,

I need to perform the following calculation in a Report Column:

Sum(ProductID where Product name = 'Golf Equiptment' and Product Item= 'Clubs' and the Order Date is between the previous month 1st and last day)

This being June I need to display for May-01-08 to May-31-08

How can I achieve this.

This is urgent please let me know how to do this.

Thanks in advance for the help!

Kbasha

rockytopmark

You want to Total the product IDs?   This seems odd...

kbasha2004

Maybe the example I gave product ID does not make sense, but what I'm trying to achieve is.......

Can you replace PRoduct ID with Requisition ID?  Requisition ID's has values ReqID = 3456, 234, 3567 which meet the criteria for past month.

So now I column value should show  3. Individually I know how to acheieve this.
Count(CASE
WHEN PRODUCT = 'Golf Equipment' AND Product_item= 'Clubs'
THEN REq_ID
END) will give answer 3. So far it is working fine.

The help I'm looking for is how to insert the  calculation for the previous month.

Hope this helps.

Thanks,
Kbasha


bdybldr

Try this...

TO_NUMBER(TO_CHAR(sysdate, 'MM')) - 1.

This will give you the month number for the previous month.

Gopinath

Try

Order Date Between _first_of_month(_add_months(sysdate(),-1)) AND _last_of_month(_add_months(sysdate(),-1))