If you are unable to create a new account, please email support@bspsoftware.com

 

Creating own total singleton based on a grouped item with Group Span set

Started by NEON81, 18 Jan 2021 12:40:49 AM

Previous topic - Next topic

NEON81

Hi everyone,
First time posting here.  Apologies if I omit something necessary!  I am pulling my hair out over this one.

I have a list report with multiple groupings.  The column [Total Cost1] is grouped and has a Group Span set to another column [Current Budget Income1]. 

The summary total auto-created by Report Studio is correct at all levels of subtotals.  I need to use these summary totals in other calculations, and I understand you can't reference them if auto-created by Report Studio.

So, I've tried to build the same subtotals myself into singletons, but I just can't get the totals correct.  Here's what I'm trying, where [Project Code Group] is a higher level group that I'm aiming to subtotal for.

total(distinct[Total Cost1] for [Project Code Group])

Using distinct removes duplicate numbers that should legitimately exist (same cost on two different rows), but without it, the group spanning is ignored and the total doubles or triples up.

How do I get it to consider the same group span as per above?

Help!

NEON81

This query was resolved.  I used View Tabular Data to see what I was missing.  The total was not taking into consideration another field that I needed to narrow down on, so it was duplicating.  A good reminder to use the View Tabular Data function more often when things aren't adding up right!

i.e.

total(distinct[Total Cost1] for [Project Code Group],[Other Data Item])