partner – SQL Ryan

Avoiding IT “Involvement Entitlement”

Avoid IT “Involvement Entitlement” by being a partner with the business, not a gatekeeper – if they’re not coming to you for help, don’t require it, but instead ask yourself why not and fix that.

You’re in the IT department, and you have some pretty solid processes in place – project request forms and approval committees, change approval and management, and some kind of steering committee. Instead of everything lining up nicely, though, you’re getting business units going around the process, building their own applications, writing their own code, and doing it without IT approval. With such a great funnel to go through and everything so neatly defined and documented, why would the business do that?

What is “Involvement Entitlement”?

This Programmers.StackExchange question is a great summary of this, and there’s a general feeling throughout many of the answers that I’ll call “Involvement Entitlement.” In summary, I’d define it this way:

All use of technology must be in a manner explicitly approved by IT, and any custom solutions in place must be written by a developer in the IT department. Any use of technology in a way not explicitly cleared by IT is forbidden.

This attitude can manifest itself in many different ways:

A policy requiring IT approval for any software
Locking down computers so nothing unapproved can be installed
Allowing custom development, but requiring that it clear some bar of “development” or “infosec” approval that’s never actually met in practice or is so complicated that it’s never actually attempted
Custom applications or processes being “allowed”, but setting up requirements with learning curves so high that it’s not feasible for anybody outside IT to meet them (for example, “all applications must be developed in C++” or “every process must have high-availability and load-balancing with automatic no-touch failover to multiple data centers”), when there’s no technical reasons for these requirements

The problem is that these policies don’t actually prevent custom solutions; they just prevent IT from being involved in (or often even finding out about) the things that are being developed. The business doesn’t want to skirt the process or avoid IT, but they do want something very simple – for their jobs to be easier. When the business builds a custom solution like an Excel macro that takes a workbook and uses keypress automation to type a long list of transactions into the accounting software, or small .NET desktop app that generates PDFs for customer paperwork for bankruptcy processing and emails it to the relevant state authority, they’re doing it for one reason – it’s easier than doing it the old way. If they’re not involving IT, it’s for the same reason – it’s easier that way.

What’s really going on?

When you stumble across one of these processes that didn’t go through IT, ask yourself the following two things:

What are they trying to accomplish by developing something themselves?
Why is the business going around the IT processes?

If you don’t like all the custom development that’s going on, forbidding it is mistaking a symptom for the problem – you should instead be asking why they’re going around IT, not just telling them it’s not allowed. Remember that you (IT) exist to help make their job easier, not more complicated. Sometime the value of IT comes in the form of ensuring that applications are fault tolerant or high availability, or in the form of ensuring an application can be supported once the lone developer that built it leaves the company. At the end of the day, you’re in the solution business, and if you and your processes aren’t enabling the business to work better, you’re not adding value.

But here’s something to remember: Every custom application that’s been developed outside of IT corresponds to a need that’s not being met by IT. There may be a good reason they’re not met – not a priority in the company, very specialized problem, not as good as other options, custom language your IT people don’t know, etc – and the lack of IT involvement may be legitimate, but these solutions were created because some department had a need that IT couldn’t (or wouldn’t) satisfy.

IT can still help – be a partner, not gatekeeper

Try to help them solve their problems, and if you don’t have the time or resources, let them solve them on their own. Mandating some language that has a steep learning curve, with the sole purpose of keeping people out of your business, only serves to enhance the elitist attitude most business users perceive IT to have. In the end, that kind of elite attitude only leads to more of the same problem, as users are afraid to approach IT for fear that they’ll either be told “no” outright or that it will add extra effort without adding any value.

There’s no reason I’d forbid this sort of development, within reason – it eases up constraints on shared resources (mainly IT), and allows each group to empower themselves to solve their own problems (as people versed in advanced Excel are pretty common, since this is a common problem, most departments have at least one).

However, you also shouldn’t be expected to solve any problems that arise from these applications, or support them after the original developer leaves the company. As some of the comments in that original post mention, this doesn’t stop the big boss from demanding that you support it, but if you keep a feel out for the kinds of custom applications or processes out there, you can get a feel for when something becomes critical and you might need to get involved to bring it “in-house.” Also, if something is connecting to and modifying systems that are under IT control, then IT should be involved, if only to ensure the security and integrity of their central systems – however, if it’s something confined to a user’s desktop, why feel the need to forbid it?

Postscript – don’t let perfect stand in the way of done

When I said I started this post 7 years ago, I mean it – it’s a classic case of not knowing exactly what perfect looked like, so I let it stand in the way of done. Go find something you’re not sure how to finish and figure out what keeps you from getting it done – and then deliver 🙂 A moderately good idea delivered is worth 100x as much as an incredible idea stuck in your head!

Wow, I let that one sit around for a while….

Passing multi-value report parameters to SSRS using the URL querystring

Passing parameters to reports in SSRS using the URL can be really helpful, especially when you want to link to a report with some of the data aleady filled out. We had an email alert that some accounts in active directory were incorrect, and we wanted a way to link to a report that provided some additional detail for those accounts. Querystring parameters aren’t very well documented (and I couldn’t find anything on multi-value parameters), and I hope this saves you some time if you’re in the same situation.

