Formula returns zero value when record does not meet criteria

[heidik]

Should return nothing. I'll include a simple example and hopefully I'm just missing something!

The report needs to have current month and the previous 11 months.

Here is my formula. My parameter input for current month is 5, report year = 2003 and division = GRDH

// initialize month/year variable

datevar Cur1 :=Date (1999,9 ,9 );
//set variables

if {?Current Month}-1 >0
then Cur1 :=(Date ({?Report Year},({?Current Month}-1) ,1 )) else
Date ({?Report Year}-1,(12 + {?Current Month}-1) , 1);


//report plan for mo-1

if{MBP_QUAL_CPPM_PLAN.PEYR} = year(Cur1) and
{?Division} = {MBP_QUAL_CPPM_PLAN.DVCO} then
{MBP_QUAL_CPPM_PLAN.CPPM}



Here is what is currently in my MBP__QUAL_CPPM_PLAN table (trying to keep this simple and I still can't get it to work!!)

PEYR FUNC BUCO DVCO CPPM
2003 QCPP EMNA GRDH 20
2003 QCPP EMNA NODV 10


and lastly, here is what my report shows.

2003
April

20


2003
April

0

The section of detail should not be there. If I add another record into my table that does not meet the criteria, I get another section the 2003, April and 0.

 

[Ngolem]

Ok...a couple of things.

In this display formula

if{MBP_QUAL_CPPM_PLAN.PEYR} = year(Cur1) and
{?Division} = {MBP_QUAL_CPPM_PLAN.DVCO} then
{MBP_QUAL_CPPM_PLAN.CPPM}



You do not assign a value when the report does not meet the criteria. So Crystal assigns a value of zero (0)...why? because it is the same datatype as the result of the IF-Then were it true.

One way to avoid this is to make your result a string and tell Crystal what to print for a non-comparison. eg.

if{MBP_QUAL_CPPM_PLAN.PEYR} = year(Cur1) and
{?Division} = {MBP_QUAL_CPPM_PLAN.DVCO} then
ToText({MBP_QUAL_CPPM_PLAN.CPPM},0,"","&quot
else
"";


From the looks of it you don't want to see the value at all...ie you want it suppressed.

You don't show the structure of your report so this is a guess on my part....

2003
April

20


It looks to me as though you are printing Group header, group header, detail....

IF this is not the case...and all results are printed in the detail section then you could use the same formula to suppress the whole section to remove it from the report...this avoids having to deal with blank sections on your report...although if this entire printing

April
2003

20

were a single formula that was null with a non matching criteria you could place a "suppress Blank Section" criteria which would accomplish the same thing.

But there is probably more to this problem....this seems to simplistic to me....hopefully this helps you



Jim Broadbent

 

[heidik]

I've changed the formula like you suggested and it no longer prints zero; it's blank. The month and year are still printing.


2002 2002 2002 2002 2002 2002
Dec Jan Feb Mar Apr May
20


2002 2002 2002 2002 2002 2002
Dec Jan Feb Mar Apr May


I can get the additional line of Year/Mo to go away if I put in a selction criteria of {?Division} = {MBP_QUAL_CPPM_PLAN.DVCO}. That sounds great, but I have several other formulas that I need to enter and this selection does not work for them.

I'm sorry, this must be confusing...

If the record does not meet the criteria of the formula, I can't have it show on the report.

Can you explain why, if the data doesn't meet the selection criteria in the formula, a value is reported at all?


Thanks for your help!

 

[dgillz]

Where did you place your formula? If it was not under report, edit selection formula, record, then of course the record will show.

Please post your record selection formula in its entirety.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Ngolem]

See...As I thought there is more to this report than you showed earlier.

2002 2002 2002 2002 2002 2002
Dec Jan Feb Mar Apr May

We cannot solve a problem unless You give all the details of your report>

Is this a crosstab report?? Seems to look like one now...

If not where are the dates and month fields located In a header or footer.

***********
Can you explain why, if the data doesn't meet the selection criteria in the formula, a value is reported at all?
***********

Sorry Heidi....but to answer your question I must have details on how your report is set up....the Crystal Ball is kinda weak without them.





Jim Broadbent

 

[heidik]

Here is the basic idea of the report.

We have created a data base to store our CPPM data. There are a total of 3 tables. One table with plan (referenced above), one with detail data and one with monthly roll up data. The roll up data is calculated and stored (roll ups such as customer, internal, external, division, business unit, function etc.) All of these table have a base of Year, Division, Business Unit and Function.

I need to report data from the plan table and from the roll up table. Below is an example of what part of the output should look like.

2002 2002 2003 2003 2003 2003
Nov Dec Jan Feb Mar Apr
PLAN 50 50 142 142 142 142
MTH 27 16 560 2937 4662 4161
YTD 243 229 560 1773 2937 3295
TTM 231 229 244 509 1007 1511

The user needs to be able to select the period for which they are running the data, the division and the year.

I take the month and year the entered and calculate the preious 11 months and display them on the report in the detail. Then I look at plan, where I have one record for each year, and fill in the qty.

MTH has 20 records that meet the selection criteria (at this time - that will increase) so I get 1 line with the correct data and 19 with blanks.

The only thing in my selection criteria is MBP_QUAL_CPPM_PLAN.DVCO = {?Division}. If I put year or month in there, I will no longer get my other 12 months on the report (right?).

 

[synapsevampire]

You need 2 parameters, the division, which you have, and a date, which you will use to construct the record selection criteria for year month.

What would be more useful is to have the layout of the tables involved (at least the columns used), sample data and expected output. The text descriptions are interesting, but they are much less meaningful if we can't see the structures.

Without this you'll go back and forth through a lengthy discovery process, as we're well on our way to now.

-k

 

[Ngolem]

yes.....and describe how your report is grouped and the fields laid out

Jim Broadbent

 

[heidik]

The plan table is detailed above. The roll up table has:


Peyr | Year |Func | BUCO| DVCO | Type | cust type | cust | mth
4 | 2003 | QCPP | EMNA | GRDH | AUTO | Internal | CH | 21
4 | 2003 | QCPP | EMNA | GRDH | AUTO | | | 73
4 | 2003| QCPP| EMNA| GRDH| AUTO| External | FORD| 55
4 | 2003| QCPP| EMNA| NODV| | | | 73
4 | 2003| QCPP| EMNA| GRDH | | | | 73



I need to report the plan for DVCO = GRDH, BUCO = EMNA, FUNC = QCPP. There is one plan for the year, but I will need to report both years plan number. In the report example above, you can see the report crosses over 2 different years. The report needs to show current month data and previous 11 months.

I also need to report MTH from the roll up table where FUNC = QCPP, BUCO = EMNA, DVCO = DRDH, AUTO = "", Internal = "",
cust type = "", cust = "". I will need the current month and the previous 11 months for this field also.

There is also a requirement to put a graph on the same page that has cust breakout of MTH.

I'm linking on peyr, func, buco, dvco. The first table is the plan table and the second is the roll up. It is an equal join.

My parameters are current month, year and division.

I should also add that I will need to do a report at the buco level next....

Did I forget anything?

 

[heidik]

There is no grouping on the report. Every field is a formula field. The formula names are in parentheses below.


(mo-5) (mo-4) (mo-3) (mo-2) (mo-1) (mo)
2002 2002 2002 2002 2002 2002
Dec Jan Feb Mar Apr May

(pln-1)
20

(mo-5) (mo-4) (mo-3) (mo-2) mo-1) (mo)
2002 2002 2002 2002 2002 2002
Dec Jan Feb Mar Apr May