We have a report that displays the following info:
Originally this report was made by a consultant and I was tasked with making this report run off of a date range (the consultant didn’t have any date info in here).
So I added the parameters and what not, and I can get it to actually run off of the StartDate and EndDate I put in there… But any PY or YTD field has “GETDATE” (today’s date) for comparisons. I would like to change those all to the StartDate/EndDate values - so that the PY Current Month will actually run off of StartDate, instead of today’s date…
Here’s a sample of the dataset:
(
SELECT
PART.ProdCode as ProdGrpCode,
CUSTOMER.GroupCode as CustGrpCode,
PART.PARTNUM AS PART_NUMBER,
CUSTOMER.NAME AS CUSTOMER_NAME,
SALESREP.SALESREPCODE AS SALES_REP,
round(SUM( CASE WHEN MONTH(ORDERHED.ORDERDATE) = MONTH(GETDATE()) AND YEAR(ORDERHED.ORDERDATE) = YEAR(GETDATE()) THEN [OrderHed].[OrderAmt] / NULLIF(OrderHed.TotalLines, 0) ELSE 0 END),2) AS CURRENT_MONTH_ORDER_AMOUNT
As you can see it’s grabbing the CURRENT_MONTH_ORDER_AMOUNT via the GETDATE function.
How can I change that to the StartDate/EndDate? I tried changing it to -
MONTH(@StartDate()) AND YEAR(ORDERHED.ORDERDATE) = YEAR(@StartDate()) THEN [OrderHed].[OrderAmt] / NULLIF(OrderHed.TotalLines, 0) ELSE 0 END),2) AS CURRENT_MONTH_ORDER_AMOUNT
But it doesn’t like that at all. Same with parenthesis around “@StartDate”…
Here’s an example of the errors I get -
or 