Solved

sorting a crystal report by formula field

Posted on 2004-08-20
16
5,193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 101

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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