Our report administrator was looking over some productivity reports that had been scheduled to be emailed to selected managers using the report server subscription option.  He had found that the reports had not been emailed in quite some time (although the managers that were supposed to receive the message never notified us!) and were failing with a message that the subscription owner does not exist.  The subscriptions had been created by our former report administrator and whose domain account had been removed.  Without the active domain account, every subscription created under that user was failing.  I submitted as a fix to create a new domain user that would never have to be removed and re-create the subscriptions when logged in under the new account.  This however did not fix the problem, as the subscription owner is tied to the owner of the report. Using this method, each report would have to be re-created using the new account.  I performed a quick search and came up with this blog which featured the solution.  The fix was to update the owner of the subscriptions directly in the subscriptions table in the report server database.  I ran the change using the example on that blog and update the owner.  We fired off a new subscription on one of the reports and it sent out perfectly. 

 

After this, I started poking around in the report server database, which I have unfortunatley not looked at much in the past. I created a quick query to bring back all of the current subscriptions, their owners, the report they were running under, and what the results of the subscriptions are.  Using this I can now at a glance check the status of all our subscriptions and look for failures and other issues.  Here’s the query I used:

 

 

I have a report where I switched all the single valued parameters to multi-value.  The report had 8 sections whose visibility was controlled through the selection of a parameter.  The user could choose one of the sections or all.  I had set the sections up as rows of a table and used the following expression on the hidden attribute for that table row: IIF(Parameters!<parameterName>.Value = <thisSectionName> OR Parameters!<parameterName>.Value = "ALL", false, true).  This expression would cause this section to only be displayed if that section’s name or All was picked from the parameter ("ALL" was a static value I added to the parameter value when it was created).  However this no longer worked when switching to multi-value parameters.  The solution was to combine the JOIN() function, which creates a delimited string out of its argument, with the InStr() function, which returns the starting position of a string within another string.  Combining the two resulted in the following expression: IIF(InStr(JOIN(Parameters!<parameterName>.Value), "<thisSectionName>") > 0 OR Parameters!<parameterName>.Count = <totalNumberOfParameterValues>, false, true).  This expression will create a string containing all of the selected values of the multi-value parameter and search it for an occurence of the section name. If found, the integer position will be returned. If that value is greater than 0, then we want the section displayed (hidden = false).  Also, the expression checks to see if all of the values are selected, which is controlled by the count property of the parameter compared against the total number of possible values, which again will signal that we want the section displayed.  If the string is not found or all of the values are not selected, then the section will not be displayed (hidden = true).

 

Hope this helps.  Happy Coding!

I was working on re-designing a report today where I was changing certain single-valued parameters to multi-value.  I love this ability in that it allows the user to create much more dynamic reports.  The previous version of the report was displaying the value of the selected parameters in text boxes in the header of the report using the expression: ="<parameterName>: " & Parameter!<parameterName>.Label.  Unfortunatley, this caused an error when switching to multi-value parameters.  I found a couple of great articles and comments that showed how to display the value / label of a multi value parameter (links below).  You can use the JOIN(expression, delimiter) function to create a delmited string of either the parameter’s values or labels. To make a comma delimited list of the parameter’s labels, you would use the expression: JOIN(Parameters!<parameterName>.Label, ",").  This solution works perfectly. I still had one other problem.  One of my parameters contained a large list of staff, and I didn’t want every  staff member to be shown if the user selected all of the values for the parameter.  Fortunately, SSRS report parameters have an attribute called Count that contains the number of selected values. With this, you can construct a conditional expression to control what is displayed depending on how many values are selected.  For example, This expression will only display the word "All" if all of the values have been selected, or else it will display the comma delimited list of values: IIF(Parameters!<parameterName>.Count = <totalNumberOfValues>, "All", JOIN(Parameters!<parameterName>.Label, ",")).  So how do you determine the total number of values to compare against? If you have a non-queried dataset for a parameter, then the solution is easy; simply count up the values you have and that’s your number.  For my purposes, I was gathering a list of staff which can change over time.  The solution was to create a new dataset that returned the count of staff, using the same query that retrieved the staff names for the parameter.  Once I had the count, I created a new internal parameter and assigned it’s default value to the value of that count.  Now in my expression, I can compare the count of parameter values to the count of staff retrieved and contained in my internal parameter.  This way the results will always be the same and dynamic. 

 

Hope this helps. Happy coding!

 

We have a report at work that displays staff payroll information.  The report is viewed by managers and executives.  Each person viewing the report only has access to certain departments (one or more) that we control through a table we built.  The viewer is presented with a list of departments to choose from, and their choice of department is validated in our table using the Global report value UserID.  They will see the information if authorized, or otherwise a message displays stating as such. As this report was written by someone else no longer working at the company, my question was why are we even offering a list of all departments if the viewer can only select specific ones?  The solution was to list only those departments for which the viewer could access in the report parameter.  To do this, I created an internal report parameter that got the network id of the current viewer using the Global UserId value, then used that parameter to query the authorization table for those departments that the viewer was assigned.  Sounded fine, except when running the report I received an error stating that "Forward dependencies are not valid."  A quick Google later (can I use that as a unit of time?)  and I found a forum entry on one of my favorite SQL sites, SQL Server Central (registration required).  The entry mentioned that the ordering of report parameters in the paramter dialog matters.  It makes total sense, but not something you would commonly think about.  I moved the user id parameter above the parameter that uses it to get the list of departments, and the report displayed as expected.     

 

Hope this info helps.  Happy Reporting!