Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: SQL question Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Topic: SQL question
    Posted: 11 Dec 2014 at 12:39pm
I have a report that i am writing. and i use a select for from and to dates on the invoicedate, and i also have a item select(between) which works.
for the date select the user inputs say 11/1/2014-11/30/2014.

and the report gives the qty/dollar sold for that range.

is there a way to create a column that will display the same data qty/dollar for 11/1/2013 - 11/30/2013.

if you create a SQL field will that go off the db table to pull the data no matter what date selection you do on the initial report select?

Peter F
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 15 Dec 2014 at 3:17am
are you displaying your report details section? or just at a group level?
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Posted: 16 Dec 2014 at 10:09am
right now, i am only displaying group footer.
i am surpressing/no drill down on detail.
Peter F
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 16 Dec 2014 at 11:52pm
Do you have 1 parameter as a date range? or two parameters from and to?

If one as a range
you could try in your select criteria

(invoicedate in {?dateRange} or
invoicedate in DATEADD("yyyy",-1,minimum({?dateRange})) and DATEADD("yyyy",-1,maximum({?dateRange})))

this will then return records for current and previous year. you can then use a formula to show the correct data at the group level.
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Posted: 17 Dec 2014 at 10:31am
I have two parameters that i created. fromdate; todate.
very new to this, does this go in the SQL expression Fields or in the select expert under invoicedate??

Thanks for helping!!

Peter F
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Posted: 17 Dec 2014 at 10:46am
this is what i put in the select expert.
tells me you cant summarize the field:
?prompt from date

{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,minimum({?PROMPT FROM DATE})) and
DATEADD("yyyy",-1,maximum({?PROMPT TO DATE})))



Peter F
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 18 Dec 2014 at 4:52am
You cannot use maximum in the select expert.  You might be able use it in a group select  (never tried before though).  The only option I can think of is a command or a stored procedure.
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 18 Dec 2014 at 9:37pm
you can use the min / max if the parameter was a range. Though as you are using two parameters your select expert will look like

{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}))
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Posted: 19 Dec 2014 at 3:34am
Z9962,

i changed my fields and made it a date range like your previous post, first time using that. thanks.

so, my select is.

{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?daterange} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,minimum({?DateRange})) and
DATEADD("yyyy",-1,maximum({?daterange})))


when i check the syntax, i get the error:

A Date-Time range is required here, and it highlights the following part of the statement.
DATEADD("yyyy",-1,minimum({?DateRange}))

my daterange parameter was created with type:DATE (Static)



Peter
Peter F
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 19 Dec 2014 at 3:51am
double check what i wrote...

in last example i did not use DateRange

{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
({AR_InvoiceHistoryHeader.InvoiceDate} in {?PROMPT FROM DATE} to {?PROMPT TO DATE} or
{AR_InvoiceHistoryHeader.InvoiceDate} in DATEADD("yyyy",-1,{?PROMPT FROM DATE}) to DATEADD("yyyy",-1,{?PROMPT TO DATE}))
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.000 seconds.