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

Crystal Reports - How to add variable sort

I need to add a variable sort to an existing report and need detailed instructions on how to do this.  By variable sort I mean that the user can choose from 2-3 different sort patterns.
Thanks
0
mbroad02
Asked:
mbroad02
  • 19
  • 13
  • 8
  • +1
2 Solutions
 
mlmccCommented:
How are they choosing?

If you are passing a parameter in you can shoose to have the report sort on a formula like

If {?SortParameter} = "Name" then
   {NameField}
Else
   {StateField}

The fields you are choosing from must be of the same type.  If they are not then you would need to use a formula like
If {?SortParameter} = "Name" then
   {NameField}
Else
   CStr({AgeField},"000")

mlmcc

0
 
mbroad02Author Commented:
The way they wish to choose is to have a parameter which alloows them to choose from 3 sort orders (they are set as defaults as a string parameter).  
I have a little bit of knowledge of this and have included a few screen prints of my "attempts", however it is not working.
Thanks
SORT-PARAMETER.doc
0
 
mlmccCommented:
Create a formula
Name - SortField
If {?Sort_Parameter} = 'Case Number' then
   {CaseNumberField}
Else If {?Sort_Parameter} = 'Receipt Number' then
   {ReceiptNumberField}
Else
   CStr({DateField},"yyyy/mm/dd")

