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

We recently setup a SQL Server Reporting 2016 on a Windows 2016 server.  We installed a 3rd party SSL cert and configured SSRS to use this cert.

When we open the reports site with IE or Edge everything works fine.  But if we try to open with Chrome or Firefox we get an error "ERR_SPDY_INADEQUATE_TRANSPORT_SECURITY" and the site won't load.

This sounds like SSRS is using an outdated cipher that is not secure so Chrome/Firefox refuses to use that cipher.

Since SSRS 2016 doesn't use IIS anymore, how do we configure what SSL protocols and Ciphers SSRS uses?

Thanks!
0
Enroll in August's Course of the Month
LVL 9
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

I need to create a tabbed report in SSRS that displays totals by Customer. See the attached report layout. Tab 1 would be Net Open Sales. This is the top left report in the attached example.

My question/Issue is how do I write the query to make this dataset easy to use in SSRS Report Builder?

For example my Customers are arranged in Alpha order however JC Penny and Kohl's are listed only once on the report however in the database I need to sum them because there are two ID's. One for in-store and one for ecommerce.

This is what I began with however it gives me the same total for every record for KOHLS. More importantly I'll need a sub-total minus walmart and a grand total. I'm not certain that I'm approaching this correctly.

SELECT  ( SELECT    SUM(price * total_qty)
          FROM      [KLL Open Orders - Header & Detail]
          WHERE     ( ( CUSTOMER IN ( 'KOH0010', 'KOH0015' )) )
                    AND ( (division = 'KLL') )
                    AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
                    AND ( ( ORD_STATUS = 'O' ) )
        ) AS KOHLS2017
FROM    [KLL Open Orders - Header & Detail];

Open in new window

Capture.JPG
0
Permission issue but i can't figure it out
0
I have a report that uses three levels of row grouping: Branch, Route, Type.  Each row group has a set of adjacent groups that show various metrics.  The customer wants the report to open with only the Branch group along with their Type groups visible.  When the Route is hidden on open the report looks ok.  However, when I toggle the Route group none of the subsequent Type groups have their labels.  The attached file shows what I mean.

How do I hide the Route  yet have the Type group text show up when toggled?  The correct data shows just not the column with the row names.
SSRS_Report.doc
0
when we upgrade the report to our new server the xml export stopped working.

I've been trying t troubleshoot but since i'm not to familiar with xslt transformations i'm having a hard time finding the problem.

I think the problem is with name spaces.
if i use this code i got from here i can get the entire xml to export
</xsl:template>
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>

but when i try to match a specific node it fails.

my original xml without the transformation comes out like this:
<?xml version="1.0" encoding="UTF-8"?>
<Report xmlns="Logos_x0020_Export_x0020_Report" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="Logos_x0020_Export_x0020_Report http://reportserver?%2FLogos%20Export%20Report&rs%3AFormat=XML&rc%3ASchema=True" Name="Logos Export Report">
   <Tablix1>
      <Textbox15>
         <Textbox19 />
      </Textbox15>
      <Group2_Collection>
         <Group2>
            <Group1_Collection>
               <Group1 Group1="PB2017-1886&amp;#xD;&amp;#xA;">
                  <NamePaidby1 NamePaidby1="INTERIOR ENVIRONMENTS&amp;#xD;&amp;#xA;48700 ADDRESS City ST 111111" PaymentType="Check Number: " CheckNumber="30000" TotalAmount="167.00">
                     <Details_Collection>
                        <Details Description="Initial Plan Review Fee" …
0
Hi ,

Please provide detailed procedure for In-Place Up-gradation from MS SQL Server 2012 to MS SQL Server 2014 include MSBI Components like SSIS Packages, Cubes/OLAP Databases and SSRS Reports.

Please consider this high priority.

Thanks,
Chandra
0
For example as per attachment, i would like a  function that looks at a single occurrence of females first on the management level field and Gender field and gives an additional column a label for example VP Black women, and at the same time look at the occurrence of both male and female and the management level and gender field and give a label/column field of for example VP Black. I would like to use this label or column in SSRS to show a field grouped by females only and a combination of males and females. Your assistance will be highly appreciated.

for example, the query i had in mind would go like this. Excuse i am a complete novice

select [Management level],[gender],[Corporate grade]
case when patindex(%Black%,[Management level]) AND patindex(%Female%,[Gender key]) the 'VP Black women' else 'VP Black'
0
I have sqlexpress with data reporting on a web application server.  How can I deploy reports that I have created on my local computer to the the server?
0
Hello,

