Sort on Formula

[franklin1232]

I am using Crystal XI. I have a number of running totals. The running total results are than used to calculate a points value for each group which is location. The points formula is supposed to rank the locations. Problem is the formula isn't sortable since it's not based on a database field directly. Thoughts on using this formula to sort.

I am thinking I will need a custom UFL to store the points values associated to each location and write them back to the report footer creating a rankings section. Don't know how I am going to do this yet but not sure what else to try.

 

[lbass]

Are you using any group selection or section suppression? Please explain how the running totals are set up.

-LB

 

[franklin1232]

The Running Totals are reset after the location group. I also have an evaluate formula that counts values for the running total based on day of the week and time of day.

For Example:
Count record if value1 is less than 10 minutes and Value 2 is Monday - Thursday and Value3 is before 5PM. Reset count after location group.

These are placed in the Location Group Footer. Along with a subreport that does similar calucations but on a subset of data and linked to main report via a key field. Seperate shared variable calculates the points for each running total in both the main and sub report for each location. Section B of the location footer than uses a formula to combined points values from the shared variables. Adding a section was the only way for the shared variable in the sub report to have a value.

I suppress the details section with no conditions. I do not have a group selection criteria but do have record selection criteria with two parameter values for startdate and enddate.

Thinking back on it I may have wanted to create the report differently but I am working with XML data that doesn't conform to normal database form. Plus scope creep has added many addtional requirement to the report that were not anticipated such as ranking the locations.

If you think there is a way I am open to a web session to show anyone how the report is setup.

Peter

 

[lbass]

If the final points are in a shared variable formula, there is no way you will be able to sort by this--the running totals aren't really the problem. Or are you saying one option could be to show the rankings in the report footer? Would they have to be sorted there?

-LB

 

[franklin1232]

ranking in the report footer is my only option. I was thinking I would pass the values to an array using some global variables. I have a custom function that can sort the array. Then loop the array to set the text value of a formula field in the footer. Overall it is getting way more complicated than I feel comfortable with. More times than not that means there is an easier way and am overthinking things. In this case I may just need to use the array option.

 

[lbass]

Yes, I think you have to go the array route.

-LB