I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks. Any help would be appreciated!

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 ?

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an Insert query which doesn't work with my pass-through query. I see a lot of mention of creating a recordset from the pass-though query and then copying the recordset to a temporary table but have not had luck location instructions on how to do that. Some background. I have a PostgreSQL server that is aggregating multiple databases via a foreign data wrapper. Across those databases I have multiple table names that a reused. I can do a normal pass-through query using the front end query properties but this doesn't handle parameters for the pass-through query. I can build a form using ADO objects by setting the record set for the form to equal a record set created from a pass-through query. (Set Me.Recordset = MyRecordset) This allows me to create the query string dynamically so I can use parameters! One step closer but not a report. Reports do not support setting themselves to a record set the way forms do so I'm back to the drawing board. Any suggestion on how to accomplish my goal? Should I give up and have Postgres store the results in a table, link that table, and call it a day?

I have a design a table for attendance and here are the genral fileds 1. AttendanceMachineLoginId 2. EmpId 1. AttendanceDateTime Whenever user will come to office, he has to make his attendance. The **first** attendance will consider **login** and the **second** will consider the **logout**. **Each time a record will be added with the time**. A user can make **multiple entries** (login logout in a single day. Like this EmpId 81 has login and logout two times in same day. Now, My aim is to generate per day employee report that how many minutes he has given to the company. I just came to know that TIMESTAMPDIFF() can provide the minutes but i am unable to understand that how can I apply it to my table. Additionally, I want to ask that, do the **table Fields** are right for the desired report or I need to change it? **One Another Strategy:** I was also thinking that I should add minutes column in the table and whenever user logout I should calculate the minutes and add that minutes with logout entry. **Sample Data:** INSERT INTO attendancemachinelogin (AttendanceMachineLoginId, EmpId, TimeTrackId, AttendanceDateTime, RecordAddDateTime) VALUES (0, 81, 315079, '2018-8-15 14:8:46', '2018-08-15 14:09:25'), (0, 81, 315079, '2018-8-15 14:20:38', '2018-08-15 14:21:17'), (0, 81, 315079, '2018-8-15 14:21:9', '2018-08-15 14:21:47'), (0, 81, 315079, '2018-8-15 14:28:37', '2018-08-15 14:29:16'), (0, 81, 315079, '2018-8-15 14:28:58', '2018-08-15 14:29:36'), (0, 81, 315079, '2018-8-15 14:36:42', '2018-08-15 14:37:21'), (0, 81, 315079, '2018-8-15 15:36:34', '2018-08-15 15:37:13'), (0, 81, 315079, '2018-8-15 15:52:39', '2018-08-15 15:53:17'), (0, 81, 315079, '2018-8-15 16:5:38', '2018-08-15 16:06:17'), (0, 81, 315079, '2018-8-15 16:6:50', '2018-08-15 16:07:29'), (0, 81, 315079, '2018-8-15 16:8:49', '2018-08-15 16:09:29'), (0, 81, 315079, '2018-8-15 16:18:28', '2018-08-15 16:19:08'), (0, 81, 315079, '2018-8-15 16:20:49', '2018-08-15 16:21:28'), (0, 81, 315079, '2018-8-15 16:23:18', '2018-08-15 16:23:58'), (0, 81, 315079, '2018-8-15 16:24:3', '2018-08-15 16:24:42'), (0, 81, 315079, '2018-8-15 16:24:47', '2018-08-15 16:25:26'), (0, 81, 315079, '2018-8-15 16:24:58', '2018-08-15 16:25:37'), (0, 81, 315079, '2018-8-15 16:25:54', '2018-08-15 16:26:33'), (0, 81, 315079, '2018-8-15 16:56:47', '2018-08-15 16:57:27'), (0, 101, 417092, '2018-8-15 17:37:53', '2018-08-15 17:38:32'), (0, 101, 417092, '2018-8-15 18:4:34', '2018-08-15 18:05:14'), (0, 101, 417092, '2018-8-15 18:7:43', '2018-08-15 18:08:22'), (0, 81, 315079, '2018-8-15 18:13:15', '2018-08-15 18:13:54'), (0, 81, 315079, '2018-8-17 10:50:16', '2018-08-17 10:50:54'), (0, 101, 417092, '2018-8-17 10:51:54', '2018-08-17 10:52:31'), (0, 4, 413034, '2018-8-17 11:45:16', '2018-08-17 11:45:54'), (0, 91, 916086, '2018-8-17 11:59:34', '2018-08-17 12:00:12'), (0, 81, 315079, '2018-8-17 12:0:19', '2018-08-17 12:00:56'), (0, 81, 315079, '2018-8-17 15:7:41', '2018-08-17 15:08:17'), (0, 101, 417092, '2018-8-17 15:9:54', '2018-08-17 15:10:32'), (0, 101, 417092, '2018-8-17 15:10:9', '2018-08-17 15:10:45'), (0, 101, 417092, '2018-8-17 15:10:23', '2018-08-17 15:10:59'), (0, 101, 417092, '2018-8-17 15:10:25', '2018-08-17 15:11:02'), (0, 101, 417092, '2018-8-17 15:11:6', '2018-08-17 15:11:43'), (0, 101, 417092, '2018-8-17 15:11:15', '2018-08-17 15:11:52'), (0, 101, 417092, '2018-8-17 15:11:17', '2018-08-17 15:11:54'), (0, 81, 315079, '2018-8-17 15:11:32', '2018-08-17 15:12:09'), (0, 81, 315079, '2018-8-17 15:12:32', '2018-08-17 15:13:09'), (0, 81, 315079, '2018-8-17 15:35:33', '2018-08-17 15:36:10'), (0, 81, 315079, '2018-8-17 15:41:58', '2018-08-17 15:42:34'), (0, 81, 315079, '2018-8-17 15:42:17', '2018-08-17 15:42:54'), (0, 81, 315079, '2018-8-17 16:8:25', '2018-08-17 16:09:01'), (0, 81, 315079, '2018-8-17 16:8:32', '2018-08-17 16:09:08'), (0, 101, 417092, '2018-8-17 16:8:53', '2018-08-17 16:09:30'), (0, 101, 417092, '2018-8-17 16:9:20', '2018-08-17 16:09:57'), (0, 4, 413034, '2018-8-17 16:10:16', '2018-08-17 16:10:53'), (0, 36, 413037, '2018-8-17 16:10:46', '2018-08-17 16:11:23'), (0, 81, 315079, '2018-8-17 16:22:21', '2018-08-17 16:22:58'), (0, 101, 417092, '2018-8-17 16:22:45', '2018-08-17 16:23:21'), (0, 4, 413034, '2018-8-17 16:23:12', '2018-08-17 16:23:49'), (0, 81, 315079, '2018-8-17 16:23:35', '2018-08-17 16:24:12'), (0, 81, 315079, '2018-8-17 16:44:4', '2018-08-17 16:44:42'), (0, 101, 417092, '2018-8-17 16:44:22', '2018-08-17 16:44:58'), (0, 81, 315079, '2018-8-17 17:6:51', '2018-08-17 17:07:28'), (0, 101, 417092, '2018-8-17 17:7:8', '2018-08-17 17:07:45'), (0, 4, 413034, '2018-8-17 17:7:52', '2018-08-17 17:08:28'), (0, 81, 315079, '2018-8-17 17:9:25', '2018-08-17 17:10:02'), (0, 101, 417092, '2018-8-17 17:9:46', '2018-08-17 17:10:22'), (0, 4, 413034, '2018-8-17 17:10:6', '2018-08-17 17:10:42'), (0, 81, 315079, '2018-8-17 17:10:24', '2018-08-17 17:11:01'), (0, 81, 315079, '2018-8-17 17:10:39', '2018-08-17 17:11:15'), (0, 101, 417092, '2018-8-17 17:10:47', '2018-08-17 17:11:24'), (0, 101, 417092, '2018-8-17 17:10:58', '2018-08-17 17:11:35'), (0, 81, 315079, '2018-8-17 17:11:10', '2018-08-17 17:11:46'), (0, 101, 417092, '2018-8-17 17:11:31', '2018-08-17 17:12:09'), (0, 4, 413034, '2018-8-17 17:40:40', '2018-08-17 17:41:18'), (0, 101, 417092, '2018-8-17 17:41:23', '2018-08-17 17:41:59'), (0, 36, 413037, '2018-8-17 17:41:37', '2018-08-17 17:42:14'), (0, 81, 315079, '2018-8-17 17:42:9', '2018-08-17 17:42:45'), (0, 3, 213020, '2018-8-17 17:47:34', '2018-08-17 17:48:11'), (0, 81, 315079, '2018-8-17 17:48:16', '2018-08-17 17:48:52'), (0, 4, 413034, '2018-8-17 17:48:59', '2018-08-17 17:49:36'), (0, 4, 413034, '2018-8-17 17:49:59', '2018-08-17 17:50:36'), (0, 36, 413037, '2018-8-17 17:52:36', '2018-08-17 17:53:13'), (0, 101, 417092, '2018-8-17 17:52:53', '2018-08-17 17:53:29'), (0, 6, 213016, '2018-8-17 17:53:30', '2018-08-17 17:54:06'), (0, 81, 315079, '2018-8-17 17:53:44', '2018-08-17 17:54:20'), (0, 4, 413034, '2018-8-17 17:54:27', '2018-08-17 17:55:03'), (0, 3, 213020, '2018-8-17 17:54:49', '2018-08-17 17:55:27'), (0, 4, 413034, '2018-8-17 17:55:23', '2018-08-17 17:56:00'), (0, 36, 413037, '2018-8-17 17:58:33', '2018-08-17 17:59:10'), (0, 101, 417092, '2018-8-17 17:58:47', '2018-08-17 17:59:24'), (0, 102, 517094, '2018-8-17 17:59:4', '2018-08-17 17:59:40'), (0, 81, 315079, '2018-8-17 17:59:33', '2018-08-17 18:00:09'), (0, 4, 413034, '2018-8-17 18:0:16', '2018-08-17 18:00:52'), (0, 3, 213020, '2018-8-17 18:0:40', '2018-08-17 18:01:17'), (0, 6, 213016, '2018-8-17 18:1:30', '2018-08-17 18:02:06'), (0, 36, 413037, '2018-8-17 18:26:24', '2018-08-17 18:27:01'), (0, 101, 417092, '2018-8-17 18:26:38', '2018-08-17 18:27:14'), (0, 6, 213016, '2018-8-17 18:27:9', '2018-08-17 18:27:45'), (0, 81, 315079, '2018-8-17 18:27:24', '2018-08-17 18:28:00'), (0, 102, 517094, '2018-8-17 18:27:38', '2018-08-17 18:28:14'), (0, 4, 413034, '2018-8-17 18:28:13', '2018-08-17 18:28:49'), (0, 81, 315079, '2018-8-17 19:36:49', '2018-08-17 19:37:26'), (0, 101, 417092, '2018-8-17 19:37:17', '2018-08-17 19:37:54'), (0, 102, 517094, '2018-8-17 19:37:30', '2018-08-17 19:38:07'), (0, 36, 413037, '2018-8-17 19:38:13', '2018-08-17 19:38:50'), (0, 4, 413034, '2018-8-17 19:38:54', '2018-08-17 19:39:32'), (0, 3, 213020, '2018-8-17 19:39:58', '2018-08-17 19:40:35'), (0, 101, 417092, '2018-8-18 10:21:26', '2018-08-18 10:22:03'), (0, 81, 315079, '2018-8-18 10:30:23', '2018-08-18 10:31:09'), (0, 4, 413034, '2018-8-18 10:31:46', '2018-08-18 10:32:27'), (0, 102, 517094, '2018-8-18 10:32:15', '2018-08-18 10:32:53'), (0, 6, 213016, '2018-8-18 10:32:44', '2018-08-18 10:33:22'), (0, 3, 213020, '2018-8-18 10:33:23', '2018-08-18 10:34:03'), (0, 81, 315079, '2018-8-18 10:42:49', '2018-08-18 10:43:27'), (0, 101, 417092, '2018-8-18 10:43:25', '2018-08-18 10:44:03'), (0, 81, 315079, '2018-8-18 10:48:51', '2018-08-18 10:49:30'), (0, 102, 517094, '2018-8-18 10:49:9', '2018-08-18 10:49:49'), (0, 81, 315079, '2018-8-18 10:56:46', '2018-08-18 10:57:25'), (0, 1, 1211003, '2018-8-18 10:57:0', '2018-08-18 10:57:38'), (0, 4, 413034, '2018-8-18 10:57:51', '2018-08-18 10:58:38'), (0, 3, 213020, '2018-8-18 10:58:43', '2018-08-18 10:59:26');

TL;DR: How can I link to an SSRS (2016) report by URL, with a parameter in the query-string, AND retain (show) the FULL MENU bar (the top "SSRS chrome" that has the Favorites, Browse, Settings, breadcrumb navigation, etc.). Why: I'm phasing out an old 2008R2 instance by putting a clickable link on the old RDLs to new versions of similar reports in the 2016 instance. When the user has selected/filled-in a parameter value, I obviously want to pass that forward to the new report when possible. I've followed the dox and built the URL like so:

://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?%2fFolder+Name%2fOrder+Line+Details&rs%3aCommand=Render&OrderNo=123456

or, the non-encoded more readable version:

://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?/Folder Name/Order Line Details&rs:Command=Render&OrderNo=123456

This works insofar as it shows the report with the parameter values filled-in, but it does NOT show the top level SSRS menu section, i.e. the branding (logo/title), the Settings/Download/Help buttons in the upper-right, and the Favorites & Browse links in the upper left. I **WANT** this stuff to show up, because it conveys to them that they're in the new reporting system and they can navigate around and find other reports. What else I've tried: just using the 'standard' URL of the report (as in browsing to it from the menu in the new instance), and just appending the parameter to the end, like so:

://ssrsInstance/reports/report/Folder%20Name/Order%20Line%20Details&OrderNo=123456

or, for the non-encoded version:

://ssrsInstance/reports/report/Folder Name/Order Line Details&OrderNo=123456

That gives an "invalid item path" error:

path of the item '/Folder Name/Order Line Details&OrderNo=123456' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

And obviously the other (less favorable) option is to simply use that URL **without** the parameter, but that forces the user to have to re-do work (especially in the case of more complex parameter sets and multi-valued params), which would suck. Hope there is a way! Thanks all. :)

