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

 

[wanzek]

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.

 

[lbass]

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

 

[wanzek]

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.

 

[lbass]

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

 

[wanzek]

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

A boolean is required here.

 

[lbass]

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

-LB

 

[wanzek]

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

 

[wanzek]

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

 

[lbass]

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