Only Max Values of a group in a cross tab

[wanzek]

I need to create a report that is a cross tab because it will have to automatically expand based on days entered. The columns will be the week ending date. The rows down will be the employees. I need the value under each column to be the max value label. So for example:
Employee A
Group A = 10 hours
Group B = 12 hours
Group C = 15 hours
Group D= 5 hours

Employee B
Group A = 15 hours
Group B = 12 hours
Group C = 2 hours
Group D = 14 hours

​

Week Ending 2/2/2019
Employee A Group C
Employee B Group A

 

[lbass]

Are the hours shown for each “group” a sum of hours for that particular group? Or are the hours a field value at the detail level?

-LB

 

[wanzek]

They would need to be a summed for each group for each employee.

 

[lbass]

Create a SQL expression like the following which will give you the maximum hours per week per group per employee. The syntax and punctuation will depend both upon your datasource and your version of CR. I am using "group" here to represent the field that gives you "Group A", etc.

(
select sum(`hours`)
from table a
where a.`Employee ID`=`table`.`Employee ID` and
a.`group`=`table`.`group` and
{fn WEEK(a.`Date`)}={fn WEEK(`table`.`Date`)} and
{fn YEAR(a.`Date`)}={fn YEAR(`table`.`Date`)}
)

This is using an access database with CR XI. You might have to use different functions, etc.

