Grouping on date ranges 1

[JSHoltsIT]

My current selection criteria:

record Selection Formula
not ({RM00101.CUSTCLAS} in ["50IC", "50R", "50W", "IC"]) and
{Sales Trx History.VOIDSTTS} = 0.00 and
{Sales Trx History.SOPTYPE} in 3.00 to 4.00

group selection formula
{Sales Trx History.GLPOSTDT} in {?Date Range}

When I add {Sales Trx History.GLPOSTDT} in {?Date Range} to the record selection formula, only the first date of my date range is returned in the report.

My current group options sort and group by Sales Trx History.GLPOSTDT in ascending order, the section prints for each day.

My first goal is to clean up the report and only pull from SQL the date range requested by the user. The second goal is to add data (order count, net sales, gross profit, gross margin & average net order) to the right from a last year during the same date range.

How do I limit the scope of the data grab from sql to the date range and then pull data from last year as well?

TIA

Jason

 

[kskid]

First remove the group selection formula (it will only display one). That is restricting the remaining sales records for that day from being selected/displayed.

 

[JSHoltsIT]

no joy. When I remove the group selection formula I still only get one day.

 

[lbass]

Did you set up the date parameter to allow range values? What are you selecting for date range?

-LB

 

[IanWaterman]

Provided your daterange Parameter is a range you can use

{Sales Trx History.GLPOSTDT} = {?Date Range}

When you say last years data, is that full year or corresponding period.

If whole of last year you can add

(
{Sales Trx History.GLPOSTDT} = {?Date Range}
or
year({Sales Trx History.GLPOSTDT}) = year(min({?Date Range}))-1
)

Ian

 

[JSHoltsIT]

Ian - yours is the approach I took. I have a feeling that my goal of only bringing in data from the two date ranges (start to end this year & start to end last year) is not going to become a reality.

JS

 

[lbass]

Were you able to get more than one date to show?

Of course you can bring in two date ranges. If you want to show the same date range this year and last, you could use a record selection formula like this:

(
{Sales Trx History.GLPOSTDT} = {?Date Range} or
{Sales Trx History.GLPOSTDT} in dateserial(year(minimum({?Date Range}))-1, month(minimum({?Date Range})), day(minimum({?Date Range}))) to
dateserial(year(maximum({?Date Range}))-1, month(maximum({?Date Range})), day(maximum({?Date Range})))
)

It is unclear what problems you are still running into.

-LB

 

[JSHoltsIT]

Thanks LB, that did the trick!

JS