I intent to put reporting/analytics database on slave and run job scheduler every night to aggregate and insert data from operational database to reporting database. Should I do this or I have to setup dedicated server for reporting/analytics database and use some tools to aggregate and insert data from slave to reporting server? Thank for help,

This is will be my first SSRS migration and please bear with me if some points don’t make any sense. I need to migrate the Reporting server but need to keep the same name so that the reporting URL don’t change. Sources Server A – Has the database and the reporting databases Server B only has the reporting server installed. Destination Server C- New SQL Server will be installed with all the databases Server D will have the reporting server installed. Below is a summary • Backup SSRS databases on source server • Backup Encryption Key on source server • Restore SSRS databases on target server • Restore Encryption Key on target server • Remove old server name from the Keys table on the target server My confusion lies with the 1) What if I want the new server (Server C and Server D) to have the same name of Server A and Server B hence my reporting Web Service URL and the Web Portal URL don’t change. http://ServerB/Reports http://ServerB/ReportServer My thought process was Rename Server A and Server B before migration and name Server C and Server D with the old name of Server A and Server B. After that the Web Service URL and the Web Portal URL will pick up the default name which will be the old one. Please correct me if I am wrong and is there any thing else that I need to be aware of which might break the reporting 2) After the migration do we need to move the rds files from the old server to the new server as well and since the names are the same so do we still need to modify the data sources?