If the Case and Receipt number fields are really numbers (are use as many 0's as there are digits in the field)

If {?Sort_Parameter} = 'Case Number' then
   CStr({CaseNumberField},"0000")
Else If {?Sort_Parameter} = 'Receipt Number' then
   CStr({ReceiptNumberField},"0000")
Else
   CStr({DateField},"yyyy/mm/dd")

The formula doesn't need to be on the report
Use the sort expert and select that field
Click REPORT --> RECORD SORT EXPERT

mlmcc
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
mbroad02Author Commented:
Getting there.  The case number is NOT numeric so I used your first example.  I am getting an error in the formula.  See attached.  Any thoughts?
Thanks
formula-error.doc
0
 
mlmccCommented:
Is Rec_Nbr a number?
If so it needs to be changed to a string

Also I think you should put the Else If on 1 line.

mlmcc
0
 
mbroad02Author Commented:
{cases.dscr} is an alphanumeric field
 {rec.rec_nbr} is a numeric field
{rec.rec_dt} is a date field

So I need to do a Cstr{rec.rec_dt} before sorting?
0
 
mlmccCommented:
Yes.  The formula can only return one data type.  You have to convert them all to strings.


If {?Sort_Parameter} = 'Case Number' then
   {cases.dscr}
Else If {?Sort_Parameter} = 'Receipt Number' then
   CStr({rec.rec_nbr},"0000")
Else
   CStr({rec.rec_dt},"yyyy/mm/dd")

There is another way

Create 3 formulas
Date_Sort
If {?Sort_Parameter} = "Date" then
   {rec.rec_dt}
Else
   Date(1900,1,1)

Receipt_Sort
If {?Sort_Parameter} = "Receipt" then
   {rec.rec_nbr}
Else
   0

If {?Sort_Parameter} = 'Case Number' then
   {cases.dscr}
Else
    ""

Add all three formulas to the sorting.  2 will do no sorting based on the value of the parameter

mlmcc

0
 
Kurt ReinhardtCommented:
FYI - If you're using Crystal Reports 2008 or 2011 (just released) then you don't need to use parameters or formulas at all.  Dynamic sorting is a native feature of those versions through sort controls.  You simply bind a sort control to a column header then click on it to sort on the fly...

~Kurt
0
 
mbroad02Author Commented:
I am using Crystal 10, but thanks
0
 
mbroad02Author Commented:
Here is a twist on this whole thing:

Yes I do want for this report to have the variable-sort as described above.  However, I ALSO need to print a "detail" line (not actually in the detail section) with case information and dollar total each time that there is a case-number break

I attempted to create a GROUP#1 for the SORT_FORMULA and GROUP#2 for the CASE-NUMBER, but it not working.

Any suggestions??
Thanks a lot!!
0
 
mlmccCommented:
You don't need to use groups. - I intended the formulas to be used in the sort expert.

What isn't working?
Is it grouping correctly?

mlmcc
0
 
mbroad02Author Commented:
The issue is that I still need a way to print the line when the case number break, no matter what the sort option that is chosen.  I.e.  If the date option is chosen, the output would sort in date order, but only print a line when the case number changes....
0
 
mlmccCommented:
In that case you could insert the 3 groups.
Put the data in each header or footer

Conditionally suppress the header/footer with the following.  CHange Receipt to the appropriate alue for the group.

{?Sort_Parameter} <> "Receipt"

mlmcc
0
 
James0628Commented:
You said that you tried making the sort order formula group 1 and the case number group 2, but it didn't work, but you didn't provide any details.  What was wrong with it?  If I'm following, the idea seems sound.  Of course if a single case number has more than one value in the sort field, like 2 different dates, then you're going to see that case number in more than one sort group.

 James
0
 
mbroad02Author Commented:
I have been out of the office for a couple of days, so I will review the answers this morning and respond.  Thanks!
0
 
mbroad02Author Commented:
Why it didn't work:  The orig report originally had case-number as the value for the group so that a line would print every time a new case number was encountered.  Of course this grouping caused the 1st sort of the report to ALWAYS be case number.  Therefore if I add a variable sort to the report with that grouping already there, it will NEVER sort (i.e.) in just date order and print a line when case number changes.  It will ALWAYS be in case number order and then date order under that.

So I am getting that I should remove the case number group and try inserting 3 groups (for the 3 sort options), putting the data for each "detail" line in each of the group's header/footer.  I get that.  I am unclear on the conditional suppression however.

You said, 'Conditionally suppress the header/footer with the following.  Change Receipt to the appropriate value for the group.        {?Sort_Parameter} <> "Receipt" '

What is receipt and where is it declared?

thanks a lot!
0
 
mlmccCommented:
"Receipt" is the value the user supplies for the parameter when choosing the grouping.  You would add those values when you create the parameter.

mlmcc
0
 
mbroad02Author Commented:
I guess I am not understanding how to set up and use that parameter in the way that you describe...sorry
0
 
mlmccCommented:
When you create the sort parameter do you add any values to the list?

What do you expect the users to select as values?

mlmcc
0
 
James0628Commented:
If you look back at post 35738796, where mlmcc first suggested using three separate groups, he was suggesting that you had a parameter named ?Sort_Parameter and the user would use that parameter to choose "Date", "Receipt Number" or "Case Number" as the sort sequence (those would be the available values for that parameter).  That's what you'd use for the conditional suppression.  So, for example, you'd have a group for "Receipt Number" and you would suppress that group header and footer when {?Sort_Parameter} <> "Receipt Number".

 However, if you just use these group headers or footers for your "details", you'll only see one line for each "Date", "Receipt Number" or "Case Number".  Do you really want to only see one line for each date and receipt number, or could there be multiple case numbers for a single date or receipt number and you want to see a line for each case number?  If it's the latter, then maybe you should create groups for "Date", "Receipt Number" and "Case Number", but only use conditional suppression on the first 2 and have the "Case Number" group always be shown.

 James
0
 
mbroad02Author Commented:
Attached is a screen print of how the report is to look.  I understand the {?Sort_Parameter} <> "Receipt Number logic.  The only thing that doesn't make sense is regardless of what sort parameter is chosen, the detail-data must be printed whenever there is a break on case-number.   How do I accomplish that?
Thanks
0
 
mbroad02Author Commented:
Whoops---here is the attachment...I darkened out sensitive information by the way
report-screen-print.pdf
0
 
mlmccCommented:
Did you put fields in the detail level?
or are you printing in the group header or footer?

mlmcc
0
 
mbroad02Author Commented:
Originally, the fields were placed in the footer, which was grouped on Case Number.
0
 
mbroad02Author Commented:
To simplify all of this, I UNDERSTAND how to do the variable sort using 3 groups and the suppression code.  The problem is using that logic and being able to print a line when the case number changes...

Thanks again
0
 
mlmccCommented:
Are you using 3 groups based on the formulas?
If so you could put a 4th group inside using the case number and print in the header or footer.

mlmcc
0
 
James0628Commented:
You might want to remove that last file you posted.  You said that you "darkened out sensitive information".  There are 2 black bars that partially covered the case numbers on the 2 lines, but the bars are still separate objects in the document and can be moved in Word, leaving the case numbers exposed.  Just wanted to let you know.

 James
0
 
James0628Commented:
OK, as I understand it, you want to sort the report by "Case Number", "Receipt Number" _or_ "Transaction Date", and you can have multiple records for a single case number, but you only want to show one line on the report for each case number.

 Can the same Case Number have more than one Receipt Number or Transaction Date?  If so, how do you want to handle that?  For example, if you had Case Number 100 with Transaction Dates on 05/10 and 05/11, would you want to see:

100
  05/10
  05/11

  or

05/10
  100

05/11
  100

  or something else?


 Assuming that each Case Number will only have one Receipt Number and Transaction Date, the answer seems fairly simple.

 Create formulas like the ones described earlier for Receipt Number and Transaction Date and create a group for each of those formulas, with the group headers and footers suppressed when those options are not selected.  Then create a third group on the Case Number field (not a special formula), and put your case number lines in the group header/footer for that group.

 If the user selects "Receipt Number" or "Transaction Date", the corresponding formula will produce results and that group will be used to sort the report, and within that group, you'll get the Case Number group, with one line for each Case Number.  If the user selects "Case Number", the "Receipt Number" and "Transaction Date" groups will be "inactive", and the report will simply be grouped/sorted by the Case Number.

 James
0
 
mbroad02Author Commented:
You might want to remove that last file you posted.  You said that you "darkened out sensitive information".  There are 2 black bars that partially covered the case numbers on the 2 lines, but the bars are still separate objects in the document and can be moved in Word, leaving the case numbers exposed.  Just wanted to let you know.

 James


James,
I appreciate it.  I am not sure how to remove a document that I added though...
0
 
mbroad02Author Commented:
James,
Thank you for your thorough answer.  There will only be one case number record, but could have several transaction dates and receipts.  Not sure how to code for that...


Are you using 3 groups based on the formulas?
If so you could put a 4th group inside using the case number and print in the header or footer.

mlmcc


mlmcc- Thank you for your response.  I can try this method.  I am not sure if it will handle multiple transactions and receipts per case...

Thanks -your help is greatly appreciated
0
 
mbroad02Author Commented:
OK, I have added three groups to the report, Case number, transaction date and receipt number, placed the "detail" fields in each group and suppressed each in the way you described.   It looks like it is working.  I will do a little more testing and let you know.

Thanks again.
0
 
James0628Commented:
mlmcc can probably remove the file (he's also a zone advisor and I believe he has some editing "powers").  Or you can try the "Request Attention" link and ask that the file in post 35779668  be removed.  I'm sure it can be done (because I've seen "mods" do things like that before).

 As for one case number having multiple dates and receipts, it really depends on how you want to handle that.

 Do you still want to see each case number only once?  If so, then what do you want to see?  The case number with the information from the earliest date/receipt, or the latest one, or something else?

 If you want to see the case number repeated for each date/receipt, then do you want to sort by case number first and then date or receipt, or date or receipt first and then case number?

 James
0
 
mbroad02Author Commented:
mlmcc can probably remove the file (he's also a zone advisor and I believe he has some editing "powers").  Or you can try the "Request Attention" link and ask that the file in post 35779668  be removed.  I'm sure it can be done (because I've seen "mods" do things like that before).

REQUESTED REMOVAL--THANK YOU

 As for one case number having multiple dates and receipts, it really depends on how you want to handle that.

 Do you still want to see each case number only once?  If so, then what do you want to see?  The case number with the information from the earliest date/receipt, or the latest one, or something else?

At this point, I would be happy with one line per case number.  Additional code may be added later for printing multiple receipts.

 If you want to see the case number repeated for each date/receipt, then do you want to sort by case number first and then date or receipt, or date or receipt first and then case number?
I will defer this question to above, stating that I only want one line per case number....
0
 
James0628Commented:
If you really want one line per Case Number (CN), that's a problem when you're grouping/sorting by the Transaction Date (TD) or Receipt Number (RN), because the same CN can appear under more than one TD or RN.  For example, if you have two records with CN 101, one with TD 05/11 and one with 05/18, then when you group by TD, CN 101 will appear under 05/11 and under 05/18.

 James
0
 
mlmccCommented:
I don't believe I can delete attachments.  If I can, I don't know how.  Mods will take care of it.

Issue - Depending on how the data is sorted and how many records a case number has you may end up with a case being listed several times.  For instance a Case 1x has records for dates 1 May and 5 May and receipts 1  and 4

So assuming these are different records  you will end up with when sorted by date then receipt you could have
1 May
  Receipt 1
    Case1x
  Receipt 4
     Case 1x
...
5 May
  Receipt 1
    Case1x
  Receipt 4
     Case 1x

What tables are you using?
What does a record have in it?

What do you want displayed based on the above scenario.  

mlmcc
0
 
mbroad02Author Commented:
After further discussing with the client, what she really wants is:

The same information that appears on the report when group is set to case number...the only difference is to sort the whole darn thing in recpt-date order.....She even suggested extracting the data from the case-number version , sorting it externally and opening with something like Excel.  I suppose that would work, but I would rather have a Crystal report.  Is it possible to use a sorted extract file as input to another report??

Thanks
0
 
mlmccCommented:
Crystal uses groups as the first sort orders.  You could use a group on the receipt date field then group by case number

You could also add a summary to the group as the Minimum of the date field.  You can then sort the groups using the summary field through the Group Sort Expert under the Report menu

mlmcc
0
 
James0628Commented:
Sure, you could theoretically export a report to something like an Excel or text file and use that as the datasource for another report, but I don't see any need for that.

 From your last post, it sounds like there are just 2 choices now, Case Number and Receipt Date.  No Receipt Number sort?

 IAC, it seems pretty straightforward.  Assuming that you only want to sort by Case Number or Receipt Date, and you want to keep the Case Number group (so that you can get one line per Case Number by using the group header/footer), just add another group above the Case Number group that's based on a formula like this:

If {?your parameter} = "Receipt Date" then
  CStr ({receipt date field}, "yyyy/MM/dd")

 That will produce the Receipt Dates if you choose to sort by them, so the report will be grouped (sorted) by the Receipt Date, and then by Case Number.  Otherwise, it just produces an empty string, meaning that all of the records will be in one "Receipt Date" group, so the report will only be grouped (sorted) by the Case Number.  You can suppress the Receipt Date group header and footer if {?your parameter} <> "Receipt Date".


 Or, you may be able to use mlmcc's suggestion for sorting the Case Number group, but if a Case Number can have more than one Receipt Date, that will only be able to sort by one of those dates, like the Minimum date for a Case Number or the Maximum.

 James
0
 
mbroad02Author Commented:
I am sorry but nothing seems to work the way that the client requires.  I will award appropriate points and close.
Thank you very much.
0
 
mlmccCommented:
Apparently we haven't really understood what your user wants to see.

Can you give us an example with fake data that shows what you are getting then show what the user wants?

Also with that what is in the data that drives the result.

mlmcc
0
 
James0628Commented:
FWIW, I agree with mlmcc.  It _seems_ pretty straightforward.  If the things that we've suggested aren't working for you, then I think we're missing something.  With more information, maybe we can help.

 James
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 19
  • 13
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now