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

Dear Experts:
I would like to set a date parameter default date value to today at 6AM.
e.g. 7/01/2018 6:00:00 AM
In other words I need the expression that can replace this expression that is not working:
=CDate(Format(Now(), "dd/MM/yyyy") + " 6 AM")


Note: I use SSRS 2016 and. I tried to run this report through the web service and fails with the invalid date error.
If I use expression =Today() then it works without any issue.
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'.
Cannot create 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
Hello,

How can I Silent Installation and Configuration for SQL Server 2017 Reporting Services ?

Best regards

Bibi
1
Points of My Scenario
1. I am admin of a Reporting Services (2008 R2) website that has been working until this morning.
2. Issue: users are now unable to authenticate/access it by its FQDN (fully qualified domain name) - it keeps prompting for credentials
3. When users type IP address instead of the FQDN, they can access the website

QUESTION: How can I resolve this issue so that users can once again access it via the FQDN?
0
I have a user trying to execute a report that uses ReportService2005:

They are getting this error and I can't figure out what needs to change to allow them access.
************** Exception Text **************
System.Web.Services.Protocols.SoapException: The permissions granted to user 'DOMAIN\username' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'DOMAIN\username' are insufficient for performing this operation.
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at test.ReportService2005.ReportingService2005.GetReportParameters(String Report, String HistoryID, Boolean ForRendering, ParameterValue[] Values, DataSourceCredentials[] Credentials) in \\server\e$\Custom\ZZ\test\test\Web References\ReportService2005\Reference.cs:line 1289
   at test.frmMain.SSRSReport(String reportName, String company, String jobNum, String assemblySeq) in \\e101swtest\e$\Epicor\Custom\ZZ\test\test\frmMain.cs:line 337
   at test.frmMain.btnGo_Click(Object sender, EventArgs e) in \\server\e$\Custom\ZZ\test\test\frmMain.cs:line 203
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at
0
I'm wanting to set a background color in my report to turn red if one condition is like "DOM" and a second condition is either "USA" or "SEECERT".  I have tried to get the syntax correct and haven't figured it out.

=iif(Fields!item_id.Value) Like "%DOM%" AND (Fields!coo.Value <> "USA" OR Fields!coo.Value <> "SEECERT"), 'Red', 'No Color'

Using Visual Studio 2012
0
We are using SSRS to create a shipping list.  Some lists can span multiple pages.

We wish the last page to contain a few signature spots (places where drivers, receivers, etc sign).

I created the signature spots.  As is they print at the bottom of each page.  I changed the visibility parameter to only show if the current page number matched the global page count.  This works... kind of.

I had to blow up the footer to accommodate all of the signature spots.  I assumed that the footer would shrink (thus giving the space back to the body) if the spots were not visible.  That is not the case... so, my footer is unnecessarily tall on all but the last page.

Thoughts?
0
Hi Experts,

This is quite strange.

I have a web application  that connected to DB1 in server1 and there is some ssrs reports from DB2 in Server2.
Domain report service account has content manager permission. application users stored in user table on DB1

reports working in report portal url and Report service URL
http://server2/Reports 
http://server2/ReportServer/ 

SSRS reports loading if I host the app as http://webserver.local/app
it popups username password box for ssrs reports if I host the site as http://app.webserver.local
with same application pool.  Same files content.  

other functionalities working fine regardless the hosting

Application and SSRS reports working fine on dev PC.

may be a small mistake. but difficult to figure out.  

Regards,

Pushpe.
0
SQL 2008 / SSRS 2008

INV         Billed            Paid
123         20.00          10.00
123         20.00          10.00
124         15.00            5.00
125         12.00          12.00

I want to create 1 SQL record that I can read in the main report query that gives me the total invoice count, total billed and total paid for all unique invoices. So this one record should show:
    3          47.00           27.00

Or,  better yet, how would I incorporate that into the main report query.
0
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
Free Tool: Subnet Calculator
LVL 11
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.

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

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
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

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.