Once you get the SQL expression working, you would create a formula {@grpwithmax:
if {%sumamt}=maximum({%sumamt},{table.Date},"weekly") then
{table.group}

Insert a crosstab in the report header or footer, and then add employee as the row, and {table.date} as the column. Click on "group options" (below the column field) and choose "for each week" and then choose "the value printed will be the last date in the period". Then add {@grpwithmax} as your summary field and change the summary to maximum. You should also suppress the column totals, as the result will be incorrect at that level.

-LB

 

[wanzek]

Thank you for the feedback! I am struggling getting my SQL expression to work but I will keep trying!

(
select sum("JCCD"."ActualHours")
from "JCCD" a
where a.Employee ="JCCD"."Employee" and
PhasePart = Left (ToText("JCCD"."Phase"),2 ) and
{fn WEEK(a.`Date`)}={fn WEEK("JCCD"."ActualDate")} and
{fn YEAR(a.`Date`)}={fn YEAR("JCCD"."ActualDate")}
)

 

[lbass]

What kind of database are you using? What version of CR?

-LB

 

[wanzek]

I did get it to work but it takes a really long time to run. I am trying to figure out what I can do to get it to speed up. It takes several hours to complete.

 

[lbass]

If you made this into a command you might gain significant speed. If you want to paste a copy of your SQL statement (database->show SQL query) into this thread, I can troubleshoot it. Also copy in your select statement (report->selection formula->record) so we can what passes to the SQL statement.

-LB

 

[wanzek]

Here is the SQL Query:
SELECT "HQCO"."Name", "JCCD"."JCCo", "JCCD"."Job", "JCCD"."PhaseGroup", "JCCD"."Phase", "JCCD"."CostType", "JCJP"."Description", "JCCD"."UM", "JCCD"."EstUnits", "JCCD"."ActualUnits", "JCCD"."EstHours", "JCCD"."ActualHours", "JCCD"."ActualDate", "JCJM"."Description", "JCCH"."UM", "JCCH"."PhaseUnitFlag", "JCCD"."PostedDate", "JCCD"."ActualCost", "JCCD"."EstCost", "JCCD"."ProjCost", "JCCD"."ProjUnits", "JCCD"."ForecastCost", "JCCH"."CostType", "JCCH"."ActiveYN", "JCJP"."ActiveYN"
FROM ((("Viewpoint"."dbo"."JCCD" "JCCD" INNER JOIN "Viewpoint"."dbo"."JCJM" "JCJM" ON ("JCCD"."JCCo"="JCJM"."JCCo") AND ("JCCD"."Job"="JCJM"."Job")) INNER JOIN "Viewpoint"."dbo"."JCJP" "JCJP" ON ((("JCCD"."JCCo"="JCJP"."JCCo") AND ("JCCD"."Job"="JCJP"."Job")) AND ("JCCD"."PhaseGroup"="JCJP"."PhaseGroup")) AND ("JCCD"."Phase"="JCJP"."Phase")) INNER JOIN "Viewpoint"."dbo"."HQCO" "HQCO" ON "JCCD"."JCCo"="HQCO"."HQCo") LEFT OUTER JOIN "Viewpoint"."dbo"."JCCH" "JCCH" ON (((("JCCD"."JCCo"="JCCH"."JCCo") AND ("JCCD"."Job"="JCCH"."Job")) AND ("JCCD"."PhaseGroup"="JCCH"."PhaseGroup")) AND ("JCCD"."Phase"="JCCH"."Phase")) AND ("JCCD"."CostType"="JCCH"."CostType")
WHERE "JCCD"."ActualDate"<{ts '2019-02-03 00:00:00'} AND "JCCD"."CostType"=4 AND ("JCCD"."Phase">=' ' AND "JCCD"."Phase"<='zzzzzzzzzz') AND "JCCD"."Job"=' 1183.' AND "JCCD"."JCCo"=1
ORDER BY "JCCD"."JCCo", "JCCD"."Job", "JCCD"."PhaseGroup"

Selection Formula:
(if {?DateOption} = "A" then {JCCD.ActualDate}<= {?EndWklyDate}else 1=1 ) and
{JCCD.CostType} = {?LaborCT} and
{JCCD.Phase} in {?BegPhase} to {?EndPhase} and
{JCCD.Job} = {?EndJob} and
{?Company}={JCCD.JCCo} and
(if {?DateOption} = "P" then {JCCD.PostedDate} <= {?EndWklyDate}else 1=1 )

 

[wanzek]

I also forgot to mention that I am using Crystal Reports 2016

 

[lbass]

Please also post your final SQL expression for {%sumamt}.

-LB

 

[wanzek]

(
select sum("ActualHours")
from "JCCD" a
where a.Employee ="JCCD"."Employee" and
a.Phase = Left("JCCD"."Phase",2 ) and
{fn WEEK(a.ActualDate)}={fn WEEK("JCCD"."ActualDate")} and
{fn YEAR(a.ActualDate)}={fn YEAR("JCCD"."ActualDate")}
)

 

[wanzek]

Also, something I was wondering if what happens if something ties for first place as the max? How does it pick the one it shows? Is there a way to get all of the tops values?

 

[lbass]

For speed, I would create this query as a command as follows. Create a new report and insert this command. Note that you will need to create the parameters within the command screen on the right (have you worked with commands before?). Make sure the datatypes are correct--I assumed that dates were actual dates. I am unsure how you create an alias name in your database, but used "sumamtactualdt" and "sumamtposteddt" to name the expressions at the end of the field list. There are many ways a command can fail, so if this doesn't work, please report back on what the error message is. If you can get this to work you should see a significant increase in speed. The entire command will process at the server and by removing the where clause and instead adding those conditions to the From clause, fewer records will be returned. You should be able to just copy this directly into a new report into the command screen (top of the table list).

SELECT "HQCO"."Name",
"JCCD"."JCCo",
"JCCD"."Job",
"JCCD"."PhaseGroup",
"JCCD"."Phase",
"JCCD"."CostType",
"JCJP"."Description",
"JCCD"."UM",
"JCCD"."EstUnits",
"JCCD"."ActualUnits",
"JCCD"."EstHours",
"JCCD"."ActualHours",
"JCCD"."ActualDate",
"JCJM"."Description",
"JCCH"."UM",
"JCCH"."PhaseUnitFlag",
"JCCD"."PostedDate",
"JCCD"."ActualCost",
"JCCD"."EstCost",
"JCCD"."ProjCost",
"JCCD"."ProjUnits",
"JCCD"."ForecastCost",
"JCCH"."CostType",
"JCCH"."ActiveYN",
"JCJP"."ActiveYN",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = Left("JCCD"."Phase",2 ) and
{fn WEEK(a."ActualDate")}={fn WEEK("JCCD"."ActualDate")} and
{fn YEAR(a."ActualDate")}={fn YEAR("JCCD"."ActualDate")}
) "sumamtactualdt",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = Left("JCCD"."Phase",2 ) and
{fn WEEK(a."PostedDate")}={fn WEEK("JCCD"."PostedDate")} and
{fn YEAR(a."PostedDate")}={fn YEAR("JCCD"."PostedDate")}
) "sumamtposteddt"

FROM ((("Viewpoint"."dbo"."JCCD" "JCCD"

INNER JOIN "Viewpoint"."dbo"."JCJM" "JCJM" ON
("JCCD"."JCCo"="JCJM"."JCCo") AND
("JCCD"."Job"="JCJM"."Job") and
(
"JCCD"."CostType" = {?LaborCT} and
"JCCD"."Phase" >= '{?BegPhase}' and
"JCCD"."Phase" <= '{?EndPhase}' and
"JCCD"."Job" = '{?EndJob}' and
"JCCD"."JCCo"= {?Company} and
(
(
'{?DateOption}' = 'A' and
"JCCD"."ActualDate"<= {?EndWklyDate}
) or
(
'{?DateOption}' = 'P'and
"JCCD"."PostedDate" <= {?EndWklyDate}
)
)
)
)

INNER JOIN "Viewpoint"."dbo"."JCJP" "JCJP" ON
((("JCCD"."JCCo"="JCJP"."JCCo") AND
("JCCD"."Job"="JCJP"."Job")) AND
("JCCD"."PhaseGroup"="JCJP"."PhaseGroup")) AND
("JCCD"."Phase"="JCJP"."Phase")
)

INNER JOIN "Viewpoint"."dbo"."HQCO" "HQCO" ON
"JCCD"."JCCo"="HQCO"."HQCo"
)

