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]
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.