Incorrect Results for my date formulas 2

[Kallen]

Hi all, I am using Crystal 8.5

I am creating a report that will show the trends in a portfolio from one month to the next. I cannot use a crosstab for this because it is going to contain other complex formulas that a crosstab cannot handle. Right now I have created a formula that is susposed to break a field out by month see below:

Here is the formula for Dec 2002
If {RptOSFlux.Date} = CDateTime (2002, 12, 31, 00, 00, 00) then {RptOSFlux.TOTAL}
Else 0

Now, here is the formula for Jan 2003
If {RptOSFlux.Date} = CDateTime (2003, 01, 31, 00, 00, 00) then {RptOSFlux.TOTAL}
ELSE 0

As you can see the formulas and fields the formulas are based on are identical. I am getting my Dec 2002 numbers without a problem. For some reason I am getting $0's for my January 2003 numbers (when in fact that is incorrect). The strange thing about this is that I am only getting $0's in the groups that had a $ amount for December. If the group did not have a true $ amount for December, then my January 2003 $'s run fine.

Does anyone know what could be causing this. I am so irritated right now I could scream!!

 

[synapsevampire]

Your formula:

Here is the formula for Dec 2002
If {RptOSFlux.Date} = CDateTime (2002, 12, 31, 00, 00, 00) then {RptOSFlux.TOTAL}
Else 0

will give you the value of a field for a given day, not a month. If that one days value is the month total, then you should be OK.

You reference groups in your text, but it's unclear what you've grouped on or why.

Perhaps if you post sample data and expected output someone can help you better.

Another means of demonstrating a months data would be to use something like:

If month({RptOSFlux.Date}) = 1 then
{RptOSFlux.TOTAL}
Else
0

If you're later suming these, you may find that you're having problems because one or more values are null, so it returns null for all of them.

Try selecting File->Report Options->Convert null values to default.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Kallen]

In Reply To: "will give you the value of a field for a given day, not a month. If that one days value is the month total, then you should be OK." I should be allright with that because I will only be tracking info as of the end of every month. Thanks for the suggestion of including a semi-sample of a report. I hope I make myself crear (this is confusing).

My report groups are based on profit centers. Each profit center may or may not have an ending balance at the end of each month. So it would look something like this. FYI: Profit center is a text field, date is a date field. Balance is a currency field

Profit Center Dec-02 Jan-03 Feb-03
1111 $3,445 $3,201 $2,999
2222 $5,498 $4,945 $6,308
3333 $ 0 $1,000 $ 999

I will also have formulas for showing balance variances from month-to-month (this is why I can't use a cross-tab.

Now, like I said before, I will not get Jan-2003 balance numbers for a profit center if they had a balance in December. It looks as though they have no balance in January when in fact the do. This is mind boggling!

Thanks for the other tips, I checked all of that and it does not seem to help this current dilema.

 

[Naith]

Hey Kallen,

There's a little bit of information missing that it would be handy to know:

Assuming 3333 comes up right in Jan, but the others come up wrong, which section of your report is your example based on? The group footer?

If this is the case, and the formulae in your initial post are in your details section, what means are you using to accumulate the totals into their relevant months, so that they can be displayed in the group footer.

If I've misinterpreted what goes where, please set me straight with indicators of which section formulas/database fields and summaries are placed in.

Naith

 

[Kallen]

Thanks for the reply. I am hiding the details from this report because it is used only as a summary report. My formulas are in the profit center group header. I am not putting anything into the details section.

Thanks

 

[Naith]

Well, that's your problem right there.

If you aren't taking the details into account, you're actually forcing Crystal to miss the records after the subsequent record.

What you're doing is like this:

Group Header:
{Date} Dec {RptOSFlux.TOTAL} (800)
Details:
{Date} Dec02 {RptOSFlux.TOTAL} (800)
{Date} Jan03 {RptOSFlux.TOTAL} (200)

Note the group header only catches the first record for each profit centre. Subsequent records - in this case, the January record - get ignored. This is why, where you have January cases as the first record, everything's all good.

What you want to do is something like this:

Suppress your details section.

Take your formulae out of the Group Header, and amend them so that they look like this:

WhilePrintingRecords;
NumberVar Jan;
NumberVar Dec;

If {RptOSFlux.Date} = CDateTime (2002, 12, 31, 00, 00, 00) then Dec := Dec + {RptOSFlux.TOTAL}
Else
If {RptOSFlux.Date} = CDateTime (2003, 01, 31, 00, 00, 00) then Jan := Jan + {RptOSFlux.TOTAL}
Else Jan
Else Dec;

Place

WhilePrintingRecords;
NumberVar Jan;

in the group footer area appropriate to the January total, and do likewise for December.

Because you're placing this formula in the Details section, you're catching every record that appears in the Details section - which is what you want, because it's the lowest level in the report.

All the best,

Naith

 

[Naith]

Forgot to mention that you'll actually have to reset the variables in the group header:

WhilePrintingRecords;
NumberVar Jan := 0;
NumberVar Dec := 0;

so that each profit centre gets a fresh start.

Naith

 

[synapsevampire]

Naith pointed you in the right direction, however you'll need separate formulas, not combined ones as your laying the results out similar to a cross tab.

All data will be displayed in the Group Footer.

The number gathering formulas will be in the details section:

//Jan Summary
WhilePrintingRecords;
NumberVar Jan;
If {RptOSFlux.Date} = CDateTime (2003, 01, 31, 00, 00, 00) then Jan := {RptOSFlux.TOTAL}

Then you would have a display formula similar to the following in the group footer:

//Jan Display
WhilePrintingRecords;
NumberVar Jan;
Jan

Repeat for each month required

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Kallen]

Thanks to both of you for the info! I did put my formulas in the detail section of the report. I then did a summary of the formulas in my headers. This seems to have solved the problem.

The only thing I am still confused on is this section:

//Jan Summary
WhilePrintingRecords;
NumberVar Jan;

You are saying to put it into the header/footer. Do I do this somewhere in format section? I am fairly new to programming in Crystal and would like to know where you would put this in the header/footer. Also where would I declare my variables?

So far so good. It is working like it should. Thanks again!

 

[Naith]

Hey SynapseVampire,

I don't get why you need to split the details formula up for each month. That seems to create 11 excessive formulas. You should only need one formula to assign values to all the months in the details section. But, in the group footer, you want to split the formulas into a separate call to each month variable.

Kallen,

Your formula placements are like this:

{Profit_Centre} Group Header
{@Reset Months}

WhilePrintingRecords;
NumberVar Jan := 0;
NumberVar Dec := 0;

Details section

WhilePrintingRecords;
NumberVar Jan;
If {RptOSFlux.Date} = CDateTime (2003, 01, 31, 00, 00, 00) then Jan := {RptOSFlux.TOTAL}

(Do the same for the December - you can add it to this formula, or put it in another one as SynapseVampire has suggested; it's 6 of one half a dozen of the other, either way...)

{Profit_Centre} Group Footer

{@Jan Display}
WhilePrintingRecords;
NumberVar Jan;

(Display additional months in separate formulas.)

You've already explicitly declared your variables in each one of the three formulas. (Where you say 'NumberVar xxx').

You don't need to format section, or anything fancy like that. It's literally a case of just dragging and dropping the fields in the afore mentioned sections of your report. These three additions are all formulas that you create as fields - not as conditions you apply to sections.

Good luck with your report,

Naith