Sending Date Criteria to MSSQL

[JSHoltsIT]

I have a report that analyzes data based upon prompting for a date range. Within the report, I am looking at sales data from this range, the same range last year, and the YTD for the current range year and the prior range year.

My Select Expert Formula and Show SQL results are below. It doesn't seem like this is an efficient manner to pull data from SQL. How can I pass to SQL the @Prior FY Start Date value to limit the record dump from SQL to CR?

JS

SE Formula ----------------
{?SKU} = {AAcsbycpi.itemnmbr} and
{AAcsbycpi.glpostdt} >={@Prior FY Start Date}
SE Formula ----------------

Show SQL

SELECT
AAcsbycpi."custnmbr", [truncated by JS]
FROM
"table1"."dbo"."AAcsbycpi" AAcsbycpi
WHERE
AAcsbycpi."itemnmbr" = '0923884'
ORDER BY
AAcsbycpi."itemdesc" ASC,
AAcsbycpi."custnmbr" ASC,
AAcsbycpi."sopnumbe" ASC

 

[lbass]

What is the content of your formula {@Prior FY Start Date} and any nested formulas within it?

-LB

 

[JSHoltsIT]

prior FY start date: DateAdd ('yyyy',-1 , {@Current FY Start})
Current FY Start: Date (Year({@End Prompt Date}) , 1 , 1 )
End Prompt Date: Maximum({?Date Range})

 

[lbass]

{?SKU} = {AAcsbycpi.itemnmbr} and
{AAcsbycpi.glpostdt} >= Date(Year(Maximum({?DateRange}))-1,1,1)

-LB

 

[JSHoltsIT]

Nice. Now the Show SQL Query yields

WHERE
AAcsbycpi."itemnmbr" = 'CASSAV' AND
AAcsbycpi."glpostdt" >= ?

What does the "?" indicate?

 

[lbass]

I guess it refers to the date result based on the parameter value. Your report should run a little faster now.

-LB