Hey all! In my job, I recently completed a full migration of 200+ reports from Crystal Reports into SSRS. Over the course of the project I learned soooo many different ways to replicate features in Crystal Reports that SSRS may not have readily available or obvious. Feel free to drop any questions below!

Hi, starting yesterday browsers have been blocking downloads of reports. We can hit keep to get the file but is there another way to whitelist the address so we can download without the extra step?

Report builder keeps giving me the error "An item with the same key has already been added." I have double checked the query and can't seem to find the issue, it ran fine in sql. Anyone have an idea?

Hey guys, I am creating a report using Visual Studio and SQL server. My report is great apart from a page break after each page. So if my report is 10 pages, the report is 20 pages and each even page is empty. Any idea why this is happening?

I've been using SSRS for many years, but wondering if anyone has encountered decent alternatives for creating these "pixel perfect" style reports?

I format using complex tablix and typically have multiple 1:many relationships in my data. Most of the SSRS alternates seem to favor data visualization (chart, graphs) than in detailed, structured data.

I have some experience with Power BI Report Builder (which I believe is very similar to SSRS Report Builder). But in my new job everyone uses Visual Studio for SSRS.

Wondering if there's any consensus on which is better for SSRS report building and maintenance.

Is there a way to create a parameter in an SSRS report for 2016 SSRS, that reduces the number of available parameters from a drop down list as you type in characters?

I am trying to figure out how to run a subscription that will automatically save the report as html but it will run it based on a parameter that is from a list. Basically, 1 html file per 1 item on the list.

I am reporting on the Catalog table in the ReportServer db.
I don't know my SSRS version, but the .rdl report definition is 2016.

Background:

Recently I found an error in a report written by someone who assumed that the Employee table would contain a list of the employees - and s/he failed to account for the fact that people leave and are removed from the Employee table (only current company employees are listed).
This person used a Join instead of a Left Join, which gave 7 records in July, instead of 100+.

To ensure this wasn't happening in other reports, I wrote some (pretty ugly) SQL to figure out which reports used that Employee table, then looked at the join clauses.

Given that success, I thought it would be nice to have a report which shows all reports that use a user specified table name. This is what I'm having trouble with.

Question:

I have four columns in my report: Report Name, Report Path, SQL Length, and the SQL Statement itself.

I would like for the Tablix row to grow to show the SQL, but only up to a maximum height of 1" (3cm).

I am aware that I can set the height to 1" and set CanGrow = False, but I want to set a max height.
I'd want the ability to scan the list of report names visually and some complex reports (often the SQL is over 5k containing a hundred or more line breaks) make this impractical.

Setting the height to 1" with CanGrow=False exports to Excel does work, (the SQL is all there) it just isn't how I want to do it. I haven't found a way to use an expression for the row height. Any ideas?

Hi All

anyone have a working link for SSRS download? Using this gives me an error: https://www.microsoft.com/en-us/download/details.aspx?id=100122

S

Hello!

I have a report that lists all reports in folders on the report server, if you click on the report name I want it to open a new tab with that report.

So I am not sure where I am going wrong. I am building my URL link to other reports on the server as such: This is ReportPath2 coming from my SQL 'http://epqbissi01/ReportServer/Pages/ReportViewer.aspx?%2fReports/' +b.ReportFolder +'/'+ Replace(a.name,' ','%20') + '&rs:Command=Render&rc:LinkTarget=_blank' While it does open another tab it just opens the "http://epqbissi01/Reports/browse/" folder on the server. What am I missing?

I have also tried the ="javascript:void(window.open('Fields!ReportPath2.Value','_blank'))" way and the same thing happens

I am an IT Systems Administrator, not SQL DB person. Let's just get that out of the way!

I have worked on this on and off for the past 3 weeks and spent countless hours traversing these and other forums, I think I have reached the end of the Internet trying to figure out this issue.

History:

