SSRS

9K

Solutions

30

Content

i
The collection of articles, videos, and posts on this topic.

5K

Contributors

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

Does any one know what table the comments are stored that can be entered on the Report Delivery Options screen?  

 
Would like to include it in a query but can't seem to find it anywhere
 
 
Report Delivery Options  
 
 
 
Specify options for report delivery.  
Delivered by:   E-Mail Windows File Share
 
   
To:  
Cc:  
Bcc:  
 (Use (;) to separate multiple e-mail addresses.)
Reply-To:  Error Only one address is allowed in Reply-To.
 
Subject:  
 Include Report  Render Format:   XML file with report data CSV (comma delimited) PDF MHTML (web archive) Excel TIFF file Word  
 
 Include Link
Priority:  Normal Low High
Comment: Where are these stored????
 
Subscription Processing Options  
 
 
 
 
Specify options for subscription processing.
 
Run the subscription:  
 
 
When the scheduled report run is complete.  
 At 8:00 AM every Mon of every week, starting 11/12/2019
On a shared schedule:   Every 5 Minutes No Schedule  
 Every 0 hour(s) and 5 minute(s), starting 11/5/2015 at 2:00 AM
 
 
 
 

Report Parameter Values  
 
 
 
 
 
 
 
Enter Due Date:
0
2 Comments
CompTIA Network+
LVL 19
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Learn More!
I obtained this code from the internet but it gives integer results.  How do I modify it so it will compute to 2 decimal places?  I am dealing with dollars and cents.

Public Shared Value as Integer=0
  Public Shared Function GetValue(Item as Integer) as Integer
     value= value + Item
     return Item
  End Function
  Public Shared Function GetTotal()
     return value
  End Function
1
3 Comments
Using SSRS 2017 I was cleaning up a report. I deleted a shared data source that was not used by any report. When I go to deploy the report I get the message "could not find file  deleted.rds". Why is it still looking for this deleted data source and how do I stop this?
0
3 Comments
Hello gurus ,
Not sure how to start the question, hopefully makes sense in a sec.
I have quite a few reports built. For some reason, the formatted numbers show a comma after each digit when an Excel exported report is viewed on a mobile device.

Does not matter if it is in a Matrix or just a Grid.
i.e.   $12,345,000.00   shows up as $1,2,3,4,5,0,0,0.00

If I change the format from #,0;(#,0) to n0 or n2 then the issues is resolved, but, if this is a global issue then I rather find the solution instead of going through all reports.

Speaking of these reports, they all show correctly on the desktop's Excel.

Additionally, the Matrix layout, expands all hidden toggleable groups.
Very strange.
Appreciate any information that may lead us to a solution.
thx

John
0
8 Comments
I have a table  with columns Billing Month, Price Category, System Part, Charge Type, FAMIS Expense Account, FAMIS Income Account,,Quantity,Rate.  I group the rows based on multiple columns.  I count the number of rows in each group and multiply times cost to get a new column, Amount.

My problem is I want a grand total of all the groups.  So in the bottom right of the table in the attached image is an expression which is where I want to put the grand total.  How do I do write the expression?
parts.jpg
0
5 Comments
How do I add a column group to a table that has a row group and details?
0
1 Comment
I need to change the formatting of 8 cells in a table in SSRS.  I want to set the number formatting.

How do I do this at one time?
0
3 Comments
Getting 'Metadata not available' when modifying existing reports and creating new reports - the reports will run and return data but cannot make any changes to the report. The Model.xml file is size 0 in the framework folder. Was able to get a model.xml file from a backup but not sure what to do with it so the .cpf recognizes it.

When I try to open the framework file .cpf it doesn't open it just flashes and closes immediately.
0
1 Comment
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
4 Comments
I am trying to back up a reporting server using the following article:

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-2017


I can back up the DB just fine but when I get to the command to back up the logs I get an error  "Backup LOG " cannot be performed because there is no current database backup.

Reading the article below, it makes it seem the COPY_ONLY command is causing me the issue. Is this the case? If I do not run the COPY_ONLY command, am I going to be missing anything important?

https://sqlbackupandftp.com/blog/backup-log-cannot-be-performed-because-there-is-no-current-database-backup
0
1 Comment
PMI ACP® Project Management
LVL 19
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Start Today!
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
6 Comments
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
1 Comment
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
3 Comments
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
5 Comments
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
2 Comments
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
2 Comments
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
14 Comments
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
4 Comments
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
3 Comments
Starting with Angular 5
LVL 19
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Start Today!
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
3 Comments
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
2 Comments
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
4 Comments
Hello:

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

Thank you!

John
0
1 Comment
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
8 Comments
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
10 Comments

SSRS

9K

Solutions

30

Content

i
The collection of articles, videos, and posts on this topic.

5K

Contributors

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.