Help with Previous Period Total

[dssguru]

I'm using Crystal Reports 8.5 and I've written a stored procedure for my data. It comes from a financial data source that can be selected by period, not time but numerical (period 01, period 02, etc.). In my report I have a parameter field where the user can select the period they want to see. My problem is this: I wrote a formula to grab the previous time period and display with the time period the user selects but I'm just getting blank info when I view the report. The formula is like this:

if {GL_DETAIL_04;1.Period} = 'October' then {GL_DETAIL_04;1.Period_1}
else if {GL_DETAIL_04;1.Period} = 'November' then {GL_DETAIL_04;1.Period_1}
else if {GL_DETAIL_04;1.Period} = 'December' then {GL_DETAIL_04;1.Period_2}
else if {GL_DETAIL_04;1.Period} = 'January' then {GL_DETAIL_04;1.Period_3}
else if {GL_DETAIL_04;1.Period} = 'February' then {GL_DETAIL_04;1.Period_4}
else if {GL_DETAIL_04;1.Period} = 'March' then {GL_DETAIL_04;1.Period_5}
else if {GL_DETAIL_04;1.Period} = 'April' then {GL_DETAIL_04;1.Period_6}
else if {GL_DETAIL_04;1.Period} = 'May' then {GL_DETAIL_04;1.Period_7}
else if {GL_DETAIL_04;1.Period} = 'June' then {GL_DETAIL_04;1.Period_8}
else if {GL_DETAIL_04;1.Period} = 'July' then {GL_DETAIL_04;1.Period_9}, etc.

The Period fields contain numeric data. Any ideas??

 

[synapsevampire]

The formula doesn't make sense to me, you should be checking the value of the parameter, not a field in the database.

In Report->Edit Selection Formula->Record place something like:

{GL_DETAIL_04;1.Period_1} = {?Period}

This assumes that your period type in the parameter is the same as the type in the database.

You posted very little technical information about data types, where ther parameter is, what is contained within the parameter, and your example formula betrays the statement that the period is numerical since you're checking for the month name.

Apparently your SP does NOT have a parameter? If this is the case, then your implementation will be slower than had you created a View as a View would have SQL passed to it and processing would occur on the database side. If your SP is returning all data, and then you're trying to have Crystal apply some filtering, it will all be done in CR, which is a slow engine by comparison.

If this doesn't help, take a few moments to rephrase your post so that it matches whatever example data you supply.

-k

 

[dssguru]

I apologize for the confusion and put the wrong version of the formula I was trying (I've tried several different iterations). The {?Acctgper} field is a varchar(2) type field. When a user chooses the {?Acctgper} field, then I want to display the previous period via the formula below. The current period data displays in the detail section of the report and someone suggested that this could be my problem. The {GL_DETAIL_04;1.Period_1} field is a money data type field. I don't understand why this won't display the value of the field since I'm defining what should happen when a user selects the acctgper parameter -- this is the formula that makes the most sense to me...

if {?Acctgper} = '01' then {GL_DETAIL_04;1.Period_1}
else if {?Acctgper} = '02' then {GL_DETAIL_04;1.Period_1}
else if {?Acctgper} = '03' then {GL_DETAIL_04;1.Period_2}
else if {?Acctgper} = '04' then {GL_DETAIL_04;1.Period_3}
else if {?Acctgper} = '05' then {GL_DETAIL_04;1.Period_4}
else if {?Acctgper} = '06' then {GL_DETAIL_04;1.Period_5}
else if {?Acctgper} = '07' then {GL_DETAIL_04;1.Period_6}
else if {?Acctgper} = '08' then {GL_DETAIL_04;1.Period_7}
else if {?Acctgper} = '09' then {GL_DETAIL_04;1.Period_8}
else if {?Acctgper} = '10' then {GL_DETAIL_04;1.Period_9}
else if {?Acctgper} = '11' then {GL_DETAIL_04;1.Period_10}
else if {?Acctgper} = '12' then {GL_DETAIL_04;1.Period_11}

 

[synapsevampire]

Where is this formula being used?

And you still haven't defined the field, you state that the parameter is a varchar(2) field, which must mmean that the parameter is supplied by the SP???

Try adding to the formula:

else "unknown: "+{?Acctgper}+chr(13)+
{GL_DETAIL_04;1.Period_1}+chr(13)+
{GL_DETAIL_04;1.Period_2}+chr(13)+
{GL_DETAIL_04;1.Period_3}+chr(13)+
{GL_DETAIL_04;1.Period_4}+chr(13)+
{GL_DETAIL_04;1.Period_5}+chr(13)+
{GL_DETAIL_04;1.Period_6}+chr(13)+
{GL_DETAIL_04;1.Period_7}+chr(13)+
{GL_DETAIL_04;1.Period_8}+chr(13)+
{GL_DETAIL_04;1.Period_9}+chr(13)+
{GL_DETAIL_04;1.Period_10}+chr(13)+
{GL_DETAIL_04;1.Period_11}

This will describe the parameter and the fields when there isn't a hit.

A good general rule is to supply:

Crystal version
Datbaase used and connectivity
Example data
Expected output

In your case, you have a parameter that you want to use to show data, but you haven't described if the rows are to be limited as a result, or just a display formula somewhere in the report, etc.

-k

 

[dssguru]

synapsevampire,

Thank you for your effort in helping -- the formula you suggested did not work. It doesn't seem that this should be so difficult. Here's some background. The data behind my report (it will be used in Crystal Enterprise) comes from a large table containing financial info. I've prepared the report to have a user select a specific time period ({?Acctgper}) and the detail section of the report prints all the information pertaining to that time period. I split the amount fields ({GL_DETAIL_04;1.Period_2}, etc.) up by period and it is a float data type field for numbers -- in this way all periods are available for the user to choose. The {?Acctgper} field is a string field and corresponds to a field named {a_period}, which is also a string field. One portion of an example of a formula that is currently working in the report looks like this (I use it to display positive numbers and use one to display negative numbers:

if {?Acctgper} = '01' and {GL_DETAIL_04;1.Period_1} > 1 then {GL_DETAIL_04;1.Period_1}
else if {?Acctgper} = '02' and {GL_DETAIL_04;1.Period_2} > 1 then {GL_DETAIL_04;1.Period_2}, etc., etc., etc.

Everything is working except when I simply try to display the field for a previous period based on the parameter selected by the user ({?Acctgper}) I get a blank field. In other words, when a user selects '02' for the {?Acctgper} parameter, the report populates with a_period = 02, {GL_DETAIL_04;1.Period_2}, etc., in the detail section and groupings. I simply want the report to also display {GL_DETAIL_04;1.Period_1} with previous period information much in the same way a year-to-date total would. Why can't I get this to work?? I'm using Crystal Reports version 8.5.