Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hey guys,

Need some help with my reports. I only know  a little with SQL since I'm currently in the networking field.
I have this problem with SSRS - Report Builder where I have a column name PublicIP which have

sample values :
10.10.1.232 (saaskhsk2121)
122.233.55.42
32.55.10.12 (sasa)

Now, i need to remove the characters with parenthesis () and retain only the IP address

I tried below command and it perfectly removed the parenthesis and retain the IP address
=LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1)

sample result :
10.10.1.232


Now the problem lies in my iif statement since there are also values which doesn't have any parenthesis () and doesn't need to be trimmed down.
I tried this expression:

=Iif((Fields!PublicIP.Value Like "*(*") ,LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1),Fields!PublicIP.Value)

Result:
10.10.1.232
#Error
32.55.10.12

As you can see, the result shows #Error for those values with no parenthesis.
Need advise. Thanks

Lester
0
[Webinar] Database Backup and Recovery
LVL 11
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Relatively new to SSRS, but have been doing a bunch of work modifying reports and have had no problem deploying them on one "Project".  Client asked me to create a new report in another project, but when I attempt to run the existing reports in that project I get an error message:

An Error occurred during local report processing.
An Error has occurred during report processing.
The execution failed for the shared dataset 'AdoptionReferrals'.
Canno creat a connection to the datasource 'Data source for shared dataset'.
A network-related or instance-Specific error occurred while establishing a connection to SQL Server....
error: 40 - Could not open a connection to SQL Server.

When I created a new report the wizard created a new Shared Data Source allowing me to select the appropriate SQL Server and database, and I was able to get my report working in design/preview mode.  But when I attempted to deploy the report, I received the error:

Cannot deploy the report because the shared data source '/path/subpath/Shared Date Source Name' that the report references does not exist on the report server.

and it provides a filename 'Adoption Incentives.rdl'

So how do I deploy the Shared datasource name to the report server?
0
I am putting together a report for our production facility to show when lines are ready to run and whether another process is also ready to run. Attached is the sample report I am trying to duplicate using SSRS (rto.png). However, when I run the report in SSRS, the lines are not continuous across the report as shown in capture.png.

The report will run once weekly for the previous seven days from GETDATE()-8 6am through GETDATE()-1 6am.

The data points the report is based upon are both either 0 or 1. There is only a data entry point if the value changes. So the way I have set up the stored procedure is to find the first data point AFTER 6am on the first date and then set the value at exactly 6am to the opposite of that. I have done this for READY and RUNNING. When the data point changes, there is an entry in the database.

I want the lines to continue across and print until the value changes.

I have manipulated the final values for READY by adding .1 to the value so they can both appear on the same chart and not be right on top of each other. The READY will show on top in the chart because this process is supposed to be READY if the line is RUNNING. The RUNNING line should always be underneath the READY line. The processes are both either on or off. I'm also including a chart showing the data this is currently pulling.

How can I get the lines to continue at their current value until that value changes?
rto.PNG
Capture.PNG
data.png
0
I'm getting a connection error when trying to render a report using SSRS 2016 Report Manager.  Here is my setup:

For testing purposes, I installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  As part of the SQL Server installation I installed SSRS 2016, on the same laptop.  Then I configured SSRS 2016 and everything seems good to that point.

I created a new data source (DataSource1) and a test report in Visual Studio.  The report previews perfectly while in Visual Studio, so I deployed both the data source and the report to the Report Manager.  

I see the report and data source when I open Report Manager web page.  However, when I run the report I get the following error:

"...Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection).  A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible..."


When I test the data source in Visual Studio it works.  When I test the data source from the Report Manager web page I get the same error as above.  

It seems simple enough but I can't figure out what the issue is.  I am using Windows authentication and I'm the admin for the laptop as well as the SQL instance.

Any help would be appreciated.  Thanks.
0
Hello experts -

I am really stumped on this one. I feel like I am just totally missing something easy but it is escaping me.

We are in the process of upgrading our SQL servers from 2008 to 2012 and part of that is to re-point the SSRS data sources on report manager to the new servers. Everything works great - I am just updating the connection strings on the new server with the IP address of the database server. However, when I use the attached code to verify the connections have been changed, it pulls the old connection string. And what is very odd is that all the connection strings should be completely blank with the exception of the few that I have entered manually for my tests.

The attached document has everything I think you will need to help me figure this out. I have attached the screen shot of report manager first along with the new IP address that the connection string should have. I have also included screen shots of SSMS with the server and database visible and the results that show the incorrect connection string. Lastly I included the actual code I found online.

I initially thought maybe I had something cached but I got one of my co-workers to run it on her machine and the results were the same. My next thought is maybe the default schema needs to be changed? But I have had no luck with that either.

Any thoughts will be greatly appreciated.

Thank you.
Connection-String-SQL-Code.docx
0
I have a tablix.  The last row of the tablix contains a subreport.  I want to put a page break before this subreport prints.  How can I do this when its part of a tablix?
0
Dear Experts,

