Subreport doesnt pass zero value to main report

[YA]

I am creating a report that shows Credit Card revenue by state. I have 2 subreports on the mail report. One of them shows all confirmed credit card transactions and the other one shows all refund(cancelled) transactions during the data range.

The problem I have is like this:

CA and MA have values for both subreports but MD does not have any transacitons for cancelled one

GH#1a - Location Name
GH#1b - Subreport to show confirmed transactions
GH#1c - Subreport to show cancelled transactions


(e.g.)
CA
Confirmed - $11500
Cancelled - $5000

MA
Confirmed - $5000
Cancelled - $1000

MD
Confirmed - $2500
Cancelled - $1000 (the subreport is supposed to return zero value but it returns the value that previous state has)


I am not sure what I am doing wrong..
I would appriciate any help! If this is not clear, please ask me questions and I will try to provide you detail information as much as possible....

Thanks very much in advance.

 

[rogar]

I assume you are using shared variables to pass subreport values back to the main report and that you are grouping by state. You need to add a formula in the main report State group header that resets the shared variable values to zero.

 

[Ngolem]

sorry it isn't clear what you are doing....are you displaying the subreport(s) or sending the values back to the main report via shared variables.

it seems you are doing the latter for the vague description.

For that last example where the value is supposed to be zero...I think the problem may be that the subreport returned a null value when it collected the information ....then it bombed when it hit the formulas that processed the data....as a result the Shared variable was not updated...and therefore repeated itself

If it is possible to encounter a null result then you should initialize the result you want returned to zero in the subreport header...then in the formulas test for a null value where appropriate...then display the value of the shared variable (even though it may be suppressed ) in the subreport report footer. Jim Broadbent

 

[YA]

Thanks very much for the responses!!!!!!

Sorry that it wasnt clear enough. Let me add some more notes here.

Both subreports are passing shared variables to the main report. I have fomulas
to initilize the shared variables to zero in the main report in the GH#1a Location Name. I think the problem appears to be a "null" issue as Ngolem mentioned.

I have 3 formulas in the subreport.

@Initialize (to initialize the result to zero in the subreport header)
whileprintingrecords
NumberVar revenue := 0;

@Evaluate (to deal with a blank/null value)
whileprintingrecords;
If IsNull({OrderTotalField}) then
NumberVar revenue := 0
Else
NumberVar revenue := {OrderTotalField}

@Display
whileprintingrecords;
Numbervar revenue;

Then I created a shared variable to send the value back to the main report

@SharedVariable
whileprintingrecords;
Shared NumberVar sharedrevenue := @Display

I also selected 'Convert null field to Default' in the report options so that a field that contains a blank will display as a zero...


The result is still the same after doing these things. If a subreport returns a value(not null), all formulas in the subreport show the results(e.g. @initialize returns 0 and @Evaluate returns 15000) and the shared variable returns the correct information. But if a subreport returns a null value, none of the formulas in subreports show any values (even @Initialize doesnt show anything) and the shared variable isnt updated and repeat the previous value...


I would appriciate any help!!
Thanks very much.

 

[Ngolem]

first I would remove the "convert null field to default" from the report and subreport

It looks like the subreport is retrieving only one value for {OrderTotalField} since you are not summing the numbers. If you are supposed to sum the numbers then you aren't and all you are getting is the final value.

I would compose your formulas this way


@Initialize (placed in the report header)

whileprintingrecords;
shared NumberVar sharedrevenue := 0;

@Evaluate (placed in the detail section)

whileprintingrecords;
shared NumberVar sharedrevenue;
If not IsNull({OrderTotalField}) then
sharedrevenue := {OrderTotalField};

OR IF YOU ARE SUMMING RECORDS

whileprintingrecords;
shared NumberVar sharedrevenue;
If not IsNull({OrderTotalField}) then
sharedrevenue := sharedrevenue + {OrderTotalField};


@Display (placed in the report footer)

whileprintingrecords;
Numbervar sharedrevenue ;
sharedrevenue ;


There is no need for this formula, @SharedVariable or the variable revenue...unless there is more to this subreport than you have shown

now you must check something else....you must check your RECORD SELECTION FORMULA

you must test for null on any fields that are used in the selection criteria....like this

(isnull({table.field}) or {table.field} = <something>) and

unless you do this your subreport will bomb before it gets to evaluate anything.

Jim Broadbent

 

[YA]

Thanks for your reply Ngolem.

I composed my formulas in the subreport the way you suggested.

Then I am not sure what I should do with the part of your suggestion. I need help on this part.

>you must test for null on any fields that are used in the selection criteria....like this
>(isnull({table.field}) or {table.field} = <something>) and
>unless you do this your subreport will bomb before it gets to evaluate anything.

Please let me know.
I appriciate for your time and help!

 

[Ngolem]

no problem...you are 3/4 of the way there.

In the design screen of the subreport go to the main menu

and click on Report|Edit Selection Formula|Record

Here is the formula for filtering out unwanted data records

You must test for null here as well

