Skip to Content
0
Former Member
Dec 05, 2011 at 02:31 PM

Null values in a cross-tab & calculations within details section

111 Views

Hello everyone!

I would very much appreciate your help with two issues in Crystal Reports:

1 - If I have a cross-tab as it is shown below:

FY1 FY2 FY3

Item ID (Sum of $, Qty)

How can I show the FY3 column if there is no data for it (null values for the given year )? Is it possible?

2 - My report structure looks as follows:

GH1 - FY

GH2 - Invoice # (when a discount is calculated, the result is placed here)

GH3 - Category/class of items

Details -

Various items * by a value from a discount formula below

Discount (only one per each invoice) - a negative value in $

GF3 - blank

GF2 - Here I placed a total amount per invoice

GF1 - blank

Since we have a discount as an amount per each invoice, I was trying to calculate it using a total amount from an invoice and then apply it to each itemID (in details section):

If {LineItem.ItemID} like "discount" //we have this text written for each discount

then

numbervar x:= {JrnlRow.UnitCost} * {JrnlRow.StockingQuantity};

if x < -1

then

( x * (-1)) / (x * (-1) + (JrnlHdr.MainAmount} * (-1))

//then the total amount per each itemID is multiplied by this formula.

And it works. Even so I played with formula evaluation time, it works only when recalculating report, but it doesn't when refreshing a database.

I would like to ask professionals, is there any other way to make it work while refreshing database as well? Or: should I change the structure of the report?

Thank you very much in advance.