Browsing the SSRS category

SSRS report permission error – rsAccessDenied

One of my users couldn’t get to an SSRS report.  I thought I had given permissions correctly to the parent folder it was in, but they got the error “An error has occurred during the report processing. (rsProcessingAborted) … the permissions granted to user [user-here] are insufficient for performing this operation (rsAccessDenied)”

The solution

This particular report was using a “Shared Dataset”.  In the Shared Dataset folder, I had to grant the same permissions – even though the report folder already had the required permissions.

Read the article

SSRS reports not showing null values

I had an SSRS report that was not displaying the right data.  I narrowed it down to, that the NULL values were not showing up; even though I did LEFT JOINs, and used the ISNULL() function in both the SELECT and GROUP BY clauses.  The issue though was with my WHERE criteria.

My criteria was something similar to:

WHERE field_name LIKE @parameter + ‘%’

The solution was to put the ISNULL() function in the WHERE clause!

WHERE ISNULL(field_name,”) LIKE @parameter + ‘%’

What made it harder to troubleshoot was, the SQL worked fine in Management Studio (it gave the expected results) … however only in Reporting Services was it not showing the right values.

Read the article

ssrs no dependent items for shared dataset

And the other thing I searched for was “ssrs The reference for the shared dataset not valid

I was having trouble with my Reporting Services reports (specifically, the shared dataset).

The Solution:

On the deployed server, manage the specific report.  Under “shared datasets”, you’ll see (one of) the shared data set that is giving the error.  “Browse” too it, and re-link too it.  I haven’t checked yet if re-deploying the specific report will mess it up (as, re-deploying the report AND the shared dataset didn’t solve it in the first place).

Read the article

ssrs graph label remove arrow

I had an SSRS Graph that had the label, with an arrow, pointing to the part of the graph.  I didn’t want this arrow to be there.

The solution:

Click on the area (NOT the data label), go to Properties

  • Labels >> Smart labels >> CalloutLineAnchor >> None
  • Labels >> Smart labels >> Disabled >> true

Read the article

Visual Studio 2017 lost ability to edit queries

I upgraded my version of Visual Studio, but I lost the ability to edit queries!  The screen I needed was the “Report Data” pane.  It’s found under the “View” menu.  To get it to stay in place (when you close and re-open), it’s Window >> Save Layout

Read the article

SSRS Sparkline with filters breaks

In SSRS, I added a sparkline, and it had some filters already on it.

I wanted to give it a transparent background. When I removed the color and went back to preview, it said “no data available” (when there previously was!).

The solution: go back into the filter: extra quotations were put around my criteria. Re-type the criteria as it was.

Read the article

SSRS 2015 Report Data tab

I have Visual Studio 2015, and I couldn’t find the “Report Data” pane/window to view the SQL of a report.

 

Still can’t find it in the menu (apparently it’s under “View”?), but the shortcut to get it is:

 

CTRL+ALT+D

Read the article

SSRS doesn’t sort correctly

I had a SQL Server Reporting Services report that wasn’t sorting the data correctly.  I had the ORDER BY clause in my statement, and I checked the “tablix” properties, but it was still wrong.

The solution was at the bottom (below the report), there are two sections for “Row Groups” and “Column Groups”.  In the “Row Groups” section, there was something there – so my data/report/query was being grouped on an additional field. I clicked on it, and then hit “Group Properties”.  In the “General” tab,  I changed the “Group on” value.  Then I also changed the contents in the “Sorting” tab.  There were incorrect values there because I had copied this report from a different one as a basis.

Read the article

SSRS dropdown selection with option for “all” in addition to values from a table

I had a SSRS report that I wanted the user to select a value (parameter) from a dropdown.  I wanted a blank value to display “All”, and the rest to come from a table.

 

Here’s the query:

 

SELECT ‘  ‘ AS Dept, ‘All’ AS DeptDescription

UNION

SELECT dept, description FROM tblDepartments

 

Note: in the first part of the UNION, there’s a space to indicate the “All”.

 

Separately, I wanted to display the value of the department, in the header of the report.  I made a new dataset, that returned one value:

 

SELECT DeptDescription FROM (

 

SELECT ‘  ‘ AS Dept, ‘All’ AS DeptDescription

UNION

SELECT dept, description FROM tblDepartments

) as qryMain

WHERE dept = @dept

Read the article

SSRS retain headers on other pages in the report

I had a report in SSRS that spanned multiple pages. The column headers were only on the first page; when you navigated to the second page (etc) it wasn’t there.

My first attempt, which did NOT work:

Right click (at the top left corner of ) the table, hit “Tablix Properties”, and check the boxes for “repeat header rows on each page” / “repeat header columns on each page”.

Again, that doesn’t work. The real solution:
At the bottom of the Design editor, there are two columns, one for Row Groups, one for Column Groups. Next to Column Groups, there’s a little arrow, that when you click it, there’s an option for “Advanced Mode”. Click it.

Now under “Row Groups” there will be many entries, they all say “Static”. Click on the first one. With that selected, go to the “Properties” pane (for me, it’s on the right), and make sure the following values are set:
KeepTogether = True
KeepWithGroup = After
RepeatOnNewPage = true

Read the article