People who code: we want your input. Take the Survey

Hot answers tagged

17

Unless I'm missing some nuance, you will simply set the Row Visibility based on the value. Right click on the detail line and select Row Visiblity... In the window that pops up, select Show or hide based on an expression Within that expression, you'll use logic like =Fields!Total_Allocation.Value = 0 And Fields!Total_Actual_Cost.Value = 0


16

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows: DECLARE @myStatement VARCHAR(MAX) SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine that's longer than 8000 characters EXECUTE (@myStatement) AT LinkedServerName In order to make sure this doesn't throw an error, you need to enable the RPC OUT ...


14

... was hoping I could get ... a rough rough estimate of what we should be running. Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate. Database: sql server 2008 r2 enterprise database Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on ...


14

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? It depends on the sync mode you have used when configuring Availability group - Sync or Async ! On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's why ...


12

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying. Your options are, in no particular order: Mirroring with snapshots : effective but has management overhead. Backup and restore : Are you restoring from full backups rather than differential ...


11

Does this mean Microsoft will eventually try to deprecate SSRS? Ref: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/ No. We moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access ...


10

There are two places you can control this visibility. The first is on the row in the tablix; the second is on the row in the Row Groups*. If you change visibility by right-clicking the row in the tablix then on the final report the row will be invisible, but still there. If you change visibility by right-clicking the Row Groups line then on the final ...


10

You actually just put a SUM around the outside: =Sum(IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100, Fields!actualwork.Value * 77)) It will evaluate that row by row and will provide the correct sum.


10

KPIs are an Enterprise feature. They are only available in the Enterprise and Developer Editions. https://msdn.microsoft.com/en-us/library/mt742500.aspx You will also need to do two things in order to have KPIs show up in the "New" dropdown. You must have Content Manager or Publisher privileges in the folder you will be creating the KPIs in. You must have ...


9

This is account 'bean counter' insanity. The $1100-$2500 you would spend on RAM could possible pay for itself back within a week! They are getting time outs for 20 employees and 5 of them are doing 'resource intensive' work. I'd imagine their time isn't cheap, and some of those reports are the ones the boss not signing off on the paycheck would love. ...


9

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some of them). First you want to access is the Reporting Services Error Catalog, depending on your installation directory, it will be something like this: ?:\...


8

Why are you running a log backup job on [ReportServerQA5TempDB]? It should be in simple mode. From MSDN: ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports. and Use the simple recovery model to backup the reportservertempdb database. Now the real question ...


8

You can add a row outside of the outermost group right under the tablix header row. Set the row visibility to Show or hide based on an expression. The expression would like like: =iif(CountRows("DataSet1") > 0, true, false) When there is no data, the table will show the headers and empty row. When there is data, the empty row will be hidden.


8

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report See this link for an explanation about the why and ...


8

If I understood you correctly, you have a large third-party system, you don't have much control over it, you make complex reports that read data directly from this third-party database, your queries depend on the internal structure of the third-party database. I would approach it like this: Set up my own separate database, which I have full control of....


8

Get SSRS Datasources from ReportServer I would also like to include the 'Connection String' at least How can I display Data Source details with a SQL query? TSQL for getting the Connection String of SSRS Data sources -- Transact-SQL script to get connection string of all SSRS Shared Datasources. /* Let's say you want to move a database to an ...


7

I found a solution. I did not properly split the values in the stored procedure with a UDF. To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using: =join(Parameters!<your param name>.Value,",") This is basically going to join multiple values into an array and pass it ...


7

You can't. When you upload data source and report definitions to the report server it does store their information in the dbo.Catalog table which you can see using Convert(Xml, Convert(Varbinary(Max), Content)) and as it's XML you can use XPath to pull out the fields you're interested in. The problems really begin once you modify anything on the SSRS web ...


7

About the separate installer for SSRS, here's a good reason: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/05/17/a-closer-look-at-power-bi-report-server/ In summary, SSRS, and PowerBI Report Server has similar features hence it might be beneficial from the developer's perspective to group them accordingly (just a hunch, I am not a Microsoft employee, ...


6

Use this syntax: ORDER BY name,CASE WHEN country='UK' THEN 0 ELSE 1 END,country


6

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead. So for your query it should look like: SELECT DISTINCT CompanyId, Company FROM User_Hierarchy_Mapping WHERE UserId = ? That way you should get a parameter named Parameter1. If you have more than one question mark your ...


5

You can, as stated by Joel, at the report level change te language field (in the properties) but to always get the language used by the user, you can fill in =User!Language So the date-format will change to the one matching the user locale.


5

I found the solution in case anyone else comes across this. The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran ...


5

Yes you can run SSRS with a Managed Service Account. I would double check that your MSA is installed on your SSRS server. You can do so with Powershell. Get-ADServiceAccount -identity msaname -properties hostcomputers |select hostcomputers If your SSRS server is not listed, you will need to install the MSA on the server. This needs to be run locally (on ...


5

If you're using a different version of Reporting Service Configuration Manager, then it wont pick it up. SSRS Config Manager 2016 doesn't see my local SSRS 2012 instance and vice-versa. Hope that helps.


5

If Madeira Data shows you how to do it, then I think it's safe to say: It's possible. It can be made to work, as evidenced by this paragraph from them: It’s especially good when your organization doesn’t have skilled programmers that can create a pretty-looking graphical user interface (GUI). Or when there are budget and / or time restrictions. Deciding ...


4

To give a user rights to work with SSRS they need to have permissions at the root folder (which is what it sounds like you have done), but you also have to go into the "site settings" link into the "security" tab and add the user as (at minimum) a system user. It is easy to do one, but not the other. Both are required to allow full access to the system.


4

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started. CREATE EVENT SESSION query_check ON SERVER ADD EVENT sqlserver.sql_statement_starting( ACTION (sqlserver.database_id, sqlserver.sql_text)), ADD ...


4

If you have your servers running in a virtual environment, you can perform a snapshot on a clone and then apply the in-place upgrade and test the instance to verify the upgrade was successful. If it works, you can apply the snapshot and make the clone the production server. If it goes poorly, you can delete the snapshot and go back to the pre-upgrade image ...


Only top voted, non community-wiki answers of a minimum length are eligible