How do I repeat a Calculated member automatically

Crystal Reports 2008
I have a CrossTab report with groupings by location, category, year.
I want to subtract last years total sales from current years total sales, after both have been displayed.
I can do this with a calculated member, but I don't know how to get this formula to repeat over all categories.  It only shows on the first one, where I "built" it with the dialog boxes.

Insertion formula looks like this:
GetRowGroupIndexOf(CurrentRowIndex) = 3
and GridRowColumnValue("Command.district") = 1000
and GridRowColumnValue("Command.iqdp_child_pc") = "101"
and GridRowColumnValue("Command.year1") = "2010"

Calculation formulation looks like this:
GetRowGroupIndexOf(CurrentRowIndex) = 3
and GridRowColumnValue("Command.district") = 1000
and GridRowColumnValue("Command.iqdp_child_pc") = "101"
and GridRowColumnValue("Command.year1") = "2010"  

I don't understand what/how this is done.  

DatakatCrystal Report DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
How about this idea

Add a formula
If Year({dateField}) = Year(CurrentDate) then
   {SalesField}
Else
   -{SalesField}

Put that in the cross tab for the yearly comparison

mlmcc
0
DatakatCrystal Report DesignerAuthor Commented:
I have summaries going across the crosstab, and grouped by years as rows.  Grouped by categories across the columns.  
So:
Store 1         -------Candy---------------
     Year1      Sales$  GrossProfit$  GP%
     Year2      Sales$  GrossProfit$  GP%

Difference     xxxxx   xxxxxxxxxx   xxxx

Is what is requested. In 2008 I can do this with calculated member and place it under the other totals.  But I don't seem to be able to get the calculated member to "repeat" in each cell.

I would do a Manual Crosstab, but there are way too many categories......
0
mlmccCommented:
A cross tab is not setup to do that kind of calculation.

mlmcc
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

PCIIainCommented:

By cell, I presume you mean under year 2 for each store?

Try an insertion formula of

Gridlabelat("Command.year1",currentrowindex))=2010

, without the other stuff. (Ensure the Group Level is set to "Command.year1")

and a value formula (edit value formula)
 of
GridValueAt (CurrentRowIndex-1,CurrentColumnIndex ,CurrentSummaryIndex )-GridValueAt (CurrentRowIndex-2,CurrentColumnIndex ,CurrentSummaryIndex )

0
DatakatCrystal Report DesignerAuthor Commented:
PCIIain, this gives me a "Not Supported" error when I refresh the data.
0
PCIIainCommented:
Hmmm, do you have any stores which only have sales in 1 year?
0
DatakatCrystal Report DesignerAuthor Commented:
Yes, in some categories there are stores that have sales only in 1 year.
0
DatakatCrystal Report DesignerAuthor Commented:
In reviewing my initial post I see I posted the calculation formula incorrectly.  It actually was:

GridValueAt(GetRowPathIndexOf(1000, "101", "2011"),
CurrentColumnIndex, CurrentSummaryIndex)
- GridValueAt(GetRowPathIndexOf(1000, "101", "2010"),
CurrentColumnIndex, CurrentSummaryIndex)

And this gives me just the one, doesn't repeat.
0
PCIIainCommented:
I would expect you to be able to set up the my inclusion formula, and that calculation formula, and get the SAME number repeated all over the crosstab.

Which mean we only need to fine tune the calculation formula to isolate the cases where there isn't a previous (or current) year value.

How about replacing the first two hard coded pathindex things with gridlabelat(<fieldname>,currentrowindex)

So GridValueAt(GetRowPathIndexOf(gridlabelat(<field1>,currentrowindex), gridlabelat(<field2>, currentrowindex, "2011"),
CurrentColumnIndex, CurrentSummaryIndex)
-........

Although I don't think this necessarily fails gracefully if there isn't a 2011 or 2010 in the data....
0
DatakatCrystal Report DesignerAuthor Commented:
Calculation Formula that works:

GridValueAt (CurrentRowIndex-2,CurrentColumnIndex,CurrentSummaryIndex)-
GridValueAt (CurrentRowIndex-1,CurrentColumnIndex,CurrentSummaryIndex)

Insertion Formula that works:

GetRowGroupIndexOf(CurrentRowIndex) = 3
and GridRowColumnValue("Command.year1") = "2010"

And it repeats across all the columns.

But the insertion formula will not work when the year changes.  I need it to equal a parameter value selected when the report is run for "Last Year".  

Any more thoughts?

0
mlmccCommented:
Try changing

GridRowColumnValue("Command.year1") = {?YearParameter}

mlmcc

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatakatCrystal Report DesignerAuthor Commented:
I did some reading and figured out the calculation formula and insert formula.  mlmcc provided the final answer to finish up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.