besy way to do

[unknownly]

Hi,

I am using CR 8.5 ver. I need to build a report on 2 important fields

1 username
2 status ( have about 20 distinct status)

and show count of status for each user

the report should look like

stat1 stat2 stat3...stat18 total worked
(col1+col2)
user1 2 4 5 1 12 6
user2 1 3 6 1 11 4
user3 4 4 2 3 13 8
total 7 11 13 5 35 18

I tried to do a cross tab by grouping on username but didn't know to work with worked col? and didn't know how to display status as columns dynamically...without using cross tab any help or advice please.

Thanks in advance,
Sweetie

 

[hneal98]

When you do a crosstab, you drag the status to the column place holder in the crosstab grid. The same with the user column, since you want that as a row header, you drag it the row place holder at the left of the grid.

 

[unknownly]

yes, I have tried the corss tab. But do I get the Worked col which is again a sum of few status together.

Thank you,

 

[lbass]

If you have a static number of statuses, you could create a second crosstab where you use userID as the row with no column, and with the following formula as the summary (using sum):

if {table.status} in ["stat1","stat2"] then 1

You could then overlay the second crosstab on the first, suppressing the row labels, and eliminating the parts of the grid that would overlap.

If the number of statuses could vary, then you could either use the two crosstabs and place the summary on the left, or you could add the formula above to the first crosstab as a summary, suppress the values in the inner cells and for the column totals. The row total for the two statuses would then appear below the row total for all statuses. You could edit the row total label so that you could indicate what the second value represented.

Otherwise you would have to do a manual crosstab where you insert a group on userID, and create a formula for each status like the one for two statuses. You would place these in the detail section and then right click on each and insert a summary(sum), and then suppress the detail section. This would be more work, and if the number of statuses can vary, you would have blank space before the final column for the two statuses.

-LB

 

[unknownly]

Thank you lbass. I worked like magic.