I set up a new SSRS2022 server. Everything works fine, we can access and etc, but every time we need to keep adding our credentials. In the .config file I could find these rows: https://learn.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-ver16&redirectedfrom=MSDN Following Microsoft pages and StackOverflow answers couldn't fix this issue. The old server has this different: 3 OUR.DOMAIN.COM but adding that didn't help (actually adding that broke SSRS and I couldn't even start, so I removed) Execution account is set to the domain service account that is the same as the service account.

I'm using Business Objects 4.3 to query my HR system and have 4 objects on the report from one table with multiple records. I want the latest record and the corresponding values for each object. Example: |Person ID|Check Date|Renewal Date|Reference Number| |---------|----------|------------|----------------| |4566|1/1/23|31/12/23|123| |4566|1/1/24|31/12/24|456| All columns currently show MULTIVALUE against the person ID (key field) as the check data is from a sub query. I have used Max on the Check Date to get the latest entry but I don't want to put max on the other columns in case of errors in the data. EG if I put max on both date columns I would get the bold entries instead of a renewal date of 31/12/23 |Person ID|Check Date|Renewal Date|Reference Number| |---------|----------|------------|----------------| |4566|1/1/23|**31/12/24**|123| |4566|**1/1/24**|31/12/23|456| So how can I make the variable for Renewal Date or Reference Number dependent on the entry returned for Check Date, ie. return the whole corresponding record? I tried where check date = max(check date) but it didn't like that. Thanks