LEFT OUTER JOIN "Viewpoint"."dbo"."JCCH" "JCCH" ON
(((("JCCD"."JCCo"="JCCH"."JCCo") AND
("JCCD"."Job"="JCCH"."Job")) AND
("JCCD"."PhaseGroup"="JCCH"."PhaseGroup")) AND
("JCCD"."Phase"="JCCH"."Phase")) AND
("JCCD"."CostType"="JCCH"."CostType")

ORDER BY 2,3,4

Once you get this working, we can work on how to use the date condition (actual or posted) and how to show ties.

-LB

 

[wanzek]

I received an error:

Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

 

[lbass]

I don't have access to an Oracle database here to test a few things. I think the problem might be in the expressions with left() and maybe the functions. Can you test a couple of things for me in a new command? First just create a simple command like this in a new report:

SELECT
"JCCD"."Phase",
"JCCD"."ActualHours",
"JCCD"."ActualDate",
"JCCD"."Employee",
"JCCD"."CostType",
"JCCD"."Job",
"JCCD"."JCCo",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = substr("JCCD"."Phase",1,2 )
)

From "Viewpoint"."dbo"."JCCD" "JCCD"
WHERE "JCCD"."ActualDate"<{ts '2019-02-03 00:00:00'} AND
"JCCD"."CostType"=4 AND
("JCCD"."Phase">=' ' AND
"JCCD"."Phase"<='zzzzzzzzzz') AND
"JCCD"."Job"=' 1183.' AND
"JCCD"."JCCo"=1

Let me know if this compiles. If it does, add the remainder of the expression, along with the alias and see if it errors out again.

-LB

 

[wanzek]

That didn't work but I switched it back to the left and it did:

SELECT
"JCCD"."Phase",
"JCCD"."ActualHours",
"JCCD"."ActualDate",
"JCCD"."Employee",
"JCCD"."CostType",
"JCCD"."Job",
"JCCD"."JCCo",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = left("JCCD"."Phase",2 )
)

From "Viewpoint"."dbo"."JCCD" "JCCD"
WHERE "JCCD"."ActualDate"<{ts '2019-02-02 00:00:00'} AND
"JCCD"."CostType"=4 AND
("JCCD"."Phase">=' ' AND
"JCCD"."Phase"<='zzzzzzzzzz') AND
"JCCD"."Job"=' 1183.' AND
"JCCD"."JCCo"=1

 

[lbass]

Okay, so now add in the lines with the week and year functions. If that errors out, try using week() and year() instead of {fn week()} and {fn year()} and see it that works. If it does, then before you revise the command I sent you, let me know, as I have slightly different approach with the command that should be faster than the one above.

-LB

 

[wanzek]

I was able to get this working but I am still unsure how to handle values when there are ties. You mentioned you might have a solution to that.

 

[lbass]

Try this command, assuming you found that week() and hour() worked. Only one half of the union all statement will execute based on the type of hours chosen by parameter, so only one SQL expression will need to be compiled--should make this faster. The order by clause belongs at the end of the entire statement only. I added a "field" called "DateType" that you can use to see what type of dates are being pulled.

SELECT "HQCO"."Name",
"JCCD"."JCCo",
"JCCD"."Job",
"JCCD"."PhaseGroup",
"JCCD"."Phase",
"JCCD"."CostType",
"JCJP"."Description",
"JCCD"."UM",
"JCCD"."EstUnits",
"JCCD"."ActualUnits",
"JCCD"."EstHours",
"JCCD"."ActualHours",
"JCCD"."ActualDate",
"JCJM"."Description",
"JCCH"."UM",
"JCCH"."PhaseUnitFlag",
"JCCD"."PostedDate",
"JCCD"."ActualCost",
"JCCD"."EstCost",
"JCCD"."ProjCost",
"JCCD"."ProjUnits",
"JCCD"."ForecastCost",
"JCCH"."CostType",
"JCCH"."ActiveYN",
"JCJP"."ActiveYN",
'Actual Date' "DateType",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = Left("JCCD"."Phase",2 ) and
WEEK(a."ActualDate")=WEEK("JCCD"."ActualDate") and
YEAR(a."ActualDate")=YEAR("JCCD"."ActualDate")
) "sumamtactualdt"