A query have two tables with inner join to reflect this desired result;
group.jpgWhere the query is;
select si_Department.dept_desc, SUM(si_sales_contract_detail.scd_qtyremain * si_sales_contract_detail.scd_rate * si_sales_contract_detail.scd_fcrate) AS Jan18
FROM            si_sales_contract_detail INNER JOIN
                         si_Department ON si_sales_contract_detail.dept_code_d = si_Department.dept_Code
WHERE        (YEAR(si_sales_contract_detail.scd_shp_date) = '2018') AND (MONTH(si_sales_contract_detail.scd_shp_date) = '1')
GROUP BY si_Department.dept_desc

Open in new window

Now, we want to use above query in SSRS report for this result;
group2.jpgWhere all of the month’s columns should be group by departments till Dec18, Please advise for required query.
0
We're using SSRS 2016 & SQL SERVER 2016 in our environment and don't want to use IIS.
We've a requirement to re-direct report manager URL to Target URL (Similar to URL rewrite function of IIS). When I access the report server URL - http://ssrs01/reports/redirect?<redirectnewurl>
In a nutshell I'm trying to achieve when someone tries to access above URL they will be automatically redirected to the target url <redirectnewurl>
I did research a lot and found this link useful which redirects from http: to https:
https://www.experts-exchange.com/questions/26319133/SSRS-2008-R2-How-to-redirect-URL-from-http-to-https-when-IIS-not-installed.html
But we don't have any need to use SSL as it is only internal environment and looking for optimal solution
Thanks
0
I have a web application that includes SSRS.  I have created a sql username for the reporting services.  Reports are delivered from a web page.  When a user selects a report he/she is prompted for a username and password.  I need instruction on how to provide the sql username/password so that the credentials are not prompted for.

Any help appreciated.
0
We are starting upgrade project to upgrade from SQL Server 2008 to SQL Server 2016

What is the best/ fastest way to upgrade all SSIS packages and SSRS reports ?

thanks
1
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi, I have a report setup on Report manager (SQL SERVER 2005)
The data source I have used many many times.
The report runs fine when I run it myself on the report manager.
I setup a subscription so the report would run and email to myself.
But I'm getting this error "Failure sending mail: Logon failed."
And I can't understand why since other reports linked to the same data source will send the reports to the same email.

Any advice would be much appreciated
Thank you
0
Trying to create an SSRS report that uses ArcGIS web service to convert coordinates to feet. No matter what I do I only get the method envelope and not the return data which is an array of geometry (x,y).
Attached is the wsdl file.
The report and the dataset have the parameters mentioned in the dataset.
This is my datasource:
http://myserver:myport#/arcgis/rest/services/Utilities/Geometry/GeometryServer
This is my dataset:
<Query>
<definitions xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns="http://schemas.xmlsoap.org/wsdl/"
targetNamespace="http://www.esri.com/schemas/ArcGIS/10.3">
<Method Name="Project" Namespace="http://www.esri.com/schemas/ArcGIS/10.3/">
           <Parameters>
                <Parameter Name="inSR" Type="String"></Parameter>
      <Parameter Name="outSR" Type="String"></Parameter>
      <Parameter Name="geometries" Type="Array"></Parameter>
      <Parameter Name="transformation" Type="String"></Parameter>
      <Parameter Name="transformforward" Type="String"></Parameter>
      </Parameters>
   </Method>
<SoapAction>=http://myserver:myport#/arcgis/rest/services/Utilities/Geometry/GeometryServer/project</SoapAction>
<ElementPath IgnoreNamespaces="True">ProjectResponse{}/Result{}/diffgram{}/Table{}/geometries{x,y}</ElementPath>
</definitions>
</Query>


I get the titlecell, breadcrumbs and apiref instead of getting the geometries x and y values.
GeometryServerwsdl.txt
0
I have a 2016 reporting services running from an independent SQL Server.  I have a .net web application that calls some reports via a hosting page.  Note it was built in an older version  but I have had this exact code run fine in 6 of my 2016 customers....i have 2 new customers that the reports fail.

There is no IIS involved..this is a native mode configuration running on port 80.

Here is stack trace
Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response. 
  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

 Exception Details: System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.

Source Error: 


 An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 



[InvalidOperationException: Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.]
   Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods() +192
   

Open in new window

0
Hello:

Everytime we try to print an SSRS form we receive the below error:


Server Error in '/Reports' Application.

An item with the same key has already been added.
  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

 Exception Details: Microsoft.Reporting.WebForms.ReportServerException: An item with the same key has already been added.

Source Error:


 An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:



[ReportServerException: An item with the same key has already been added.]

[ReportServerException: An error occurred during rendering of the report.]