I have a query, but want to create a store procedure that will set a reminder if 'Stock on Hand is 0' 'Blank' The following columns(Crystal, Cylical) needs to set a reminder to a recipient via email to action on them once if its blank or 0. // sql query from the database DECLARE @body NVARCHAR(MAX); -- Prepare the email body with the list of items having Stock on Hand = 0 SET @body = N'

Stock Alert: Items with zero stock on hand

StockCode Description Warehouse QtyOnHand QtyAllocated QtyOnOrder QtyInTransit QtyOnBackOrder '; -- Add the rows to the email body SELECT @body = @body + N' ' + InvWarehouse.StockCode + N' ' + InvMaster.Description + N' ' + InvWarehouse.Warehouse + N' ' + CAST(InvWarehouse.QtyOnHand AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyAllocated AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyOnOrder AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyInTransit AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyOnBackOrder AS NVARCHAR) + N' ' FROM sysproR.dbo.InvMaster InvMaster JOIN sysproR.dbo.InvWarehouse InvWarehouse ON InvMaster.StockCode = InvWarehouse.StockCode WHERE InvWarehouse.QtyOnHand = 0; -- Close the HTML tags SET @body = @body + N''; -- Check if there are items with zero stock on hand IF EXISTS ( SELECT 1 FROM sysproR.dbo.InvWarehouse WHERE QtyOnHand = 0 ) BEGIN -- Send the email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test', -- Replace with your Database Mail profile name @recipients = 'training@metrocon.com', @subject = 'Stock Alert: Items with zero stock on hand', @body = @body, @body_format = 'HTML'; END

