Consolidated Sales Report

[Former Member]

Dear Experts,

To know order wise GP report I am using following query.

SELECT
T0.[DocDate]As 'Order Date',
T0.[U_VSPORDSR] As 'EyeCare No',
T0.[DocNum] As ' SO No',
T0.[NumAtCard] As 'Job No',
T0.[CardCode] As'Customer ID',
Sum(T1.[OpenSum]) AS 'Total Before Disc & Tax',
Sum(T1.[LineVat]) AS 'Tax(INR)',
T0.[DiscSum] AS 'Discount (INR)',
Sum(T1.[StockValue]) AS 'COGS Value',
(Sum(T1.[OpenSum]) - (T0.[DiscSum])) As 'Sales Amount (INR)',
(Sum(T1.[OpenSum]) - (T0.[DiscSum])) - Sum(T1.[StockValue]) As 'Gross Profit',

(Sum(T1.[OpenSum]) - (T0.[DiscSum]) - Sum(T1.[StockValue])) / (Sum(T1.[OpenSum]) - T0.[DiscSum]) * 100 As 'GP%'

FROM [Assalama_Institute_Calicut].[dbo].[ORDR] T0 INNER JOIN [Assalama_Institute_Calicut].[dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]' AND T1.[WhsCode] = 'ALIOCAL'
GROUP BY T0.[DocDate], T0.[U_VSPORDSR], T0.[DocNum], T0.[NumAtCard], T0.[CardCode],T0.[DiscSum] ORDER BY T0.[DocDate]

In this query I am getting detailed report with each order.

Now I wand to get consolidated report for a period.

Eg: From Date : 01-04-16 To Date : 31-12-2016

Total Discount Total Tax Total COGS Value Total Sales Value Total Gross Profit GP %

2500.00 196939.87 1844376.23 3938797.38 2094421.15 53.17

How to get this kind of consolidated report using query?

[jitin_chawla]

Hi,

Since I am not having the UDF's or associated values in the DEMO database, tried the following. Check if it works as expected:

SELECT

Sum(T0.[DiscSum]) AS 'Total Discount (INR)', Sum(T1.[LineVat]) AS 'Total Tax(INR)',

Sum(T1.[StockValue]) AS 'Total COGS Value', (Sum(Sum(T1.[OpenSum])) - (sum(T0.[DiscSum]))) As 'Total Sales Amount (INR)', (Sum(sum(T1.[OpenSum])) - (sum(T0.[DiscSum]))) - Sum(T1.[StockValue]) As 'Total Gross Profit',

(Sum(sum(T1.[OpenSum])) - (sum(T0.[DiscSum])) - Sum(T1.[StockValue])) / (Sum(T1.[OpenSum]) - sum(T0.[DiscSum])) * 100 As 'GP%'

FROM [Assalama_Institute_Calicut].[dbo].[ORDR] T0

INNER JOIN [Assalama_Institute_Calicut].[dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]'

AND T1.[WhsCode] = 'ALIOCAL'

ORDER BY T0.[DocDate]

Regards,

Jitin

[Former Member]

Thanks Mr.Jitin for your comment.

While executing the query I am getting following error message.

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) c