Hi My report has a parameter called Site. I have created a dataset called DefaultSite that is populated by a text query (not a SP). It looks like this: SELECT DISTINCT SiteID FROM Sites WHERE SiteCode IN ('M928', 'M071', 'M274', 'M102', 'M761', 'M776') ORDER BY SiteID On my parameter Site, under 'Default Values' I have 'Dataset' set to 'DefaultSite' and 'Value field' set to 'SiteID'. The 'Available Values' come from another dataset and are set correctly. When I run the report, all the available values are displayed but none of them have been defaulted ? If I change my DefaultSite data set to use an SP then it works fine. If I enter the below query as a test then it also works fine, provided I dont return more than 4 values else it just comes up with no default values set, another strange thing ? SELECT 3251 as SiteID SELECT UNION ALL 3252 SELECT UNION ALL 3267 If I try the first query above with just one SiteCode in the list it also does not work. Nor will it work if I do that and change the IN to a =. I am going crazy trying to figure this out. Yes, I could create a SP but its an overkill and it would never be re-used. Im not looking for a workaround, Im looking for an explanation. Thanks

Hi There What is the data type of your SiteID. Please make sure that it will be same both in available value as well as default value or you can try to convert that inside query as int and see if it work for you SELECT DISTINCT CONVERT(int, SiteID) AS SiteID FROM Sites WHERE (SiteCode IN ('M928', 'M071', 'M274', 'M102', 'M761', 'M776')) ORDER BY SiteID SELECT DISTINCT CONVERT(int, SiteID) AS SiteID FROM Sites WHERE (SiteCode IN ('M928', 'M071', 'M274', 'M102', 'M761', 'M776')) ORDER BY SiteID If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed Thanks for your reply. Have tried that already but did not fix it. Thanks

Hi There Can you please put a screenshot for your parameter setting as well as your available value data and default value data.ne thing more When we preview a report, the data for the report is cached to a file on the local computer. When we preview the same report report Designer retrieves the cached copy rather than rerunning the query. The data file is saved as <reportname>.rdl.data in the same directory as the report definition file. In normal situation, the cached file doesnt work once we modify the query. At this time, I still suggest that you delete the cached .rdl.data file and then check the issue again. If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi ,I created two tables .one for DefaultSite dataset and the other for AvailableSites dataset as below Create Table Sites(SiteID int identity(1,1),SiteCode varchar(10)) insert into Sites values('M928') insert into Sites values('M071') insert into Sites values('M274') insert into Sites values('M102') insert into Sites values('M761') insert into Sites values('M776') select * from sites Create Table AvailableSites(SiteID int identity(1,1),SiteCode varchar(10)) insert into AvailableSites values('M100') insert into AvailableSites values('M200') insert into AvailableSites values('M300') insert into AvailableSites values('M400') insert into AvailableSites values('M500') insert into AvailableSites values('M600') insert into AvailableSites values('M928') insert into AvailableSites values('M071') insert into AvailableSites values('M274') insert into AvailableSites values('M102') insert into AvailableSites values('M761') insert into AvailableSites values('M776') Now for the Site parameter, i used the DefaultSite dataset for default values and the other Availablevalues..and Checked the Allow Multiple values property as the default values are more than one...I see the default values selected inthe preview mode.. Hope this will help you..

Hi There This would be the result of your query. How would you expect that you available value will be selected from default value. The only way is if you use sitecode instead of SiteID. I hope this will help If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. Create Table Sites(SiteID int identity(1,1),SiteCode varchar(10)) insert into Sites values('M928') insert into Sites values('M071') insert into Sites values('M274') insert into Sites values('M102') insert into Sites values('M761') insert into Sites values('M776') select * from sites Create Table AvailableSites(SiteID int identity(1,1),SiteCode varchar(10)) insert into AvailableSites values('M100') insert into AvailableSites values('M200') insert into AvailableSites values('M300') insert into AvailableSites values('M400') insert into AvailableSites values('M500') insert into AvailableSites values('M600') insert into AvailableSites values('M928') insert into AvailableSites values('M071') insert into AvailableSites values('M274') insert into AvailableSites values('M102') insert into AvailableSites values('M761') insert into AvailableSites values('M776') select * from AvailableSites drop table AvailableSites drop table Sites

