Solved Total on a Report base on a Crosstab Query (1 Viewer)

Monoceros

Registered User.
Local time
Today, 12:01
Joined
Oct 30, 2006
Messages
27
I have a Report based on a Crosstab Query.

Rows : Date - value for A - Value for B - Value for C

Totals for the columns A-B-C were created automatically.

I also want to have the Total for (A+B+C). When I create a field in the detail section (ValueA+ValueB+ValueC) , this total is only shown on the report when all the columns for that date have a value.
So, when e.g. Value B is blank, the total is not shown.

How can I solve this ?

I added an example in attachment. In blue are the Totals I added.
 

Attachments

  • Example.pdf
    393.5 KB · Views: 230
Last edited:

June7

AWF VIP
Local time
Today, 04:01
Joined
Mar 9, 2014
Messages
5,425
Are A B C always the same header values? Can force columns to generate even if there is no data. Review http://allenbrowne.com/ser-67.html#ColHead

Then use Nz() in the expression.

Provide sample data if you need more help. Can provide db - follow instructions at bottom of my post.
 

Monoceros

Registered User.
Local time
Today, 12:01
Joined
Oct 30, 2006
Messages
27
Are A B C always the same header values? Can force columns to generate even if there is no data. Review http://allenbrowne.com/ser-67.html#ColHead

Then use Nz() in the expression.

Provide sample data if you need more help. Can provide db - follow instructions at bottom of my post.
Thanks I added an example in my original post.
 

June7

AWF VIP
Local time
Today, 04:01
Joined
Mar 9, 2014
Messages
5,425
Okay, did you try method from referenced link?

CROSSTAB query should produce the Total column. Use the query design wizard for CROSSTAB to get the syntax.

That isn't your raw data.
 

Monoceros

Registered User.
Local time
Today, 12:01
Joined
Oct 30, 2006
Messages
27
The Total column is not a problem. I want a Total of the Row.
 

June7

AWF VIP
Local time
Today, 04:01
Joined
Mar 9, 2014
Messages
5,425
I thought CROSSTAB would do that by its design? I need to do test. Could provide your SQL statement and sample raw data.

Otherwise, try Nz() as suggested.

Nz(A) + Nz(B) + Nz(C)
 

June7

AWF VIP
Local time
Today, 04:01
Joined
Mar 9, 2014
Messages
5,425
I verified that CROSSTAB can generate the Total column and will calculate even if any other columns are Null. Your expression to calculate Total is likely unnecessary. Example:

TRANSFORM Sum(Rates.[Rate]) AS SumOfRate
SELECT Rates.[RateLevel], Sum(Rates.[Rate]) AS [Total]
FROM Rates
GROUP BY Rates.[RateLevel]
PIVOT Rates.[Pos] IN("Plate", "Base", "None");
 

SHANEMAC51

Active member
Local time
Today, 15:01
Joined
Jan 28, 2022
Messages
310
Code:
TRANSFORM Sum(Rates.[Rate]) AS SumOfRate
SELECT Rates.[RateLevel], Sum(Rates.[Rate]) AS [Total]
FROM Rates
GROUP BY Rates.[RateLevel]
PIVOT Rates.[Pos] IN("Plate", "Base", "None")
union all
TRANSFORM Sum(Rates.[Rate]) AS SumOfRate
SELECT "total", Sum(Rates.[Rate]) AS [Total]
FROM Rates
GROUP BY "total"
PIVOT Rates.[Pos] IN("Plate", "Base", "None")
 

Users who are viewing this thread

Top Bottom