I am looking for a standard method, a library or maybe best practice approach that can generate a time-interval/state-duration report from a time/state series. This seems like a common reporting requirement for time/state data, and I would assume there is framework support for this. I am specifically seeking a function or library to achieve this, rather than a custom SQL solution, but maybe there is only a custom way to do it. Below, I provide (in postgres) a simplified structure and sample data to facilitate the discussion. **Creating the input table**

-- Create the time_state table
CREATE TABLE "time_state" (
  "state_time" TIMESTAMP WITH TIME ZONE NOT NULL,
  "state_name" VARCHAR(50) NOT NULL
);

**Adding example input data**

INSERT INTO "time_state" ("state_time", "state_name") VALUES
('2023-07-01 21:00:00+00', 'OFF'),
('2023-07-01 21:15:00+00', 'IDLE'),
('2023-07-01 21:30:00+00', 'HEATING'),
('2023-07-01 21:45:00+00', 'COOLING'),
('2023-07-01 22:00:00+00', 'FAULT'),
('2023-07-01 22:15:00+00', 'OFF'),
('2023-07-01 22:30:00+00', 'IDLE'),
('2023-07-01 22:45:00+00', 'HEATING'),
('2023-07-01 23:00:00+00', 'COOLING'),
('2023-07-01 23:15:00+00', 'FAULT'),
('2023-07-01 23:30:00+00', 'OFF'),
('2023-07-01 23:45:00+00', 'IDLE'),

