Ranking on a calculated group field.

[tkaz]

Using CR XI
SQL 2008 DB

I have new report development that includes date range parameters to provide weekly/monthly/yearly reporting.

It includes ONE Group, Plant ID.
Database fields include Production,RapTons, and Shingle Tons that were placed on the detail line and summarized at group and report level.

There is a calculation on the detail line and summary for RapTons + Shingle Tons by plant.

I inserted a calculation for each plant for %All Recycle that is:

if Sum ({PlantDay.TonsProducedTotal}, {PlantDay.PlantID})= 0
then 0
else Sum ({@All Recycle}, {PlantDay.PlantID})/Sum ({PlantDay.TonsProducedTotal}, {PlantDay.PlantID})*100

Plant1 6708.84(ProdwklyTtl) 1673.31 RapTons(wklyTtl) 47 Shingles(wklyTtl) 1720.31 AllRecycle(WklyTtl) 25.64%
Plant2 1833.80(ProdwklyTtl) 441.02 RapTons(wklyTtl) 61.01 Shingles(wklyTtl) 502.03 AllRecycle(WklyTtl) 27.38%
Plant3 390.54(ProdwklyTtl) 71.02RapTons(wklyTtl) 18.02Shingles(wklyTtl) 89.04AllRecycle(WklyTtl) 22.80%

Plant 2 would Rank 1
Plant 1 would Rank 2
Plant 3 would Rank 3

I need to rank the plants based on that %, but it is not available in the group sort options because it is a calculated field.

I looked here for some examples and it seems there may be an option to use a sql expression, but I do not know how to do that...really don't understand how to start this to achieve the results I need at a group level.

If anyone has time to help me to do this and understand why I'm doing what, I would be grateful.

If I need to provide any further information, let me know...and thanks in advance.

 

[lbass]

Do you need to sort based on the rank? Or do you need the ranks to be displayed based on the current sort?

Are you working with more than one table? If so, what are they and how are they linked?

What is the content of {@All Recycle}?

-LB

 

[tkaz]

I need them to sort in order of rank.

I am using 3 DB tables and a view.

CODates
Plant
PlantDay
vDailySummary

All are inner joins: I am including the SQL generated by the joins and the selection criteria...

SELECT "PlantDay"."PlantID", "PlantDay"."Date", "PlantDay"."TonsProducedTotal", "Plant"."PlantName", "vDailySummary"."TonsProducedTotal", "vDailySummary"."RAPTons", "CoDates"."WK", "CoDates"."GLMonth", "vDailySummary"."NatGasCCF", "vDailySummary"."BIOCONV", "vDailySummary"."RFOCONV", "vDailySummary"."Date", "vDailySummary"."ShingleTons"
FROM (("AsphaltPlant"."dbo"."Plant" "Plant" INNER JOIN "AsphaltPlant"."dbo"."PlantDay" "PlantDay" ON "Plant"."ID"="PlantDay"."PlantID") INNER JOIN "AsphaltPlant"."dbo"."CoDates" "CoDates" ON "PlantDay"."Date"="CoDates"."ActualDate") INNER JOIN "AsphaltPlant"."dbo"."vDailySummary" "vDailySummary" ON (((((("PlantDay"."ID"="vDailySummary"."ID") AND ("PlantDay"."PlantID"="vDailySummary"."PlantID")) AND ("PlantDay"."Date"="vDailySummary"."Date")) AND ("PlantDay"."ProdWasteCalcPercent"="vDailySummary"."ProdWasteCalcPercent")) AND ("PlantDay"."ProdWasteCalcTons"="vDailySummary"."ProdWasteCalcTons")) AND ("PlantDay"."TonsTickedTotal"="vDailySummary"."TonsTickedTotal")) AND ("PlantDay"."TonsProducedTotal"="vDailySummary"."TonsProducedTotal")
WHERE ("PlantDay"."PlantID"=N'04024' OR "PlantDay"."PlantID"=N'04025' OR "PlantDay"."PlantID"=N'04026' OR "PlantDay"."PlantID"=N'04027' OR "PlantDay"."PlantID"=N'04028' OR "PlantDay"."PlantID"=N'04129' OR "PlantDay"."PlantID"=N'04130') AND ("PlantDay"."Date">={ts '2012-02-05 00:00:00'} AND "PlantDay"."Date"<{ts '2012-02-11 00:00:01'})
ORDER BY "PlantDay"."PlantID", "CoDates"."WK"

I have some idea of how to write an expression, but not when I need to have the results at this group level. I appreciate any help you can give me.

 

[lbass]

What is the content of {@All Recycle}?

-LB

 

[tkaz]

Sorry...you asked me that the first time.

It is the sum of RAPTons + ShingleTons...

 

[lbass]

Please show the actual formula.

-LB

 

[tkaz]

{vDailySummary.RAPTons}+{vDailySummary.ShingleTons}

 

[lbass]

Are you using a parameter for date or for any other field in your record selection formula? Because you can't use parameters in SQL expressions.

-LB

 

[tkaz]

Yes, I am using a start and end date parameter from PlantDay.Date
and a plant pick from PlantId.Plant.

I had read in some of your other posts about this that you couldn't use parameters in the SQL Expression and I don't know if there is a way to do this by just removing the parameters and selecting dates between and hard coding the plants. I'm not at all familiar with using SQL expressions. I've used SQL commands and I'm pretty good with SQL but I have no clue what to do to get started.

 

[lbass]

You could use a command as your sole datasource and then build the summary into the command, and then you could add the parameters to the subquery. Is that an option?

