Data Connectivity
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Data Connectivity
Message Icon Topic: Setting report Sort order and direction at runtime Post Reply Post New Topic
Page  of 2 Next >>
Author Message
DuaneL
Newbie
Newbie
Avatar

Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
Quote DuaneL Replybullet Topic: Setting report Sort order and direction at runtime
    Posted: 16 Mar 2009 at 10:25pm

A report set I am working on requires the report(s) to be sorted at runtime based either on the user's last selected sort order and direction in a gridview, or, a sort field and direction selected from a report setup form.

What I need to do is set the sort order programmatically from field values I have already saved, and pulled from the user session class.  If a user last selected "Title" "Descending" in a gridview, I save that sort/direction in the class and use it for the report. Or, if the setup form, the user selects "Author" "Ascending" I use the corresponding values to set the order.
 
I have to do this outside the SQL stored procedure that pulls the records.  I am not using a DataSet, but instead executing and building the report from the stored procedure directly.
 
Does anyone have suggestion how to set the sort order and direction programatically through the reportdocument, or other means?
 
Thanks,
D
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Mar 2009 at 1:09pm

Setting the field for the sort is not difficult, setting the direction can be.

Here's how I would handle both:

1. Create a parameter that will contain your option - Title, Author, etc.  Create another parameter for sort direction - Ascending or Descending.

2.  Create two formulas - one for the Ascending sort and one for Descending.  The Ascending one will look something like  this:
If {?Sort_Direction} = 'Descending' then 'A'
else
  switch(
    {?Sort_Field} = 'Title', {table.title},
    {?Sort_Field} = 'Author', {table.author},
    {?Sort_Field} = 'blah', {table.blah},
    True, {table.default_sort_field})
The Descending formula will look the same except that you'll use 'Ascending' in the first line instead of 'Descending'.
 
3.  In the Record Sort Expert, add both of the formulas, setting Ascending or Descending appropriately.  It doesn't matter which comes first.
 
Explanation:  Because of the first part of the If statement in the formulas, each will default to a constant value if the other direction has been selected, so all records will have the same value and won't be sorted.
 
NOTE:  The result of the formula has to be the same "type".  In other words, you can't have a result be text sometimes and numbers other times.  So, you have to convert numbers to text.  The problem is, numbers don't sort well as text unless you pad them on the left with spaces or zeroes.  So, if you have a number field as a potential sort option, you need to do something like following:
 
right('0000' + ToText({table.number_field}), 4)
 
In the string of zeroes, use as many as will fit in the max length of the field.  For the number at the end, also use the max length of the field. 
 
ToText sort example without leading zeroes:
 
1
12
143
2
25
3
30
 
ToText example with leading zeroes:
 
001
002
003
012
025
030
143
 
-Dell
IP IP Logged
Duane
Newbie
Newbie
Avatar

Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
Quote Duane Replybullet Posted: 18 Mar 2009 at 3:32pm

Thanks for your reply.  This got me started in the right direction.  I Don't fully understand the line:

If {?Sort_Direction} = 'Descending' then 'A'
 
Can you give me a little deeper explanation of this concept?
 
The formulas do work as submitted with the exception of the numerical sorts as you mentioned.
 
Is this Crystal's recommended method of  handling sorting in ASP/Crystal
.NET? 
 
Thanks. 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 19 Mar 2009 at 9:30am

You can't specify the direction of a sort based on a parameter at runtime.  So, if you want to have the option of either ascending or descending based on a parameter, you have to create the sorts when you create the report and you have to have two of them.  You're parameter will, in effect, "turn on" one sort and "turn off" the other.  So, in your sort formulas, you first need to see if the parameter says to use the other sort and, if that's the case, provide a constant value so that no actual sorting occurs in the formula that's being turned off.  The "If {?Sort_Direction} = 'Descending' then 'A'" line in the formula effectively turns off the ascending sort when you want to sort descending.

The folks from Crystal don't necessarily recommend specific ways of doing things - that's what forums like this are for.  This is a method of handling dynamic sorting inside Crystal, without having to write code to access the specific properties of a report from either ASP or .NET. 
 
I've been using Crystal for about 15 years.  I also have done a lot of work in the Crystal .NET SDK.  I've seen this method recommended by a number of folks in different forums and I've used it.  I prefer it because it's very easy and straight-forward to set up.  Since the sorts are handled in the report through parameters, you can write a fairly generic interface to run reports in general instead of having to write special code for a particular report.
 