FROM ((("Viewpoint"."dbo"."JCCD" "JCCD"

INNER JOIN "Viewpoint"."dbo"."JCJM" "JCJM" ON
("JCCD"."JCCo"="JCJM"."JCCo") AND
("JCCD"."Job"="JCJM"."Job") and
(
"JCCD"."CostType" = {?LaborCT} and
"JCCD"."Phase" >= '{?BegPhase}' and
"JCCD"."Phase" <= '{?EndPhase}' and
"JCCD"."Job" = '{?EndJob}' and
"JCCD"."JCCo"= {?Company} and
'{?DateOption}' = 'A' and
"JCCD"."ActualDate"<= {?EndWklyDate}
)
)

INNER JOIN "Viewpoint"."dbo"."JCJP" "JCJP" ON
((("JCCD"."JCCo"="JCJP"."JCCo") AND
("JCCD"."Job"="JCJP"."Job")) AND
("JCCD"."PhaseGroup"="JCJP"."PhaseGroup")) AND
("JCCD"."Phase"="JCJP"."Phase")
)

INNER JOIN "Viewpoint"."dbo"."HQCO" "HQCO" ON
"JCCD"."JCCo"="HQCO"."HQCo"
)

LEFT OUTER JOIN "Viewpoint"."dbo"."JCCH" "JCCH" ON
(((("JCCD"."JCCo"="JCCH"."JCCo") AND
("JCCD"."Job"="JCCH"."Job")) AND
("JCCD"."PhaseGroup"="JCCH"."PhaseGroup")) AND
("JCCD"."Phase"="JCCH"."Phase")) AND
("JCCD"."CostType"="JCCH"."CostType")

union all

SELECT "HQCO"."Name",
"JCCD"."JCCo",
"JCCD"."Job",
"JCCD"."PhaseGroup",
"JCCD"."Phase",
"JCCD"."CostType",
"JCJP"."Description",
"JCCD"."UM",
"JCCD"."EstUnits",
"JCCD"."ActualUnits",
"JCCD"."EstHours",
"JCCD"."ActualHours",
"JCCD"."ActualDate",
"JCJM"."Description",
"JCCH"."UM",
"JCCH"."PhaseUnitFlag",
"JCCD"."PostedDate",
"JCCD"."ActualCost",
"JCCD"."EstCost",
"JCCD"."ProjCost",
"JCCD"."ProjUnits",
"JCCD"."ForecastCost",
"JCCH"."CostType",
"JCCH"."ActiveYN",
"JCJP"."ActiveYN",
'Posted Date' "DateType",
(
select sum(a."ActualHours")
from "JCCD" a
where a."Employee" ="JCCD"."Employee" and
a."Phase" = Left("JCCD"."Phase",2 ) and
WEEK(a."PostedDate")=WEEK("JCCD"."PostedDate") and
YEAR(a."PostedDate")=YEAR("JCCD"."PostedDate")
) "sumamtposteddt"


FROM ((("Viewpoint"."dbo"."JCCD" "JCCD"

INNER JOIN "Viewpoint"."dbo"."JCJM" "JCJM" ON
("JCCD"."JCCo"="JCJM"."JCCo") AND
("JCCD"."Job"="JCJM"."Job") and
(
"JCCD"."CostType" = {?LaborCT} and
"JCCD"."Phase" >= '{?BegPhase}' and
"JCCD"."Phase" <= '{?EndPhase}' and
"JCCD"."Job" = '{?EndJob}' and
"JCCD"."JCCo"= {?Company} and
'{?DateOption}' = 'P'and
"JCCD"."PostedDate" <= {?EndWklyDate}
)
)

INNER JOIN "Viewpoint"."dbo"."JCJP" "JCJP" ON
((("JCCD"."JCCo"="JCJP"."JCCo") AND
("JCCD"."Job"="JCJP"."Job")) AND
("JCCD"."PhaseGroup"="JCJP"."PhaseGroup")) AND
("JCCD"."Phase"="JCJP"."Phase")
)

INNER JOIN "Viewpoint"."dbo"."HQCO" "HQCO" ON
"JCCD"."JCCo"="HQCO"."HQCo"
)

LEFT OUTER JOIN "Viewpoint"."dbo"."JCCH" "JCCH" ON
(((("JCCD"."JCCo"="JCCH"."JCCo") AND
("JCCD"."Job"="JCCH"."Job")) AND
("JCCD"."PhaseGroup"="JCCH"."PhaseGroup")) AND
("JCCD"."Phase"="JCCH"."Phase")) AND
("JCCD"."CostType"="JCCH"."CostType")

ORDER BY 2,3,4

Regarding showing ties, I had been thinking you could use topN and include ties, but that option isn't available within a crosstab. You could easily do a vertical display by week. Or you could create a manual crosstab. What is the maximum number of weeks you would want to show in a crosstab?

-LB