Previous Periods balance based on current selection

[Trogg]

Hello all...
Using Crystal Reports 2011 and SQL2008 DB.

I have built a report that prompts for Fiscal year(?FiscalYr) and Period(?FiscalPd). Fiscal periods are 1 through 12. Based on the period selected the select formula({FISCAL_PERIOD.ped_no} <= {?Fiscal Period}) grabs the balance for each account and period in the fiscal year selected in the detail. A summary field in the footer gives me the total for each account and the detail is hidden. The report is grouped on account number. Based on the (?FiscalYr) selected I would like to get the balances for the previous 2 fiscal years at the 12th period. It should look like this:

Account-----Selected Balance------Period 12 Previous Year-------Period 12 two years ago
1011---------------300
1012---------------875
2011---------------1500

I know I can hard code the previous periods to 12 and can do formulas for the previous years (ie. (?Fiscalyr)-1). I can get the previous years data with sub reports but would like to avoid that if possible. I appreciate your help in advance and please let me know if more clarity is needed. Thanks!

 

[pmax9999]

Without knowing your data it is impossible to be specific but in broad terms you will need to change you record selection so that the report returns data for the period selected and the same period 1 and 2 years previously. You will then need to use running totals or If-Then-Else formulas (based on the {?FiscalYr} parameter to pick out the right records for each of the columns.

If you are having difficulty with the record selection, post your existing Record Selection Formula and we will be able to assist with the necessary changes.

Cheers
Pete

 

[Trogg]

Thank you for replying... here is my current select statement:

{FISCAL_PERIOD.fis_yr} = {?Fiscal Year} and
{FISCAL_PERIOD.ped_no} <= {?Fiscal Period}

As mentioned this will get me the balances from the Period Totals table {PERIOD_TOT.ped_bl} based on the user input. If the user inputs period 3 then the select will get me the balances for periods 3, 2, and 1 for the fiscal year selected and then I use a summary field to add those up. What I need then is to get all 12 periods from the previous 2 fiscal years based on {?Fiscal Year} and sum those up. I hope this is clearer and thanks for your help.

 

[pmax9999]

If I understand you correctly, the following Record Selection formula should work:

(
	{FISCAL_PERIOD.fis_yr} = {?Fiscal Year} and
 	{FISCAL_PERIOD.ped_no} <= {?Fiscal Period}
)
or	{FISCAL_PERIOD.fis_yr} >= {?Fiscal Year} - 2

For the FY entered this will get you the Periods from 1 to the {?Fiscal Period}, and all Periods for the previous 2 years.

How you set up the report will depend on what you want to display and how the data is structured, eg do you want to see the account balance at the end of each of the months for the {?Fiscal Year} plus previous 2 years (which is what the latest post seems to suggest, but not how I understood the requirements based on the OP), and whether the table you are drawing data from has the account balance (and therefore you can just pick up a single figure) or is transactional and you need to add up all of the transactions.

If this doesn't get you to where you need to be, please provide a bit more detail of what the report should look like and how the data is structured.

Hope this helps.

Cheers
Pete

 

[Trogg]

Thanks Pete... I will try to make this clearer using the example below. The user is prompted for a {?Fiscal Year}and a{?Fiscal Period}. If he enters 2014 for the year and 3 for the period then the record select will grab the period balances for the first 3 periods in 2014 in the detail section. I am only interested in the summary so the detail gets suppressed. Based on the {?Fiscal Year} selected by the user, in this case 2014, I need to show a balance summary for all 12 periods for the previous 2 years which in this case would be 2013 in one column and 2012 in the last column. I can do this by adding 2 sub reports but they are not very efficient... there has to be a better way. Thanks for your help.

Account-----Selected Balance------Period 12 Previous Year-------Period 12 two years ago
1011-----------1----100
----------------2----150
----------------3-----50
------------Sum------300
1012-----------1-----75
----------------2-----400
----------------3-----400
------------Sum-------875
2011-----------1-----500
----------------2-----500
----------------3-----500
------------Sum------1500

 

[pmax9999]

The code I provided in my earlier post will return he data you need to achieve what you want.

Next, create a Group on the Account field and create the following 3 formulas:

[Code {@CurrentYear}]
If {FISCAL_PERIOD.fis_yr} = {?Fiscal Year}
Then {Table.AccountBalance}
[/Code]

[Code {@CurrentYear-1}]
If {FISCAL_PERIOD.fis_yr} = {?Fiscal Year} - 1
Then {Table.AccountBalance}
[/Code]

[Code {@CurrentYear-2}]
If {FISCAL_PERIOD.fis_yr} = {?Fiscal Year} - 2
Then {Table.AccountBalance}
[/Code]

A sum of {@CurrentYear} will give you the total for the Year Entered (for the number of periods selected), a sum of {@CurrentYear-1} will give you the total for the prior 12 months, and a {@CurrentYear-2} will give you he total for the year before that.

Hope this helps.

Cheers
Pete

 

[Trogg]

Hello Pete and thanks...
Your formula for {@CurrentYear} works just fine. However the formulas for the previous years do not because the value of {?Fiscal Year} is still set to whatever value the user entered to start with. This "If {FISCAL_PERIOD.fis_yr} = {?Fiscal Year} - 1" does not change that value. How can we change that value so the previous years formulas will work? Thanks again for helping.

 

[pmax9999]

Not sure I understand. If the user enters {?Fiscal Year} as 2013, then ({?Fiscal Year}-1) = 2012, and the code condition:

If {FISCAL_PERIOD.fis_yr} = {?Fiscal Year} - 1 ...

should apply the Then statement where the {FISCAL_PERIOD.fis_yr} is 2012.

Obviously there is something I am missing. Sorry.

Pete

 

[Trogg]

It seems to be working as follows... If the user has entered 2013 then {?Fiscal Year} = 2013. The If statement will not work because it is not true. Somehow {FISCAL_PERIOD.fis_yr} has to be set to equal {?Fiscal Year} - 1 before it is compared in the If statement. I do not think the If statement is working because {FISCAL_PERIOD.fis_yr} is still set to 2013. Your thoughts?.... and thanks.