Sort Group based on Percent Formula

[jenfromjax]

Let me start by saying that I've seen the other threads on this topic and gather that a SQL Expression is required, which gets inserted into details, then a max summary created (thank you lbass), but that is where I am stuck. I've been looking at this all day so please forgive me if I've left something out or it doesn't make sense. Thanks so much. ~Jen

I'm running Crystal 2008 connected to a view from SQL Server 2008. I need to first calculate the sales region's % sold toward quota, then sort them in decending order based on that %. The report is grouped on {@Region}.

First the formula to calculate %:

IF {@Region Manager Quota} <> 0 THEN
{@TCVTowardQuota RG} / {@Region Manager Quota} * 100
ELSE 0

-----------------------------------------------------------

Formulas referenced above:

//{@Region Manager Quota}
IF {jen_vw_RepAccountActivity.DealRegion} = {@Region} THEN {jen_vw_RepAccountActivity.DealRegionManagerQuota} ELSE
IF {jen_vw_RepAccountActivity.eRegion} = {@Region} THEN {jen_vw_RepAccountActivity.eRegionManagerQuota} ELSE
999999999999

-----------------------------------------------------------

//{@TCVTowardQuota RG}
if ({@Region} in ["New Accounts"])
then Sum ({jen_vw_RepAccountActivity.TCVTowardQuota}, {@Region})- 4757802.50

else if ({@Region} in ["IBS Eastern"])
then Sum ({jen_vw_RepAccountActivity.TCVTowardQuota}, {@Region}) + 4451379.50

else if ({@Region} in ["Western"])
then Sum ({jen_vw_RepAccountActivity.TCVTowardQuota}, {@Region}) + 306423.00

else Sum ({jen_vw_RepAccountActivity.TCVTowardQuota}, {@Region})

-----------------------------------------------------------

//{@Region}
IF {jen_vw_RepAccountActivity.DealSalesOrg} = FALSE
AND {jen_vw_RepAccountActivity.eSalesOrgID} = FALSE
THEN "Exceptions" ELSE

IF {jen_vw_RepAccountActivity.DealSalesOrg} = True
THEN {jen_vw_RepAccountActivity.DealRegion} ELSE

IF {jen_vw_RepAccountActivity.DealSalesOrg} = FALSE
AND {jen_vw_RepAccountActivity.eRegionID} <> [2, 18, 21, 63]
AND {jen_vw_RepAccountActivity.eSalesOrgID} = True
THEN {jen_vw_RepAccountActivity.eRegion} ELSE

"Exceptions

 

[lbass]

Because of your nested formulas, I think this is too complex for the SQL expression approach. Another approach might be to export your current report that contains the calculated percentages to Excel, and then use the resulting Excel spreadsheet as the datasource for a new report, that you should now be able to sort because the group values are now appearing as "fields". Not ideal, but it should do the job, though in two steps.

-LB

 

[jenfromjax]

Thank you for the quick reply!

Crap... This particular report is scheduled and distributed so I don't get a chance to export to excel first. What if I can create all of these formulas in my view so they're done on the server side instead? Then all I would need is the sum of TCVTowardQuota and the formula for their %. Is that doable?

Many thanks,
~Jen

 

[jenfromjax]

Now that I am thinking about it I don't know if I can do TCVTowardQuota in the view. Is one nested formula an issue?

~Jen

 

[lbass]

If you can return {@Region} as a field in a view, then I think you could use SQL expression to return:

Sum ({jen_vw_RepAccountActivity.TCVTowardQuota}, {v.Region})

Once you have that, you can plug it into a conditional formula that you then use in a percentage formula. The issue is that you have to end up with a formula for percentage that you place in the detail section and insert a maximum on--because inserting the summary is what activates the group sort.

-LB