Duplication in summary at GroupHeader

[former_member336330]

Hello all,

Report Hierarchy:

GH 1: Branch Name     [Branch wise vendor wise Invoice summary]

GH 2: Vendor

GH 3: Doc.No.

Query Result:

Branch  Vendor        Doc.No.          AMT       Ref.No.

     B1      v1                P1               400          p1a

     B1      v1                P1               400          p1a

     B1      v1                P1               400          P1

     B1      v1                P2               100          p2a

     B1      v2                P3               400          p3a

At GH1 i want Doc No. wise sum of AMT [ i.e. 400 (for v1-p1) + 100 (for v1-p2) + 400 (for v2-p3) = 900 (for Branch1) ]

But it is giving me [ 400*3+100+400 = 17000 ]

I have tried suppressing duplicate entries for Doc.No. and then running sum, but it gives the summary at Group Footer.

And Sub - Report takes too long [almost half an hour].

Is there any other way to have this summary at group Header1 ?

[abhilash_kumar · Accepted solution]

Hi Devanshi,

Here's what you need to do:

1) Expand Field Explorer > Right-click Running Total Fields > New.

2) Choose Amount as the Field to summarize. Use 'Sum' as the summary operation.

3) Under 'Evaluate', choose 'On Change of Group' and choose the 'Doctor No. Group'.

4) Under 'Reset', choose 'Never'.

5) Insert a Crosstab and place it on Group Header #1.

6) Go to the Crosstab Expert > Add the Running Total field you created above as the 'Summarized Field'.

7) Don't add any Row or Column fields.

😎 Go to the Customize Style tab > Format Gridlines > Uncheck 'Show Grid Lines'.

9) Highlight the two 'Total' headers cells > Right-click > Format Field > Common tab > Suppress

10) Highlight the suppressed Total header cell on the Row and reduce its width so that the crosstab doesn't take a lot of space.

11) Right-click the crosstab > Grid Options > Uncheck 'Show Cell margins'. This should further reduce the size of the crosstab.

Add a nice label beside this crosstab and fine-tune the positioning and you should have what you're looking for!

I hope this helps.

-Abhilash

[former_member336330 · Accepted solution]

Thanks a ton Abhilash !!!