Browsing the SSRS category

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

Reporting Services – unknown page numbers (“?”)

We upgraded our MS SQL Server Reporting Services reports to version 2008.  Now for lengthy reports, the total number of pages doesn’t appear (“Page 1 of 2?”).  There’s a question mark in there.

The solution: put a textbox in in the header or footer that has the value of “=Globals!TotalPages“.  I think this specific value can be in a textbox that’s only in the header or footer.  Then just hide the textbox after.

Read the article

Setting up Reporting Services 2012

We’re in the process of setting up Reporting Services on a SQL Server 2012 database.  To edit SSRS reports, in version 2005, we had used Visual Studio 2005.

Now I’m using Visual Studio 2012, and there wasn’t an option for creating a new Business Intelligence / Reporting Services Reports.  While I do remember having to download (and install) something separately, the real trick is that you’re not going to open up VS2012 directly.  Instead, go:

Microsoft SQL Server 2012 >> SQL Server Data Tools

(Surprise! It’s not “Microsoft Visual Studio 2012”)

The “Visual Studio 2010 Shell” will open up.  Now you’ll be able to create a new project with “Report Server Project (category: Business Intelligence)” as an option.

Additional note for SSRS Security: I had to give my user account administrative access.  In the top-right, I clicked the button for “Site Settings” and adjusted the security there.  But also, on the homepage, you need to go to “folder settings” and set the permissions there, too.

Read the article

Decipher complicated passwords (TPAM)

At work, I need to remotely log into a Microsoft SQL Server (to update SQL Server Reporting Services reports).  I need administrative access, and have to put in a request through some system called “TPAM.”

The password that I get is so complicated – many characters (numbers, letters, special characters), and it’s long.  And I can’t copy/paste it in – I need to type it.  But what’s worse is that the characters are so hard to differentiate.  A lowercase “L” looks very similar to an uppercase “i” which looks very similar to the number “1”.

Solution: After pasting the password into MS Word (and enlarging the font size), I change the font-face to “Book Antiqua”.  That makes it a little decipher the password / tell which character I should be typing.

Read the article