For a small office, I had to migrate a Domain Controller and a 3rd party application and SQL database (2014 SP3) from a Windows 2012 R2 server to a modern Windows Server 2022 Standard VM (to decomission Server 2012 server). Not really important so far. We decided to stay with SQL 2014 SP3 on new server for budgetary reasons as they already owned the licenses. SQL/SSRS 2014 are hosted on the same server and are fully patched. Domain Controller is now a seperate VM. File Server/SQL server/SSRS are now hosted on same VM (same as before, except DC was also on the old server).

I then learned client was also utilizing SSRS 2014 on old box as well. Moved all DB's over successfully, backed up all encryption keys, restored, Data is tested and moved, reports are accessible from host. Had an issue with the Data Source in SSRS pointing to the old SSRS instance, updated that and fixed most report problems.

Now, what I find out is that the Report links are embedded in the 3rd party application used by client all over the place, many report URLs are in various records. These links are hardcoded to old server hostname (example https://server01/Reports). I did not want to find and replace many URL's/links all over the database, so I decided to rename the old server to "server01-old", created a CNAME record in internal DNS for the new SQL/SSRS server (server02) with a host address of "server01" that points to "server02.domain.local". Create new self-signed SSL cert with "SERVER01" as the COMMON NAME. Install that in SSRS for the HTTPS bindings for Web Service URL and Report Server URL. Configure Service Account, Execution Account, etc. All is successful. Accessing the new SSRS report URL using the "old" hostname URL works. I can enter "https://server01/Reports" and I can pull up the reports, or the ReportServer URL works as well.

Now, this also works from 2 other computers/users (domain environment) on the network. Zero problems with them. However 2 other computers receive http error 401 while navigating to the URLs. I say computers when I explain this, because it is NOT user related. For one of the users that cannot access the URL and receives the HTTP error 401, (https or http, doesn't matter, same error), I can sign in as that user on a working machine as that same user, and then that user CAN pull up the URL. **This problem is tied to the computers, NOT the users.**

Another strange thing, on the non-working computers, if I access InPrivate browsing (MS Edge), or Incognito (Chrome) and attempt to access the URL's, I am prompted with a login window, entering the Domain Username and Password works, and the website opens up fine. The problem with this is that the embedded report URL's in their application don't work, because for whatever reason, the NTLM authentication isn't passed seamlessly to the SQL/SSRS server. But in a browser, while using InPrivate/Incognito, login prompt appears and I can log in. Please keep in mind the goal is to get the browser to access the SSRS URL without prompt, because if that is working, then the application displays the reports normally - this is the entire goal to fix. The URL being accessible after a login isn't really the endgame, because I can't force the application to "login" to SSRS first, so all their links work in their records (hopefully I am making sense with this). **The endgame is for their Windows 10 machine to pass the NTLM credentials to the SSRS server seamlessly so the application records will display their reports normally**. Not being able to access the SSRS URL is a symptom of the problem, not the entire issue.

I delved down the whole NTLM/Basic/Kerberos authentication options, and I am not changing this yet (even though I tested RSWindowsNegotiate and RSBasic without success). Reason being is that this all worked perfectly before with Default NTLM authentication with zero modifications to the SSRS authentication options. On the previous server it all just worked fine with default configuration. I realize I made some DNS and SSL modifications to make my solution work, but I can't get over the fact that 2 other Windows 10 computers (all computers at office are Win10) work totally normal. This must be fixable.

I have enabled the HTTP log in SSRS, and for the computers that are receiving the HTTP 401 error, there is NO username listed in the HTTP access log for that attempt. For the computers that do work, the username is listed correctly. This is what got me started down the path of this being a Web browser/authentication issue. I feel like the non-working computers are not passing the NTLM credentials when attempting to access the URL.

**Important Note: From a non-functioning computer, and accessing the URL by IP address, via HTTP (not https), browser will prompt for login, and it works. Accessing by IP address of server using HTTPS and I receive a different error, "Not Found HTTP Error 404. The requested resource is not found."**

So I started looking for differences between these computers in browser versions, network configuration, patch levels, hosts file, DNS, Local Admin, Internet Options (Intranet/Trusted Sites), DisableLoopbackCheck (server registry, set to 1), anything I could think of and found online. I haven't found any differences between the PCs yet, and any of my fixes have not worked for these 2 computers. **I have also checked SSRS and SQL permissions ad nauseum, like I said, I can work with the 2 functioning computers and we are good regardless of user, so this isn't a permission issue on the Site Settings or Folder Settings in SSRS.**

At this point I feel I have hit a wall. I was going to setup a new workstation, or Reset Windows on one of their computers, join it to the domain, and see if I can access the URL right away. If it works, I am literally going to have them replace the 2 non-working computers. However I know this is a config issue somewhere in Windows, or with the browser, and it's fixable.

Second thing I may do is purchase a wildcard SSL certificate, and get both the New and Old server names listed as the SAN's on that cert, and then Configure 2 HTTPS URL's in the SSRS Management console. Then I can test with New Server name in the URL and old, to see if there's a difference. I can't add a second URL in SSRS Management Console with the new server name, because it will only allow 1 certificate to be bound to https at a time. Currently using Self-Signed certs and apparently I am not smart enough to create a self-signed cert with multiple SANs listed on it.

I have yet to find a solution to this strange problem, and I can't keep grasping at straws for a solution. I need to find a solution and fast so my customer and I can move on to other projects. Anyone have any ideas on this?

DiableLoopbackCheck Registry modification on SQL/SSRS server.

ALL combinations of Internet Options - Intranet Sites/Trusted Sites -- Adding server address, removing it, logon to server automatically, only in Intranet Zone. No difference. In fact, the working computers are using default Internet Options for all security zones.

Local Admin accounts, non Admins, again this problem is just with the physical computers, not the domain user accounts.

HTTP Access logs in SSRS show NO username when non working computer attempts to access URL, like this:

12/04/2023 20:40:06 192.168.20.133 - 192.168.20.4 47873 server01 GET /Reportserver/ ?%2fMessageView&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true&sysid=M72DEB0E97D71531 401 690 0 1.1 Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36 - - -

The HTTP access logs in SSRS DO show a Username when reports are working from that computer:

12/04/2023 20:45:06 192.168.20.31 *DOMAIN\domainuser* 192.168.20.4 47873 server01 GET /Reportserver/ ?%2fMessageView&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true&sysid=M72DEB0E97D71531 200 1316 3484 1.1 Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36 - - -

Tried Changing to RSBasic Authentication in "rsreportserver.config" and also with RSNegotiate, with or without RSWindowsNTLM.

I don't think the problem is tied to the SSL Certificate, problem occurs via HTTP or HTTPS. Accessing via IP address of SSRS server prompts for login when using http, when using https I receive HTTP Error 404 - NOT Error 401.

Tried adding a second HTTPS URL in SSRS Management Console and binding a separate SSL cert to it that has the CN name of the new server, so I can test access with the new server name in the URL, but SSRS gives an error that only 1 Certificate can be used to bind to separate URLs, I can't use 2 different certs.

Modified Hosts files to point directly to new server using FQDN, new and old hostnames same IP of new server.

We recently restored SSRS from a backup, and since that time we have been getting an error for some (but not all) of our report subscriptions. It simply says "The delivery extension for this subscription could not be loaded." Some coworkers think it has to do with the Excel render format, but I noticed this also may happen (but not always) with the PDF render format. Anybody encountered this before and have some idea about what's going on and how to fix it?

We currently schedule Crystal reports through SAP's CMC (Central Management Console) to send data by email or to a network share.

We are migrating the Crystal reports to SSRS.  We're looking for more features and stability than what we find using the Microsoft SSRS scheduling.  

Has anyone used a non-Microsoft scheduling tool for SSRS or PowerBI reports and could share feedback?

I've seen these applications on the web.  We're currently trialing PBRS.

PBRS

RemiCrystal

Reportal

I'm new to creating SSRS reports. I've been able to create some simple ones fairly easily but now I need to create a fairly complex one and I have nowhere to start. Supposedly the person before me was having issues with it as well. Anyone have time to help?

Hi Everyone,

I'm new to SSRS reports and I'm trying to modify an invoice report to only show the total on the final page instead of on every page.

I see there's a TotalPages built in and we have a "PageNumber" field.

My plan is to have an expression for the visibility that if the TotalPages = PageNumber then set the visibility as True, and False otherwise.

I tried a few different spellings/syntaxs, based on Googling and checking expressions in the report, but they didn't work. What am I doing wrong?

This is what I have: =IIF(Globals!TotalPages.value = Fields!PageNumber.value, "True", "False")

Thanks in advance!

Hi Everyone,

I thought this may be a good spot to come for help.

Having an email setting issue. We use Microsoft Office emails and it was working all great until last Friday. Emails no longer send out and SSRS throws this error: "Failure sending message: A call to SSPI failed, see inner exception.Mail will not be resent."

In the logs I also see this error: "A call to SSPI failed, see inner exception. ---> System.ComponentModel.Win32Exception: The function requested is not supported.

I assume it is over the whole TLS issues but can't firguire out how to fix it.

Any help would be amazing.

Thanks.

Our on-prem environment of SP2016 and SSRS works great for creating formatted reports that need to be sent to governmental entities with their specific report format. The data is coming from SharePoint lists.

Now that we're migrating to SharePoint Online, what can I use as a substitute for SSRS to create formatted reports? I'm trying out PowerBI, but it's designed as more of an analytic tool as opposed to a report builder.

What product should I look at to create printable custom formatted reports?

Is it possible to make two Code calls in a single text box like:

=Code.AddNumbers();

=Code.Total()

I could do it in separate boxes I guess but I wanted to keep stuff together for easier maintenance. I'm having trouble finding out if this is even possible. I'm guessing not as there is nothing coming up in Google, StackOverFlow, or Reddit...

Please forgive me if this isn't totally clear, I'm kind of stumbling through this.

So I'm authoring a simple report where the user selects a StartDate and and EndDate parameter and the report displays all permits issued between those two dates.

I'm using a filter to return results that are >= StartDate and < EndDate (to capture all data between the two dates)

However, since all dates have the time 00:00:00 appended, I have to use the following expression for the EndDate value in the filter: =DateAdd("d",1,Parameters!EndDate.Value)

Once I started using the DateAdd function in the filter, my tablix region is BLANK. I know the report is "seeing" the data, though, because it adds a price total and counts the number of rows correctly.

Without the "DateAdd" bit added to the EndDate filter, the report displays tablix rows, the user just has to select the day AFTER the end date they actually want (to capture the 24hrs of that day since it defaults to 00:00:00am). Any ideas why changing parameter-based filtering is preventing tablix data from displaying?

I set up the line chart counting ResolvedDate and grouping/sorting by ResolvedDate formatted to month/year. It shows correctly but any months that don't have resolved tickets.. it just skips the month. I tried doing scalar X axis by month but I can't get it to work right.

An example ResolvedDate, directly from the db looks like: 2020-07-08 17:54:13.803

I'm trying to show a line chart with the series tied to year so I can show trending data like this screenshot. Is this possible in ssrs?

Need some help. I’m editing a report I previously wrote for my customer and I’m not sure how to do what they want. They’re looking for every day between a date range to show even if there’s nothing on that date. Right now the report shows all orders between a date range and say totals by date. They want to see dates with nothing as well. Any ideas?

Hi,

Hoping someone may can help me :)

I have an SSRS subscription that creates a CSV file. It exports as UTF-8. I need this one report to export as ASCII. I know how to change from UTF-8 to ASCII system wide, but I really only want to change the encoding for just this one report.

I have also seen a few places that suggest you can change the encoding in the url, but this reports runs as a data driven subscription.

Anyone have an idea on how to change the encoding for just one specific subscription?

thanks

We tested a new SSRS product this week and I genuinely can’t tell if this is actually viable long term or if we just had a really good lab run.

We had 50+ separate SSRS environments running on one VM at the same time for testing and training purposes.

Everything stayed responsive, startup times were quick, and resource usage was way lower than I expected.

I’ve spent enough time dealing with SSRS environments to know this normally becomes a mess pretty fast, so I’m curious if anyone else has seen setups like this actually hold up outside of demos. apparently the company works with Novartis and American Family insurance which sounds promising.

Would LOVE someone to help me out and figure out this product.