How to have a new Total Column in crosstab query

[mrchonginhk]

I have a crosstab query that has Month as Column Header field.
So now the output columns sometimes looks like

Name/Jan/Feb/Mar

Sometimes it is

Name/Sep/Oct

I actually want another column that adds up the total for all months, ie

Name/Jan/Feb/Mar > Name/TOTAL/Jan/Feb/Mar

Name/Sep/Oct > Name/TOTAL/Sep/Oct

 

[Norie]

How did you create the original crosstab?

If you used the wizard it's normally added automitcally.

Anyway all you need to add is another row heading for the total column, something like this:

Sum(ENT.Procedures) AS [Total Of Procedures]

Where Procedures is the value field of the crosstab.

By the way, the reason your Name column is appearing in the middle of the months could be because of some sort of ordering 'problem'.

You can use something like this in the PIVOT clause to make sure you get the right order.

PIVOT Format([DateField],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

That, or something similar might already be there. Perhaps it just needs adjusting.

 

[mrchonginhk]

Thank you.
I found the solution myself
Just drag the value field as row field and I get it.

Have a good day.