ERROR: When editing a report in report builder, opening the query editor gives the following error

You have specified integrated security or 
credentials in the connection string for the 
data source, but the data source is configured 
to use a different credential type. To use the values 
in the connection string, you must configure 
the unattended report processing account for the report server.

SETUP: 1. SQL server 14.0.2027.2 (2017) 2. SSRS 14.0.600 .1274 (2017) configured with an **unattended service account** 3. Reporting portal is accessed through active directory login 4. The report uses a shared datasource with this connection string

Server=tcp:xxxxx;Database=yyyy;Integrated Security=true;
Encrypt=SSPI;Connection Timeout=30;MultipleActiveResultSets=True;
Persist Security Info=False;App=SSRS;

5.The Credentials are set to **Without Any Credentials** 6. Setup as documented by MSFT https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/configure-the-unattended-execution-account-ssrs-configuration-manager?view=sql-server-ver15 OBSERVATIONS: 1. Viewing reports and subscriptions work as expected 2. If i set credentials to **As the user viewing the report**, this fixes the issue but breaks subscriptions as they need either stored credentials or no credentials 3. In report builder, doing a **Test Connection** on the data source, returns **Connection created successfully** WORKAROUND: 1. In report builder, changing the datasource from **Use a shared connection or report model** to **Use a connection embedded in my report** with the exact same connection string as above allows the query editor to open. However i have to change it back to the using a shared connection after I make the required edits. The workaround is not ideal for our user base and I would like to fix the underlying issue. Has any one experienced this issue before ?

An equivalent question was answered for a **tablix**, but I would like to do this for a **list**: "how to keep the structure of a tablix where there is no data". I am showing about half a dozen fields in the list, each with their own label (which I want to repeat for each row). If there are no rows, the user would like to use the report as a "template" that can be filled in manually so I would like to print the labels at least once. https://dba.stackexchange.com/questions/48101/how-to-keep-the-structure-of-the-tablix-when-there-is-no-data-to-show

I need assistance getting labels to print from SSRS 2014 to a Zebra GK420D direct thermal label printer. The labels show up when running the report but when I print them to the label printer, all that comes out is a blank page. If I export to a PDF and then print, it works fine, but this isn't a viable option for our end users. I've set the printer to the correct label size as well as changing the page size in the report to match and I've changed the printer settings to "Always use driver settings" and to print directly to the printer (bypassing the spooler) which were the two recommendations I found thanks to googling. Any assistance is greatly appreciated.

We have a table in which all kinds of different Words are stored with a unique id each. In other tables, certain fields which should contain words instead only contain the id of these words. Let's look at an example. This is our example "words" table:

+---------+--------+
| word_id |   word |
+---------+--------+
|       1 |  Apple |
|       2 |  Melon |
|       3 |    Car |
|       4 |   Boat |
+---------+--------+

In another table (example: "rows"), there are references to that table:

+--------+----------+------------+
| row_id | fruit_id | vehicle_id |
+--------+----------+------------+
|      1 |        1 |          4 |
|      2 |        2 |          3 |
|      3 |        1 |          3 |
|      4 |        2 |          4 |
+--------+----------+------------+

For my SSRS Report, I need to select many rows of the "words" table and insert the value of the "word" column into the report. For now I have solved it by creating a different SQL query for each field using report parameters to select the id, but I was wondering if I could make the report more efficient by cutting down the number of SQL queries. How I did it for now using our example:

SELECT
    rows.id,
    words.word AS FRUIT
FROM
    rows
INNER JOIN
    words ON
    rows.fruit_id = words.word_id
WHERE
    words.word_id = :FRUIT_ID
----------------------------------------
SELECT
    rows.id,
    words.word AS VEHICLE
FROM
    rows
INNER JOIN
    words ON
    rows.vehicle_id = words.word_id
WHERE
    words.word_id = :VEHICLE_ID

The ideal output for row = 1 would be:

+--------+-------+---------+
| row_id | fruit | vehicle |
+--------+-------+---------+
|      1 | Apple |    Boat |
+--------+-------+---------+

Sadly, I have not found a way to select multiple values of the same column in just one SQL statement. I would be very grateful for any ideas or solutions, Thanks! EDIT: I know the "words" table setup is not ideal and can have its own problems, however, I cannot do anything about them since the database is to be used as-is and I am not allowed to change anything.