-LB

 

[tkaz]

I can try that and see...I am willing to try anything at this point to get this project off my desk It's very frustrating to get so close to the finish and get stuck on something like this I don't know how to handle.

I really appreciate your help!

 

[lbass]

Just realized that you have a multi-value parameter for plant ID, so I don't think this will work either--since commands in XI don't take multiple values, unless you use a subreport solution or you are willing to enter Plant IDs as a single string.

-LB

 

[lbass]

SELECT "PlantDay"."PlantID",
"PlantDay"."Date",
"PlantDay"."TonsProducedTotal",
"Plant"."PlantName",
"vDailySummary"."TonsProducedTotal",
"vDailySummary"."RAPTons",
"CoDates"."WK",
"CoDates"."GLMonth",
"vDailySummary"."NatGasCCF",
"vDailySummary"."BIOCONV",
"vDailySummary"."RFOCONV",
"vDailySummary"."Date",
"vDailySummary"."ShingleTons",
(
select sum(B."RAPTons"+B."ShingleTons")
from PlantDay A, vDailySummary B, CoDates C
where A."Date"=C."ActualDate" and
A."ID"=B."ID" and
A."PlantID"=C."PlantID" and
A."Date"=B."Date" and
A."ProdWasteCalcPercent"=B."ProdWasteCalcPercent" and
A."ProdWasteCalcTons"=B."ProdWasteCalcTons" and
A."TonsTickedTotal"=B."TonsTickedTotal" and
A."TonsProducedTotal"=B."TonsProducedTotal" and
A."PlantID"='{?Plant}' and
A."Date">={?StartDate} AND
A."Date"<{?EndDate}+1 and
A."PlantID"=PlantDay."PlantID"
) "All Recycle",
(
select sum(A."TonsProducedTotal")
from PlantDay A, vDailySummary B, CoDates C
where A."Date"=C."ActualDate" and
A."ID"=B."ID" and
A."PlantID"=C."PlantID" and
A."Date"=B."Date" and
A."ProdWasteCalcPercent"=B."ProdWasteCalcPercent" and
A."ProdWasteCalcTons"=B."ProdWasteCalcTons" and
A."TonsTickedTotal"=B."TonsTickedTotal" and
A."TonsProducedTotal"=B."TonsProducedTotal" and
A."PlantID"='{?Plant}' and
A."Date">={?StartDate} AND
A."Date"<{?EndDate}+1 and
A."PlantID"=PlantDay."PlantID"
) "TPTotal"

FROM (("AsphaltPlant"."dbo"."Plant" "Plant"

INNER JOIN "AsphaltPlant"."dbo"."PlantDay" "PlantDay" ON
"Plant"."ID"="PlantDay"."PlantID"
)

INNER JOIN "AsphaltPlant"."dbo"."CoDates" "CoDates" ON
"PlantDay"."Date"="CoDates"."ActualDate"
)

INNER JOIN "AsphaltPlant"."dbo"."vDailySummary" "vDailySummary" ON
(((((("PlantDay"."ID"="vDailySummary"."ID"
) AND
(
"PlantDay"."PlantID"="vDailySummary"."PlantID"
)
) AND
(
"PlantDay"."Date"="vDailySummary"."Date"
)
) AND
(
"PlantDay"."ProdWasteCalcPercent"="vDailySummary"."ProdWasteCalcPercent"
)
) AND
(
"PlantDay"."ProdWasteCalcTons"="vDailySummary"."ProdWasteCalcTons"
)
) AND
(
"PlantDay"."TonsTickedTotal"="vDailySummary"."TonsTickedTotal"
)
) AND
(
"PlantDay"."TonsProducedTotal"="vDailySummary"."TonsProducedTotal"
)

WHERE (
"PlantDay"."PlantID"={?Plant}
) AND
(
"PlantDay"."Date">={?StartDate} AND
"PlantDay"."Date"<{?EndDate}+1
)
ORDER BY "PlantDay"."PlantID", "CoDates"."WK"

Try the above, and then in the report, create a formula like this:

{command.All Recycle}%{command.TPTotal}

Place this in the detail section and insert maximum on it at the group level (in order to activate the group sort. Then apply the group sort.

To enter the plant IDs as a string, you have to NOT use single quotes around the parameter and also you need to use "in". Then at the prompt, enter the following including the parens, e.g.: (N'04026',N'04027')

I'm not sure the subqueries are set up correctly as I don't know your database. Not sure whether all the links to the view are necessary, and I assumed that the link to the first table wasn't required either, so might be wrong there.

Be sure to create the parameters within the command screen, on the right. If you need to add a list of values, you can do that in the main report, after the command is compiled.

-LB

 

[crystalkiwibruce]

I had a similar problem at a client site and solved the problem with a User Function Library. Save the value for each plant in a Export File (Simple XML format) using the Export UFL, and then retrieveit WhileReadingRecords to use on the Group Sort.

Does require two runs of the report to get the records in the correct sequence (first run saves the values correctly, but sorts buy the previous sort sequence). Second run has the same values, but has sorted correctly.

Download a trial version from my web site.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[tkaz]

Thank you both for your suggestions. I've not had a chance to try either today because I've been in meetings all day. I will post when I get a chance to try and get one of these working.

 

[tkaz]

I wanted to let you know that I wound up writing a stored procedure to accomplish what I wanted. I did all the summary for the values by plant in the SP and then placed those values in the detail line, created the formulas I needed. I then could group on the AllRecycle% value and sort descending.

Thank you for always offering great solutions!