Solved

sorting a crystal report by formula field

Posted on 2004-08-20
16
4,460 Views
Last Modified: 2012-08-13
I am using crystal reports with MS Visual Studio.NET enterprise architect.
I have 1 group in the report, 4 parameter fields and 2 formula fields.
The group has 3 database fields and the 2 formula fields.
The 4 parameter fields are search fields.
1 of the formula fields is used for finding the sum of 1 of the search results data fields by the group field, and the other formula field value = databasefield - formulafield1.
 I need to sort the crystal reports by the third formula field, ascending or descending. How do I do this??

Thank you much.
0
Comment
Question by:krisme77
  • 5
  • 5
  • 5
  • +1
16 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11853392
Based on what you described, you cannot do it.  In Crystal a grouping always overrides a sort and it sounds like you want the opposite behavior which simply can't be done.

If I'm not understanding correctly then please clarify what it is you want to do.

frodoman
0
 
LVL 7

Expert Comment

by:mnye
ID: 11853750
there is one alternative, if you can combine your formula that you want to sort by and current grouping field into another formula, then group by that second formula.  this only applies to two data items that are uniquely related (ie ID fields and Name fields).  And even that isnt 100% true, if say you have duplicate names with diffrent ids.  

example:
If you are currently grouping by CustomerID and you want to sort by Customer Last Name, create formula that concats the two with the Last Name first, then use the Options table to specify the display name.  

you can also try changing at runtime, however, i dont think this will override the default groupings sort.
http://support.businessobjects.com/library/kbase/articles/c2007233.asp


hth
mnye
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11854046
What is the third formula?  First you state you have 2 formulas then you say you want to sort by the third formula.

As frodoman stated the groups take the first level of sorting and cannot be overriden.  So if you need to group then any sorting will be within the group.

mlmcc
0
 

Author Comment

by:krisme77
ID: 11854513
This is how my report is laid out.

There are 4 database tables in play.

table1, table2, table3, table4

I have a group by table2.column2. All the report fields are in the group footer.

There are 2 formula fields,

formula1 = sum(table1.column1)*200
formula2 = table2.column1 - formula1

the report displays
 table2.column2, table2.column3, table3.column1, formula1, table2.column4, formula2

 mlmcc,
 it was a mistake when i said I need to sort by the third formula field. I need to sort by the 2'nd formula field(formula2 above).
0
 

Author Comment

by:krisme77
ID: 11854602
I believe my purpose will be served if I could sort within the group. But I am not sute how to sort the group by a formula field. any pointers?
0
 
LVL 7

Expert Comment

by:mnye
ID: 11854622
add a second grouping, below the first, that groups by the formula2, put your fields in this groups footer and suppress group 1 footer. this will force the sort order you want . . i believe.

hth
mnye
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11854654
I agree with Matt - If we're understanding what you want that will work.

The report will be sorted by Group 1, and within Group 1 it will be sorted by Group 2.  You can suppress the footers and headers as mnye indicates to achieve the "sort within group" effect.

frodoman
0
 
LVL 7

Expert Comment

by:mnye
ID: 11854675
btw if you were just looking for fundamentals on sorting, sorting by a formula is done the same as by the field, depending on your version:

>Go to the Report menu
>select "Sort Expert"
>select the field or formulai n the left hand pane that you wnt to sort by and move it to the right pane

hth
mnye
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:krisme77
ID: 11854789
The Sort expert does not list the formula fields in the left pane, as being available to sort by. The formula fields are also not displayed as a part of the fields I can group by. If they were, I believe I would'nt have had the problem to begin with..:)..pls do enlighten me if this is because of something I am overlooking...
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11854817
You aren't going to be able to use these formulas because they are in the group footer.  You can't group by a field in a group footer because that's circular logic.

If you want to create a secondary grouping it has to be based on a field that is available at the detail level.  If you can explain the ultimate goal here maybe we can give you clearer direction?

frodoman
0
 
LVL 7

Expert Comment

by:mnye
ID: 11854823
thats because of the runtime evaluation sequence. I beleive frodo or mlmcc have that link.  but you cant use a formula before it has been evaluated, usually agregates. Because you are sumarizing in formula 1, it has to run through all of your records to return that sum values first.

try removing the "Sum" from formula1, im guessing you dont really need that.  if so, try creating a running total field and then using that in your formula.

hth
mnye
0
 

Author Comment

by:krisme77
ID: 11854910
frodoman,

 I'll try what you suggested. My ultimate goal is to be able to sort by the formula field.

mnye,
 I'll also try u'r suggestion.

pls lemme know what exactly about my question do u need me to clarify??
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11855001
>>> pls lemme know what exactly about my question do u need me to clarify??

If you can show us something like this:

Department #1
Employee #1
    Phone Call #1       $5.00
    Phone Call #2     $15.00
    Total  Phone Calls   $20.00
Employee #2
    ...
Department Total

And explain what you want sorted how.  Just a simply layout of what you want to see may be very helpful in talking you through this.

frodoman
0
 

Author Comment

by:krisme77
ID: 11855144
Allright. Here we go.

Database tables: PhoneBill, Caller, PhoneCall

I have a group by PhoneBill.Number. All the report display fields are in this group footer.

There are 2 formula fields,

formula1 = sum(PhoneCall.time) * 10
formula2 = PhoneBill.Credit - formula1

the report displays

 PhoneBill.Number, PhoneBill.Address, Caller.name, formula1, PhoneBill.credit, formula2

I want the results to be sorted by descending formula2.
0
 
LVL 7

Accepted Solution

by:
mnye earned 125 total points
ID: 11855290
do you have the ability to create views and/or stored procedures?
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11855398
Matt is thinking as I am - your best approach here is to have the db calculate this value instead of using a CR formula.  You can then group/sort based on the pre-calculated value.  Do you have rights to do this or can you have it done for you?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now