('2023-07-02 00:00:00+00', 'HEATING'),
('2023-07-02 00:15:00+00', 'COOLING'),
('2023-07-02 00:30:00+00', 'FAULT'),
('2023-07-02 00:45:00+00', 'OFF'),
('2023-07-02 01:00:00+00', 'IDLE'),
('2023-07-02 01:15:00+00', 'HEATING'),
('2023-07-02 01:30:00+00', 'COOLING'),
('2023-07-02 01:45:00+00', 'FAULT'),
('2023-07-02 02:00:00+00', 'OFF'),
('2023-07-02 02:15:00+00', 'IDLE'),
('2023-07-02 02:30:00+00', 'HEATING'),
('2023-07-02 02:45:00+00', 'COOLING'),
('2023-07-02 03:00:00+00', 'FAULT'),
('2023-07-02 03:15:00+00', 'OFF'),
('2023-07-02 03:30:00+00', 'IDLE'),
('2023-07-02 03:45:00+00', 'HEATING'),
('2023-07-02 04:00:00+00', 'COOLING');

**Result of the report that shows the duration of each state within each hour should be then:**

date_with_hour	state_name	state_duration_in_minutes
2023-07-01 21:00:00	COOLING	15.0
2023-07-01 21:00:00	HEATING	15.0
2023-07-01 21:00:00	IDLE	15.0
2023-07-01 21:00:00	OFF		15.0
2023-07-01 22:00:00	COOLING	15.0
2023-07-01 22:00:00	FAULT	15.0
2023-07-01 22:00:00	HEATING	15.0
2023-07-01 22:00:00	IDLE	15.0
2023-07-01 23:00:00	COOLING	15.0
2023-07-01 23:00:00	FAULT	15.0
2023-07-01 23:00:00	HEATING	15.0
2023-07-01 23:00:00	IDLE	15.0
2023-07-02 00:00:00	COOLING	15.0
2023-07-02 00:00:00	FAULT	15.0
2023-07-02 00:00:00	HEATING	15.0
2023-07-02 00:00:00	IDLE	15.0

Thanks Dave

