Skip to Content
avatar image
Former Member

Totaling a Group

I have created a report that is grouped by Company, Contract, Department. Inside the detail section of the report are Contract Item, Department and Amount. I am trying to total by department at the Contract level. Not work

Report layout

Group 1 - Company

Group 2 - Contract

Group 3 - Department

----------Detail

Item - Department - Amount

i.e.

10

123456

02

----------1 - 02 - 500.00

----------2 - 02 - 100.00

03

---------3 - 03 - 100.00

---------3 - 03 - 100.00

04

----------4 - 04 - 50.00

----------4 - 04 - 50.00

What I am looking for is a single line that totals each of the departments from the detail at the contract group header?

i.e.

Contract-----02-----03-----04-----Amount

12345-----600.00-----200.00-----100.00-----900.00

67890-----200.00-----200.00------200.00-----600.00

Thank you in advance

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

12 Answers

  • Feb 06, 2017 at 07:52 AM

    Hi Dwayne,

    1. Insert a Crosstab Object and place it on the Contract Group header

    2. Use the 'Contract Field' as the 'Row'

    3. Use the 'Department' field as the 'Column'

    4. Use the 'Amount' field as the 'Summarized Fields' and set its Summary as 'Sum'

    5. Use the 'Customize Crosstab' tab to format the crosstab's Gridlines etc.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 04:25 PM

    Thank you for your response

    Is there a way to add a contract description to the cross-tab object

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 04:27 PM

    Or change the column names to reflect each department

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2017 at 04:31 PM

    Yes, here's how:

    1. Go to the Crosstab Expert.

    2. Highlight the field under columns > Group Options > Options tab > Check 'Customize Group Name Field' > Select 'Choose from Existing Field' > Choose the Description field from the drop-down.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2017 at 04:50 PM

    You can have manual column names too, however, you'd need a formula for that.

    At step 2 from my previous reply, instead of selecting 'Choose from Existing Field', you'd select 'Use a Formula as Group Name'.

    You'd then use a formula in here to replace your existing column names with new ones.

    E.g:

    If CurrentFieldValue = 'xx' then 'ABC'

    else CurrentFieldValue

    As for adding the PM, you'd simply add that field as the second 'Row' in the Crosstab (Go to the Crosstab Expert > add that field to the Row List).

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 04:45 PM

    Abhilash - thank you for the response

    Is there no way to edit this manually - I have some specific names I would like to use for my columns. Also I need to add a description to the cross-tab object along with who the PM is on each contract - is this possible?

    Thanks in advance

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 05:01 PM

    Abhilash - thank you for the response

    Okay the formula worked on the column name but when I add description and PM as a column I get sub columns under department. I just want to add a description for each row and who the PM is on each row.

    Thanks in advance

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 05:02 PM

    Also is there a way to change the total column description

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2017 at 06:30 PM

    Hi Dwayne,

    Could you please attach a screenshot of current vs expected output of the crosstab.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 06, 2017 at 07:25 PM
    Add comment
    10|10000 characters needed characters exceeded