Solved Total on a Report base on a Crosstab Query

[Monoceros]

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.

 

[June7]

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]

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]

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]

The Total column is not a problem. I want a Total of the Row.

 

[June7]

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)

 

[Monoceros]

It worked with Nz ... thank you !!!

 

[June7]

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]

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")