Although I'm using SQL Server, as this is a question of whether an optimiser will re-write a query to remove redundant columns, I'm pitching this at all data servers: RDBMS, NoSQL, MPP, anything capable of holding and querying stored data using SQL that shall optimise the query before running it. I kinda feel that they would, or at least it would seem logical that they would especially as it'd be crazy to fill a cache unnecessarily, but I can't find any evidence to say they would. I don't want to get bogged-down on how environments, network, server, table, and cache loads, and table size and performance will alter the selected plans; this is just a very high-level question of: would the server rewrite a query to remove redundant columns and/or joins, but mostly columns, that are not in any way used to generate the result. On my isolated dev server, I have this test query running against a tiny test 290k row table, it has a pk on an identity field, and a composite index which whilst two of the fields from the derived table are covered as part of the index, the primary field under test is not The derived table in this instance has 7 redundant columns, and I'm executing these three DBCC commands before each run so as to start with a cold cache : DBCC FreeProcCache; DBCC DropCleanBuffers; DBCC FreeSystemCache('sql plans'): Select a.provider_type ,Count(1) As count_of_provider_type From ( Select customer_id ,access_plan ,provider_type ,ap_postcode ,browser ,session_start_date ,session_end_date ,payment_method From adhoc..datacentre )a Group By a.provider_type; Returns this actual plan: And from the profiler, CPU: 92, Reads: 23703 And then, having re-ran the three DBCC commands to return the cache to cold, running this re-expressed query: Select provider_type ,Count(1) As count_of_provider_type From adhoc..datacentre Group By provider_type; Gives me this actual plan: And profiler, CPU: 78, Reads: 23476 Notice any similarities? Which given the batch count, io and cpu, leads me to wonder that the optimiser did rewrite the first query to remove the derived table and the redundant columns. But how can I prove it. I can't find anything at learn.microsoft under the Query Processing Architecture to suggest that the optimiser would rewrite the query, neither can I find a way of seeing what was transferred to cache. Does anything exist that can definitively say exactly what was read and cached. Remember - although I'm using SQL Server, I'd be interested to know how other RDBMS / MPP such as GBQ, Redshift, Athena, Snowflake etc would handle this And finally, the why. What nutjob would write the first query without having realised it could be re-expressed? This is twofold: Firstly views, and secondly and more prominently: SQL from visualisation and reporting tools capable of accepting an SQL script, which is often functionally equivalent to a non-materialised view. As we all know, views can be abused. They shouldn't, and in an ideal world, users would create views as isolated models to spit-out a result-set ready for ingestion by the tool that the model was designed for, which is also the same direction for visualisation and reporting tools. But we all know this never happens. Just like a doctor who has a cream for that, so too have engineering built a view that includes "what you're after" in its output, "so you don't need to go and create a new query, just query that view". And if the view is basic enough, maybe it's being used to replace a table, but includes scd:2 logic, and maybe its selecting non-engineering and/or PII data; or maybe it is a basic model with a couple of non-complex joins. But if a user were to query this view for only a couple of fields, would the optimiser rewrite the query to remove redundant columns of a single-table view, and possibly remove redundant joins from a multi-table view? As I said at the beginning, I feel the optimizer would, but I need to be able to evidence this beyond conjecture or theory.

Can we install Power BI and SSRS on Windows Server 2019 without having issues? Does SQL Server 2019 Enterprise Edition with Software Assurance get Power BI?

I have such question. Is it possible to add logout functionality for current user IN SSRS web page in browser?

I want to have two databases on separate severs. Let's call the databases Source and Target. Target is the reporting server copy of Source. Except for the changes caused by a stored procedure that I need to regularly run in Target, I want Source and Target to be identical and sync'd up roughly once a day. All of the data needed to create Source comes from the same server as Source. Is log shipping a suitable method to solve this problem? Or does my "I need to run a stored procedure that changes database Target" restriction completely rule that out?

We are replacing an old VM which was configured with reporting services 2016. The Web Service and Web Portal URL's were configured for both a "public" URL which looks nicer as well as a URL that reflects the name of the VM SSRS is installed on which is typically only used by our developers. When I went to configure our new VM, the only option I had for the connection string was to use the certificate of the machine, meaning the URL is not pretty and most importantly will require many clients to update their bookmarks. We are trying to avoid this by also configuring the "public" URL to be the same as the old server but I don't seem to have an option to make a custom URL. Ex. **Old Server has: https:\\\\vm_name.domain.com && https:\\\\custom_url_here.domain.com** **New Server has: https:\\\\vm_name.domain.com - I cannot for the life of me get the custom URL configured in the reporting services configuration manager** Things I have tried: - Editing the rsreportserver.config file to both match the old server or at the very least have the URL connection I need added (breaks both connections) - Manually exporting the old servers HTTP registry keys for the custom URL only in tandem with editing the config file (strangely allows the custom URL to work, but the VM URL then breaks despite the registry entry for the VM URL not changing - I did not export that value from the old server as obviously the new server has a different name value) *As an FYI - Each time I have tried something I have restarted the services accordingly and seen the changes reflected in SSRS.* When I edit the config file (Microsoft says this won't work fully but doesn't provide another option, hence why I tried the reg keys at the same time), the "custom URL" actually starts working, but then the regular URL breaks. Why is it so difficult to just define my own URL? Do I need to generate a certificate for that custom URL and add it to the new VM in the hopes Reporting Services Configuration Manager finds that? When I hit the certificate drop down in SSRS COnfig Manager the only option I have is for the server name hence why I was thinking generating a custom certificate with the name I want might get around this. **Any suggestions??? I'm at a loss as to how the old server had a custom URL name. I also did not find any certificates on that old VM for the custom URL so I really don't know how it could have been created....**

