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

Hi there,
Today I tried to setup a usual subscription for one of my reports, to email out. A fairly regular process that never seemed to cause issue previously.
However, I keep getting a "Login Failed" message.
Unknown user.

I don't know how this could be.
I have setup the subscription directly on the server
the only place I can enter in credentials are under the data source settings.
I have added the username and password that is currently being used successfully for other datasources.
I tried using windows account and it failed, I tried setting up with my own account and it failed, I'm just not sure what I'm doing wrong.

Any advice would be greatly appreciated.
Thanks,
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

I need to give access to reports to users that are NOT in Active Directory. Is this possible?

SSRS 2016 Standard Edition on Windows 2012
0
Hi Experts,

I want to display the "number" with "Unit"  for ex. 12 oz. I am using the expression "=Fields!AdjustedPan.Value & Fields!Unit.Value" on that textbox. It displays 12oz. I need and space in between. So I tried ""=Fields!AdjustedPan.Value & "  " & Fields!Unit.Value". But It does not work. What can I do to give a space between the number and the unit. Also need a "." at the end( Like 12 oz.) .

Thanks in advance.
0
experts,

I have many sharepoint sites (3000+) that I'm  thinking about making use of the tasks lists. Each administrator has several hundred sites. Each admin could have a number of tasks spread across each of their sites. I'd like to give them a way to see all tasks that they have assigned to them across all of their sites instead of them having to visit every site to see what's assigned to them. Has anyone done anything like this before? What's the best approach to take? I'm toying with utilizing a SSRS report,  maybe a webpart, or maybe a custom application that queries the sites. I'm not sure though.

Any guidance would be much appreciated.

Thanks in advance,
0
I have a report that is ran once a month that gets exported into Excel, then two additional formulas are performed.  I get the Max and Median on (3) different columns on each row. I have no idea how to perform these in SQL and would like some help.

Using SQL 2012 - SSRS 2012

The 3 columns I want to show the Max and Median are labeled [3month avg], [6mth avg] and [12mth avg]
I want these to calculate for each row, NOT a grand total.  So when I run the report, I want 2 additional columns to appear with the Max and Median to show for each row.