[ReportServerException: An error occurred during rendering of the report. (rrRenderingError)]
   Microsoft.Reporting.WebForms.SoapReportExecutionService.ServerUrlRequest(AbortState abortState, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +1002
   Microsoft.Reporting.WebForms.SoapReportExecutionService.Render(AbortState abortState, String reportPath, String executionId, String historyId, String format, XmlNodeList deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension)
0
Hello experts -

I don't know if what I want to do can be done, so I figured this is the place to find out.

Is it possible to remove the blank fields when creating column groups in SSRS? Please see the attached Excel file for an example. What I want is for all the data to be at the top of the column headers instead of each group starting when the previous group ends. For example, the data in cells C43 to C131 would actually start in cell C3 instead of C43.

Thank you.

-- Lisa
Dwell-Report.xls
0
Experts,
Until know we developed SSRS reports in VS 2013, saved the Projects to a network fileshare and deployed to SQL Server.

I have upgraded my team to VS 2017 and want to deploy VSTS/GIT for version control etc. I am figuring it out (sort of). My local Git REPO is on local C drive, and we all push to the same Online repo. My question relates to publishing the changes to SQL server. How (and where?) would we do this from in this model?
0
Hi all,
Has anyone been able to get SSRS Email to work with office365?
I have just found, after eight hours, an article saying that only SQL 2016 and up supports office365.
0
In an SSRS report, when I attempt to make a selection for a parameter that allows multiple values, I am not able to click and drag the slide bar to scroll. When I click it, the prompt selection box disappears. I can scroll using the roll button on my mouse. Is this a resolution for this? I am told that this does not occur using Firefox.
0
-- Need help with this report layout


Title Job# appears -- only once

Piece List -- May have Multiple Pieces

Labor -- Needs to sub group by Piece #

Material --  Needs to sub group by Piece #

Totals for all pieces.


Example;

Job # 123686

Piece # 01 A-Widgets
  Labor
  1 drawings 10 hours
  2 construction 5 hours

  Material
  1 Something1 10
  2 Something2  5

Piece # 02 B- Widgets
  Labor
  1 drawings 10 hours
  2 construction 5 hours

Piece # 03 C- Widgets
  Material
  1 Something1 10
  2 Something2  5


Summary
Labor Total      60
Material Total   30

Job Total        90
0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have an SSRS report that displays the TextBox contain muliple fields and it's content correctly in SSRS but when I try to export it to Excel the data is not show .

SSRS formatExeclLEDES-1998B.rdl
0
Background:
Sometimes when printing postal addresses to mailing labels, there may be a limited number of mailing addresses that do not consume an entire sheet of Avery labels. If an Avery sheet starts out with 30 blank labels and there are 20 addresses to print, this would leave 10 unused labels on the sheet. I was using a report that I created in SSRS 2008 to print the labels.

At a later time, when I needed to print more mailing addresses, I would have my SQL Server query generate 20 empty records, plus UNION in the actual mailing addresses, and this data would be passed to the SSRS report. This caused SSRS 2008 to skip over the 20 labels that were already used on the sheet, and begin printing on label 21. This allowed me to finish using the remaining labels on the sheet. The addresses would be properly printed on the remaining unused labels.

There was probably a better way to manage this, but at the time, it was the only idea that worked.

Current Day:
Several months ago, our environment was upgraded to SQL Server 2012 and uses SSRS 2012. The 2008 SSRS report was successfully deployed to SSRS 2012, but it no longer works as it did in 2008. If I were to repeat the scenario described above, the query is still generating 20 blank rows, but what SSRS 2012 appears to do is execute a carriage return / linefeed 20 times, and then starts printing, instead of skipping pass 20 "used labels" in order to start printing on label 21.

I have been researching to …
0
I am looking for a good third party tool that will help manage SSRS report subscriptions. We want to expose this to our business users so that they can manage their subscriptions as well as client subscriptions. Is there anything out there right now that can help with this particular requirement?
0
I have problem in txt file output. I have record "His short film features 4 readings of a prose poem from Leonard Cohen’s novel Beautiful Losers". when output comes in txt file like"His short film features 4 readings".
It will shows half records.But in PDF format i got well result..I can not the drag the width of SSRS reports bez it will show unformatted manner.
1img.PNG
2img.PNG
0
We have SCCM current branch 1706, with a SQL 2012 server on the back end running SSRS. I would like to allow our Service Desk access to create their own reports in one specific folder only - protecting all the other folders containing the standard SCCM reports.

Is there a way to define a security scope that's SSRS folder aware? I don't think I can do this from the SSRS side. I believe SCCM sets all permissions via policy on those SSRS folders every 15 minutes.
0
I am having trouble with an installation of reporting server where I have chosen to have the credentials stored securely in the report.  The user I have entered is a domain admin account, but when I test the connection it takes about 30 secs to return the Green connection created successfully.  This is causing all reports to run incredible slow.  When I change  to windows integrated or run the report via SSDT then all reports run in about 1 sec.  Sql has about 75 gb of ram on a server with 100 gb of total memory.  What could be causing this issue?  Is this account messed up?  Should I try another domain account? Any way to test?
0

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.