| Author |
Message |
DuaneL
Newbie
Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
|
   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 Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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:
|
|
|
|
IP Logged |
|
Duane
Newbie
Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
Duane
Newbie
Joined: 15 Mar 2009
Location: United States
Online Status: Offline
Posts: 2
|
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 Logged |
|
naresh_katakam
Newbie
Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
naresh_katakam
Newbie
Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
naresh_katakam
Newbie
Joined: 15 Aug 2009
Online Status: Offline
Posts: 4
|
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 Logged |
|
|
|
