Skip to Content
0

Totaling a Group

Feb 03, 2017 at 09:47 PM

76

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

12 Answers

Abhilash Kumar
Feb 06, 2017 at 07:52 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 04:25 PM
0

Thank you for your response

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 04:27 PM
0

Or change the column names to reflect each department

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 06, 2017 at 04:31 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 06, 2017 at 04:50 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 04:45 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 05:01 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 05:02 PM
0

Also is there a way to change the total column description

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 06, 2017 at 06:30 PM
0

Hi Dwayne,

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

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 06, 2017 at 07:25 PM
0
Share
10 |10000 characters needed characters left characters exceeded