expand a crosstab report to add a percentage

I created a crosstab report that lists the Sales, Cost of Goods, and the Margin by Grouping and by Month that the Sale was made.  Everything works fine, but now the user would like me to calculate a Percentage which is Margin / Sales.  I tried to do this first by taking the Crosstab query and making a new query that will select the data from that query and calculate the percentage, but the monthly data is set and I don't want that, so I went back to the query that created the crosstab and tried to add it there.  This is the code that I wrote before I started to make changes:
 TRANSFORM Format(Round(Sum([WrkFRCGSUnion].[Amount]),0),'#,###') AS SumOfAmount
SELECT WrkFRCGSUnion.Grouping, WrkFRCGSUnion.[P/LCategory], Format(Round(Sum([WrkFRCGSUnion].[Amount]),0),'#,###') AS [Grand Total]
FROM Specifics, WrkFRCGSUnion INNER JOIN WrkFRCGSPerSales ON WrkFRCGSUnion.Grouping = WrkFRCGSPerSales.Grouping
WHERE (((Format([DATEBILLED],'yyyymmdd')) Between (Select BegDate from Specifics where Id = 1) And (Select EndDate from Specifics where ID =1)))
GROUP BY WrkFRCGSUnion.Grouping, WrkFRCGSUnion.[P/LCategory]
PIVOT Format([DATEBILLED],'yyyymmdd');
In design mode I added a new column called Percent, but it won't show anything  when I describe it as an expression.  This is what I wrote:
Percent: IIf([WrkFRCGSUnion].[P/LCategory]="3-Margin",Format([Grand Total]/[WrkFRCGSPerSales].[SumOfAmount],"#.##%"),"")
Can this be done?  
Any suggestions would be appreciated.
Who is Participating?
GRayLConnect With a Mentor Commented:
Yes, but don't you see, in the crosstab, it is not named WrkFRCGSUnion!Amount (value 4) - it is - qxtName![20070902] (value 4) - which is why I said if you were going to query the crosstab query, you would need to know all the names of the pivot fields which are date values.  You have 5 dates in Sept 2007 listed.  
You have to remember that every row you put into the SELECT clause must be balanced In the GROUP BY clause if the Insert is not an aggregate function.  I don't see anything in your Percent: that is aggregate so that phrase must also be in the the GROUP BY.
LenTompkinsAuthor Commented:
I went back to the drawing board and added a colum that I called percent and added this code:
Percent: Format(Sum([WrkFRCGSUnion].[Amount],"#,###")  and that worked fine.  Then I wanted to divide that total by a total that I have in my linked table and I get this message:
Tried to execute a query that doesn't include the specified expression " Format(Sum([WrkFRCGSUnion].[Amount])/[WrkFRCGSPerSales].[Total],"#,###")" as part of an aggregate function.

Any thoughts as to how to do this?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Going back to your question:

Percent: IIf([WrkFRCGSUnion].[P/LCategory]="3-Margin",Format([Grand Total]/[WrkFRCGSPerSales].[SumOfAmount],"#.##%"),"")

can this be done?  I say no.  Firstly, SumOfAmount is not a field in table WrkFRCGSPerSales, it is a calculated value deposited in each of the Pivot date columns.  It would probably vary from date to date.  You are trying to get two values per date, the sum of Amount on that date (which is what the crosstab is all about), and the ratio of the grand total / sum of Amount on that date - from the SELECT clause of the crosstab - which I would say you cannot do - from the same query.  If you knew all the dates that would be produced by the crosstab, you could write a second query that used the crosstab to calculate the percent.  BTW do you not have the ratio backward in the question.  I see you have it corrected in the post above.
LenTompkinsAuthor Commented:
GRayL  I realize that my first attempt couldn't be done, but in my last attempt I was recalculating the total Margin records and then dividing by the total Sales from a linked query .
When I just calculate the sum it creates the correct total and I want to add the division part.  That Cant be done?
If not,  what suggestions do you have to create a report like this:
Grouping      P/LCategory      Grand Total      20070902      20070903      20070904      20070905      20070906      
Account      1-Sales      34,740                              4,
Account      2-CGS      9,926                              149
Account      3-Margin      24,813                              4,377

except add a Percentage for the Grand total amount.      
I also increased the point value because I may have to go about creating this report in a completely different fashion and I would  like some ideas.  Thanks            
Thanks, but why the B?  Didn't like the answer?
All Courses

From novice to tech pro — start learning today.