I have a multi value Country parameter in SSRS which is populated by an Oracle dataset.There is also a province parameter which is populated based on the Country selected by the user.
These parameters are passed in turn to another dataset which the report is based on.
The problem is when the report is run,the first province and country are always in the report whether they were selected or not.
If I run the query in SQLPLUS with the parameter hardcoded in,the data is correct but in SSRS, the same query is inaccurate.
any help on this would be greatly appreciated...thanks
province dataset :
SELECT * FROM PROVINCE
WHERE PROVINCE.COUNTRYCODE IN (:PARAM_COUNTRY)
ORDER BY PROVINCE.PROVINCENAME
country dataset:
SELECT * FROM COUNTRY
ORDER BY COUNTRYNAME ASC
dataset 1:
SELECT
COUNT(DISTINCT SHSUBSCRIBERNR),
CUSTOMER_CLASS.DESCRIPTION,
ST.STATUSDESCRIPTION,
FP.DESCRIPTION,
P.PROVINCENUMBER ,
P.PROVINCENAME
FROM
SUHISTOR
INNER JOIN DEVICE_HISTORY dev_hist ON dev_hist.FK_SHSHNR=SHSHNR
INNER JOIN SUBSCRIB ON SUBSCRIB.CUCUSTNR=SUHISTOR.SHSUBSCRIBERNR
INNER JOIN SUSTATUS ST ON SUBSCRIB.CUSTATUS=ST.STATUS --GET STATUS
INNER JOIN v_products_per_customer V ON v.CUSTOMER_ID=SHSUBSCRIBERNR
INNER JOIN AGREEMENT AG ON AG.CUSTOMER_ID=SUBSCRIB.CUCUSTNR
INNER JOIN CUSTOMER_CLASS ON CUSTOMER_CLASS.ID=SUBSCRIB.FK_CLASS_ID
-- LEFT OUTER JOIN AGREEMENT_DETAIL AG_DET ON SUBSCRIB.CUCUSTNR=AG_DET.CUSTOMER_ID
INNER JOIN AGREEMENT_DETAIL AG_DET ON SUBSCRIB.CUCUSTNR=AG_DET.CUSTOMER_ID
INNER JOIN ADDRESS AD ON AD.ADDRCUSTNR= SUBSCRIB.CUCUSTNR
INNER JOIN PROVINCE P ON AD.ADDRPROVINCENUMBER=P.PROVINCENUMBER
INNER JOIN COUNTRY C ON C.COUNTRYCODE=AD.ADDRCOUNTRY
INNER JOIN FINANCE_OPTION FP ON FP.ID=AG_DET.FINANCE_OPTION_ID
WHERE
SHPRODUCTNR IN (26,34,41)
AND
dev_hist.STATUS_ID=30
AND
v.TECHNICAL_PRODUCT_ID=14
--AND SUBSCRIB.CUCUSTNR IS NOT NULL
--AND AG_DET.CUSTOMER_ID IS NOT NULL
AND
SUBSCRIB.CUSTATUS IN (:PARAM_STATUS)
AND
AG_DET.FINANCE_OPTION_ID IN (:PARAM_FINANCEOPTIONID)
AND
P.PROVINCENUMBER IN (:PARAM_PROVINCE)
--P.PROVINCENUMBER = ANY (SELECT PROVINCENUMBER FROM PROVINCE WHERE PROVINCENUMBER IN (:PARAM_PROVINCE))
AND
C.COUNTRYCODE IN (:PARAM_COUNTRY)