Skip to Content
0
Former Member
Jun 24, 2009 at 09:17 AM

How to avoid grouping by date with dates as incoming parameters?

36 Views

Hello, I have the following Query from SAP Business One set as DataSource:

SELECT T1.SlpName as 'Vendor', convert(varchar(12), T0.[U_ER_PROV])as 'City', T0.[CardCode] as 'Client Code', T3.CardName as 'Client Name', T0.DocDate as 'Date', T2.ItemCode, T4.ItemName as 'Reference Description', sum (T2.Quantity) as 'Toal Units per Reference',

avg (T2.Price)as 'Average Price', sum(T2.LineTotal) as 'Total'

FROM ODLN T0

INNER JOIN OSLP T1

ON T0.SlpCode = T1.SlpCode

INNER JOIN DLN1 T2

ON T0.DocEntry = T2.DocEntry

INNER JOIN OCRD T3

ON T0.CardCode = T3.CardCode

INNER JOIN OITM T4

ON T2.ItemCode = T4.ItemCode

group by T1.SlpName, convert(varchar(12), T0.[U_ER_PROV]), T0.[CardCode], T4.ItemName, T0.DocDate, T2.ItemCode, T3.CardName

order by T1.SlpName, convert(varchar(12), T0.[U_ER_PROV]), T0.CardCode, T0.DocDate, T2.ItemCode

What I´d like to do is, grouping in Crystal Reports, the result of this query by Item Code, not by date. The problem I have is that I need users introduce "from date" to "end date" as incoming paramater.

The way I am running it, for example I have some results like:

date Item Code Total

01/01/2009 4646_R2 120 u20AC

10/01/2009 4646_R2 34 u20AC

And I´d like to take something like this:

Item code Total

4646_R2 154 u20AC

Not grouping by date ......

Is there any way to do this using this query in Crystal Reports?

Thanks very much for your time.

Miguel A. Velasco