How can I force language report server 2016 culture fr-fr. Report Server has been installed on server 2012 US, regional settings are french. But some reports display date in format En-US.
I try the following without result :
UPDATE dbo.Catalogue
SET Property = N'<Properties> <Language>fr-FRLanguage> </Properties>'
WHERE CAST(Property AS NVARCHAR(MAX)) = N'<Properties> <Language>En-US</Language> </Properties>'

How can I resolve?

Thanks

regards
0
I have a reportviewer control in my winforms application.  I've got it working with ProcessingMode.Local using an rdlc file.
I am unable to load a report using ProcessingMode.Remote.  The issue seems to be the ReportServerURL  and/or ReportPath parameters of the ServerReport.
my code:
        If Not isLocal Then
            'use Report Server Report                       
            ReportViewer1.ProcessingMode = ProcessingMode.Remote
            Dim serverReport As ServerReport
            serverReport = ReportViewer1.ServerReport
            Dim myCred As NetworkCredential = New NetworkCredential("username", "Password", "domain.com")
            Dim credentials As System.Net.ICredentials
            credentials = myCred ' System.Net.CredentialCache.DefaultCredentials
            'Get a reference to the report server credentials  
            Dim rsCredentials As ReportServerCredentials
            rsCredentials = serverReport.ReportServerCredentials
            'Set the credentials for the server report  
            rsCredentials.NetworkCredentials = myCred
            'Set the report server URL and report path  
            serverReport.ReportServerUrl = New Uri("http://reports.domain.com/Reports")
            serverReport.ReportPath = "/Dock Activity Reports/PiecesScheduled" 'GetXMLdata("Report")  
            'Create the location name report parameter  
            Dim reportParam As New ReportParameter("location", locationName)
            'Set the report 

Open in new window

0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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. It selects data from a view and the single parameter is used as the where clause. Without trying to explain things explicitly, the report is essentially a quote form. So, I pass in a reference number and it generates the quote. Basic stuff like company name in the report header, company address, then in the body, the quote items list. Assume the quote will always be a single page. As the same quote is being sent to more than one company, the quote header has the company name and address, then the quote items duplicated on each page in the report body. The footer contains the count of companies the qujote is being sent to so they know how many people are in the running for the quote. I've got everything working except the company name and address in the header doesn't change.

I have the exact same report in Crystal and that works because instead of apage header, I put what is essentially the page header into a group header. This allows me to change the name and address as the group changes. How do I do the same in SSRS?
0
Hello,

We have a system displaying some reports from SSRS, however since most of the users are using Chrome they cannot see the datepickers in the report viewer so they type the dates by hand, leading to format errors.

The MSSQL is version 2012 Web Edition SP3 x64.

Is there a way to fix the issue?

P.S. Upgrading the server is not an option due to licensing issues.
0
Hello,

After migrating a reporterver database from a server whose operating system and regional settings were in French to a server whose operating system is English and regional options in French, the date format is mm-dd-yy. I have changed default language of user account in french but date still in English format  Why? Do I need to reinstall reporting services in French? How can I resolve this problem?
Thanks

regards
0
I have a TFS   2010 and SP 2010, sql2008r2 report server in SharePoint integrated mode  Whenever I try to create a new project it fails and get the message :

TF218027 This operation is not supported on a report server that is configured to run in SharePoint integrated mode

See screenshot

I don't believe this is a permissions issue, As I have created many projects in the past using the same credentials.

I am aware that you can use Report Services Configuration Manager to change the DB to native mode, but I have many projects set up with the current DB in integrated mode and I dont want to cause issues with them.

We a planning to migrate to the latest versions of sharepoint and TFS but we cant right now.

I am just trying to create a new project the must be a way to do this.


Thankserror message
0
I have a SSRS report using Report Builder 3.0 - I have a Tablix in a Rectangle.  There are 10 columns in the tablix.  The last column goes onto another page; even though there is plenty of room for it on one page.  How can I get all these columns to stay on "one page wide"?

Page size is 8.5 x 11
Margins:  Left & Right .02", Top & Bottom .50"
0
Hello,

