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 am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Hello:

In SQL Reporting Services (SSRS), I have an "If, Then" formula shown in the first of two formulas pasted below.

I have this formula as an "expression" in a cell.  I want to multiply this cell times a value in another cell and have the product (multiplication result) appear in another cell.  I keep getting an error message that does not explain itself.  But, the error is clearly letting me know that I;'m not doing this correctly.

Specifically, I'm trying to tell SSRS to multiply this formula times another field.

This other field contains the second formula pasted below.

What formula do I use to accomplish this multiplication?

Thank you!

John


=If(sum(Fields!TRXQTY.Value)=0,0,sum(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
/ IIF(sum(Fields!TRXQTY.Value)=0,1,sum(Fields!TRXQTY.Value))


=Sum(Fields!TRXQTY.Value*(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
0
We have a number of reports that run out of our batch system behind data loads. There have been some recent updates. Previously, we have only one account type, so the parameter was just a single-value text value. We now use a multi-select parameter, and am getting an error when we attempt to run in batch although it runs properly through the web interface. I've captured the command line from our batch system:
\\xxx.com\share\CM\DEV\OPS\Apps\SSRSReportBuild\SSRSReportBuild.exe "Report=Solutions/ZZ-XX-12345-AccountAuditReport" "FileName=FCT-ZZ-XX-12345-AccountAuditReport.rdl" "MaxRunTime=1800" "Path=\\rpt2webtest\MFTest" "pAcctType=ABC,XYZ" "pAcctTypeList=ABC,XYZ" "pRunDate=20190906" "pRunType=D" "Render_Format=EXCEL"

Open in new window

When executed, the job fails with this error:
System.Web.Services.Protocols.SoapException: This report requires a default or user-defined value for the report parameter 'pAcctType'. To run or subscribe to this report, you must provide a parameter value.

Open in new window

Any suggestions of how to correct this and properly pass the value for pAcctType, a multi-value parameter?

Some of the questions that might come up
  • The source of available values for the multi-select parameter in the report is a SQL query.
  • The values 'ABC' and 'XYZ' do exist in the data.
  • We cannot pass the same parameter name twice on the command line, as the API does not allow this and will generate and error (I mention this becasue some web searching I've done indicate that is how you pass multi-select values in the web interface (e.g. &pAcctType=ABC&pAcctType=XYZ...)
0
Hello:

Does anyone have links to any free and good video tutorials on SQL Reporting Services (SSRS)?

Thank you!

John
0
Hello:

I have attached a screenshot of the layout of my SQL Reporting Services report (SSRS report) in both the Design and Preview tabs.

As you can see, my issue is that EXTDCOST field does not appear (i.e. shows 0.00 for all records), even though data from the other three columns does appear.

The report and, therefore, the attached .rdl file pull data from a stored procedure which I have pasted below.

How can I get this one field to show data, without modifying this stored procedure.

Thanks!  Much appreciated!!!

John


ALTER PROCEDURE [dbo].[p_cis_seeHITB] @I_nSortBy TINYINT = NULL
	,@I_nReceiptOptions TINYINT = NULL
	,@I_sStartItemNumber CHAR(31) = NULL
	,@I_sEndItemNumber CHAR(31) = NULL
	,@I_sStartAccountNumber CHAR(128) = NULL
	,@I_sEndAccountNumber CHAR(128) = NULL
	,@I_sStartLocationCode CHAR(11) = NULL
	,@I_sEndLocationCode CHAR(11) = NULL
	,@I_nStartQTYType INT = NULL
	,@I_nEndQTYType INT = NULL
	,@I_dtStart DATETIME = NULL
	,@I_dtEnd DATETIME = NULL
	,@sStartClass CHAR(11) = NULL
	,@sEndClass CHAR(11) = NULL
	,@sStartGenericDesc CHAR(11) = NULL
	,@sEndGenericDesc CHAR(11) = NULL
	,@I_fUseGLPostDate TINYINT = NULL
	,@I_fIncludeZeroQtyItems TINYINT = NULL
	,@O_SQL_Error_State INT = 0 OUTPUT
AS

		
--testing
/* 
if object_id('cis_seeHITBParms') is not null drop table cis_SeeHITBParms 
Select @I_nSortBy SortBy
	,@I_nReceiptOptions ReceiptOptions
	,@I_sStartItemNumber StartItemNumber
	,@I_sEndItemNumber EndItemNumber
	,@I_sStartAccountNumber 

Open in new window

0
I need to link and additional table to filter by the plant in doing so create multiple records for each entry. I am looking to just get the first entry for one.

SELECT 
    OrderHead.CreditOverride, 
    OrderHead.OpenOrder, Customer.CreditHold, 
    OrderHead.OrderNum, Customer.Name, 
    OrderHead.EntryPerson, OrderHead.OrderDate, 
    Customer.TermsCode, OrderHead.ShipToCustNum, OrderRel.Plant
FROM  
    Customer 
INNER JOIN
    OrderHead ON Customer.Company = OrderHead.Company 
             AND Customer.CustNum = OrderHead.BTCustNum 
INNER JOIN
    OrderRel ON OrderHead.OrderNum = OrderRel.OrderNum
WHERE 
    (OrderHead.CreditOverride = 0) 
    AND (OrderHead.OpenOrder = 1)  
    AND (Customer.CreditHold = 1) 
    AND (OrderRel.Plant = 'mfgsys')
Trying to grab the first unique record from orderhead.

Open in new window


2019-08-25_14-25-15-p1.pdf

it doubles up my entries   i know but i only want to see the first order number for each one.
0
Hi EE,

This question is more of a query looking for approaches to resolve the following issue:

The user has an SSRS 2016 report and filters the datasets efficiently with filters they have been given.

However, the user wants to save specific rows within the report to key the data into another system e.g Desktop application. Exporting initial the result set data to XML result is a simple task. My question is can you have a button on the SSRS which launches a subform that asks what rows /columns specifically do you want to save from the resultset in XML format.  Completing the necessary criteria on the subform, SSRS exports the data to the user via a download.  

Is this possible in SSRS if so are there examples out there?

Any links or tips, advice are welcome.

Thank you.
0
I will like to gather some stats on SSRS report usage such as when the report was last run, number of executions etc.
Is it possible to create a SSRS report which is able to give me such stats or a query execute on the server.
I am a newbie to SSRS and currently have a url which display the reports
0
hi Can someone tell me if we have csv rendering format option for email subscription of ssrs reports?
as i want to email report only in csv format.
0
i have 3 tables from oracle db and need to create report based on them
but in query designer it's showing unable to parse query text ';'
seems one of the column in table is having ; value for the data
so how can we avoid this
select * FROM products p,
       CUSTOMER c,
       order  s
where p.product= s.order and p.productID = c.productID;
that's the query i have used in report

i am totally new to oracle db so don't know how to combine tables and get data in dataset
kindly can some one send me link where we have information on ssrs reports using oracle db
thanks in advance
0
Become a Microsoft Certified Solutions Expert
LVL 13
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

i need to create ssrs report using oracle db
when i preview the report it's not returning any data
in query designer also it just returned table
can some one tell me why it's not returning data
query which i used is select * from table
just normal query
1
I want to deploy my SSRS reports on my local laptop. How do i determine what my TargetServerUrl should be?>
0
Hi:

I really hate that someone has made it very difficult to create or edit a SQL Reporting Services (SSRS) Report.  In the old days, when I worked with SSRS everyday, I simply used the Business Intelligence Development Studio (BIDS).

Now, you cannot find anything like BIDS in the newer versions of SSRS.  I hate Report Builder and refuse to use it.

Last week, I followed instructions found online and installed Visual Studio Tools.  And, I asked the IT department to reboot the SQL box late at night.  This was all in an effort to find the latest equivalent of BIDS.

But, I cannot find this.

What in the world do I use and how do I find it?

Please help!  I can't believe that this tool has been buried, in the newer version of SSRS.  

Thank you!  Much appreciated!

John
0
Management wants to know what employees are doing if working remotely. I would like to track and store their activity while they are connected to our work server via remote access.

I need to track all user activity such as select, insert, update and delete and put it into a table in order to create a report.

The Database we use is Microsoft SQL Server 2012.
0
1.  I need to change the destination of an SSRS report in Visual Studio 2010 so that when I deploy it, it goes to the correct folder.  There appear to be about 6 or 10 places where I can change options, I just cannot seem to find the right place to change these deployment options.

2.  On this particular report, there are two subreports.  I would like to deploy these two reports to a separate (subfolder) under the folder which contains the main report.  I assume that the changes I make in #1 are for SSRS, not for a particular report (is that correct).  If so, I will need to change the path for those subreports as well.  How do I change the path to these subreports in the Visual Studio report designer?
0
we have migrated our SSRS 2014 Reports to SSRS 2016 . we have successfully deployed all the reports and data sources. However, the Report Subscriptions are not migrated .

can anyone help me how to migrate SSRS email subscriptions to 2016 ? I don't want to create them manually as there are loads of them.

Many Thanks
0
SSRS subreport does not display properly after deployment.

I've developed (modified actually) a report in SSRS (via Visual Studio).  The report and subreports display properly in the VS report designer, but after I deploy the reports, the main report deploys properly but the two subreports fail to render.

Any ideas?
0
Hi EE,

I have a rather unusual problem I can only get either  Web Portal URL working or  Web service URL working but not both.

What is currently happening is if the web service URL and web portal URL match (virtual directory name is the name) the web service works but the web portal doesn't.

And if the web service URL and web portal URL have different (virtual directories) the web address for the web portal works.

Attached are pictures of my current settings.



Right now I can only see the web service working.

From the reportserver config file.

<URLReservations>
            <Application>
                  <Name>ReportServerWebService</Name>
                  <VirtualDirectory>Autumncareuatreport</VirtualDirectory>
                  <URLs>
                        <URL>
                              <UrlString>http://+:81</UrlString>
                              <AccountSid>S-1-5-80-4000206335-4242116595-3118339656-1734867201-556326921</AccountSid>
                              <AccountName>NT SERVICE\ReportServer$AUTUMNCARETESTRS</AccountName>
                        </URL>
                  </URLs>
            </Application>
            <Application>
                  <Name>ReportServerWebApp</Name>
                  <VirtualDirectory>Autumncareuatreport</VirtualDirectory>
                  <URLs>
                        <URL>
                              <UrlString>http://admin:81</UrlString>
                              <AccountSid>S-1-5-80-4000206335-4242116595-3118339656-1734867201-556326921</AccountSid>
                              <AccountName>NT SERVICE\ReportServer$AUTUMNCARETESTRS</AccountName>
                        </URL>
                  </URLs>
            </Application>
      </URLReservations>

Thank you
Web-Portal.PNG
Web-Services.PNG
0
Hi Experts,
I have an ssrs 2008r2 report linked to images.  The report renders the image in Visual Studio but does not find the image in runtime
scr= ""  when looking at the browsers response i.e there is no path included to render the image.
When you type out the full web address of the photo in a browser it can be seen in the browser
The photo is stored locally on the PHP website.  Both the reporting services and database server are on the same domain.
The folder has anonymous access for testing, so not sure what else I can trouble shoot.
Any help would be appreciated.
Regards
Chris
0
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I work in VS 2015 VB.Net
I creatd SSRS report with subrepot.
I read that to do that you have to populate both your main report and subreport in code, and for the subreport you create an event handler to populate.
In my case the subroutine that handles the event handler never gets invoked, and the subreport is not getting populated. Report displays only the main report, the subreport returns a message: Error: Subreport could not be shown.
Here are the references I have on the page:
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Here is my page load event:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            ReportViewer1.ProcessingMode = ProcessingMode.Local
            ReportViewer1.LocalReport.DataSources.Clear()
            ReportViewer1.LocalReport.ReportPath = Server.MapPath(".") + "/SSRSReports/" + "PvPDOrder.rdlc"
            ReportViewer1.LocalReport.DataSources.Add(GetReportDataSource)
            AddHandler Me.ReportViewer1.LocalReport.SubreportProcessing, AddressOf SubreportProcessingEventHandler
            Me.ReportViewer1.LocalReport.Refresh()
        End If
    End Sub

   Public Sub SubreportProcessingEventHandler(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
        Try
            e.e.DataSources.Add(GetSubreportDataSource)
        Catch ex As Exception
            ' MessageBox.Show(ex.Message, …
0
SSRS rdlc with two dataset. The report prints a main textural record from the first data set.  The second data set prints related items to the first dataset as textural data.  Basically I'm creating documents.  I have two RECTANGLES and assigned the correct dataset to each. Inside each RECTANGLE I have a text box for the text and corresponding column data.  This works great if I am filtering on one set of data points; such as one record that relates to other records being in the second dataset.

Dataset1
name address owner

Dataset 2
occupant1
occupant2
etc...

I've been trying to keep them together but am running into problems.  I know how to deal with this developing RDL projects with sub reports; but can't seem to get that working correctly with RDLC. Is there anyway, given my current approach to group these correctly and print one record per page regardless of the grouping as well?
0
What are the top 10 most common package failures? Thanks
0
I have a background in computer programming including sql, access, cobol, vb for apps. I worked for a fortune 500 company generating reports with access at the time.  I have been a stay at home mom for the last 5 years.
I had minimal training 10 years ago in data warehouse. I took a couple of courses online and understand the fundamentals of using visual studio - creating a package with a data flow, defining the source and target, configuring the connection manager, linking table lookups to the reference tables in the warehouse. I brushed up as  i had an interview for a data analyst job that required ssis and ssrs ( I am familiar with reporting tools) - I did not put on my resume but it did state on the requirements. They want to interview me to trouble shoot failed packages. I can figure things out very quickly. However, I am not sure what type of questions they might ask that are more basic relating to ssis and ssrs. I was wondering if you might please be able to provide some basic questions regarding package failures or any questions that might help me get through this. Maybe I am over my head. Thanks again for your help
0
how do i add a user to the BUILTIN\Administrators group for SSRS
0
I am trying to change an SSRS report so that the PROJECT parameter needs to be an exact match or if left blank returns all.

WHERE (V_WO.CCN=:CCN) AND (V_WO.MAS_LOC=:ML) AND (V_WO.STATUS <>'C') AND (V_WO.ORD_QTY-V_WO.COMPLETE_QTY>=0) AND (V_WO.MFG_CLOSE_DATE Is Null OR V_WO.FIN_CLOSE_DATE Is Null) AND (A.CUSTOMER LIKE '%'||:CUST||'%' OR A.CUSTOMER IS NULL) AND C.RN=1 AND ((:DUE ='LATE') AND (CASE WHEN(A.PO_DATE2 is NULL) THEN(SYSDATE) ELSE(A.PO_DATE2) END-V_WO.DUE_DATE<0) OR (:DUE='ANY')) AND (TRIM(V_WO.WO_NUM||'-'||V_WO.WO_LINE) LIKE '%'||:ORD_NUM||'%') AND (' '||A.PROJECT LIKE '%'||:PROJECT||'%') AND (A.QTY<>0 or A.QTY IS NULL)
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.