Parameters and Previous Month Data with Current Data

[dssguru]

I'm using CR 8.5 and connecting to a SQL 2000 financial database. The database lists information by accounting period (varchar(2) field) and financial information is also split up by account period (period 1, period 2, with money amounts). In the CR I'm using a parameter value so a user can select which accounting period to look at (the report will be in Crystal Enterprise).

The report is arranged something like this:

Detail Section
Period, Account No., Description, Previous Period, Credit amount, Debit amount.

I've grouped on Account No.

The {?Period} parameter field is also a varchar(2) type field. When a user chooses the {?Period} 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 {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 period parameter -- this is the formula that makes the most sense to me...

if {?Period} = '01' then {Period_1} //(the money field)
else if {?Period} = '02' then {Period_1}
else if {?Period} = '03' then {Period_2}
else if {?Period} = '04' then {Period_3}
else if {?Period} = '05' then {Period_4}
else if {?Period} = '06' then {Period_5}
else if {?Period} = '07' then {Period_6}
else if {?Period} = '08' then {Period_7}
else if {?Period} = '09' then {Period_8}
else if {?Period} = '10' then {Period_9}
else if {?Period} = '11' then {Period_10}
else if {?Period} = '12' then {Period_11}

If user selects 03 for the {?Period} parameter then my expected display under the headings listed above the result would be:


Period 03, Acct. No, Account Description, Period 02 amount, Period 3 credit amount, Period 3 debit amount

I get all the Period 3 data but the Period 2 previous amount is blank no matter what parameter you choose. I'm not an expert but I've never run into this problem and would appreciate any assistance or reference points. This is a simple accounting report where everyone wants to see current information and previous month's data in a column right next to current data.

 

[lbass]

I saw your previous thread, and it is still unclear what kind of fields {Period_1} to {Period_12} are. If these are formulas, you must share the contents of the formulas. You say that the datatype is currency, but it seems unlikely that they are actual database fields, or you probably wouldn't be running into this problem. Please explain.

-LB

 

[dssguru]

Fields {Period_1} to {Period_12} are float fields in a temporary database created by a stored procedure. The report fields come from that stored procedure and everything else works great.

Could the fact that a temporary table is being used be affecting the report?

 

[lbass]

I can't help, since I don't know what a float field is and have never created a stored procedure. Maybe someone else can help. Did you post this under another user name as well? This all seems familiar. It's usually best to stick with one thread, so responses can build upon previous responses...

-LB

 

[vidru]

If the report is based off of a stored proc, I'd pass the {?Period} parameter to the procedure, and handle getting the previous amount there.

Why aren't you doing it that way? Just curious...

-dave

 

[MColeman]

I would try:
1. create a subreport for the previous period data
2. link the period number selected to the subreport
3. in the subreport's record selection, extract data based on period minus one

 

[dssguru]

Vidru:
I did use the parameter in the stored procedure and when I looked at the report it gave me both periods in the detail section. I also wanted to have all time periods available in case a user wanted to look back at a historical period --yes they should save reports but they ultimately don't. Thus the reason for totaling each period separately and having it available.

MColeman:
I've thought of this option as well and because the parameter field is varchar (string) I wasn't able to get a formula to do a minus one on a string field...

I feel like I should be snakebit on this one!! I'm not the first person to ever do a financial report where you look at current timeframe and previous timeframe for comparison! ARGH I can't find the trees much less the forest!

 

[MColeman]

You can use tonumber(stringfield) to convert to numeric data.