By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

SSRS report parameters are not wroking properly

P: 55
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)
Mar 11 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.