Forum Widgets
Latest Discussions
SSRS - Slow SQL Connection Opening with ActiveDirectory User
Hi Everybody TL;DR: We are facing a problem with very slow SqlConnections (~20sec per connection) from a specific ReportServer to a Microsoft SQL Server and an Analysis Service Instance. Details see below. The current setup: SQL, SSAS and SSRS instances all installed on the same (Windows) server. There are two datasources configured on our SSRS (one connecting to SQL and one connecting to SSAS). The login-credentials for both datasources are configured with the same AD-user. Our problem: The SSRS-Reports are slow to load (it takes up to several minutes to render the report). We have viewed the log file and could see, that the sqlConnection opening takes up to 20 seconds. And that for each connection! On other ReportServers we have installed, it takes only a few milliseconds. It's not only the first call after some standby, but also every subsequent call. We have also made some performance tests for the specific SQL and MDX queries. The queries themselves are fast. Also the connection to the DB via OLEDB-driver (with the same AD-User) is instantaneous. What we have already tried: - During our analysis, we found the SPNs weren't registered. This has since been fixed: still the same poor performance. - We changed the credentials of the SQL-datasource on the ReportServer to a SQL-user: the test connection was now instantaneous. - But since we need an AD-user for the SSAS-datasource, we tried a totally new AD-user and used its credentials for the datasources: still the same poor perfomance. The next step that comes to mind, would be to completely remove and re-install SSRS, but we'd like to avoid this, if it turns out to not improve the situation at all. Are there any other steps that come to mind, we should/could try before a re-install? Thanks in advance for your help, Toby
SSRS SERVER DOESNT CONNECT TO ORACLE DATABASE
hello guys i did migration of the whole machine (ssis ssas ssrs, databases ..) to another machine everything worked fine for me except the ssrs server wouldnt connect to oracle database. the ssrs report works fine in visual studio and retrieve data from oracle correctly but when i deploy the dashboaed in the server doesnt work. and also i figured out that the server wouldnt connect to any other provider (oracle, odbc and oledb). do you have any idea guys ?
Navigate to different report from one SSRS Report
Sent by you: I have a Invoice SSRS report, for that I have format(SSRS Report) A, B and C, I want when user looking at format A then user should be able to switch to other format, here format means other report. although it isworking fine in visual studio but when i added that report in CRM, it show this error: Details are not available because the subreport is missing. For help adding the subreport to Microsoft Dynamics 365, contact your Microsoft Dynamics 365 administrator.
Duplicating report without field references
Hi all, I'm fairly new to SSRS and this might be pretty simple but I'd greatly appreciate the community help. I'm working with a report which has a couple of Tablix referencing a number of different dataset fields in cells, column and row groups and parameters. I have to replicate this report but with a new data source and new data sets but I wouldn't want to build it from scratch. When I start replacing the fields in the Tablix and grouping I'm always hitting errors because at some point the former datasets fields are called from the report. So the thing is, is there a way to replicate/copy the Tablix structure without any referenced field and then start over with the configuration of the report with the new datasets? Or, can I somehow remove all the field references and keep the structure as it is? Any help would be highly appreciated. Thx!SSRS report sum on visible expression value on Table
Hi Experts, I am trying to sum of visible expression Total days diff = 136+15+13=164 Total days diff expression is=DateDiff("d",Fields!PRWFDate.Value,Fields!POWFDate.Value) Total days diff expression for last row is=SUM(IIf(DateDiff("d", Fields!PRWFDate.Value, Fields!POWFDate.Value) <= 0, DateDiff("d", Fields!POWFDate.Value, Fields!PRWFDate.Value), DateDiff("d", Fields!PRWFDate.Value, Fields!POWFDate.Value))) giving value 43596446. I am trying to sum Total days diff = 136+15+13=164 than divide number of PO Id which is 3 than Average = (164/3)=54.66 The value 17.67 = 53/3 Thanks, Faizcannot cast nvarchar to numeric error
I'm getting the following error no matter what I do. I've casted all the columns to nvarchar in the sql query and the input parameter is text in SSRS and nvarchar(500) in the stored procedure. If I run the query/stored procedure it works fine. but if I run it in SSMS I get this error no matter what I do? What I have tried: 1. Casted all the select columns to nvarchar 2. the ssrs report parameter is "text" and the stored procedure parameters are numeric but casted as nvarchar 3. Some of the data has null rows would that cause this error? SQL (obfuscated of course) ALTER PROCEDURE SSRS_GetSomething @ListOfIds as nvarchar(500) AS BEGIN SET NOCOUNT ON; ---Check the string for multiple premiseid's and parse the string to convert from premise to numeric DECLARE @result as nvarchar(500) select TRY_CAST(id as nvarchar) as 'ID1',TRY_CAST(id2 as nvarchar) as 'ID2',streetaddress as 'StreetAddress',TRY_CAST([PossibleYear] as nvarchar) as 'Year',TRY_CAST(test2 as nvarchar) as 'test2',TRY_CAST(year3 as nvarchar) as 'Year3',TRY_CAST(numeric8 as nvarchar) as 'NumericColumn',TRY_CAST(numeric9 as nvarchar) as 'numeric9',TRY_CAST(numeric10 as nvarchar) as 'Numeric10',TRY_CAST(numeric11 as nvarchar) as 'numeric11',TRY_CAST(numeric12 as nvarchar) as 'Numeric12' from table1 b order by b.numeric1 desc,b.numeric2 , b.numeric3; END GO Error: An error has occurred during report processing. (rsProcessingAborted) Cannot read the next data row for the dataset datasource1. (rsErrorReadingNextDataRow) Error converting data type nvarchar to numeric.
Slow in report
i have one ssrs report i shared execution log3 performance for that report. my users complain the report take time to display around 5 min The report very simple and straight forword we have Microsft crm server and ssrs server and sql server all in same network please advise please What the issue
Pass parameter by URL with Default values on booleans
I have Parameter Field (type boolean) and a default value =True. When I pass the parameter by url (= 0) it still shows it as True, how to prevent that behaviour. (Default value is needed) https://learn.microsoft.com/en-us/sql/reporting-services/pass-a-report-parameter-within-a-url?view=sql-server-ver16
SSRS - Pagination of two tables in a single page
Question : we are facing an issue to achieve the pagination of data from two tables when these both tables are placed under single page. The requirement is to get the 12 rows from each table and show in the respective table of a page in SSRS report. Current Result : Pagination of second table is not showing under the same page where the first table pagination rendered. Second page pagination is getting started only after the pagination for data of first table is completed. Overall the the pagination is happening sequential. But we need the pagination to happen parallel for both tables under the same page. Any help is much appreciated in advance. Expected : Pagination of first and second tables should be rendered in the same page across all the pages rendered as part of the pagination.Put SSRS parameters in a Tab Control
I need to create an SSRS report which uses numerous optional input parameters (dropdowns and tetboxes). I am working with Visual Studio Professional 2019 and MSSQL v 18. It does not seem to be possible to put parameters in a Tab control, so that I can choose the ones I want and run the report. Any workarounds I see seem to only emulate a tab control without having the functionality I require. Do I ned to upgrade to Report Builder or is there anything else i can do? Thankyou
