• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Cannot Rank or Group By Second-Pass Formula Results in Crystal Reports

I am attempting to build a report that pulls the 10 highest valued results from a second-pass formula.  The formula I want to pull the top 10 from is the result of a subtraction of 2 second pass formulas:
"{@PrintITGNAV}-{@PrintNoITGNAVs}"
These are basically SUMs using the 3 formula methodology in the second pass.

Can I do this, and if so how?
0
Idratherbegolfing
Asked:
Idratherbegolfing
  • 5
  • 3
1 Solution
 
mlmccCommented:
You can but it requires using a subreport.

Does the report have a subreport?
If so you can't do it.

mlmcc
0
 
IdratherbegolfingAuthor Commented:
Thanks!  I don't currently have a sub-report, but I could certainly build one and insert it into this report.  I did some research on this topic online before I wrote this post and found in several places that people suggested using a subreport.  Unfortunately none of them really talked in detail about how to do this.  I've tried several ways I thought it could be done with a subreport, but haven't had any success.  Could you point me in the right direction?  Thanks again!
0
 
mlmccCommented:
Check this aritcle.  It shows a method of passing arrays into a subreport to do what you want.  You would have to modify it to fit your needs.

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40e7130d-aa1e-2b10-ce9f-eee2a54ff4cd

mlmcc
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
IdratherbegolfingAuthor Commented:
Thanks.  I'll give this a try and let you know how it works!
0
 
IdratherbegolfingAuthor Commented:
I still can't seem to get this to work as the field I need to sum "Cannot Be Summarized" in the first summary formula that you insert into the report footer (its a second pass formula result):

//This formula uses Crystal syntax.
WhilePrintingRecords;
Shared CurrencyVar curCounter;
Shared CurrencyVar curTotal;
curCounter := curCounter + {Customer.Last Year's Sales};
curTotal := (curCounter/Sum ({Customer.Last Year's Sales}))*100;
ToNumber(curTotal);

While playing around with this, I think I got everything else to work using a formula that can be summarized (but won't give me the data I need) except for the fact that my subreport only pulls the first "ShowValue" number wherever I insert it into the report, which seems odd.  

Basically I have a table that has 3 fields I use for this report.  Product, Price, and Price Type.  I have 2 Price Types that I want to analyze the differences on so I've built formulas to calculate the difference between PriceType1 and PriceType2 for each Product.  Then I want to show only the top 10 results (in descending order) from this difference calculation.

So maybe I'm taking the wrong approach by using the 3 formula method to calculate this difference?

Anyone have any ideas?

Thanks!
0
 
mlmccCommented:
So you have a table like

Product       Price       PriceType
book            1.00         wholesale
book            2.00         retail
pot               2.00        wholesale
pot               3.00        retail

One thing you could try is
Add a second copy of the table
Link on the product field
In the select expert
Table1.Pricetype = 'Retail'
and
Table2.PriceTyoe = 'Wholesale'

You then should have a single record for each product and should be able to do the comparison without printtime formulas

If that doesn't work and the table structure is as I assumed, I'll try to come up with an idea.

mlmcc
0
 
IdratherbegolfingAuthor Commented:
That's a great idea.  I'll give that a shot and see how that works.  Thanks again!
0
 
IdratherbegolfingAuthor Commented:
This worked.  I added a second instance of the table and was able to get all my data into one record so as not to have to use second pass formulas.  Without using the second pass formulas I was easily able to do some topN groups to pull my top 10 price differences.  Thanks!
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.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now