Not able to sort on formula field

[wesleylamb]

Not able to sort on formula field


I am basing a report on a one long spread sheet Called 'course' it is laid out like this.

ID Course_Title Attendees Location

1 Benefits 1(x) Bimrmingham
2 Wages 3 London
3 Wages 5 Bristol
4 Pension 2(x) Scotland
5 Benefits 4 Rugby


If there are less than 3 attendees per course then the course is cancelled and (x) is place in the field

I have grouped on Course_Title and need three formulas fields in each group

1.Courses Advertised(using 1 Formula) :
CountID
count({Course2003.ID},{Course2003.Title})


2.CourseCancelled PerSubject(using 2 formulas):
StringSearchForX
if instr({Course2003.Attendees},"x&quot> 0
then 1
else 0

sumOfStringSearchforX
sum({@StringSearchForX},{Course2003.Title})


3.Percent Course Canx Per Subject(using 1 formula):
percentCanceled
{@sumOfStringSearchForX} /{@CountID} *100

I wish to sort on field number 3.'Percent Canx per Subject' but cannot do so. Is there anyother way I can go
about solving the problem. Thanks.

Cc

 

[Ngolem]

You cannot sort or group on this formula becase it contains summary functions....because of the math involved I don't think you can do a TopN on it either...if it was a straight sum you could...you can still try it though.

Your formulas are very short and choppy...they could be combined a bit and maybe through the use of manual calculations you might overcome the "summary" problems....let us see....

ID Course_Title Attendees Location

1 Benefits 1(x) Bimrmingham
2 Wages 3 London
3 Wages 5 Bristol
4 Pension 2(x) Scotland
5 Benefits 4 Rugby

You don't show the look of the final report...Are you just interested in the % cancellation....that is the formula you want to group/sort on?

perhaps you should show what the final report would look like and we can go from there.









Jim Broadbent

 

[lbass]

Even though you can't use your third formula in TopN, you might try inserting a summary on {@StringSearchForX} and checking whether using this in TopN approximates the results you want. If the number of CourseIDs per Title were the same for each Title, this would do the trick.

Otherwise, you can accomplish the sort "manually", although it is not the ideal solution. After you have run the report, document the percent canceled by subject (print the report for reference), and then go to the change group expert->options and choose "specified order" and enter the order that will result in the sort you want. It may be tedious, but it works--if you are willing to do this with each report run.

-LB

 

[wesleylamb]

Thanks for your replies I will have a look this morning.

The report looks like this

-----------------------------------------------------------
Benefits Courses Advertised 2 Percent Cancelled 50%
Courses Cancelled 1
-----------------------------------------------------------
Wages Courses Advertised 2 Percent Cancelled 0%
Courses Cancelled 0
-----------------------------------------------------------Pensions Courses Advertised 10 Percent Cancelled 50%
Courses Cancelled 5
-----------------------------------------------------------


The bar chart will be taken from the Percent Cancelled figure.

Thanks again

cc