[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Is there a way to strip HTML (Rich Text actually) from a SQL Server column in a query?

I've inherited a SQL Server database with an Access frontend.  Users have been allowed to edit a memo (long text) fields using Rich Text format, which leaves Rich Text (Div and font) tags embedded in the fields.  In Access, I can strip these tags using the PlainText function, but I'm working on editing a report in SSRS, and need to strip these tags using a SQL Server function.
0
Hire Technology Freelancers with Gigs
LVL 11
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Hello Experts,
Today was my first of a 3 day training for Microsoft SQL Server Reporting. Every hour, the Report Builder would automatically disconnect from the Server and everyone in the class needed to click, 'disconnect' and then 'connect' to re-establish the connection. Do you know what is causing the program to disconnect? And where we can we adjust the settings to keep the program from disconnecting every hour?
Thank you very much for your help with this.
Respectfully, Chop
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
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
Hi there,
I would like to create a query which looks at sales during a particularly large period of time, and the results are then passed into a temp table to view these results.

For example.
Over the past 60 days I want to check our sales where the actual customers phone number would have appeared in this group (introduce a sub query which will then hold all of those phone numbers for those same 60 days) - what is important here is that the phone number that appears in, for example, a sale on day 1 should be checked against the sub query for the same day 1 (not any other day). Does that make sense?



Select salesdate, sum(salesrevenue) from SalesTable where salesdate between '2017-01-01 and '2017-01-02'
And phonenumber in (select phonenumber from customertraffic table where customer traffic hitdate between '2017-01-01' and '2017-01-02')
Group by salesdate

However I want to be able to have this query run over a longer period of time not just one day, but it needs to be day to day,
so I'm thinking if I could loop the query to run
day+1 type thing, I'm just not sure how to go about it.
and then insert the results into a temp table so I can see all the dates and their results.

declare @datefrom  datetime, @dateto datetime
SEt @datefrom = '2017-01-01'
SET @dateto = @datefrom+1

--how Stop the loop when 60 days of data is returned. ?


Insert into #TempTable (salesdate, salesrevenue)
Select salesdate, sum(salesrevenue) from SalesTable where …
0
I have an SSRS combo box that will pass 1 of 3 values to my stored proc.

'ALL'
'NATIVE'
'LINKED'


I need the report to do the following based on one of these 3 options being picked.

If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..

If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL

Please help me build the where clause to handle this.

Here is what I have ..but its not working.


(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
0
I have an SSRS report....it was working fine it seemed in that I was handling blanks...but then I reliazed I had nulls in my data...and since then I have not been able to get it to work.

Here is how im handling nulls.  I will show you all the where clauses..im essentially handling them all the same.  

I will also provide the dataset for two of them so you can see how im manipulating the dropdown.

In the where clauses below..it was working with blanks....
In the native linked for example..if the  user selects all...I changed it so is null should be part of all....not sure if thats correct ..but I need is null to show in those params.

In the others I added the coalesce...that also does not seem to be working.  In my report I change the value in the dropdown for a blank to the word 'None'...and that is why you see my use of none here.  I do not pass the choice for null in my dropdowns...but the situation needs to be handled so isnull values would appear just as if they were blanks.

CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or ((@ActiveClient = 2) and @ActiveClient = 0 or @ActiveClient= 1 or c.lActive is null))
And 

Open in new window

0
Hi,

Within a parent Rectangle I have a Textbox alongside a Table but the Table only appears below Textbox rather than alongside of it. I've tried various combinations of KeepTogether True/False with both the TextBox and the Tablix but to no avail. Screenshots attached to visualise the issue.

Report Layout
What i'd like is the List to appear alongside the "Sequence of Operations"

Report Issue
0
I am trying to write this the most effectively.  I have about 42 values that are past from a multiselect combo in an SSRS report.
These values will represent the column name.

If the user selects all or select discrete choices it should return those values.   I will be making an option called none in the dropdown as well so if that I can give the user a way to ignore and we just ignore this set of parameters.

This is just a snippet of the proc.

Here I am stuffing the values of the dropdown into a temp table.

I have created one example of the where statement..but I think this needs to some how be dynamic.

The column is a bit...so after verifying the column exists I also check that its true as well.

Select * into #tempTable
from (SELECT splitdata FROM dbo.fnSplitString('@Services',',') ) s
declare @servicesCOUNT int
select @servicesCOUNT = count(*) from #tempTable
select * from #tempTable
drop table #tempTable







select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services

Open in new window

0
I have a dataset in SSRS that will return a  list of comma delimited strings.

based on the values returned...I need this list of strings turned into a temp table...so I can evaluate the temp table in the stored proc in an In clause.


Please help
0
Important Lessons on Recovering from Petya
LVL 11
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Is there a design pack or CSS template I can purchase that will help me transform my SSRS reports from the plain black and white to something much more attractive?

If there is not a for purchase theme / style template, can anybody suggest a good youTube video or URL that show how to make these reports a little more attractive?

Thanks experts!
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
Hi,

I have some data that includes Broker names, product they bought, and corresponding values and quantity of the products. I need to show top 4 brokers (each product wise) based on values of the product as well as "other than top 4". If for a particular product, there is only two brokers that have some value and rest of the brokers have just quantities and value equal to 0, then i want their sum to be removed from "top 4" field and added into the "other than top 4" column. I know it sounds really complicated. I am preparing this report on sql and SSRS. Below is the sample of desired report:

Sample Data Table:

product      Brokername      Values      quantity
Casualty        ABC                      500                2
Casualty              XYZ                      300                3
Casualty              LMN              250                4
Casualty              PQR               0                4
Casualty        OPQ               0                6
marine              XYZ                       200        1
marine              ABC                       300        2
energy              LMN               600        4
energy              MNQ               0                1

Desired report (On SSRS):

Capture.JPG
I have inserted the image of the desired report. I am almost done with rest of the data on SSRS. These are some of my queries.

Thanks
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
What I have is 10 columns, each with 0-50 names that display, wrap around, etc. They have been strung together and separated by a ";".  As requested.  If a name has a "+" sign in front of it I need to turn that name blue, if it has an "*" I need to turn it blue... otherwise I leave it black.  I am looking for a way to turn everything between each "+" and ";" blue (and there could be any number of these in a string) likewise with the red. Example
"Steve; +Robin;Mike; *David; +Shelia; BillyBob; Joseph;+Frank".  I need +Robin, +Shelia, +Frank to be blue, David to be red and the rest the usual black.  If at all possible I would like to keep the SQL as is (it works, runs fast, provides the customer what they want in the way they want it, and the code is very complex and long).
0
Here's the situation:
I'm reviewing the relatively large number of SSRS reports our company has created over the years with the intention of eventually archiving or deleting reports which are no longer being used. I currently have a stored procedure in place which is combining information from the ExecutionLog3 view with information from the Catalog table in the ReportServer database. As new records are selected, they are inserted into a log table I created. As reports which have already been logged are executed, the existing records in the log table are updated with the most recent execution date/time. Records which haven't been executed in the time since I've started logging the data have a default date of 1/1/1900. This way, I have a table which contains 1 record for each report in the Catalog, updated regularly to show the most recent date/time that report was executed. This all works well...

However, when the time comes to actually start archiving or deleting reports based on this information, I'm concerned that I might remove reports which aren't showing as having been recently executed, only to learn they were being used as drill-though or subreports and that I was only grabbing execution dates/times from the execution log for main reports.

This is all running on SQL Server 2008 R2.

Here's the question:
How can I create a SQL script that will identify subreports and drill-through reports being used by other main reports?
0
CREATE TABLE SSRSReport
(
    YearMonth		    INT,
    SSRSReportDateKey   INT,
    Membership		    INT,
    Admits              INT,
    AdmissionType	    VARCHAR(200),
    ManagingEntityName  VARCHAR(200)

)


INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALS')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PHYSICIAN Primary (CDPG)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','CULLMAN Secondary CARE IPA (CULL)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ETOWAH IPA (ETOW)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','FLN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GAN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GOTT')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IL')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IN (INAIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','INDEPENDENT PHYSICIANS (IPM)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MA MAIND')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSGC - LIGHTHOUSE IPA (MSGC)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSJAX')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','N/A')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','NC (NCIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PA PAIND')
INSERT INTO SSRSReport 

Open in new window

0
CREATE TABLE Report
(
    YearMonth        INT,
    ReportDateKey    INT,
    Admints          INT,
    AdmisionType     VARCHAR(200)
)
GO

INSERT INTO Report VALUES
(201701,20170131,10,'Acute Care')
 ,(201703,20170330,10,'Acute Care')
  ,(201704,20170430,70,'Acute Care')
,(201706,20170630,10,'Acute Care')
,(201702,20170228,80,'Acute Care')
,(201701,20170228,20,'LTE')
,(201702,20170228,30,'LTE')
,(201701,20170131,50,'RHEAB')
,(201702,20170228,100,'RHEAB')

Open in new window


EXEPECTED OUTPUT:-

AdmisionType	ADK	PreviousMonthADK	YTDADK"
Acute Care	80	10	90
LTE	30	20	50
RHEAB	100	50	150

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
Prep for the ITIL® Foundation Certification Exam
LVL 11
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

I have a report from SSRS.   I need to include blanks...so that if a user selects all any value is returned for that column.

I am listing the dataset which populates the dropdown in SSRS.
I am listing the function used to parse the string from report server and create a comma delimited list of strings.

I am listing the where clause from the report where I call the function.
This where clause should be able to select all or just items on the list if a user selects individual values.

It was my thought that moving the 'blank' to the bottom of the sorted list in the dataset would fix this.  It is not working.


dataset - I also replaced the commas in the list ..just in case that was messing up...no luck
select row_number() OVER (order by t1.cTitle) as titlerow,replace(t1.cTitle,',','') as cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by CASE when  t1.cTitle = '' then 1 else 0 end

Open in new window



Here is my string parser:

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 8:58:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window



Finally my where clause to get the list of values including a blank....as I need all values evaluated.

( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )

Open in new window

0
I have a dataset like the following:

 select row_number() OVER (order by t1.cTitle) as titlerow,t1.cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by t1.cTitle
			 

Open in new window



This creates as dropdown on report server...

Iit is a "MULTISELCT"   with first value blank, and then all the distinct values.



Now my stored proc where clause for this is the following:


t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') )

Open in new window




When I do this...I can still get the value..but not when its ''


here is my split function which I think might ignore the leading ''

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 6:14:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window

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
I have a select statement...here is very simplified version.


Select
value1
value2
value3
value4
value5

from table

---these values are bits


I need to be able to have a dropdown in SSRS that uses the friendly value of each of these values in one dropdown.  so the dropdown might be

label1 of value1
label 2 of value 2
lable 3 of value3

etc.


Then my parameter passed would be one or more of these values in the select.

Since they are not pivoted and in one column Im not sure how to do this.

im guessing I need a cte of these values..  There are about 20 to be exact.
So that means 20 items in the dropdown..and 20 columns in the select

Please help.
0
I have a bunch of dropdowns on an SSRS report.

Each drop down is basically a distinct list of values.

Each drop down is a multi select drop down.


Here is the issue:

If a null or blank value is in the recordset...or the distinct list pulls nothing back in the dropdown...

I need the parameter passed to allow all rows to be sent....or perhaps I need an option to allow in the multiselect for the user to pick a blank or null value.

This way when the user selects the next set of dropdowns a value will be returned.

I dont have a way that I can athink of to make each dropdown based on the other.
0
what is the best way to do this.

I have a report in SSRS that will have a drop down  that is Active/Inactive/all.


The column in the stored proc to evaluate would be

lactive = 0    (Inactive)
lactive  = 1   (Active)

But the user woudl like to let the user select all so that both would be in the query as well.


Thanks,
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.