How to create and add a calculated column in a crosstab

[Former Member]

Hi All,

I need to be able to create a crosstab that looks like following:

Employee         Activity     Productive          Activity    Productive         Activity     Productive

Name                Name        Hours                     Name       Hours                   Name          Hours

                      Charts                             Check-In                     Check-Out    

John Doe          100                2                     200             1                       300               3

Jane Doe           50                 1                     100            0.5                     200               2

As seen above, The cross tab should have Activities listed in the Columns section and the Employee Name in the Rows section. The second column for Productive Hours will be a division of the No. of Units per Activity / Standard per hour for each activity.

The standards for each activity will be pre-defined. e.g. Charts activity will have a standards of 50 units per hour, Check-in Activity will have 200 units per hour and Check-out activity will have 100 units per hour.

Hence based on the above standards when the employee John Doe has completed 100 units of Charts his Productive Hours will be:

No. of Units per Activity / Standard per hour  = 100 / 5 = 2 hours as indicated in the above cross tab.

Will it be possible to build this logic in a cross-tab report? Any help is highly appreciated.

[abhilash_kumar]

Hi Nimish,

Would you be able to send me a data sample or the actual .rpt file with saved data?

The crosstab would have the Activities listed as the first column and not the way you've listed it above.

Each Activity would have Activity and Productive as the two columns below it.

-Abhilash

[Former Member]

Hi Abhilash,

I have sent you an email with the sample.

Please review the crosstab and the formula for Standard_Hours. Basically, I need the second column in the cross tab (Productive_Hours) to be calculated as below:

Productive_Hours = Sum of @Quantity

                               _____________

                          Standard_Hours per Quantity

Each Activity in the cross tab has its own Standard defined in the Standard_Hours formula.

Hence, the denominator in this formula will only pick the Standard_Hours corresponding to that particular activity.

I Look forward to hear back from you. 

Thanks,

Nimish

[Former Member]

Hi Abhilash,

I thought of 1 method to solve this but not sure how to build this within my crosstab report.

Basically, is there any way to add a column next to each of my Activities columns and use a formula in this column that takes the value from the previous cell and divides it by a specific number (this number in the denominator will be the standard for each Activity.)?

[Former Member]

Hi Abhilash,

Do you have any ideas on how I can achieve this requirement? I would greatly appreciate your response.