Only Max Values of a group in a cross tab
Only Max Values of a group in a cross tab
(OP)
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
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
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
(
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
RE: Only Max Values of a group in a cross tab
(
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")}
)
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
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 )
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
(
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")}
)
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
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
RE: Only Max Values of a group in a cross tab
Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.
RE: Only Max Values of a group in a cross tab
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
RE: Only Max Values of a group in a cross tab
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
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
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
RE: Only Max Values of a group in a cross tab
The problem with a manual crosstab is I need it to be able to run for the life of a job and there isn't a maximum number of weeks that I could determine.
RE: Only Max Values of a group in a cross tab
You could also do a crosstab with multiple summaries using topN (with N = 1, 2, etc.), but how many possible groups are there? If it’s just a few, there might be a way to suppress all but the ties.
Did you get the revised SQL to work? How was the speed?
-LB
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
whileprintingrecords;
stringvar x := currentfieldvalue;
false
Then select the remaining summaries->right click->format objects->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
stringvar x;
if currentfieldvalue=x then
"" else currentfieldvalue
You are essentially using the suppress and display string formatting options for variables that will control whether the group name will show or not.
-LB
RE: Only Max Values of a group in a cross tab
A boolean is required here.
RE: Only Max Values of a group in a cross tab
-LB
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
RE: Only Max Values of a group in a cross tab
-LB