How to create a custom variable to sum based on logic

[Gen8888]

Hello all,

I have a question, I'm currently using BOBJ Crystal Reports 2008 and pulling from a ECC6 SAP Datasource. I've setup a formula which pulls data based on current date using the following:

{BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE} in dateserial(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+4,1)

Now that I have my output for Current Month + next 3 months I need to also create a variable which sums all the previous Month's value called "Past Due" based on current date. I've tried the following but can not successfully make it work. Can anyone help.

WhilePrintingRecords;
Global NumberVar PASTDUE := 0;

PASTDUE := PASTDUE +({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS});

This is how I'd like the Final Output to look like:

Past Due 1/2013 2/2013 3/2013 4/2013
Field Supply 15 2 5 1 3
Field Demand 10 1 2 9 1
Balance 25 3 7 10 4


Thank you in advance!

-Andy

 

[IanWaterman]

This should be a separate formula called

@reset
WhilePrintingRecords;
Global NumberVar PASTDUE := 0;

and placed in report or apprpriate group header

@eval // place in details

WhilePrintingRecords;
Global NumberVar PASTDUE;
PASTDUE := PASTDUE +({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS});

@display //place in report or group footer
WhilePrintingRecords;
Global NumberVar PASTDUE;

Ian

 

[Gen8888]

Hi Ian, thanks for the quick reply. I have a question on what you posted if you could help clarify please. I would need to create 3 different variables, @reset, @eval, @display? Also for the logic to sum the field called "BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS", does that look correct to you and I also need a condition where I only sum the records for this field for any dates prior to current date.

Thank you

-Andy

 

[Madawc]

Try a crosstab, using months as rows. Base the totals on a formula like

if PASTDUE = 0
then {BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS}
else 0

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[IanWaterman]

Formula is fine, just add an extra condition to the evaluate

@eval // place in details

WhilePrintingRecords;
Global NumberVar PASTDUE;

If datefield < currentdate then
PASTDUE := PASTDUE +({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS});

if your date field is datetime

If date(datefield) < currentdate then
PASTDUE := PASTDUE +({BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS});

Ian

 

[lbass]

You don't really need to use variables at all. You could create conditional formulas like this:

if {table.date} < currentdate then
{BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS}

Place this formula in the detail section and right click on it and insert a sum on it at the group and/or report level.

-LB

 

[Gen8888]

Hello all,

Thank you for the replies. lbass, I tried to simply add a formula as you stated above but that works only for records already read from the database and in the output. I need to read back in the database and then perform the logic you have written. I tried WhileReadingRecords but I received no results back.

WhileReadingRecords;

if{BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.AVAIL_DATE} < currentdate then {BAPI_MATERIAL_STOCK_REQ_LIST.T_MRP_TOTAL_LINES.RECEIPTS}

Thanks.

 

[lbass]

If you want to report on previous data, the data has to be present in the dataset for your report--allowed in via the record selection formula. Or instead you could add a subreport that includes the previous data.

-LB