-Dell
IP IP Logged
Duane
Newbie
Newbie
Avatar

Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
Quote Duane Replybullet Posted: 19 Mar 2009 at 11:50am
That explanation makes sense now as I look at the way the code in the formula is evaluated.
 
My experience with Crystal.NET is limited, and my experience with Crystal Reports goes back version 5; 6; and 7, but I haven't used it much since 1999, I am in effect starting over.
 
I appreciate these forums and the support from seasoned pros like yourself.
 
Thanks again.
IP IP Logged
naresh_katakam
Newbie
Newbie


Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
Quote naresh_katakam Replybullet Posted: 27 Dec 2009 at 11:25pm
Hi,
 
I've tried your suggestion and this works fine in CR2008. When published to Infoview, the report fails when giving the sort direction as Descending.
 
Here are the formulas I've created:
 
Ascending Sort:
 
 If {?sortOrder} = "Descending" then "A"
 else
    switch
    (
      //select the sort field from {?sortBy}
        {?sortBy}= "bankref", {Command.BREF},   
        {?sortBy}= "customerref", {Command.CREF},
        {?sortBy}= "transactionamt", right("000000000000000"+CSTR  ({Command.TAMT}),31),
        True, {Command.ANO}
)
 
Descending Sort:
 
 If {?sortOrder} = "Ascending" then "A"
else
    switch
    (
      //select the sort field from {?sortBy}
        {?sortBy}= "bankref", {Command.BREF},   
        {?sortBy}= "customerref", {Command.CREF},
        {?sortBy}= "transactionamt", right("000000000000000"+CSTR({Command.TAMT}),31),
        True, {Command.ANO}
    )
 
 
Please help me with this issue. It's very urgent to resolve and am unable to get any clue.
Thanks alot in advance
 
Naresh
Thanks
Naresh
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 28 Dec 2009 at 6:29am
What is the error you get when you run the report in InfoView?  Which version of BOE or Crystal Server (both have InfoView) are you using?
 
-Dell
IP IP Logged
naresh_katakam
Newbie
Newbie


Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
Quote naresh_katakam Replybullet Posted: 30 Dec 2009 at 2:14am
BO XI R3.1 and CR2008.
The error is
"Your request could not be completed because a failure occurred while the report was being processed. Please contact your system administrator. [RCIRAS0546]"
 
Also, for number fields, I've converted to string as in the formula. But, we also have decimal places. Will the fbelow formula takes care of decimals even?
 
right("0000000000000000000000000000000"+CSTR({Command.TRAN_AMT},{Command.TRAN_CCY_DECI_PLAC}),31),
 
The descending and ascending formulas looks as below:
 
Ascending sort:
 
//Add this formula in Record sort expert and set the
//sort order option as "Ascending"
If {?sortOrder} = "Ascending" then
    switch
    (
      //select the sort field from {?sortBy}
        {?sortBy}= "bankref", {Command.BREF}, 
        {?sortBy}= "customerref", {Command.CREF},
        {?sortBy}= "transactionamt", right("0000000000000000000000000000000"+CSTR({Command.TAMT},{Command.DECI_PLAC}),31),
        True, {Command.ANO}
    )
//else
//(
//    'A' 
//)
 
 
Descending sort
 
//Add this formula in Record sort expert and set the
//sort order option as "Descending"
If {?sortOrder} = "Descending" then
    switch
    (
      //select the sort field from {?sortBy}
        {?sortBy}= "bankref", {Command.BREF}, 
        {?sortBy}= "customerref", {Command.CREF},
        {?sortBy}= "transactionamt", right("0000000000000000000000000000000"+CSTR({Command.TAMT},{Command.DECI_PLAC}),31),
        True, {Command.ANO}
    )
//else
//(
//    'D' 
//)

Thanks
Naresh
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 30 Dec 2009 at 6:18am
I see a difference between your example at the top which has "{Command.TRAN_CCY_DECI_PLAC}" and the formulas which have "{Command.DECI_PLAC}".  I don't know if this is significant for you or not.
 
Since you're using a command instead of tables, I would try converting the amount to a string in your SQL instead of doing it in Crystal.  It becomes an extra field in your result set and you use that instead of converting the TAMT field in Crystal.
 
-Dell
IP IP Logged
naresh_katakam
Newbie
Newbie


Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
Quote naresh_katakam Replybullet Posted: 30 Dec 2009 at 8:39am
That was a typo for deci_plac
 
We are using views and we are just selecting all the fields as the view report has around 100+ fields to be slected from view
 
So, creating select clause for the string conv will be very tedious...
 
pls suggest if there is any other way...
 
Naresh
Thanks
Naresh
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.029 seconds.