×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

(OP)
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")}
)

RE: Only Max Values of a group in a cross tab

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

-LB

RE: Only Max Values of a group in a cross tab

(OP)
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.

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

(OP)
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 )


RE: Only Max Values of a group in a cross tab

(OP)
I also forgot to mention that I am using Crystal Reports 2016

RE: Only Max Values of a group in a cross tab

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

-LB

RE: Only Max Values of a group in a cross tab

(OP)

(
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

(OP)
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?

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

(OP)
I received an error:

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

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

RE: Only Max Values of a group in a cross tab

(OP)
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

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

(OP)
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.

RE: Only Max Values of a group in a cross tab

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

RE: Only Max Values of a group in a cross tab

(OP)
What is a vertical display by week?

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

As in your original post, a week group with the employees listed below with the name of the group with the maximum amounts, and then below that week, another week, with employees listed with the name of the group. What you lose is the horizontal display by employee across weeks.

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

(OP)
Yes, it seems to be working correctly and it is much faster than it was. As for the top N I don't even see that as an option in the cross tab. I see Nth Largest, Nth Smallest and Nth most frequent.

RE: Only Max Values of a group in a cross tab

Sorry, yes, it would be nth largest. To capture ties, you would add {@grpwithmax} multiple times, just changing the summary to nth largest (n=1, n=2, etc.). In order to suppress the appearance of the same group name, after running the report, you would first select the n=1 (top) summary->right click->format field->suppress->x+2 and enter:

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

(OP)
I didn't get an error on the first formula but when I try the second I get:

A boolean is required here.

RE: Only Max Values of a group in a cross tab

I think you put it in the wrong place. The formula belongs in format field->DISPLAY STRING->x+2.

-LB

RE: Only Max Values of a group in a cross tab

(OP)
You are correct. I was putting it in the surpress. I put this formula in the Display string.

RE: Only Max Values of a group in a cross tab

(OP)
Which hid the values but now I have a large white space. Is there a way around the large gap?

RE: Only Max Values of a group in a cross tab

You can go in the customize style tab and uncheck “show cell margins”. You can also check “suppress empty rows”—but that didn’t work for me to eliminate that white space. I don’t have a solution for that. Sorry.

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close