There’s a pretty good reference on Technet, but it’s got some gaps (for me, the biggest was even mentioning multi-value/multi-select parameters). The basic syntax is pretty straightforward – normally, a report is called with a URL like the following:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName

From there, you can select the parameters and run the report. To provide a link to the report with the parameters already filled out (if they’re blank or if you gnat to override the defaults), just append this to the end of the URL:

?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

You can pass any type of parameter with this same syntax – text, datetime, integer, float, or boolean (use Yes/No). Your full url would look like this:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

To do a multi-value parameter, you repeat the name of the parametr to provide it multiple values:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportMultiParam=SomeValue&ReportMultiParam=SomeOtherValue

If this is the only parameter on the report and it doesn’t need any other user input, the report will automatically execute because it has everything it needs. If you want the report to wait for the user to hit “View Report”, you need to leave at least one parameter empty.

Two quick notes if you’re having trouble:

Use the parameter name, not the prompt text. If your parameter is called “Office” but the prompt says “Which office?”, use “Office”. Parameter names aren’t always intuitive and you may need to open the report in Visual Studio to get these names if you can’t guess them.
In the same way, you need to use the field’s value, not the label. If the dropdown contains the full name of the office, but the value behind the list is the office number, you’ll need to provide the numbers on the querystring.

Good luck!

Microsoft Connect item for an SSRS pre-report canvas

Current state:

About six months ago, I submitted a connect request for SQL Server (my first one) to suggest an improvement to the SSRS interface – a customizable canvas that’s displayed before an SSRS report renders, while parameters are being entered. My issue was that there was information I wanted report users to have (like expected wait time, suggestions for the parameters, the report title and a short description that might help them, etc.) and there was no way to get it to them.

With a giant empty screen staring at them, it seemed like a great place to add that information:

The suggestion:

Ideally, while the user is considering parameters, I’d love to see something like this (ignoring that it’s not a super-photogenic report):

This would be a great place for details about the report that help the user before (or while) they run it, like:

The name of the report (not always clearly visible in the URL or browser title bar, especially when you’re Sharepoint-integrated. Especially helpful if they’ve left multiple report tabs open or if report generation fails, they’re often left with a white screen and no easy way to tell the tabs apart.
A short description of the report, including when you’d use it or any notable caveats.
An estimated runtime – this isn’t always possible exactly as parameters vary, but even a general estimate (or better yet, an average or a 95% confidence range, pulled from the report server database) would be better than the complete blank they get today.
Suggestions/restrictions on the parameters – If a report can’t be run for all offices at once, you can say so here. If running it for everybody at once means you can’t export to Excel because it’s too large, let the user know.
A mock-up/screenshot of the report – so they user can see if it’s the one they want. They may be looking for a report with a specific chart, and waiting for 3 minutes for each one to render may not be ideal.
A link to any other documentation/resources, or related reports, or pretty much anything else.

It wouldn’t need to be anything fancy – I’d even settle for text you can format, or some kind of additional panel on the report that you can design, but isn’t rendered with the report itself. Access to data (to pull estimated on execution times or dynamic owner/description details, for example) would be nice, but not required – I’d rather have a design-only, no-data canvas than nothing at all.

The plea:

Thanks to the motivation from the folks at Brent Ozar Unlimited, I’m asking for your help in voting for this suggestion (if you think it would be beneficial, of course). As part of T-SQL Tuesday, they’ve asked people to find a connect request that they’d like to see filled and post about it, so here I am asking humbly for your support. : )

If you like the idea, please vote for it – if you don’t, please comment and let me know why not (I’m always open to understanding the opposition). There are a ton of great Connect items out there and this is only one, but I think it would help out (especially for our longer-running reports).

Thanks for your help!

Direct URL for the Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2809098/ability-to-customize-report-splash-screen-during-parameter-submission

Side note:

I’ve attached the two images (current/proposed) to my connect item three different times and they’re still not showing up there – if anybody knows how to get those images published, people can see what I’m talking about when I describe my suggestion! : )

Error creating emailed report subscriptions in Sharepoint-integrated SSRS

Anybody who has the rights to run a report in SSRS can set up a subscription, delivering the report to their own email address. However, if you try to deliver the report as an email to somebody else, you might be greeted with this unclear error message:

It reads:

A subscription delivery error has occurred. (rsDeliveryError)
One of the extension parameters is not valid for the
following reason: The account you are using does not
have administrator privileges. A subscription cannot
be created for bob.thompson@mydomain.com.
(rsInvalidExtensionParameter)

I was setting up a subscription in our Sharepoint-integrated SQL Reporting Services site, and was attending to send the report to our shared group email address. I could set it up to deliver to me and I could forward it, but it wasn’t letting me send the email to anybody else’s email address, either inside or outside the organization.

Solution: Grant your user “Create Alerts” and “Manage Alerts” permission

I received this error because I lacked the “Create Alerts” and “Manage Alerts” on the report in question (or folder, or higher up). The error says you need to be an administrator, but doesn’t really tell you what’s wrong with your request – it’s really complaining that you’re delivering an alert to somebody else. Granting those rights to your user account (for that report, or to an object above it so they propagate down to that report) allows you to send the report to any email address you want.

I hope this helps!