Hey Reddit Mafia,

I have a client that is wanting to migrate to SQL 2019 from 2012. That upgrade path as you all know de couples SSRS from the SQL upgrade. The problem that I am having is that migrating the SSRS to 2019 around forms based authentication, their 2012 setup uses forms based auth for login. I have tried several times to migrate this and am running into problems. I have installed 2019 SSRS and assigned it the correct virtual directories and it upgrades the SSRS database fine, then i restored the encryption keys sucessfully and stopped services. i copied the aspx login page as well as the dll and pdb files to both reportserver/bin and /portal directories and then edited rsreportserver.config file and web.config file but when i start the services and try to access the url i always get an http 500 error.

I am at intermediate level on SSRS here and need help to get the forms auth working. what am i missing?

Thanks in advance!

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?

​

Any suggestions on training?

How does a Tablix work?

Hey,

I am trying to find a solution to quickly add users to ssrs servers. The users we add are always the same to each server. I know I can do this manually in the webserver but is there a quicker way to do this? Powershell, ssms.

Any and all help will be greatly appreciated.

ive been having issues for a while on shared schedules where reports will show as expired and wont show a next run date, (they will run properly but show wrong on the schedule tab). but if i edit the report and just hit apply and dont change anything at all it will show all the correct info until the next run then it will go back to blank on next run and expried on status. any ideas? example :

https://preview.redd.it/pkjjq6mkh0sc1.png?width=1653&format=png&auto=webp&s=1c72afbee6b09d8303d3f0abd3dbfff10f0cd483

heres what it looks like after i press edit and apply without actually changing any of the settings in the schedule.

https://preview.redd.it/6hxg4iuyh0sc1.png?width=1657&format=png&auto=webp&s=f595f9f2abe347dddd2e8b2a6b717d672f00fa78

Hello All, I'm really banging my head against my desk on this one. Previously when I've setup SSRS the organisation I was in had enterprise licensing so I didn't really need to think about it.

Now though, I'm in a medium sized business and trying to work out the licensing for a single SSRS setup with ~100 users. So far our licensing partner has been less than useless with their advice.

Any advice is appreciated!

I have customers that want few parameters and then i have customers that want more parameters. Is there a way to make both sides happy without creating two reports?

Looking for some advice on feasibility for a SSRS report for single record. Im looking to pull information from a single record and then I want to display like so

Title of Report / Record ID

Is this format possible and if so is there some documentation I could read or an example that I could try to utilize?

We recently upgraded a server from SQL 2012 to SQL 2019. One of the reports on this server is made up of multiple sub reports. When loading this report and exporting to PDF, I get the error below.

• The processing of Parent for the tablix 'list1' cannot be performed. Cannot compare data of types System.Int32 and System.String.

This report worked before the upgrade. In trying to isolate the issue, I made a test copy of the report and started deleting various controls. There must be some type of caching going on between the Parent and Child report because there is no consistency in what I'm seeing. Is there a way to eliminate the cache between the parent and child reports?

Any help is much appreciated.

​

Hi guys, I’m trying to do the following:

Use the report as a drill-down by linking, say, a report of orders for a customer and then a separate report showing the items on a particular order.

That’s not the real use case but in standard help-me speak, that’s the ask.

I don’t want to have an embedded sub report because that gets all lines for all orders and there can be 1000s of order lines across many orders.

Find the order, then show me the 50 lines for this particular order when I press ‘this one’.

I’ve found a page on learn.microsoft.com talking about linked reports but the ‘linked report’ item is just a pointer and I can’t assign an actual report (.rdl file) to that pointer.

I’ve tried A) creating the child report first and then trying to link that-I get “duplicate report exists” B) creating the linked report from the master- this just gives me a pointer and no way to point that at an existing report that I can see. [Edit: this is the bit I had missed! Telling the pointer what its base report is, that’s the key. ]

Is this a task that can be done, and is it dependent on particular software versions etc?

Ps I create my rdl files in MS Visual Studio but they could be written in Notepad…I just upload to SSRS and link the shared data source. That bit works fine-I’ve got scores of reports.