I have copied the current SQL query into a word document and attached it.
SELECT     p21_view_inv_loc.location_id, p21_view_inv_loc.item_id, p21_view_inv_mast.extended_desc, p21_view_inv_mast.net_weight, 
                      p21_view_inv_loc.moving_average_cost, p21_view_inv_loc.stockable, p21_view_inv_loc.inv_min, p21_view_inv_loc.inv_max, p21_view_inv_loc.product_group_id, 
                      p21_view_inv_loc.purchase_class, COALESCE (derivedtbl_1.[3 month sales], 0) AS [3mnth sales], COALESCE (derivedtbl_1.[6month sales], 0) AS [6mnth sales], 
                      COALESCE (derivedtbl_1.[12 month sales], 0) AS [12mnth sales], ABS(COALESCE (derivedtbl_2.[3 month prod ord], 0)) AS [3mnth prod ord], 
                      ABS(COALESCE (derivedtbl_2.[6 month prod ord], 0)) AS [6mnth prod ord], ABS(COALESCE (derivedtbl_2.[12 month prod ord], 0)) AS [12mnth prod ord], 
                      COALESCE (derivedtbl_3.[3 

Open in new window

0
Hi Experts,

I am trying to pass a multi value parameter.


User has a downdown:
Severity dropdown
I am creating a manual and it looks like currently it is not correct.
see attachment.

I am also dealing with a drillin report which at this point is not passing correctly.  I have tried many different ways using what is mentioned in the Manual.

Ex:
On Main report I first do the Dataset like:
Main Dataset Dataset Properties
I click on fx to enter expression:
Express within the dataset
On the drillin from the main report under SEVERITY:
Click fx to go into the expression for SEVERITY
Within the expression:
Expression for the drillin from main report
On Drillin Report DataSet:
Drillin Dataset Expression
And I have followed all on the manual for the all SP's involved.

Please help and thanks.
SeverityCheckBoxParameter.docx
0
I have built a C# MVC web application and I have a requirement to add the ability to schedule reports to be ran from this application. I have SSRS 2014 and I have been trying to use the SOAP API available to perform this task.

As you see in my code below I threw it in a Controller for testing. I implement the ReportingService2010SoapClient and use the CreateSubscription Method. But I recieve the following error:
System.ServiceModel.Security.MessageSecurityException: 'The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'NTLM'.'

I figure it must be because I am passing in an empty value for the TrustedUserHeader, but I am not sure what it is supposed to be. Any suggestions? I have no found very good documentation for this.

public ActionResult SSRSReports()
        {
            ReportingService2010SoapClient so = new ReportingService2010SoapClient();

            string report = "http://localhost" +
            "/ReportServer/SiteReport.rdl";

            string desc = "Send to Document Library";
            string eventType = "TimedSubscription";
            string scheduleXml =
               @"<ScheduleDefinition>" +
                "   <StartDateTime>2003-02-24T09:00:00-08:00" +
                "   </StartDateTime>" +
                "   <WeeklyRecurrence>" +
                "      <WeeksInterval>1</WeeksInterval>" +
                "      <DaysOfWeek>" +
                " 

Open in new window

0
I am trying to convert a stored proc to an SSRS report for a lot of reasons. We are tying to get away from writing stored procs for reports and move more towards a self service BI type thing.

One of the things I am struggling with is how to reproduce this type of thing in SSRS:

* Stored proc has variables @startDate, @endDate
CREATE PROC usp_report1(@startDate, @endDate)
AS ....
SELECT @startDate = fn_convert_date(@startDate)
SELECT @endDate = fn_convert_date(@startDate)

The function fn_convert_date takes the date passed in and converts it to the local time zone of the state running the report.

Is there a way to reproduction this behavior in SSRS?
0
I have been working wtih SSRS and I like how you can create reports in the portal and even have subscriptions set up for reports to be ran automatically and emailed. Now I have a C# MVC web application I am building and I can display and run those SSRS reports. But I was trying to figure out how through my web application I could tie in maintaining and creating subscriptions to these SSRS reports so my users do not have to login and run the reports manually. I have searched the web but I have not found very good references. Any suggestions?
0
Is there a way to put comments into SSRS reports?  It would be helpful when returning to a query later to remind myself or inform others of the design of the query.
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

I'm trying to have all dollar signs line up left aligned  (in my $amt text box)  while the values are right aligned without using 2 fields.

Here is an example:

$             6,123.00
$                     1.00
$                   12.22
$                       .35


Is there a way to do this ?
0
I am trying to create a new data source in SSRS to connect to a csv file.  Below is my connection string and i am getting a "Could not find installable ISAM" error message.  I am using  OLE DB as the Data source type.

Connection String
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\SERVER\FOLDER\SUBFOLDER;Extended Properties=”text;HDR=No;FMT=Delimited”
0
Guys: I have a simple sql table

Acctid | PrdSrvc1|
================
1001   | $200
1002   | $300

In SSRS how can i use the subtract function to get the value of -100  - Thx

P.S. I am using SQL Sever 2016
0
I am upgrading a series of applications from SQL 2000 and VB.net 2003 to SQL2014 and Visual Studio VB 2013.

The code all upgrade automatically, but I have to upgrade the reports.  I was going along fine until I got to subreports.

When I try to pull data for the subreport I get the error:  Data retrieval failed for the subreport Subreport3 located at "path to report"  Please check the log files for more information.

I have done some research and found that I need to add subreport processing.  I have found and deployed some code like the following.

Public Sub SubreportProcessingEventHandler(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

e.DataSources.Add(New ReportDataSource("AdventureWorksDataSet_Contact", Me.AdventureWorksDataSet.Contact))

End Sub

Private Sub <FormName>_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'
' Other code is here.
'
Dim instance As LocalReport = Me.ReportViewer1.LocalReport

Me.ContactTableAdapter.Fill(Me.AdventureWorksDataSet.Contact)

AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf Me.SubreportProcessingEventHandler
Me.ReportViewer1.RefreshReport()
'
' Other code is here.
'
End Sub

But when I change the e.DataSources.Add statement to reflect the values from my programs,  which results in    e.DataSources.Add(New ReportDataSource("DSPrtCommSheets", Me.DSPrtCommSheets14.usp_PrtWrittenComms4SalesRep))

 I get a compile error of "Error      77      …
0
Is there a way to display SSRS pages immediately while long report is still executing ?

I do not want the users waiting until the entire report is completed before they can start seeing the report.
0
I have a main page with all the data in a tabular format.

I report dynamically generates data in number of tabs based on parameters with the main page as first tab.

Now when i export to the report and click on any one of the item (row data) in the main page then it should go that particular tab.

I tried DocumentMap - but it creates a separate tab with the links which i cannot use as the client wants to display the data in tabular format.

Also I tried bookmark - but it work only with the first row of the table in main page. On click of other tabs it displays an error message says "Reference is not valid".

Can you please help regarding this.

Thanks in advance.
0
There is probably a simple solution to this, but I've searched around the various properties within the Matrix and Chart controls and cannot work it out!
Anyway, using BIDS, I have a shared dataset which returns a simple value of the Number of Starters within a company Grouped by Gender. When I add the shared dataset and test using Query Designer, I get the expected results: "F 27, M 36" (columns are Gender, Starters and the field datatype on the SQL side is INT).
However, when I add a Matrix or a chart to the SSRS report, I always get M 1, F 1. It seems that the report is simply returning a count of the genders rather than displaying the starters.  

The dataset itself is from a stored procedure in SQL; there are several parameters passed to the SP, but in this case I am passing NULL to most of the parameters apart from the Dates (First Date and Last Date in order to get the number of starters within a date range).

I think I am missing something simple, but have no idea what it could be!

Output from Query DesignerQuery designer output

Output in SSRS ReportChart and Matrix output

Bar Chart SetupChart definition
0
Hello,

I need to create an ssrs report using 2 different tables from 2 databases on the same server. I"m looking for examples on how to to do this. I tried by creating a dataset from a view that I setup that works in SSMS but I keep getting errors when trying to use it in SSRS. Any examples would be helpful.

Thanks.
0
Hi have 2 charts on my ssrs report and below it is a tabular report. When I look at the report it looks fine but when I export the report to SSRS, 1 chart is exported normally and other chart is shrieked. How to show both charts to same size when they are exported to excel. This is only with excel export other exports like PDF are fine.
0
On Demand Webinar - Networking for the Cloud Era
LVL 9
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

SSRS from Microsoft SQL Server 2012 - 11.0.5058.0 (SP1) when I export a report to CSV with no result, I'm getting header and commas below the headings.   I am expecting a header but I'm not expecting the commas.

 SSRS report Output
 Reference,Portfolio
, ,

 I'm looking for output without commas
 SSRS report Output
Reference,Portfolio

 ------------------------------------------------------------------------------------
 My rsreportserver.config setting is as below
             <Render>
                   <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
                         <Configuration>
                               <DeviceInfo>
                                     <Encoding>ASCII</Encoding>
                               </DeviceInfo>
                         </Configuration>
                   </Extension>
             </Render>

With same SSRS rsreportserver.config setting from Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0  I'm getting correct result.
0
HI,

I have a parameter, that has multiple values. I tried to allow it to have null or blank value, but it says I can't do that with multiple values selected. I want to make the parameter optional to the users, because they may not always want to select it.

In the attached file, there are the parameters the user has to select. Products, and EventDetails need to be optional parameters

C--Users-039824-Pictures-Capture_Par.PNG
0
Hi Expert,

I want to inventory all of my SQL Server Instances along with Server Name, DB_Name, DB_Owner, Create_Date, Version, Edition, Recovery_model as much i can retrieve.

Please Help!
0
Hi Expert,

In the following report i want to see total section based count like this Total EBS Core count xyz
Expression i used =sum(cint(trim(Fields!processorecount.value)))


is_ebs      Hostname appname      Processorcount memory      instancename

OPEN      xyz                         ddd            4                    15.42 GB      NULL
OPEN      xxx                         ccc            48                    125.64 GB      NULL

=Total EBS Core count xyz



is_ebs      Hostname appname      Processorcount memory      instancename

EBS                        yzx       eee            10                          251.57 GB      DEV


=Total Core count xyz


is_ebs      Hostname appname      Processorcount memory      instancename

NON-EBS crs                   rrr            10                          251.86 GB      xyx

=Total OPEN Core count xyz

  Please help me!
0
I have a schedule report. Each row represents a time in 30 minute increments. Each column is a person. Each cell has information on what the person is scheduled to do at that time. It is highlighted in a color to indicate the type of task. What I need to add is if the task is longer than 30 minutes, I need to highlight the cells below to show the duration of the task. So if Joe has OT (Blue) from 1pm to 2pm, The information goes in the 1pm cell and is highlighted BLUE. What I need to do is also highlight the 1:30 pm cell. I may be able to come up with a solution by changing the query and creating more records for the report that will contain information so I can highlight the cell without repeating the text information. But what I would like to do is make the textbox with the highlight span multiple rows based on the duration. In essence, what I'm trying to do is duplicate the look of an Outlook Calendar where the height of appointments is based on how long the appointment lasts.
Thanks
Example of Result needed
0
Hi Experts,

I have a report that until recently used one SQL database to populate.  Business requirements are forcing me to split the database up.  When I deploy the report it fails and I get the following error.  Both databases are housed on the same server.  
I checked the documentation and found that "Remote data sources are not supported. Reports that are hosted in a SQL Server Express report server must retrieve SQL Server relational data from a local SQL Server Express Database Engine instance."  Which both are but still getting the error.  Report runs fine until I deploy it.  Any suggestions?  

An error has occurred during report processing.
Cannot create a connection to data source 'POSReportSupport'.
The feature: "The edition of Reporting Services that you are using requires that you use local SQL Server relational databases for report data sources and the report server database." is not supported in this edition of Reporting Services.
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.