I have created a report that has multiple rows returned from one query. I have a chart that is created for all the row and I have it grouped on the server location. the group properties of the details group i have visibility set to hidden and set to display when toggled. I want to do this for another column so there is another nested group that can be toggled. When I try to add a child group under the details group it tells me *cannot group inside detail*. Is it not possible to add another group that can be toggled to the report?

How to use OR in IIF statement in SSRS =IIF((Fields!txtCurrentSubjectReportName.Value="", Fields!txtSubjectArchivedName.Value, Fields!txtCurrentSubjectReportName.Value) = "School Life" OR "My goal for this term..." OR "Student Reflective Comment", True, False) Its throwing error

I had trouble phrasing the question, but I think the following example will clear things up. Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in one of the tables. I have a SQL Table (let's call it AllEvents) in the database with a format like this:

UserName   |    Grouping    | EventDate | NumberOfEvents
________________________________________________________
Alice      | Red Grouping   | 1/1/2000  | 1
Alice      | Red Grouping   | 1/2/2000  | 2
Alice      | Red Grouping   | 1/3/2000  | 3
Alice      | Blue Grouping  | 1/4/2000  | 1
Alice      | Blue Grouping  | 1/5/2000  | 2
Anderson   | Red Grouping   | 1/3/2000  | 1
Anderson   | Blue Grouping  | 1/5/2000  | 1
Anderson   | Green Grouping | 1/6/2000  | 1
Anderson   | Green Grouping | 1/7/2000  | 2

And I want to select the results such that I get an output of the following form, where it counts the number of events for the UserName before and up to the row's EventDate.

UserName    | EventDate   | EventNumber
_______________________________________
Alice       | 1/1/2000    | 1
Alice       | 1/2/2000    | 2
Alice       | 1/3/2000    | 3
Alice       | 1/4/2000    | 4
Alice       | 1/5/2000    | 5
Anderson    | 1/3/2000    | 1
Anderson    | 1/5/2000    | 2
Anderson    | 1/6/2000    | 3
Anderson    | 1/7/2000    | 4

We can ignore the grouping column, since we're only interested in the rows with the same UserName. What I have been trying to do is use a subquery like below:

SELECT

   SUM
   (
      CASE WHEN AE.EventDate> AllEvents.EventDate
         THEN 1
         ELSE 0
      END
   )
   OVER (PARTITION BY UserName, Grouping)

FROM AllEvents AS AF

WHERE
   AE.UserName= AllEvents.PatientId
   AND AE.EventDate= AllEvents.EventDate

In the following query:

SELECT
   UserName,
   EventDate,
   [The subquery above] AS EventNumber

FROM
  AllEvents

However, the subquery is returning more than one value and I'm struggling to determine how to troubleshoot. I have also been toying with the COUNT function, but have failed to have it count conditionally based upon the current row's EventDate value. Any advice or suggestions would be appreciated. I have been trying to be creative in my approach, but I don't know if I've hit the best method.

I have a report in SSRS and I want to update the data source to use a named instance instead of the default instance. The server host will remain the same. I thought this would be easy. I simply changed this: clever-sql1 to this: clever-sql1\MSSQLSERVER02 When I click "Test Connection" in the Connection Properties, the test succeeds. Next, I click "OK" and "OK" again to close the Connection properties. I can see my connection string has been generated, and I click "Test Connection" again, but this time, the connection fails. These are the things I have tried to fix the problem: 1. Checked the SQL configuration and made sure named pipes and TCP are enabled 2. Can confirm that remote connections are possible via the management studio 3. Disabled the firewalls on my PC and the server 4. I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this: Data Source=clever-sql1\\MSSQLSERVER02,8000;Initial Catalog=cleverdataout This generated a different error:

__Current Environment__ I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server. The users are connecting to Reports Manager which obviously points to the server where Reporting Services are installed. User use a generic login (a local admin account on the Report Server) to connect to the reports manager. But when the users click on the Report Builder button on the Reports Manager, It initially failed to launch complaining `"Application cannot retrieve the files. Authentication failed." After a lot of research I found out that, even though users entered the credentials of a Local account when connecting to the Reports Manager, but when they clicked on the Report Builder button, the credentials from their windows account were picked up hence the ClickOnce application failed to Authenticate the user and failed to Launch. I changed this behavior by allowing Basic Authentication on Reporting Server and Allowing Anonymous Authentication on Reports Builder. Now when the users click on the Report Builder instead of report builder just picking the credentials from the current user windows account it prompts for the credentials , the user passes the credentials of the Local Admin account on the reporting server and the ClickOnce Application is downloaded as expected. __Problem__ Now the report builder is launched fine and I can right click the The datasources folder in the left pane of the designer, browse for the available data sources , test connection (shows tested successfully) . But as soon as I click on the DataSet and try to add a new dataset it throws an error saying "Authentication failed, cannot connect to the datasources." Even though in the last step the connection test was successful but at this stage it is failing. __Important Note__ The SQL Server has no external IP, it can only be seen from the reporting server. Could this be the reason that the connection is failing? I mean does the report builder try to obtain a direct connection to the SQL Server when launched? Or is there anything else that I have missed out? Any pointer any suggestions are much appreciated , Thank you.

I've got SQL Server 2017 Developer installed on my workstation, and just installed SSRS 2017. I am able to navigate in the SSRS portal, and upload files of dummy data, and create KPIs. However, when click "+New", then "Dataset" or "Data Source", I get the message "Something went wrong. Please try again later." The only clue in a log file is in my RSPortal_yyyy_mm_dd......log file which shows the following: OData exception occurred: Microsoft.SqlServer.ReportingServices2010.RSConnection2010+MissingEndpointException: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. ---> System.InvalidOperationException: Client found response content type of '', but expected 'text/xml' I get the same error if trying to upload an .rdl file. I did a "next/next/next" install of SSRS, keeping the defaults. The only unusual thing is that I am logged in with a machine account, not a user account. This machine account does show in the top right corner of the SSRS web portal, so that is passing through at least. What could be happening here?

Same issue as described here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6bf89cd-75de-49f9-8a3a-743419ecd631/ssrs-2016-editing-text-in-a-textbox-very-slow-in-the-designer?forum=sqlreportingservices But since those forums are dead as a.. well, a dead thing.. I wanted to re-ask here. As stated, when designing a report in VS 2015 against SSRS 2016, when I have a **tablix** and go to edit the text in a textbox in that tablix (say, a column header one, where I'm typing something in manually instead of pulling the value from the dataset), it goes V..E..R..Y..S..L..O..W..L..Y. Every character/keystroke takes many fractions of a second, if not OVER one second. It literally took almost 5 seconds to finish typing & previewing the text 'Units' in one box. Any help, suggestions, things I can disable or check? (And if the answer is "upgrade VS to 2017 or 2019", that is acceptable **IFF** you have legit real-world evidence that it helps! I'm not avoiding the upgrade entirely, I'm just not convinced that it's THE solution to this particular problem!) I will also say, anecdotally, this doesn't *seem* to have always been true; it seems to have started at some point within the past year-ish, but when specifically I couldn't tell you. Software: > - VS Pro 2015: 14.0.25431.01 Update 3 - BI Developer Extensions for VS 2015: 2.1.1 (formerly BIDS Helper) - SQL Server Data Tools 14.0.61707.300 - SQL Server Reporting Services: 14.0.806.134 - Redgate SQL Prompt: 9.2.4.6028 System: > - Core i5-5200U @ 2.2GHz, 12 GB RAM, Samsung 850EVO SSD - Windows 10 Enterprise v1809 / build 17753.379

I'm good with basic types of queries but this has me stumped. I have the table schema listed below, I need display the results in a table, with each row displaying the available results for a given date. I've also drawn out the required table layout below. I hope this is clear enough. Is there a query I could use to fetch these results at once? Currently, I'm queries each date and then stitching the results together which is inefficient. **Fields Table Schema** ` +--------------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+----------------+------+-----+---------+-------+ | id | varbinary(255) | NO | PRI | | | | value | varchar(255) | YES | | NULL | | | form_submission_id | varbinary(255) | YES | MUL | NULL | | | form_field_id | varbinary(255) | YES | MUL | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | deleted_at | timestamp | YES | MUL | NULL | | +--------------------+----------------+------+-----+---------+-------+ ` **Required Query Results** ` +--------------+---------------+---------------+-----------------+ | created_at | form_field_id | form_field_id | form_field_id 3 | +--------------+---------------+---------------+-----------------+ | Apr 14, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 13, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 12, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 11, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 10, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ `

I have a report with a parameter that usually gets its default values by running a query that takes another parameter as it's input. This works fine when the report is run interactively. However, I have a requirement to call this report from code where all the filters are supplied by the code. The code I have for the other parameters works fine: string report = "Analysis/Report.aspx?ReportPath=/MyReport"; string fullReportPath = string.Format("{0}&LocationId={1}&BatchNumber={2}&SearchReference={3}", report, locationId, batchNumber, reference); RunReport(fullReportPath); There is a drop down where the both the available values and default values are retrieved from the database via a query which has the locationId as a parameter. This is so all the values are selected by default. What do I need to change in the report and/or the code so it works as it does now and I can supply the selected values for this drop down as a parameter? I've found [this blog post](https://www.timmitchell.net/post/2012/02/07/conditional-default-values-in-ssrs/) which uses the concept of internal or hidden control parameters to use when setting the values of other parameters, but the example given just sets the other parameter to a default value or leaves it blank. I need for the parameter to run the current query.

I am using SSRS 2016 and Report Builder. I need the page breakinging with a group to be conditional on the value of a parameter. Does anyone know if this is even possible?

I'm trying to print labels on a Zebra printer in SSRS 2014 and the orientation is coming out wrong. In report builder, I set the page size to 4.15 x 1.15 with correct margins. Also, in report builder, if I print it will typically come out the correct orientation (landscape), but occasionally I have to go into Page Setup, change the Orientation from Portrait to Landscape & back again to get it to print correctly (Not sure why it requires a Portrait orientation to print in Landscape in Report Builder. However, when I try to print from SSRS directly, it comes out in portrait and prints across several labels. I've done some research and saw recommendations to change the printer to print directly and bypass the print spooler, which I've attempted to no avail. Any assistance is greatly appreciated.

I'm trying to get my report to display different color cells, depending on the condition. This is my current code: =Switch(Fields!DaysToExpiration.Value > 31, "Green", Fields!User_Certification_Status.Value = 8, "Firebrick”, Fields!User_Certification_Status.Value = 13, “Orange”, Fields!User_Certification_Status.Value = 11, “Orange”, Fields!User_Certification_Status.Value = 10, “Orange”, Fields!User_Certification_Status.Value = 12, “Orange”, Fields!DaysToExpiration.Value <= 30, "Yellow", 1=1, "Gray") So, my problem lies with the last part of the code. I'm using a matrix view, which means there are some cells where there is no data. SSRS is treating these like they have a value though (I'm assuming 0). This means that all the blank ones automatically turn to yellow, rather than gray. How can I get around this? Thank You.

I am trying to catch a SqlException on a SSRS report and display it on a pop-up. So far I have not been able to find this configuration on Report Builder and I thought maybe adding some code on the Code section of the report. Please note that I am not trying to render a report from a remote location, but only continue to render the current report. A little more context: the SSRS report connects to SQL server on the same host, and executes a stored procedure that may throw an error when invalid input is provided. By default SSRS will output the error on the report itself and nothing else. I think it will be more intuitive for my end user to have the error displayed as a pop-up and not in the report. Microsoft has this article but it is not too descriptive as I wish: https://learn.microsoft.com/en-us/sql/reporting-services/report-server-web-service-net-framework-exception-handling/best-practices/using-try-and-catch-blocks some tentative source could be: public void RenderReport(param1, param2){ try { //pseudocode execute current report ... } catch (SqlException e) { MessageBox.Show(e.Message); // could be an alert instead, not really sure .. } } Not sure if I am just over complicating this ... Can somebody help me to figure out this?

As the image below shows, on my DB I also have 2 columns that appear in the results pane separately - first name and last name - (lower part of image) How do I join them together in the report builder?

I'm currently using Microsoft SQL Report Builder to create a custom report for my data. I have a table of customers, a table of devices and a table of jobs (each customer has a couple of devices, each device has a couple of jobs). What I want is a list of customers, and when I click on a customer it should expand and show all associated devices, and when I click on the device it should expand and show all jobs. Should I just use a list in a list in a list? Or should I embed a subreport inside my list? Should I split all 3 tables in different datasets? Or just use 1 dataset? I imagine there are a lot of different options (I did my research online) but there seems to be little consensus about whether it's a good idea to use subreports or not for example.