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...t hanks
province dataset :
SELECT * FROM PROVINCE
WHERE PROVINCE.COUNTR YCODE IN (:PARAM_COUNTRY )
ORDER BY PROVINCE.PROVIN CENAME
country dataset:
SELECT * FROM COUNTRY
ORDER BY COUNTRYNAME ASC
dataset 1:
SELECT
COUNT(DISTINCT SHSUBSCRIBERNR) ,
CUSTOMER_CLASS. DESCRIPTION,
ST.STATUSDESCRI PTION,
FP.DESCRIPTION,
P.PROVINCENUMBE R ,
P.PROVINCENAME
FROM
SUHISTOR
INNER JOIN DEVICE_HISTORY dev_hist ON dev_hist.FK_SHS HNR=SHSHNR
INNER JOIN SUBSCRIB ON SUBSCRIB.CUCUST NR=SUHISTOR.SHS UBSCRIBERNR
INNER JOIN SUSTATUS ST ON SUBSCRIB.CUSTAT US=ST.STATUS --GET STATUS
INNER JOIN v_products_per_ customer V ON v.CUSTOMER_ID=S HSUBSCRIBERNR
INNER JOIN AGREEMENT AG ON AG.CUSTOMER_ID= SUBSCRIB.CUCUST NR
INNER JOIN CUSTOMER_CLASS ON CUSTOMER_CLASS. ID=SUBSCRIB.FK_ CLASS_ID
-- LEFT OUTER JOIN AGREEMENT_DETAI L AG_DET ON SUBSCRIB.CUCUST NR=AG_DET.CUSTO MER_ID
INNER JOIN AGREEMENT_DETAI L AG_DET ON SUBSCRIB.CUCUST NR=AG_DET.CUSTO MER_ID
INNER JOIN ADDRESS AD ON AD.ADDRCUSTNR= SUBSCRIB.CUCUST NR
INNER JOIN PROVINCE P ON AD.ADDRPROVINCE NUMBER=P.PROVIN CENUMBER
INNER JOIN COUNTRY C ON C.COUNTRYCODE=A D.ADDRCOUNTRY
INNER JOIN FINANCE_OPTION FP ON FP.ID=AG_DET.FI NANCE_OPTION_ID
WHERE
SHPRODUCTNR IN (26,34,41)
AND
dev_hist.STATUS _ID=30
AND
v.TECHNICAL_PRO DUCT_ID=14
--AND SUBSCRIB.CUCUST NR IS NOT NULL
--AND AG_DET.CUSTOMER _ID IS NOT NULL
AND
SUBSCRIB.CUSTAT US IN (:PARAM_STATUS)
AND
AG_DET.FINANCE_ OPTION_ID IN (:PARAM_FINANCE OPTIONID)
AND
P.PROVINCENUMBE R IN (:PARAM_PROVINC E)
--P.PROVINCENUMBE R = ANY (SELECT PROVINCENUMBER FROM PROVINCE WHERE PROVINCENUMBER IN (:PARAM_PROVINC E))
AND
C.COUNTRYCODE IN (:PARAM_COUNTRY )
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...t hanks
province dataset :
SELECT * FROM PROVINCE
WHERE PROVINCE.COUNTR YCODE IN (:PARAM_COUNTRY )
ORDER BY PROVINCE.PROVIN CENAME
country dataset:
SELECT * FROM COUNTRY
ORDER BY COUNTRYNAME ASC
dataset 1:
SELECT
COUNT(DISTINCT SHSUBSCRIBERNR) ,
CUSTOMER_CLASS. DESCRIPTION,
ST.STATUSDESCRI PTION,
FP.DESCRIPTION,
P.PROVINCENUMBE R ,
P.PROVINCENAME
FROM
SUHISTOR
INNER JOIN DEVICE_HISTORY dev_hist ON dev_hist.FK_SHS HNR=SHSHNR
INNER JOIN SUBSCRIB ON SUBSCRIB.CUCUST NR=SUHISTOR.SHS UBSCRIBERNR
INNER JOIN SUSTATUS ST ON SUBSCRIB.CUSTAT US=ST.STATUS --GET STATUS
INNER JOIN v_products_per_ customer V ON v.CUSTOMER_ID=S HSUBSCRIBERNR
INNER JOIN AGREEMENT AG ON AG.CUSTOMER_ID= SUBSCRIB.CUCUST NR
INNER JOIN CUSTOMER_CLASS ON CUSTOMER_CLASS. ID=SUBSCRIB.FK_ CLASS_ID
-- LEFT OUTER JOIN AGREEMENT_DETAI L AG_DET ON SUBSCRIB.CUCUST NR=AG_DET.CUSTO MER_ID
INNER JOIN AGREEMENT_DETAI L AG_DET ON SUBSCRIB.CUCUST NR=AG_DET.CUSTO MER_ID
INNER JOIN ADDRESS AD ON AD.ADDRCUSTNR= SUBSCRIB.CUCUST NR
INNER JOIN PROVINCE P ON AD.ADDRPROVINCE NUMBER=P.PROVIN CENUMBER
INNER JOIN COUNTRY C ON C.COUNTRYCODE=A D.ADDRCOUNTRY
INNER JOIN FINANCE_OPTION FP ON FP.ID=AG_DET.FI NANCE_OPTION_ID
WHERE
SHPRODUCTNR IN (26,34,41)
AND
dev_hist.STATUS _ID=30
AND
v.TECHNICAL_PRO DUCT_ID=14
--AND SUBSCRIB.CUCUST NR IS NOT NULL
--AND AG_DET.CUSTOMER _ID IS NOT NULL
AND
SUBSCRIB.CUSTAT US IN (:PARAM_STATUS)
AND
AG_DET.FINANCE_ OPTION_ID IN (:PARAM_FINANCE OPTIONID)
AND
P.PROVINCENUMBE R IN (:PARAM_PROVINC E)
--P.PROVINCENUMBE R = ANY (SELECT PROVINCENUMBER FROM PROVINCE WHERE PROVINCENUMBER IN (:PARAM_PROVINC E))
AND
C.COUNTRYCODE IN (:PARAM_COUNTRY )