Sorting on Formula in Group Footers 1

[NixyJ]

Hi there,

I know there are loads of threads on this subject but none seem to quite answer my question... if anyone can help I would be so grateful!

I am using CR 10.
The data structure I'm working with is
Designer,ProductNo, SubProductNo.
I have buying and sales data at SubProductNo level

Group 1 of my report is Designer Level and Group 2 is Product No level.

In the footer of group 1 I have sums of sales and buys to eliminate the subproduct detail level. I then have a field that calculates sellthru - i.e. (Total of Sales) / (Total of Buy).

Within the Designer Group (which is sorted alphabetically) I need the products sorted on the sellthru formula in descending order.

Please can somebody show me the way as the sellthru calculation is not showing as on option in the sorting expert. I'm withering away here!

Many thanks!
NixyJ

 

[lbass]

If this is available to you, try creating a SQL expression {%sellthru} like the following:

(SELECT (SUM(AKA.`SalesAmount`) / SUM(AKA.`BuyAmount`) FROM Table AKA WHERE AKA.`ProductNo` = Table.`ProductNo`)

Substitute your table name for "Table" and substitute the correct field names for "SalesAmount","BuyAmount" and "ProductNo".

Next place {%sellthru} in the detail section of your report. Then right click on it and insert a maximum. Because it is already a calculated field, the result will be the same in the detail and the group footer section, but you need the maximum function so that you can use topN. Go to report->topN/group sort, and in the tab for ProductNo, choose "maximum of
%sellthru", descending order.

This should give you the results you are looking for.

-LB

 

[NixyJ]

Thank you so much for getting back to me so quickly - can't tell you how much I appreciate it.

I'm going to give your method a go but I'll have to try to modify it a bit and if you have any advice I would be, again, so grateful.

My Sales and Buy amounts come out of the same field from a Stock Movement table dependent on a combination of other fields. i.e

SUBPRODUCT MOVEMENT TYPE CODE
1234_5 -1 "Sale"
1234_5 +10 "Buy"
1234_5 -2 "Manual" "A"
1234_5 +1 "Manual" "B"

In the above sample data, where [Type] = "Sale" or (Type = "Manual" and

="A"), [Movement] is summed to be the SalesAmount Field in the Report and everything where [Type] = "Buy" or (Type = "Manual" and [Code]="B"), [Movement] is summed to be the BuyAmount field.  This happens at SUBPRODUCT level and then for each PRODUCT (Group 1 in the report) is totalled.

Therefore my sellthru is calculated off of 2 summaries of calculated fields, as opposed to being summaries of 2 distinct fields in the original data table which is what your solution requires.

My fault entirely, I should have explained that in my original post - my apologies!

I'm going to give it a go by including the above logic into the select statement that you provided - but if you can offer any help I'd be so grateful,

Many Thanks,
NixyJ

 

[NixyJ]

Dear lbass,(and all other gurus)

I so desparately need your help - i just cannot get this to work at all and I'm on the verge of tears!

Subsequent to my earlier post I have created a simple test data table to test out lbass's solution and have the following in an sql expression:

(SELECT sum(AKA."Buy") FROM tbl_TrialStockMovement AKA
WHERE AKA."sku" = tbl_TrialStockMovement."sku")

...but it won't save - I keep getting the error description:

"The column prefix 'tbl_TrialStockMovement' does not match with a table name or alias name used in the query."

I've tried every combination thinkable of brackets, ' and ".. but to no avail - this is just the very basic beginnings of the solution I need and I can't even get that to work - please, please, please help me!

Yours thankfully,
NixyJ

 

[lbass]

I don't know what you mean by "created a simple test data table". Try saving your report under another name and use the table in the report. The above syntax works for me when I test it, but syntax can vary depending upon datasource, I believe. Try adding in fields from the available list to see what the correct use of quotes is with your tables/fields and then flesh is out that way.

I'm only learning how to use SQL expressions, and I'm not sure how to do something as complex as what you need (or whether it can be done), but will try to look at it when I have time later today. Or perhaps someone with more experience in this area can jump in.

Basically you need to be able to return a value for sellthru that is no longer a summary, but acts like a detail level value. You might be able to accomplish this by creating a view, but I personally don't know how to go about that.

-LB

 

[NixyJ]

Hi there!

Many thanks for all of your efforts above - I really appreciate it. Unfortunately I think this is probably something that can't be done - there are several layers of complexity that i can't represent in an SQL expression.

I've tried your above ideas to try and get the SQL expresion to even save but still to no avail! It seems such a simple thing to want to do but ho hum - maybe the weekend will bring fresh perspective!

Many thanks once again,
NixyJ

 

[lbass]

NixyJ,

I did spend some time working on this, and was able to build in part of the additional criteria, but not the "or" piece, and finally gave up. If you specified your datasource and connectivity, there might be a SQL expert here who could provide a solution, or at least help you determine the correct syntax for SQL expressions for your setup.

As a last resort, as a substitute for doing this in CR, you could easily export the report to Excel and do the sort on percentages there. This might not be an acceptable solution though, depending on your distribution needs...

-LB