Is there a way to have static text in this area of the page? If yes, can you point towards a guide?

​

https://preview.redd.it/ukutwre25ujc1.png?width=1402&format=png&auto=webp&s=3b4f962aadac046c21c3bf7ce7a9995a239307c3

I have around 20 data shared sources created directly by me on the web report server. Now i started to use Visual Studio instead of report builder.

How can I import all these shared data sources to my newly created VS solution? I don't want to create them again.

I have some limited experience of creating reports on Power BI report Builder (no experience using SSRS). I'm curious, how similar is Power BI report Builder to SSRS?.

I have a purchased version of SQL Server Enterprise in my company - it is installed on one physical host - is it possible to install the SSRS service only on another (virtual) host - is it consistent with the license terms?

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.

How to send csv and mhtml report having different queries in a single ssrs report email, currently tried to use data driven subscription with a link to a csv file report but link not working outside the system.

I have a ssrs server which is basically on a cloud machine, and i have a oracle database named ABC which is also on another cloud machine.

how do i make a connection between the database and the ssrs for report creation keep in mind both are different machines but cloud machines.

and also guide, is oracle ADB the correct solution, if yes then how? if no then what ?

I have read a lot of conflicting stuff online and I am hoping to get some more real world info about the current status.

As a relatively new user I am dumbfounded by the lack of plug n play interoperability between Microsoft Products. I had a hell of a time getting a simple export of report to PDF, and Im still not thrilled about solution, but it works. Topic fo another discussion.

At any rate If anyone is using VS 2022 to create and deploy SSRS reports, can you report your experience? pros n cons.

Coming from a VS2017 and MS SQL Server 2019 targeting 4.8.1 .net (Id like to keep it that way). Im mainly looking for confirmation that this will be a stable enviroment to develop in CURRENTLY and what my limitations might be.

Thanks everyone!

What are the best practices for high availability SSRS - I was thinking about Windows Fileover CLuster or Always On availability group.

​

I assume that the application cannot be clustered, but the database can - what about reports and IIS?

​

Someone can give me some advice.

​

I would like to make What is an AO AG with three nodes and three replicas - two seynchronous replicas, the third one is synchronous.

​

How does this apply in the context of SSRS?

Not sure this is the right place but I was informed that the PowerBI report builder is basically just SSRS and there seems to be essentially no information available for my issue.

I have a report in PowerBI where I have already defined measures to populate text based on slicers the team has selected. The text measures include concatenating things like the vendor name, some legal jargon based on their contracts, dates based on slicers, etc. It works great in PBI. However, the users need to be able to quickly export the output of those into a PDF file to send to our vendors, including potentially several pages of table data to show where the issues are. Basically, I need to use this to build the letter for the team.

I have the table more or less setup (can't figure out why my one measure is importing as an integer when it is clearly a string but I'll figure that out later if this issue gets solved). I need the first page of the letter to populate with those measures though, and I am not sure how to do that.

I have tried just text boxes with those measures in them but they don't filter based on the parameters. I have tried a calculated field but that doesn't allow aggregate values. I tried using a table instead of a text box but it seems to only evaluate at the row level, and as such returns every possible combination of the text measures, repeated by however many workorders are in the dataset, rather than the one unique value based on the parameters.

If it helps, one of the measures is essentially

"Text"&(start date from legal contract)&"text"&(specific line from contract)&"text" & (contact info for vendor)&"text"&(dates from slicer)&"text"

I am not sure what to do at this point. I am very new to this so I am hoping there is a way to do this that I have just been unable to find thus far.

UPDATE: As it turns out, keeping reports in sync should be handled by the "scale out" configuration ; however, ours was never completed. The second server was still in "waiting to join" state. When we try to add it to the farm, an error is thrown. We'll have to work through this with the DBA team.

Original post:

Is there a recommended method of keeping RDLs in sync across multiple SSRS servers?

We have added a second SSRS server in "scale out" mode and are looking for a means of synchronizing reports (new or updated) across both servers. I had (perhaps foolishly) thought this would be handled by the scale-out config, but that's not the case.

