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

Hi:

I continue to have issues, with this SSRS report.  I have entered five cases, here in experts-exchange, on this.

Now, I'm getting an error on the main report saying that the "Subreport could not be shown".

Last week, the subreport's data appeared just fine.  This week, it does not.

What would cause this issue?

Thanks!

John
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hello:

As of last Thursday evening, a SQL Reporting Services (SSRS) report was working.  Today, it's not.

I have a main report containing two subreports.

Today, the two subreports are not showing any data.  Just now, I "re-added" these two subreports onto the main report to see if that would have any sort of "good effect".  It did not.  They are, still, not showing any data.

The subreports, when run individually today, do indeed generate data successfully.

Anyway, I'm asking the IT department to restore a backup, in case someone did anything in the environment since last Thursday.  

In the meantime, please let me know if you have had this experience and let me know what else can be done.

Thank you!

John
0
I need assistance with the following requirements:

I need the font on my text to equal "DimGray" when IsRunning = "Processing" else if the MostRecent.Value > today = Red else Green. With what I have below, its making them all DimGray.

=Switch(Fields!IsRunning.Value ="Processing" , "DimGray", IIF(Fields!MostRecent.Value
>= TODAY(), "GREEN", "RED"))
0
I just now implemented a simple filter saying that cost should not be equal to 0.  Now, SSRS is giving me an error saying that it "cannot compare data of types system.decimal and system.double".

Thoughts?
0
In SSRS, I want to filter out only those employees with more than 1 partfile number.  

My query is

Select e.name,
            p.pfnumber from employees as e
left join partfile as p
on p.recid=e.partfilerecid
group by e.name

What expression do I use in the filter to be > 1?
0
Hello,
I'm running into a strangely difficult problem.  I'm trying to build a report with the structure in the picture.  No problem with the SQL and the dataset (I think) but I can't strong arm a table or Matrix to show the format I want.  The top row should show 4 fields from the parent table and when the user clicks the + the report opens up to show the related fields from 3 child tables.  The child tables are all related via the ID field on the parent table.  Since I'm not familiar with the report builder process I'm not sure if I should be using a table, matrix, sub report, or some combination to achieve this.

Can anyone help?

Desired format
0
Hello:

In SQL Reporting Services (SSRS), I created a subreport that displays the Grand Total of dollar figures on the main report.

When I copy and paste that subreport onto the main report and link it by parameters for both reports, the Grand Total does not display accurately on the main report.

I have another subreport that is configured very similarly.  On the main report, that subreport correctly displays its data.

What are some things that I should review, in order to have the Grand Total display accurately?

Thank you!

John
0
Hi:

Why would simply running a subreport or resizing columns in SSRS cause an error on the main report that that subreport is on?

The error is "One or more parameters were not specified for the subreport".

Attached is the main report and subreport.  The subreport exists in the far-right column of the main report.

What's strange is that I can alleviate this error by moving the subreport to a different location, running the main report, moving the subreport to its original location, and re-running the main report.

But, when I afterward re-run the subreport, I get two instances of this error.

Thank you!

John

Historical-Inventory-Trial-Balance-S.rdl
Extended-Cost-by-Account-Number.rdl
0
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
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
Introduction to R
LVL 13
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hi:

Attached is my main report, along with its subreport.

As you may be able to see in the screenshot, I need a subtotal based on account number (the first column).

The subtotal needs to be by the extended cost, which is the "Ext" column of the main report.  This column represents extended cost.

I have produced this total in the subreport.  But, I cannot tell where to place it on the main report to generate the subtotal.

If you have any thoughts at all, please share them.  Even the "littlest" idea will help.

Thank you!

John




Historical-Inventory-Trial-Balance-S.rdl
Extended-Cost-by-Account-Number.rdl
Screenshot
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
I'm having trouble with my internal SMTP mail relay. A user had some malware and we were flagged as spammers. Now SSRS email does not work. I have loads of stuff that uses the internal mail relay but am slowly going through and changing things but am stuck on SSRS

The only options I have within SSRS Config Manager are :

Sender Address:
Current SMTP Method: (set to 'Use SMTP Server' but greyed out)
SMTP Server: (This has IP address of internal mail relay)

Does anyone know how to get this to work with an external SMTP server?
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
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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
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
I need help writing an SSRS expression.

I need to count the distinct patients where a condition is met. For my example, I want to distinctly count the patients where HCPCLabel = 'Medical'.

Distinct Count PatientProfileId, where HCPCLabel = 'Medical'
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

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.