Grouping and Group Tree

[DeafBug]

I write/maintain over 70 reports using VB6 to manage the reports. We want to turn on the GroupTree for Preview as clients are getting tired of wadding thru the preview to find the data they want prior to printing a few of hundred of pages.

So I started working on the Change Group Options by putting a formula in each report so it looks pretty and meaningful in the Group Tree. Some reports are simple others are complicated. Almost all of them have sorting as well as grouping.

The problem is that we had CR7 for a very long time until we upgraded to 10. So back then I was using what BusinessObjects KB states to deal with sorting and grouping.

http://technicalsupport.businessobj...?cmd=displayKC&docType=kc&externalId=c2008546

In KB, it is for sorting, but I applied the same thing by creating two groups to handle issues with grouping. Now wiht the Group Tree displayed. It shows an extra heirarchy that is blank. I have searched here and learned that you can't suppress them.

So how do I combine the two separate group formulas into one without having the results thrown off. That should take care of the blank grouping showing in the Group Tree.

Remember the sensitive data is the numeric part for grouping. Strings are no biggie. There are lots of numeric data that we group by. I will use Store number for example. It is a number from 1-9999. Converting it to string will throw off the order.

Instead of 1,2,12,23,123,234,1234,2345 it would be 1,12,123,1234,2,23,234,2345.

I know when you output a formula using select or if-then all output must be the same data type. So I need to combine the two groups of two different data types into one.

How do I do that?

Again using VB6 and CR10 SP1.

Thanks.

 

[kenhamady]

The trick is to convert the number into a string that still sorts in numeric order. Something like this will work as long as the number of zeros is more than your longest numeric:

ToText ( {numeric.field} , "00000000" )


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[DeafBug]

Thanks for the tip. Sorry for the late reply as I got busy on something else.

I did your trick and it worked but one problem.

The old numeric group was set to Descending. While the old string group was set to Ascending.

I put it together in one group, it works just find and the group tree doesn't show that extra group. But all the grouping is done in one sort method.

Is there a way to say to do Desc. or Asc. sort based on how it was grouped? FWIW, I pass a numeric parmeter value to the report from VB. Based on that value, it groups by that field. It is a GroupBy formula I have.

Thanks.

One idea I had was using subreport but not sure how it would work.

 

[kenhamady]

In v11 you can do a condition formula to set the order as A or D, but to do this in v10 you would have to have 2 groups, which puts you back to where you started. If it were two numerics you could flip the sign but there is no equivalent that I know of for strings.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[DeafBug]

Business Objects KB has an article on this.

It shows a piece of code for VB for RDC that allows you to change the group sort direction before the report is viewed. So I added a Select Case statement in VB. Depending on the value of the GroupBy it will set the sort direction.

And it works.

 

[kenhamady]

Of course it does. You could have also changed the original group field itself from the RDC without the Totext. For some reason I was only thinking in terms of what you could do in the report designer.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/