SSRS 2016 - Standard Default Settings. Work Horse for Reporting. Some large reports can run 5 hours+. Most reports cannot be optimized/tuned - 3rd party vendor. On occasion noticed procs "WriteLockSession,CreateChunkSegment,ReadChunkSegment" in multiple sessions with millions of reads and writes , and at times Blocking. Noticed these table have over 400k records: SegmentedChunk, ChunkSegmentMapping and Segment. Is there a standard process to clean up these tables (unused data) ,or check these tables for orphan records? Does 400k sound high (like a problem), have other SSRS instances with much lower counts. Restarted SSRS - no change. Shrink db - no change. Index optimization and update stats - regular maintenance.

I need to produce a report showing all our Azure SQL Databases with their size. The databases are split among many resource groups in several subscriptions. I can get the information for a single instance, or even for all DBs in a resource group, but I have too many RGs to make that a workable solution, and I cannot find a way of getting the information for a whole subscription, or whole tenancy.

I am trying to install SSRS on a new server to report on our new database server and want to use a MSA account on our domain (server is on our domain) but I keep getting errors when trying to start SSRS as the first step to installation. This is the error: >System.InvalidOperationException: Cannot start service SQLServerReportingServices on computer 'MYSSRSSERVERNAME-NOTREALNAME'. ---> System.ComponentModel.Win32Exception: The service did not start due to a logon failure --- End of inner exception stack trace ---\ at System.ServiceProcess.ServiceController.Start(String[] args)\ at ReportServicesConfigUI.Panels.ConfigurationPanelWithErrors.StartOrStopServiceTask(Boolean start, String serviceName) I have tried a domain account and that works, so it has something to do with the MSA account. Event Viewer states the failure reason as Unknown user name or bad password. I never set a password for the MSA account as I used MSA GUI to set the password and it never prompted me to create one. The MSA account has logon on service permissions and is a domain user. I have added the account to local administrators groups to test but that still didn't work. Anyway to resolve this?

I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by _year_ and _month_. Each sale made on the website will generate a relational link between multiple tables, where I need to get some values to display the proper report. Besides showing just what items were sold, I also need to show, for example, how many of those sales were made using credit card, money, etc... How many of them were shipped via postal office, third part services, etc. Before I continue, keep in mind I'm not an advanced user in dataBases/BackEnd, so any improvement/suggestions is very welcome. I'm using PHP/SQL on the backend and a MySql database with a model as the following image: The basic information I need to know is (all of these are the total for each month): - Value: The total amount of sales; - Sales: Total sales (based on orderStatus); - Payment: Total sales using each payment type; - Delivery: Total sales using each delivery type; - **Reported sales**: Total sales reported by user (based on orderStatus); - **Canceled sales**: Total sales canceled (based on orderStatus); I was able to built this report, but It seems to be very poor and/or not the right way to do this. It felt very messy, so this is why I'm here to ask your help to guide me on how to build this type of query and to improve my code. To create the segment by Year/Month I'm using this code: SELECT DISTINCT YEAR(created) as 'year' FROM order for ($i=1; $i= 3 AND b.status As you can see, the sql works, but it's not very optimal. Also, It feels like I'm repeating myself a lot. For example, I created a variable with the most common piece of code used in each query, most of them need to match an specific range of orderStatus, and I need to get the total count of those informations. With the exception of the canceled and reported sales, which are of a different orderStatus. Besides that, i'm creating a manual check on the delivery and payment status, it could be somehow automated. The way I'm doing will also return every type of result, for example, if there were no payment with money, it will execute the query and return 0. This isn't needed, as I can check for a valid and existing information on the FrontEnd. If this can be done, great, but if not, it's not a big problem to get only when there is data stored. So, how can I optimize this code, or even my database structure in order to have a better result/query to generate these sales report?