Crosstab & computed columns

[Bracha]

Hi All,
is thee any way to specify the column in a crosstab report?
I have a tabel whick includes userid, code and value, the code can be x, y or z, and I want to show it as follows:

X Y (X+Y)Z
user A
user B
user C
Total

 

[lbass]

What version of CR? You could create a formula:

if {table.code} in ["X","Y"] then 1 else 2

You this as your first (topmost) column field, and {table.code} as your second column. You can then suppress any unwanted totals or subtotals.

-LB

 

[Bracha]

Thank you lbass!

the formula give me the abilty to set value of a specific field, but I want to add a computed column (e.g. X+Y...).
In the crosstab I would like to see both X, Y and X+Y.

Do you have any idea how to do it?
(I'm using v10)

-Bracha

 

[lbass]

I don't follow what you're saying. The formula will cause a subtotal of X+Y to be displayed. You need to provide more information. What is your column field that results in X and Y? What is your summary field and the summary you are using? Please show a sample crosstab that shows the summaries you want to see.

-LB

 

[Bracha]

I have a table that stroe login data of users, breaking by several types (e.g. wait time, call time, break time, lunch time etc.), the table structure is: userid, type (of time), and value (in seconds)
I need to pull a 'user times' report which calculate the values and show them as follows:
login time = all times
work time = wait + call
% work time = work / login
non work time = break + lunch

-Bracha

 

[lbass]

Please show a sample of how you would expect the crosstab to look, using the field names you have just now described.

-LB

 

[Bracha]

'login time' 'work time' '% work time' 'non work time' 'break time'
user a
user b
user c
...

 

[lbass]

I think it would be easiest if you just created a manual crosstab. Insert a group on user ID, and then create conditional formulas like:

//{@worktime}:
if {table.type} in ["wait","call"] then {table.seconds}

//{@non-worktime}:
if {table.type} in ["lunch","break"] then {table.seconds}

//{@breaktime}:
if {table.type} =" break" then {table.seconds}


Add {table.seconds} to the detail section, along with these formulas. Then right click on each and insert a sum at the user ID level. Then drag the groupname into the group footer and suppress the detail section and group header. For the percentage formula, use:

sum({@worktime},{table.userID}) % sum({table.seconds},{table.userID})

-LB

 

[Bracha]

thanks lbass, can you please explain me again how to do it?...
1. where should I create the formulas? on report -> formula workshop -> formula fields?
2. what do you mean in 'insert a group on user id'? how and where should I do it?
3. how can I add something into the detail section?

 

[lbass]

1-report->field explorer->formula->new

2-Place the user ID field in the detail section and then go to insert->group and choose the user ID field.

3-Drag fields or formulas from the field explorer into the detail section of the report.

-LB

 

[Bracha]

I created formulas, added the grouping but can't find how to sum the data for each user, any idea?
lbass- do you have a link for documentation of such things?

thanks

 

[lbass]

After placing the formulas in the detail section, right click on each and insert->summary->sum->group level.

These are fundamentals of report design. You might want to purchase one of the George Peck books.. The Complete Reference: Crystal Reports (version #).

-LB

 

[Bracha]

thanks lbass, it works fine, and now I need to add few more features...:

* All the values are in secondes, so I would like to divide the summary fields by 3600 (to get it in hours), can I do it by the format tab?

* I have another grouping level which call "manager", each manager can have number of users.
The following columns need to be added as follows:
1. number of users = will be "1" in user level, and "distinct count" on manager level.
2. average login time = login time \ number of users (will be equal to 'login time' in user level, and avg in manager level)
3. average work time = work time \ number of users.

the final report sould be as follows:
manager user # users login avg login work avg work % work
man1 u1 1 8.00 8.00 5.00 5.00 62.5%
man1 u2 1 10.00 10.00 7.00 7.00 70%
man1 u3 1 7.00 7.00 4.00 4.00 57.14%
man1 TOTAL 3 25.00 8.33 16.00 5.33 64%
man2 ........

do you have any idea how to do it?
thanks again for you helpful responses!!!

 

[lbass]

Just change your detail level formulas to end in:

...then {table.seconds}/3600

Insert a group on manager and make it the highest order group. Not sure why you would want to show an average or the number of users at the user level. If you want it though, insert a distinct count on {table.user} at both the user and the manager levels. You can also get the manager figures for login and work by inserting sums on the detail level formulas at the manager level. To get the averages, create two formulas, one for {@login} and one for {@work}:

sum({@login},{table.manager})/distinctcount({table.user},{table.manager})

Then create the percentages by using:

(sum({@work},{table.manager})/distinctcount({table.user},{table.manager})) % (sum({@login},{table.manager})/distinctcount({table.user},{table.manager}))

-LB