[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I repeat a Calculated member automatically

Posted on 2011-10-05
12
Medium Priority
?
773 Views
Last Modified: 2012-05-12
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.  

0
Comment
Question by:Datakat
  • 6
  • 3
  • 3
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36919352
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
 

Author Comment

by:Datakat
ID: 36919857
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 36922066
A cross tab is not setup to do that kind of calculation.

mlmcc
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 13

Expert Comment

by:PCIIain
ID: 36923228

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
 

Author Comment

by:Datakat
ID: 36923966
PCIIain, this gives me a "Not Supported" error when I refresh the data.
0
 
LVL 13

Expert Comment

by:PCIIain
ID: 36924030
Hmmm, do you have any stores which only have sales in 1 year?
0
 

Author Comment

by:Datakat
ID: 36924176
Yes, in some categories there are stores that have sales only in 1 year.
0
 

Author Comment

by:Datakat
ID: 36924234
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
 
LVL 13

Expert Comment

by:PCIIain
ID: 36924432
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
 

Assisted Solution

by:Datakat
Datakat earned 0 total points
ID: 36951607
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 36952405
Try changing

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

mlmcc

0
 

Author Closing Comment

by:Datakat
ID: 37036566
I did some reading and figured out the calculation formula and insert formula.  mlmcc provided the final answer to finish up.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question