I was greeted this morning with reports of many SSRS subscriptions not running. When I checked, it's because the username of the owner has been changed (due to marriage). I have changed owners before but I can't get SSRS to even recognize the new username (when I look in the ReportServer.dbo.Users table for either name, only the old one comes back.

How do I get the owner to change to the new username so the subscriptions can be run again? I can't find anything definitive on Google about it. I did change a registry setting (LsaLookupCacheMaxSize) but I don't see where that's had any effect.

I could change to generic account but this user actively manages these subscriptions, so I need to assign it to her new username.

Thanks!
0
Hi

I want to express the conditions for a column in a SQL query in the Select part rather than the Where part of the SQL

So I am using

Select Case When [ID] = 234 Then [Quantity] End

Instead of

Select [Quanity] Where [ID] = 234

Is this a good way of doing it?
0
The first query needs an additional column that shows the blocking text...but I eliminate that from the where clause...so am thinking I need a union...or some other outer query.. please help.  I have put a 2nd query below that will return the blocking test.
I need to unify this logic into one output.

select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>2
---final touch for sending mail notification when blocking duration goes above 2 Minutes

Open in new window



2nd query that does get the blocking text I am after...but  I need it an aditonal column in the above query.

select qt.text as [blocking text]

FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

Open in new window

0
Several months back I loaded a bunch of images into a SQL Server table as image. I then created a SSRS report to display the images. Report worked. The other day I added more images but those images don't display but old ones still do. Red X. I loaded the images through Access as object with linked table to SQL Server. All images are jpg. Odd thing is, in Access if I double click on the new images, they pop up in image viewer so I know they are there. But the old ones don't display but yet they show up on SSRS report. For the old ones in Access I get the following:
"A problem occurred while Microsoft Access was communicating with the OLE sever or ActiveX Control."
I tried converting Image type to varbinary (max) but got the same results.
Any ideas?
Using SQL Server 2008 R2, Access 2010.
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Is there anything Telerik Reporting can do that SSRS (2005) cannot?

Specifics: We're looking to migrate our current Reporting infrastructure from in-house ASP NET + Crystal (VS Edition) to something else.

Ignoring the cost factor (we have a Telerik license), is there any pros or cons to going with Telerik over SSRS?
SSRS
Key elements we need are:

generate reports from a custom ASP.NET application
schedule reports
email reports on demand or on a schedule
pdf output, drilldown html
massage the dataset in C# before passing it to the rendering engine
Any experiences or sites that point out advantages or limitations (with either engine) would be greatly appreciated.
0
I have an SSRS 2012 report that has multiple columns, and has a group by employee name and week
One column is a calculated column on the aggregate by week. So I have this expression in the "week "group row.
=IIF(sum(Fields!Regular.Value)<=40,0,IIF(sum(Fields!Regular.Value)>40,(sum(Fields!Regular.Value)-40),0))

I do need to sum that column at the employee name group level. I've tried different things and don't get the expected results. In excel is very simple as I can simply sum that column....Ideally, I get this calculation in the SSRS report.
I've search for a couple of days and nothing works.
I've attached my report output.
Thank you for any help you can provide.
0
Hi

I want to create some reports based on the content of a Sharepoint list. We are running SP on premises. I would like to use SSRS but cannot get that working in Sharepoint. We are using SQL 2008 R2 and SSRS is configured. I also tried to use Excel using oData but we are using Excel 2010 so it's not available.

Any ideas on a) if I can use SSRS with a combination of SP2013 (on premises) and SQL 2008 R2 or b) if there is a way to get Excel 2010 to connect to a SP list or c) are there any other options open to me?

Thanks
Lee
0
Hi , When i'm downloading the excel file,i'm getting following attached error.How avoid this error

XML file output


<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error042240_01.xml</logFileName>

<summary>Errors were detected in file 'C:\Users\abc.com\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\PGARRIST\Report_Isys.xlsx'</summary>


-<additionalInfo>

<info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info>

</additionalInfo>


-<removedParts summary="Following is a list of removed parts:">

<removedPart>Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1, column 267478.</removedPart>

</removedParts>

</recoveryLog>
1.png
0
Hi Experts,

 I am trying to create an Expression:
=IIf(IsNothing(Fields!Field1.Value/Fields!Field2.Value),"#ERROR",Fields!Field1.Value/Fields!Field2.Value)

Open in new window


I am getting:
NaN in the field.

In the SP:
Select
NULL as [Field1]
NULL as [Field2]

Please help and thanks
0
I have a SSRS List report that is only displaying the first record of each Group (24). My SQL query shows 501 records.
I am using a Subreport to grab the data of the Group. Any Suggestions? This is a super rush..
subMOR.rdl
MOR_Test.rdl
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.