Go Premium for a chance to win a PS4. Enter to Win

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

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
Free Tool: ZipGrep
LVL 11
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Hi Experts,

I have this bit of code:
Declare @NotUsedDays as int 
Set @NotUsedDays = 120

SELECT Distinct 
Name,
Path,
Convert(Date, [LastUsedDate], 120) as LastUseDate,
NotUsedsince=DATEDIFF(DD,LastUsedDate,GETDATE()),
Statuskey

  FROM [LSV].[BISL_SSRSLog].catalog C

  JOIN (

                SELECT ReportID,LastUsedDate= MAX(timestart), StatusKey 

                     FROM [LSV].[BISL_SSRSLog].[ExecutionLog]

                       WHERE RegionName = 'Region that is in charge of SharePoint'

                       and ReportLocation = 'Location'

                       and Environment = 'Production'

                       and SPVersion = 2013

                       and (DateKey like '2016%' Or Datekey like '%2017%')
					    and [USERNAME] Not In ('bah1', 'bah2')


                 GROUP BY ReportID, StatusKey

          ) E

       --ON C.ItemID = E.ReportID

       ON C.ReportID = E.ReportID

 WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NotUsedDays And StatusKey <> 34 -- 34 = Non existing reports??? 

 ORDER BY NotUsedsince desc

Open in new window


As you can see I am guessing that 34 means the reports are not available out on the share.  I am also guessing that column "Statuskey" is the correct column?

As a result I get reports that are no longer available (they have been deleted)

I want only reports that are available to come up in the query.

Thank You,
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
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
I have a complex query I am trying to build and was hoping for help.

This stored proc will be called from report server.


Here is a snipit of the general idea of this part of the query.

I have some rows being called from a bunch of joins.

From one of my tables I have the following columns:
select 
c.cName as [Customer],
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 

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
Free Tool: IP Lookup
LVL 11
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.

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,

I would like to create a webpage in which I list the most frequent reports on our reportingserver used by SCCM.
Now a user needs to go to
ConfigMgr_PRD > Software Updates - A Compliance > Compliance 2 - Specific software update then select the right collection. Users never know what collection it is.

I can create a link to http://ourreportingserver/Pages/Report.aspx?ItemPath=%2fConfigMgr_PRD%2fSoftware+Updates+-+A+Compliance%2fCompliance+2+-+Specific+software+update

But I cannot change the value of the collection in the dropdown menu. Is there a way to do this?
F.e.
http://ourreportingserver/Pages/Report.aspx?ItemPath=%2fConfigMgr_PRD%2fSoftware+Updates+-+A+Compliance%2fCompliance+2+-+Specific+software+update/AllClients


Please advise.
J.
0
Hi I have a query returning all the information I need and want to put it into a SSRS report.

Item Number             Item Des.                Qty On Hand          SKU Number       QTY On Order
1234                                  Test Item                            10                     4567                  Sum Of PO QTY(28)
                                         PO Number             PO QTY
                                            9991                                    5
                                            9992                                   23
                                     
Every time I add the "PO" section it inserts as a column beside the item number and I cant move it...

Thanks in advance.
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
I'm relatively new to SSRS.

I have a dataset (HouseholdMembers) that returns the names and positions of everyone in a household, and use that for one table in the report.

But in another table, I need to extract the values of the FullName field for the individuals where the Positions are 'CG1', 'CG2', and 'Child'.

What expression should I use to perform this lookup?
0
I am working on rewriting a SSRS report which currently runs extremely slowly.

in one table of the report, the data comes from a field with contains <div></div> and formatting (<font face=Arial size=1></font>) tags.

In the original report, these tags are in the recordset, but do not show up when I generate the report.  However, in the new report, those tags are visible.  Any idea on how to remove the tags and display the text normally, as formatted in the SSRS textbox properties?

Dale
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
Vote for the Most Valuable Expert
LVL 7
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

I have a url in my database so when I display a record per page I would like to have the web page from the url display as part of the report page. I see I can do it in 2016 but I only have 2012.
0
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 inherited some web application that seems to be using some reports within aspx pages. I am trying to migrate them to new server. When page in question is browsed on new server it pops up with windows username and password dialogue box.
Has anyone encountered this before?
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

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.