Crystal Reports 2008 Oracle 11g Group based on formula - Specific Order

[hmax]

Crystal Reports 2008
Oracle 11g

Hi,

I have created a formula to retrieve Group results for @SiteType (5 results).

I now need the results to show in a specific order, rather than defaulting to alpha sort.


How do I do this, please?

Thank you.

 

[briangriffin]

Here's one way of many: change your formula to append a number to the first character:

Sales becomes 1Sales
HR becomes 2HR
Warehouse becomes 3Warehouse

Now the groups fall out in the order you want. In the change group dialog, customize your group name fields to:

mid({@SiteType,2,len({@SiteType) - 1)

This approach only works with less than 10 values.

 

[CoSpringsGuy]

you said you had 5 results
sort on a formula similar to this


Select {@SiteType}
Case "result1" :
1
Case "result2" :
2
Case "result3" :
3
Case "result4" :
4
Case "result5" :
5

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[hmax]

Thanks, briangriffin:

Working with your solution, I receive

String length is less than 0 or not an integer.


Thanks, CoSpringsGuy:

Trying this now ...


Appreciate your speedy feedback

 

[FireGeek21]

Hi,

I do this often and use the following:

IF {@SiteType} = "BUILDING A" THEN 100 ELSE
IF {@SiteType} = "AREA 5" THEN 200 ELSE
IF {@SiteType} = "SECTION 67" THEN 300 ELSE
IF {@SiteType} = "AILSE 2" THEN 400 ELSE
IF {@SiteType} = "SHELF 100" THEN 500 ELSE

I use the 000 number format so if there is a new {@SiteType} that is later added, it can easily be inserted without a lot of rework.

Hope this helps!


FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

Not sure that the second solution will work with groups, but Firegeek's formula will work. However, if my formula isn't working then there would almost have to be some null values, in which case Firegeek's formula would need some catchall value in the else clause to work - ELSE 9999 for instance.

 

[FireGeek21]

Yes Brian, include 999 at the end. Sorry about leaving that off.

Glad it helps.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[CoSpringsGuy]

The second formula works great with groups I use it every day...

and if you want a catch all for that one add

Default :
999;

at the end

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[briangriffin]

Right, didn't mean to imply anything insidious... I saw the word 'sort' and thought you might have missed the grouping part.

 

[hmax]

Thanks, All!

Will get back to you asap with hopefully remarks of success.

Cheers,
hmax

 

[pmax9999]

Or, working with briangriffin's approach, amend the formula to customise the group name to:

If	Not Isnull({@SiteType})
Then	Mid({@SiteType},2)

Note, with the [Bold]Mid[/Bold] function it is not necessary to include the length of the string. Everything from the starting point onward is the default.

 

[hmax]

Success! Awesome.

Went with briangriffin's and pmax9999's update, but am definitely keeping all these notes handy, as I know I'll be using them in the future. Really helpful.

Thank you.

hmax

 

[pmax9999]

Just another note on this.

As briangriffin noted, this approach only works with less than 10 values. It can be amended to cater for more, by using a prefix of 01, 02, 03 etc [and a customsed group formula of: If Not Isnull({@SiteType}) Then Mid({@SiteType},3)] to work with up to 99 records, and just add additional leading zeros if more capacity is required.

The reason it is necessary to pad with leading zeros is to preserve the sort order when the numbers become strings (otherwise the sort order would become 1, 10, 11, 12, ... 2, 20, 21 when sorted alphabetically)

Regards
Pete.

 

[CoSpringsGuy]

Im really confused by this thread for some reason....

Forgive me for dragging this one on but I frequently have the scenario which requires me to order my groups in a specific order so I am interested.

Am I correct in assuming brians formula and pmax subsequent modifications are for modifying the group name after appending the numeric digits? I am curious to see the formula used to add those digits. I am picturing an If Then as Firegeek mentioned or a select case as I offered. The reason I create a separate formula to group/sort on is because I may need @SiteType (ex.) somewhere else in the report where as I would have to create a formula like Brians anyway to display it correctly.

Just curious .. Thanks

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

One other thought ... and please forgive me if I am just so far off the point of this thread that I am annoying folks ...

IF you only have 5 possible results for @SiteType (which is what I read intitially) ...

couldnt you use in group expert - options - in the common tab .. select specified order and order your groups that way? A drop down appears with possible results of @SiteType...

Again... not sure why I am having problems following this thread except that I have this requirement often..

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[briangriffin]

COSpringsGuy - you're exactly right, you could do this one with Specified Groups. However, I personally find that to be kind of a pain so I generally write a formula, group on the formula, and then modify the group display name if needed. It's a much more scalable approach, but mostly personal preference. As you know, in Crystal there are always at least three ways to do everything.