Hi Thanks for the replies. Its definately not a caching problem as I have 'cleaned' the solution and run the report and it still does not work. Another thing I should clarify. SiteID can change between environments, SiteCode does not but it is used for the Label Field, not the value field in the available values. So, you see the SiteCode in the drop down and select it but the SiteId gets passed into the SP, which can be different across environments. Neither of the above 2 solutions will work as your SiteID and SiteCode combinations are not the same across the tables ? I have however figured out what is causing the problem, although I have no idea why. So the first data set that pulls all the available sites calls a SP to do it. The second data set that pulls the default sites is just a text query. Its the SP that is causing the issues. Inside the SP, it does a simple query to pull all the SiteID and SiteCode values, but it also joins to some tables to check that the user running the report (UserID passed into the SP) has access to view these sites. These tables actaully sit on another server and are called by means of synonyms. If I comment out this security block of code everything works perfectly !! As soon as its back in, the defaulting of values does not work. Why does this affect SSRS at all ? I thought SSRS just cared about the returned result set, how does it even know what is going on inside the SP ? Thanks

Hi There you still not understand what I mean my friend You available values has siteid 12 for sitecode M776 and You default value has siteID 6 for your sitecode M776. How would you expect that your parameter value will be selected It must be either 6 on both side or 12 on both sides(Avaialable as well as default values) Did you get my point I hope If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Sorry, not following you, but maybe i am not explining this properly. We have a table called Site. It has SiteID (surrogate key) and SiteCode (unique value). Everyone in the company knows things by SiteCode. So if i select everthing from the site table for my available values in my Site parameter, I will display the SiteCode to the user to select and the value behind it that gets passed into the SP to actaully get the site data will be the SiteID. I dont really care what the SiteID is as it could change across environments. What this means though, in order to default the Site parameter, is I have to pass it SiteIDs and so that is the second query in my original post. It gets the SiteID (for whatever environment) based on the SiteCode. All that said, no of the above logic is the problem, that is sound. Its the fact that even though I am selecting the correct SiteIDs for the default values, it is not defaulting them, which I have figured out is because of code within the SP that brings back the list of all SiteID and SiteCode used to populate the available values for my parameter. Hope that made sense and hope someone has an answer :) Thanks

Hi Syed Just re-read your last post and get what you are saying. This is not the problem though, the available values and the default values have the exact same mappings of SiteID to SiteCode as both lists are selected out of the same table. Thanks

Hi Sorry, not following you, but maybe i am not explining this properly. We have a table called Site. It has SiteID (surrogate key) and SiteCode (unique value). Everyone in the company knows things by SiteCode. So if i select everthing from the site table for my available values in my Site parameter, I will display the SiteCode to the user to select and the value behind it that gets passed into the SP to actaully get the site data will be the SiteID. I dont really care what the SiteID is as it could change across environments. What this means though, in order to default the Site parameter, is I have to pass it SiteIDs and so that is the second query in my original post. It gets the SiteID (for whatever environment) based on the SiteCode. All that said, no of the above logic is the problem, that is sound. Its the fact that even though I am selecting the correct SiteIDs for the default values, it is not defaulting them, which I have figured out is because of code within the SP that brings back the list of all SiteID and SiteCode used to populate the available values for my parameter. Hope that made sense and hope someone has an answer :) ThanksGRDixon

Hi Syed Just re-read your last post and get what you are saying. This is not the problem though, the available values and the default values have the exact same mappings of SiteID to SiteCode as both lists are selected out of the same table. ThanksGRDixon

Hi Sayed hahahaha, me again. Only realised now that your reply with the screen shots was to tell Ravi that his idea will not work. Thought you were also trying to tell me how do it but knew that was not the issue so skimmed over it. We are on the same page !! :) Thanks

Hi Sayed hahahaha, me again. Only realised now that your reply with the screen shots was to tell Ravi that his idea will not work. Thought you were also trying to tell me how do it but knew that was not the issue so skimmed over it. We are on the same page !! :) ThanksGRDixon

Hey GD Sooo(this is just a check list) for the Parameter Site , you have Allow Multiple Values selected? you have Available values from your "UNION" query you have Default Values from your DefaultSite Query do you have any Nulls in your default Values Query when I try it and my default values query has Nulls it fails to select any of the default values, so thats a possibility too

Hi Available values: Supplied by call to SP. Inside SP it joins to synonyms that point to tables on another server. This is what is causing the problem. Defalut values: Supplied by text query. No NULL values returned in any datasets. All the correct check boxes selected and data types are correct. Thanks