Our normal report change model is:

• check out RDL from repo.
• edit/test locally
• commit back to repo.
• upload to SSRS to deploy.

Given that we now have two SSRS servers, we would have to upload to each SSRS server separately, unless there is a mechanism for synchronizing reports across SSRS servers. Google isn't showing very much -- an old project on Google Code called "reportsync" is about it.

Any pointers/advice would be appreciated. Thanks.

Hi all,

I have a report that I'm working on where a customer will have multiple records and I need to display the data for each record in a table format rather than a list view. This image is the template for the report and what fields of the customer data is captured.

​

This is the template for the report.

​

The image below is a mockup of what I'm trying to do. When a customer has 2 or more records, instead of listing them all under the same table, I would like for SSRS to put one record only in the table and then create a new table for the second, a new for the third, and so on.

​

This is a mockup of what I'd like SSRs to do with the data. There are 2 appointments above for the same client. The data for each appointment occupies the corresponding column for the record.

Above I changed the column names and data to make it a little easier to see what I'm trying to do. The picture below contains the actual column names and how SSRS is currently returning the data.

​

SSRS is listing the data rather than putting each record into a separate table.

​

​

Please let me know if something is unclear of additional information is needed.

​

​

Hi all,

I have an annoying issue that is causing issues with reports.

Servers: Windows 2019.

SQL: 2019.

SSRS: 2019.

I have created an Always on Availability Group in SQL / Cluster and this is working as expected. The is with reports. I have installed SSRS on the servers, set the URLs / Certs etc.

When I access https://avg/reports, I receive a message stating I do not have access. OK. I then access reports via http://server/reports and I am allowed to log in. I check the site settings - Builtin\administrators has System Admin access. Great, I am already in that group. I still cannot sign in. I then have to add my actual Admin account to the site settings, and then to Manage folder in Home before I can access https://avg/reports.

I've never had to do this before.

The next issue is that when I then deploy reports, the service account I am setting to be able to access the reports doesn't get applied to the reports, only the accounts in Home > Manage folder.

What am I doing wrong? I've deployed SSRS before and I didn't have this issue, so I am confused why it's an issue now.

Thanks for reading.

If {SCS_IncidentReport.PrimaryAction} = "308" then "Yes" else

if {SCS_IncidentReport.SecondaryAction} = "308" then "Yes" else

if {SCS_IncidentReport.TertiaryAction} = "308" then "Yes" else "No"

​

Any way to create a calculated field for this?

I have a connection string embedded in my report. The name of which is DBConnection

Data Source=LocalHost\\SQLEXPRESS;Initial catalog=MYDB;Trusted_Connection=True;TrustServerCertificate=True;

This works absolutely fine.

However, I want to use an expression to take in the database name from a parameter.

I have tried

="Data Source=LocalHost\\SQLEXPRESS;Initial Catalog="& Parameters!DB.Value &";Trusted_Connection=True;TrustServerCertificate=True;"

where DB is parameter containing the name of the database.

I am getting this error within my report viewer.

An error has occurred during report processing. (rsProcessingAborted)Cannot create a connection to data source 'DBConnection'. (rsErrorOpeningConnection)Instance failure.

The incoming parameter is a valid Database name.

I have been googling for a while now and suspect I am just not searching up correctly as for the life of me I can't work out what's wrong.

Any help gratefully received!

To start, if this is not the correct sub-reddit to post this - Please let me know.

We are using SQL Server Report Services to create our reports. A request has come in to have the report formulas used to generate the data in the different columns of the report exported along with the data into the spreadsheet.

The user wants to be able to modify certain fields in the spreadsheet and have others fields auto update. They do not want to have to manually add the formulas to the cells in Excel.

The majority of research I have done - says it cannot be done but the developer who was on the original project told the user that he had found a way to do it. He is no longer at the company and the user is holding on to what he told them.

If it is doable - can you point me in the right direction - I am the new BA on the project and am trying to find a resolution for this request.

Thanks so much.