cross tab in XI 1

[Lhuffst]

I have to create a report that counts the number of reviews per person with percentages for each type of review per person. I have everything but the percentages. The only percentage that I've been able to make work was an overall percentage.

What I need is:

Amendments HPA NonAbutting
[cnt] [%] [cnt] [%] [cnt] [%]
edwards 4 57 20 36 0 0
Smith 2 29 8 15 0 0
.
.
JULY total 7 100 46 100 3 100


what I get is:

Amendments HPA NonAbutting
[cnt] [%] [cnt] [%] [cnt] [%]
edwards 4 100 20 100 0 0
Smith 2 100 8 100 0 0
.
.
JULY total 7 100 46 100 3 100



Any ideas are greatly appreciated.
lhuffst

 

[Lhuffst]

I finally figured it out. I was doing a column percentage when I needed a row percentage and I needed to do a sum of the total and count.

This works fine except for the rounding now.

I have

cnt %
1 13 (really is .125)
0 0
2 25
5 63 (really is .625)

this actually adds up to 101%. Any suggestions on how to display this disparity?
Thanks again
lhuffst

 

[lbass]

You can use the method in thread767-1536302 in order to add decimals to the crosstab percentage result.

-LB

 

[Lhuffst]

Thanks for the help. Now the user has added a new twist and I'm hoping you can provide some guidance.

The layout that the users actually want is:

col1 col2 col3 total selfTotal col4 col5
emp1
emp2

Where
Total= Really not sure what this column is yet but put it in so you could see layout. It is like the row total but the numbers don't add up. Waiting on user input.
Self total. Actually the row total.
Col4 & Col5 - This is one of the columns but users want this specifically after the row total and the counts are separate.

I tried to put in a separate cross tab but they just overlay each other. I checked keep size/position relative but that didn't help either.
Is there a way to manipulate the columns so I can have the total column on the inside or do you know how to have the two cross tabs print side by side and not touch?
thanks
lhuffst

 

[lbass]

With this level of complexity, I would consider creating a manual crosstab instead of using inserted ones.

-LB

 

[Lhuffst]

ok. By manual cross tab do you mean make each column individually and not use cross tab at all?

 

[Lhuffst]

Another question. I tried creating 2 cross tabs and placing them side by side. This worked but the 2nd cross tab overwrites part of the first one. I clicked on "lock position and size" but it still seems to overwrite. Do you know how to keep them separate?
lhuffst

 

[lbass]

Yes, I did mean to create separate formulas per column, with groups on month and name. I'm not sure what field (if any) you are using for the column field, so if you want more info about a manual crosstab, please reply with that.

If you create the crosstabs and in design mode make sure they don't overlap, then go to the section expert->check "relative positions", you will not get overlap.

-LB

 

[Lhuffst]

thanks for all the help

 

[Lhuffst]

The relative position worked great! On the 2nd cross tab, I really don't need to the row column because I am trying to place this right next to the first cross tab. I have supressed and sized the row name as small as possible but wasn't sure if there was a way to have omitted from the report entirely. I need it for the cross tab data selection but not on display.
thanks again for all your help.

 

[lbass]

Suppression and resizing is the best you can do.

-LB

 

[Lhuffst]

Hi. My report looks great thanks to you but unfortunately, the users just threw another monkey wrench into the deal.

On the column side of the cross tab, we have 4 items that we are displaying. The last column is work days available. I am being asked if we can calculate this value and have the answer displayed. I can calculate the value but can't figure out how or where to change the cross tab to say if it is items 1-3 print their column titles and values, if it is item 4 then print their column title and calculate the result.

Is this doable?
lhuffst