MS Query - Adding Parameter in complex SQL

[harshaltannu]

I have a SQL which I have built uising MS Query. It links 11 tables together, and displays the output by grouping the key fields.

Here is the SQL code for it -
SELECT SWAPP_SRVCE_CENTRE_DIM.srvce_svc_ctr_nm, SWAPP_PRVOF_OFFICER_DIM.prvof_class_cd, SWAPP_PVACT_ACTIVITY_DIM.pvact_activity_cd, SWAPP_CU_CL_UNIT_LK.cu_prvn_hr_grp_nm, SWAPP_CALENDAR_DATE_DIM.caldr_yr_text, Count(SWAPP_PVACT_ACTIVITY_DIM.pvact_doc_num) AS 'Count of Docs', Sum(SWAPP_OACTY_ACTIVITY_FACT.actvy_elapsed_time_amt) AS 'Total Time'
FROM BIASDWDM.dbo.SWAPP_ACTVY_CLASS_DIM SWAPP_ACTVY_CLASS_DIM, BIASDWDM.dbo.SWAPP_CALENDAR_DATE_DIM SWAPP_CALENDAR_DATE_DIM, BIASDWDM.dbo.SWAPP_CU_CL_UNIT_LK SWAPP_CU_CL_UNIT_LK, BIASDWDM.dbo.SWAPP_EC_EMPLR_CU_LK SWAPP_EC_EMPLR_CU_LK, BIASDWDM.dbo.SWAPP_EOC_EMP_OL_CU_DIM SWAPP_EOC_EMP_OL_CU_DIM, BIASDWDM.dbo.SWAPP_GPACT_ACTIVITY_FACT SWAPP_GPACT_ACTIVITY_FACT, BIASDWDM.dbo.SWAPP_LOCHP_LOCATION_HLP SWAPP_LOCHP_LOCATION_HLP, BIASDWDM.dbo.SWAPP_OACTY_ACTIVITY_FACT SWAPP_OACTY_ACTIVITY_FACT, BIASDWDM.dbo.SWAPP_PRVOF_OFFICER_DIM SWAPP_PRVOF_OFFICER_DIM, BIASDWDM.dbo.SWAPP_PVACT_ACTIVITY_DIM SWAPP_PVACT_ACTIVITY_DIM, BIASDWDM.dbo.SWAPP_SRVCE_CENTRE_DIM SWAPP_SRVCE_CENTRE_DIM
WHERE SWAPP_OACTY_ACTIVITY_FACT.srvce_centre_surr = SWAPP_SRVCE_CENTRE_DIM.srvce_centre_surr AND SWAPP_PVACT_ACTIVITY_DIM.pvact_activity_surr = SWAPP_OACTY_ACTIVITY_FACT.pvact_activity_surr AND SWAPP_PRVOF_OFFICER_DIM.prvof_officer_surr = SWAPP_OACTY_ACTIVITY_FACT.prvof_officer_surr AND SWAPP_CALENDAR_DATE_DIM.caldr_date_surr = SWAPP_OACTY_ACTIVITY_FACT.actvy_date_surr AND SWAPP_ACTVY_CLASS_DIM.actvy_class_surr = SWAPP_OACTY_ACTIVITY_FACT.actvy_class_surr AND SWAPP_GPACT_ACTIVITY_FACT.pvact_activity_surr = SWAPP_OACTY_ACTIVITY_FACT.pvact_activity_surr AND SWAPP_GPACT_ACTIVITY_FACT.lochp_group_surr = SWAPP_LOCHP_LOCATION_HLP.lochp_group_surr AND SWAPP_EOC_EMP_OL_CU_DIM.eoc_emp_ol_cu_surr = SWAPP_LOCHP_LOCATION_HLP.eoc_emp_ol_cu_surr AND SWAPP_EC_EMPLR_CU_LK.ec_emplr_cu_surr = SWAPP_EOC_EMP_OL_CU_DIM.ec_emplr_cu_surr AND SWAPP_EC_EMPLR_CU_LK.cu_cl_unit_surr = SWAPP_CU_CL_UNIT_LK.cu_cl_unit_surr
GROUP BY SWAPP_SRVCE_CENTRE_DIM.srvce_svc_ctr_nm, SWAPP_PRVOF_OFFICER_DIM.prvof_class_cd, SWAPP_PVACT_ACTIVITY_DIM.pvact_activity_cd, SWAPP_CU_CL_UNIT_LK.cu_prvn_hr_grp_nm, SWAPP_CALENDAR_DATE_DIM.caldr_yr_text
HAVING (SWAPP_PRVOF_OFFICER_DIM.prvof_class_cd='OS') AND (SWAPP_PVACT_ACTIVITY_DIM.pvact_activity_cd='IR')
ORDER BY SWAPP_SRVCE_CENTRE_DIM.srvce_svc_ctr_nm, SWAPP_PRVOF_OFFICER_DIM.prvof_class_cd, SWAPP_PVACT_ACTIVITY_DIM.pvact_activity_cd, SWAPP_CU_CL_UNIT_LK.cu_prvn_hr_grp_nm, SWAPP_CALENDAR_DATE_DIM.caldr_yr_text


I am now trying to add parameters to the query. I need to add 'caldr_yr_text' from table SWAPP_CALENDAR_DATE_DIM as my new parameter, so that my query can retrieve relevant data depending upon the date entered in my input cell.

When I try to add parameter to the above query, I get the message saying 'Invalid column name [Yr]'. I tried using 'In' / 'Between' as my logical operator for having this new parameter, however I still am not able to add a parameter.

Am I missing something? Any help will be really appreciated.

Thanks!

Harsh.