Sort on same field using two different sort criteria 1

[dadoggh]

I am currently working with Crystal Reports Professional 7.0 trying to pull data from an accounting software package. I am trying to calculate the beginning balance for a given month selected by a parameter. I want to calculate it by using the previous month's posted figures. The software stores the figures in the database based off a field called POSTMONTH. I want to be able to take the BEGINNINGBALANCE field (which is 0 for the fiscal year) and add the posted balances for all of the months prior to the one selected in the parameter and use it as a Monthly Beginning Balance year-to-date. From there I want to calculate the monthly posted figures and then add the monthly posted figures to the Monthly Beginning Balance year-to-date to come up with an ending balance year-to-date. I am having problems pulling the figures from the previous months to come up with my Monthly Beginning Balance year to date.

 

[kenhamady]

Write 2 formulas:

@prior
If PostMonth < ?PromptMonth
then Amount
else 0


@current
If PostMonth = ?PromptMonth
then Amount
else 0

Create totals of these two columns and you have current and prior. Add them together and you have YTD. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dadoggh]

I don't think I gave all of the pertinent information. The monthly posted balance called CURRENTBALANCE is just the financial figures for that given month, not any other months prior. For example I am trying to get the beginning balance for the month of March. I choose my MonthParameter (which is equal to my POSTMONTH so only the selected month figures are displayed) which is 3. To figure out my beginning balance for March, I need to add the BEGINNINGBALANCE (which is zero for the beginning of the fiscal year) and all the CURRENTBALANCE financial figures (anotherwords the posted figures) from the month of January and the month of February. By doing my record sort where my POSTMONTH equals my MonthParameter, I am unable to perform my calculations gathering the figures from the prior month(s). I basically want to sort my data in my report to the selected month in the MonthParameter, but still access the data for the months that are prior to the month selected in the MonthParameter.

 

[kenhamady]

First, the term you want to use is &quot;Select&quot; not &quot;Sort&quot;. Select determines which records are included in the report. Sort determines what order the details appear in the report.

You can't do a calculation using the prior month records without selecting those records to be in the report. You don't have to display those records (you can suppress them), but they have to be selected. Once all the records are selected, and you have the appropriate totals, you can decide which sectiions to display.

So, select all three months, use the formulas I have given to get the opening balance from the prior months, and display the current month while suppressing the records from the prior months. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)