Applying this format to any part of the formula there

(isnull({table.field}) or {table.field} = <something>) and


for example if the record select is

{Table.field1} = 2 and
{Table.field2} = {?someParameter}

then change it to

(isnull({Table.field1}) or {Table.field1} = 2 ) and
(isnull({table.field2}) or {Table.field2}={?someParameter})

then your report should be fine Jim Broadbent

 

[YA]

Hi Ngolem.

I changed the all record selection formulas in the subreport to:

(IsNull({table.field1}) or {table.field1} = <something>)and

But the result is still the same. The shared variable from the subreport doesnt return any value because of no records matched.


I modified @evaluate in the subreport so that if {table.field1} is null, it returns zero like this:


@evaluate
whileprintingrecords;
shared currencyvar sharedrevenue;
If not IsNull({table.field1}) then
sharedrevenue := sharedrevenue + {table.field1}
else
sharedrevenue := 0;


But the subreport with no records returns nothing and the running total in the main report cant work correctly..

any idea???
Thanks VERY MUCH in advance..

 

[Ngolem]

you are resetting your totals on encountering 1 null here...modify the formula to this

@evaluate
whileprintingrecords;
shared currencyvar sharedrevenue;
If not IsNull({table.field1}) then
sharedrevenue := sharedrevenue + {table.field1};

ALSO when posting examples .... post the exact formula you are using...not the generic example that I post....I do this because I don't know the fields you are working this...I am not sure you are doing it properly when you repost my formula text.

if you have done this

*****************************

I would compose your formulas this way


@Initialize (placed in the report header)

whileprintingrecords;
shared NumberVar sharedrevenue := 0;

@Evaluate (placed in the detail section)

whileprintingrecords;
shared NumberVar sharedrevenue;
If not IsNull({OrderTotalField}) then
sharedrevenue := {OrderTotalField};

OR IF YOU ARE SUMMING RECORDS

whileprintingrecords;
shared NumberVar sharedrevenue;
If not IsNull({OrderTotalField}) then
sharedrevenue := sharedrevenue + {OrderTotalField};


@Display (placed in the report footer)

whileprintingrecords;
Numbervar sharedrevenue ;
sharedrevenue ;

**********************************

then this should work...post the exact record selection formula so we can have a look at it


Jim Broadbent

 

[YA]

Okay, I will post the formulas from the report.


//@Initialize (placed in report header)
whileprintingrecords;
shared currencyvar sharedILTCancel := 0;


//@Evaluate (placed in detail section, and summing records)
whileprintingrecords;
shared currencyvar sharedILTCancel;
If not IsNull({TPV_PUB_PAYMENT.PAY_DATE}) then
sharedILTCancel := sharedILTCancel + {TPV_PUB_PAYMENT.AMOUNT};


//@Display (placed in reprot footer)
whileprintingrecords;
shared currencyvar sharedILTCancel;
sharedILTCancel;


If there is any record in the subreport, this works and returns a value back to the main report, but if no records, it doesnt return anything..

Thanks!

 

[YA]

Also the record selection formula in the subreport:

(IsNull({TPV_PUB_LOCATIONS.STATE}) or not isnull({TPV_PUB_LOCATIONS.STATE})) and
(IsNull({TPV_PUB_BASE_CLASSES.ID}) or left({TPV_PUB_BASE_CLASSES.ID},5) = &quot;class&quot and
(IsNull({TPV_PUB_PERSON.CURRENCY_ID}) or {TPV_PUB_PERSON.CURRENCY_ID} = &quot;crncy000000000000001&quot and
(IsNull({TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC}) or {TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC} = &quot;Credit Card&quot and
(IsNull({@Uppercase State}) or {@Uppercase State} = {?Pm-@Company State}) and
(IsNull({TPV_PUB_PAYMENT.PAY_DATE}) or {TPV_PUB_PAYMENT.PAY_DATE} in {?Pm-?Start Date} to {?Pm-?End Date}) and
(IsNull({TPV_PUB_COURSES.CUSTOM0}) or {TPV_PUB_COURSES.CUSTOM0} = &quot;ILT&quot and
(IsNull({TPV_PUB_PAYCAT.NAME}) or {TPV_PUB_PAYCAT.NAME} in [&quot;Project Cancel&quot;, &quot;Item Late Cancel&quot;, &quot;Item Cancel&quot;, &quot;Invoice Item Cancel&quot;, &quot;Inv. Item Cancel&quot;])

 

[Ngolem]

your problem is in the record select formula...@Uppercase State ....this is why it is important to show us all the REAL details...

If all you are doing is an uppercase comparison of the value of STATE then you don't need a special formula for this.

modify your Record Selection Formula as follows:

(IsNull({TPV_PUB_LOCATIONS.STATE}) or
uppercase({TPV_PUB_LOCATIONS.STATE})=
{?Pm-@Company State}) and

(IsNull({TPV_PUB_BASE_CLASSES.ID}) or
left ({TPV_PUB_BASE_CLASSES.ID},5) = &quot;class&quot and

(IsNull({TPV_PUB_PERSON.CURRENCY_ID}) or
{TPV_PUB_PERSON.CURRENCY_ID} = &quot;crncy000000000000001&quot and

(IsNull({TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC}) or {TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC} = &quot;Credit Card&quot and

(IsNull({TPV_PUB_PAYMENT.PAY_DATE}) or
{TPV_PUB_PAYMENT.PAY_DATE} in {?Pm-?Start Date} to
{?Pm-?End Date}) and
(IsNull({TPV_PUB_COURSES.CUSTOM0}) or
{TPV_PUB_COURSES.CUSTOM0} = &quot;ILT&quot and

(IsNull({TPV_PUB_PAYCAT.NAME}) or
{TPV_PUB_PAYCAT.NAME} in [&quot;Project Cancel&quot;, &quot;Item Late
Cancel&quot;, &quot;Item Cancel&quot;, &quot;Invoice Item Cancel&quot;,
&quot;Inv. Item Cancel&quot;])

I added spaces just for readability...if there is something more complex in the @uppercase State formula let me know...as I said...I don't think it is necessary so I'd remove it.

For the record...if there are more formulas in this subreport then you will have to test for nulls in those as well...I ASSUME there are no other formulas....Hope this works for you now. Jim Broadbent

 

[YA]

Hi Jim

I modified the selection formulas exactly what you mentioned as follows:

(IsNull({TPV_PUB_LOCATIONS.STATE}) or
not isnull({TPV_PUB_LOCATIONS.STATE})) and

(IsNull({TPV_PUB_BASE_CLASSES.ID}) or
left({TPV_PUB_BASE_CLASSES.ID},5) = &quot;class&quot and

(IsNull({TPV_PUB_PERSON.CURRENCY_ID}) or
{TPV_PUB_PERSON.CURRENCY_ID} = &quot;crncy000000000000001&quot and

(IsNull({TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC}) or {TPV_PUB_LOV_ORDER_CONF_TYPE.CONF_DESC} = &quot;Credit Card&quot and

(IsNull({TPV_PUB_LOCATIONS.STATE}) or uppercase({TPV_PUB_LOCATIONS.STATE}) = {?Pm-@Company State}) and

(IsNull({TPV_PUB_PAYMENT.PAY_DATE}) or {TPV_PUB_PAYMENT.PAY_DATE} in {?Pm-?Start Date} to {?Pm-?End Date}) and

(IsNull({TPV_PUB_COURSES.CUSTOM0}) or
{TPV_PUB_COURSES.CUSTOM0} = &quot;ILT&quot and

(IsNull({TPV_PUB_PAYCAT.NAME}) or {TPV_PUB_PAYCAT.NAME} in [&quot;Project Cancel&quot;, &quot;Item Late Cancel&quot;, &quot;Item Cancel&quot;, &quot;Invoice Item Cancel&quot;, &quot;Inv. Item Cancel&quot;])

Still the subreport doesnt return anytning but it repeats the previous value if there is no record returned from the subrepot...

Since @uppercase state formula is used as one of the links between the main report and subreport, I guess I need to make a modification like this?)

1, Right click on the subreport in the main report and choose Chenge Subreport Links
2. @company state formula in the main report is linked to @uppercase state formula in the subreport. since I changed the selection formula in the subreport and removed @uppercase state, I unchecked the checkbox that says &quot;&quot;select date in subreport based on the field.&quot;

These is no more formula in the subreport...so there must be something we havent resolved....

I will keep working on it..Thanks.

 

[Ngolem]

??? don't remove your link to @company state in the main report....just link it to {TPV_PUB_LOCATIONS.STATE} instead

caution though when you do this...Crystal will automatically add something to the record Selection formula...but remove this addition and replace it with

(IsNull({TPV_PUB_LOCATIONS.STATE}) or uppercase({TPV_PUB_LOCATIONS.STATE}) = {?Pm-@Company State}) and

As I had indicated....if this doesn't work then there are other things going on that you haven't told us about. Jim Broadbent

 

[marina9]

Hi Ya!

Have you tried to modify your shared variable to test for your group criteria as well?

For example, since you seem to be grouping by state (and that is one of your links for your subreports), try the following formula in your subreports:


@Evaluate
whileprintingrecords;
shared stringvar state := {Table.<state field>};
shared currencyvar sharedILTCancel:=
if state = {Table.<state field>} and not isnull({TPV_PUB_PAYMENT.PAY_DATE}) then
sharedILTCancel := sharedILTCancel + {TPV_PUB_PAYMENT.AMOUNT}
else
0;

Then in your main report modify the display formula to account for the state field as well:

@Display
whileprintingrecords;
shared stringvar state;
shared currencyvar sharedILTCancel;
state;
sharedILTCancel;

Let me know if this helps, and good luck!!!

 

[YA]

Thanks Jim and Marina for the information.

I havent had a chance to fully test my report with your information yet..but I will let you know the result when I test it out..

Thanks very much!!