Budget with months

[Former Member]

Hi,

I am trying to set up a CR that will pull information for a certain month. I am trying to set it up so it will automaticly pull last year's stats (from the same month the year before) and then I'd set up a variance column.

I cannot seem to get it set up right. I'm trying to set up a cross-tab with as much flexablity as possible.

Thank you in advance for any assistance you can offer.

[Former Member · Accepted solution]

Justin,

What you are trying to do shouldn't be too tough. The actual "Hot To Do It" really depends on how you've got things setup with you parameters.

I hate making assumptions, but I'll assume that you are using a single date parameter that accepts range values (you put both begin and end dates in the same parameter prompt).

1. Open you Select Expert dialog box.

2. Click the "Show Formula >>>" button

3. If you have more than one selection criteria, find the portion that pertains to the date selection.

It should look something like:

{Table.DateField} = {?ParameterName}

4. Add the following code to it:

OR ({Table.DateField} >= DATEADD('yyyy', -1, MINIMUM({?ParameterName})  AND 
{Table.DateField} <= DATEADD('yyyy', -1, MAXIMUM({?ParameterName}))

Hope this helps,

Jason

[Former Member]

Thank you! It worked.

It may just be the fact that I had a long day at work, or I just stink at this. I am trying to get the totals to show the difference, but I can't seem to change the summery. Any idea?

[Former Member]

Justin,

Let me think on that one a bit...

Jason

[Former Member]

Justin,

Here's what you want to do.... It involves the creation of a few new formulas.

1) CurrentRange = IF {tbl.Dates} = {?DateRange} THEN {tbl.FieldYouWantToTotal}

2)PreviousRange = IF {tbl.Dates} != {?DateRange} THEN {tbl.FieldYouWantToTotal}

3) CurrectTotals = SUM({@CurrentRange})

4) PreviousRange = SUM({@PreviousRange})

5) Difference = {@CurrentTotals} - {@PreviousTotals})

I think this should do the trick.

Jason