Date Parameter in SSRS

SSAS and SSRS
1

Hi,

I am a newbie to SSRS and I'd like to create a begin and end date parameter for a report I'm creating in SSRS. Here is my query:

SELECT
VCHR.VEND_ID
,P.PROJ_ID
,A.ACCT_ID
,VCHR.INVC_DT
,V.VEND_NAME
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) as CST_AMOUNT
,VCHR.VCHR_NO
,V.S_CL_SM_BUS_CD
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL

FROM WEBAPP_CP.DELTEK.V_VEND V
RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON V.VEND_ID = VCHR.VEND_ID
RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
JOIN WEBAPP_CP.DELTEK.ACCT A
ON LNHS.ACCT_ID = A.ACCT_ID
JOIN WEBAPP_CP.DELTEK.PROJ P
ON LNHS.PROJ_ID = P.PROJ_ID

GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,V.VEND_NAME
,P.PROJ_ID
,VCHR.INVC_DT
,A.ACCT_ID
,VCHR.VCHR_NO
ORDER BY PROJ_ID, VEND_ID ASC

The query is in SSRS as a text query. I have a BegInvDate and EndInvDate parameter created on the report. I want the user to be able to choose a date range and the report look at the column VCHR.INVC_DT and pull invoices for that range. Any advice?

Thanks !

2

can you try this.
Delete the parameters from your report,the parameters from your query will automatically create parameters for you in the report.
You can get the calendar for your parameters by setting the parameter properties in the report.
Declare @BegInvDate date
EndInvDate date
SELECT
VCHR.VEND_ID
,P.PROJ_ID
,A.ACCT_ID
,VCHR.INVC_DT
,V.VEND_NAME
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) as CST_AMOUNT
,VCHR.VCHR_NO
,V.S_CL_SM_BUS_CD
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL

FROM WEBAPP_CP.DELTEK.V_VEND V
RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON V.VEND_ID = VCHR.VEND_ID
RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
JOIN WEBAPP_CP.DELTEK.ACCT A
ON LNHS.ACCT_ID = A.ACCT_ID
JOIN WEBAPP_CP.DELTEK.PROJ P
ON LNHS.PROJ_ID = P.PROJ_ID
WHERE VCHR.INVC_DT BETWEEN @BegInvDate AND EndInvDate
GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,V.VEND_NAME
,P.PROJ_ID
,VCHR.INVC_DT
,A.ACCT_ID
,VCHR.VCHR_NO
ORDER BY PROJ_ID, VEND_ID ASC

1 Like
3

Another tip for you: After SSRS creates the date parameter or you create it yourself, double click the parameter to look at its properties. The date type defaults to "Text." If you change the data type to "Date/Time," SSRS will display a calendar date picker icon next to the field so that users do not have to type in a date.