Report parameter defaults not updated during deployment
Parameter defaults do not get updated when re-deploying existing reports. These either have to be updated manually or the reports deleted and re-deployed. The latter regenerates all report ID's (GUID's) and makes traking usage from the ExecutionLog more difficult.
This is explained here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=100960&SiteID=1 as being by design however I can't envisage parameter defaults and prompts being maintaned by an administrator.
An override mechanism similar to OverwriteDataSources should be added to Reporting Services projects to allow deploymnent from Visual Studio.
Upvotes: 132<=-=Oct 25 2007 6:20PM=-=>
Thanks for the suggestion. We’ll consider for a future release.<=-=Aug 14 2008 3:56PM=-=>
Parameter defaults could easily be maintained by an administrator in an ISV scenario, as opposed to an internal IT solution scenario. The ISV ships reports with hidden parameters used to customize behavior, using sensible default settings. Those defaults are changed by the customer’s administrator after the report is deployed to customize the behavior of the report to their needs.
Those customized defaults should not be overwritten when an updated report definition received from the ISV is deployed.<=-=Sep 12 2008 10:09PM=-=>
A default parameter override check-box should be available. Not all reports have an administrator setting default params. And having to go and set parameters after a deploy is not an optimal use of a report developers time.<=-=Aug 4 2009 2:33PM=-=>
I agree with TDog3, and so does every professional I’ve ever discussed this with. Please provide an override check-box as mentioned.<=-=Mar 4 2010 8:45AM=-=>
Did Microsoft released anything to fix this issue yet?<=-=Apr 15 2010 7:32AM=-=>
Please provide an override check-box as mentioned.<=-=Mar 22 2011 3:27PM=-=>
The parameters have all kinds of issues sometimes… I had a report and for testing I set defaults for two of the parameters; one was a clientID and the other was the connection string for the datasource of the report. Since there is more than one database the Report Server must connect to for data I have to do it this way to make sure it grabs the right data for the right client. So – I set the defaults, deployed, tested everything I needed to.
Then I tried to remove the defaults (and they looked gone everywhere: in my report in SSRS, in the code in SSRS, in the code on the server, in the Parameters part of the Report Properties) but the report wouldn’t work when passed a different connection string but would only work with the connection string I used for testing. Frustrated, I deleted the report off the server, re-deployed, deleted it again, copied the code to a new .rdl and nothing worked. Then, I defaulted the parameters to something else, deployed, deleted all the defaults, deleted the reports off the server, deployed — and then it worked from everywhere with any set of working parameters.
I wish there was some logic to this madness so at least I knew exactly what to do, what it would take, when SSRS starts acting funny when I try to update any of my reports. I get that it acts “weird” sometimes when you try and change parameters and sometimes all you need to do is copy the report into a new file. But when that doesn’t fix things and the report works everywhere except one place it’s so frustrating. Or when the rdl code looks correct but somehow the report is getting deployed wrong or rendered wrong…<=-=Apr 20 2011 12:56PM=-=>
I’m using 2008 R2, and I still see this as a problem. If I want to redeploy a report using CreateCatalogItem( ) and overwrite previous parameter definitions I have to a)delete the existing report and redeploy—which, as previously stated, makes tracking usage more difficult; in addition, it would delete any existing subscriptions or other information related to the report— or b)update the parameters manually using SetItemParameters( ) which is prone to errors in attempting to build the ItemParameters array from the RDL for all but the simplest of parameter definitions. I can do this through Report Builder 3.0, overwriting the parameters on an existing report without deleting and recreating the report which goes entirely against the response in the forum post( ) that reads, “The original intent was to allow for an admin to change the defaults, prompts, etc. and not have it overwritten by the report developer. We should provide a way to override this behavior.” A report developer using Report Builder 3.0 is not prevented from overwriting defaults, prompts, etc. This problem only affects developers trying to deploy reports using the ReportingService web service.
Please provide a simple mechanism for replacing the “Parameter” column in the “Catalog” table as if the report were being uploaded for the first time.<=-=Jun 17 2011 6:27AM=-=>
The problem has a larger scope than so far mentioned.
Looking only at the post-deployment admin, I’ll just mention two needs: Internal parameters whose defaults cannot be tampered with using URL access (works: both rightmost checkboxes off) and Optional parameters, meaning he can hide and un-hide parameters wihtout losing prompt text (OK in 2005, broken in 2008 R2, well enough mitigated by Firefox’ form cache).
Loking at both ReportManager and BIDS design intentions stuff gets complicated.
- The legitimate use case that a designer wants to add an optional parameter as described above to an already-deployed report. It should start hidden by default in the existing deployed report and all its links, but allow the admin to switch to user prompt. This is broken in several ways (a hidden parameter does not store its prompt text in ReportManager despite it being mandatory in BIDS, and it will kill admin decisions upon redeployment) → Design overrides Admin but shouldn’t (and regression from 2005, where at least admin control was consistently left in peace)
- Internal parameters are initially deployed as Non-Hidden no-prompt, which is correct. If opened by the admin and redeployed, this is changed to hidden, meaning insufficient protection (Parameter CAN be overriden on the URL). This behaviour is both inconsistent and unexpected.
- Another legitimate case: A report with a parameter using a list of allowed values is designed using a static list, deployed, linked, then redesigned to pull the list from the database instead and redeployed: Links will NOT learn the new logic.
- Wilder still: A report defined with a hidden parameter defaulted to a formula (e.g. Dateadd(“d”,1,Today) – quite commen) and deploeyd, then the parameter is opened by the admin and the default removed. Upon redeployment the parameter is hidden, the prompt text killed, but the formula is not reinstated. Most will need to delete and redeploy losing history, but can be fixed manipulating the XML and re-adding the missing True
So yes, we ***need*** design-time control over which parameter properties will override after-deployment choices, both ways. If override is specified, propagation to linked reports should be guaranteed.
- There should be a way to design an optional parameter that starts hidden on initial deployment, but carries a default prompt text. After being opened by an admin (without the need to re-type the prompt text which BIDS has required), it should stay as configured on redeployment.
- There should be a way to properly define a parameter used in drillthrough – meaning it requires either a user prompt or the hidden flag otherwise the parent→child link breaks, with permission given to the ReportManager admin to open the parameter to user input (redeployment will set the hidden flag if and only if user prompting is off)
- There should also be a way to design a parameter used in drillthrough but not allowing the admin to open the parameter to user input (deployment ensures hidden is always on) – thus the current behaviour needs to stay as one of the options. In this case, however, ReportManager could change to prevent the admin to implement choices with no long-term change of survival.
- There should be a way to design a truly internal parameter (consolidate formulas or similar uses) which cannot be deprived of its default expression or opened to user input or URL access by the ReportManager admin.
- There should be a way to define the inter-parameter dependencies at design time for the all-too-common case where the automatism decides to play it safe and re-evaluate a parameter on ALL changes to other parameters just because I used a redundant equals sign… Oops, off-topic.
And just to annoy readers I’ll mention that migration from 2005 can produce reports with parameter properties that cannot be reproduced with a fresh report from BIDS (but hey, the whole issue was even more broken on the whole in 2005 – hidden and internal had completely different meanings in BIDS and ReportManager – so such side-effects from partial attempts at fixing the mess should be welcomed ;-)
@gloomy.penguin: Take a look at SELECT Path, CONVERT FROM Catalog WITH – the complete Parameter blob as deployed contains several invisible properties which would certainly (I’d bet) have explained your observations.
@MFaletra: AFAIK UPDATE Catalog SET Parameter=NULL WHERE Path = N’(…)’ and then redeploying fulfils your wish.<=-=Nov 4 2012 4:24AM=-=>
still have the same issue here. Any updates from Microsoft improving the visual studio deployment of parameter changes in SQL2012 (planned)?<=-=Dec 6 2013 4:04AM=-=>
Similar to the above comments, I have an intense dislike that parameters in redeployed reports loose their hidden status. I.e. If the parameter was updated in Report Manager to be hidden, it becomes visible when the report is redeployed.
An example using SSRS 2012. I have a report with Parameter1 (visible), Parameter2 (visible). I use Manage report menu to set Parameter2 Hide = yes. So, now when the users run the report, only Parameter1 is visible. After redeploying the same report (no changes), both parameters are now visible to the users. Similarly, when the Parameter3 is hidden and set visible within the ReportManager.
This is a problem when I have one report used as 2 versions.
We are running into this problem as well with both SQL Server Reporting Services 2017 and Power BI Report Server (SQL 2017 EE). We are deploying the reports via TFS and use the web api to deploy the report to the report server. After the initial deployment, if the default value(s) are changed, those changes have no way to updated on the report server itself.
Deleting the report before deployment doesn't work because all the subscriptions for that report are also deleted. Recreating subscriptions is a real PITA.
Having the developer or a server admin remember to alter the default parameter values after the report is deployed is also a huge problem. First, simple human error gets introduced when we spent the time building a full DevOps process using TFS to automate and control the deployment process. Second, it means I need to grant elevated privileges on a production server to developers. This introduces its own set of problems with auditors.
There should be a simple flag in the API that allows us to override the default parameter values with what the report being deployed specifies.