[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 had this question after viewing SSRS difference/Varience Column between 2 years.

I'm a beginner and need help understanding how this solution applies to my problem.  My table consists of account number, quantity and date with multiple records per account and per date.  I total the quantities grouped by account number and date but I am confused how to find the difference between the group totals.  New records are added each month and the report is to select the most recent two months and compare and show the difference.  I don't have the report using any parameters or variables.  Just a table row grouped by account number and column grouped by date.
 
I'd love to be able to specify cell#a - cell#b /cell#a * 100 to get the percentage but I haven't learned SSRS well enough to understand what is happening.

My goal is to have a table that shows

                                                         Previous month                         Current Month                       Percentage change
Account number A                       1000                                                1100                                            10%
Account number B                       2000                                                 2500                                           25%
Account number C                       5000                                                 4500                                         -10%
0
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

I create SSRS report with 2 series. One is the number of tests, another is the number of tests that passed on the first try. I want to show it as stacked bar report. When I create the report with 2 series, both numbers are in the same bar, but they are one above the other, while I want the bar colored according to the percentage. For example, if in given month there were 100 tests, of which 90 passed on the 1st try, I want the bar filled with the 2nd color at 90% - instead of the current 100 and 90 on top of each other. Is it possible?
screenshot
0
Unable to install SSDT for 2017, SSMS for 2017 or SSRS for  2017 due to language mismatch.  I have a W10 Professional with SQL2017 and VS Community 2017 installed. In the log files there are messages about language mismatch and on the screen there are messages about unknown registry key. The language is English and I have been using the English version of the OS and the tools. Does anyone have any ideas on how to get past this? The few notes I have been able to find say it cannot be done.
0
I've got a SSRS report which currently filters a dataset on "[ProgramID] = @ProgramID" and the report parameter is set to only allow a single selection.  This is currently done in the dataset which looks something like:

SELECT * FROM myTable WHERE ProgramID = @ProgramID

My client wants to change this to allow the user to select multiple Programs from the Program dropdown.

How do I modify the SQL query in my data set to accommodate this multi-select parameter?  I've tried a number of things and cannot seem to get it to work by modifying the SQL or by removing the criteria from the query and adding a filter to the tablix which uses the dataset.  I've searched and tried a number of "solutions" from here in EE and other web-sites and just cannot get this right.

Thanks
0
SSRS Subscription and Daylight Saving Time

What is the best way to handle  Daylight Saving Time in SSRS subscription


Can that be updated in DB ?

thanks
0
This problem used to happen once or twice a year, but it's happening with higher frequency now, and I'm at a loss how to troubleshoot it - because the normal troubleshooting method I'd use is the temporary and unintended "fix" itself.

I have an SSRS 2012 report deployed in SharePoint (2013) mode on a Windows Server 2012. The report is configured to use a dynamic data source, such that it can be run against 1 of 5 similar sites (databases). When the problem arises, whether I run the report from IE or within SSDT itself, I get this red-herring message:

The error message
Here are some important outcomes from my troubleshooting so far:
  • My normal troubleshooting strategy is to gather the list of inputs the SP behind the report would use, and run the SP manually from SSMS. As soon as I run this query, the problem disappears
  • The date range on the report makes no difference. Even if the report ran last week on last week's data, setting the date range to last week when the problem arises this week still results in this message
  • The problem typically happens on 1 or 2 sites at any given time, but not all sites. The "fix" is to connect to each site's DB and run the query once in SSMS
  • I use the term "red-herring" because if it were a data issue, the problem wouldn't disappear simply by running the query in SSMS. One would expect to find a bad piece of data that would require correction
0
Hi,

I have something somewhat similar to the Data Connections, Data Sources, and Connection Strings (Report Builder and SSRS) with the Expression-based Connection Strings. This is with SSRS 2014.

But when I go to publish it's asking for datasources on the Report Server -> Report Manager. How would/could I do the Expression-based Connection Strings with that?

Any information on that would be greatly appreciated.

Thanks
0
Good day experts,

Here are my issues with an SSRS I've got to get working:

1. I'm trying to build a report that should have details in columns ( left to right or horizontally ) rather than rows.
    For this, I was able to create a Matrix, delete the Row groups and then create the details in the columns groups, as you can see in the attachment. It seems to work however I'm mentioning it because it might be the issue with the page break.

Example of my grouping inside a matrix tablix.
2. Then I'm trying to apply page break to this column group.
    For this, I created an expression on the column group that is: =Ceiling(RowNumber(nothing)/10) in order to have ten details per page. I then apply page break option to this group however it doesn't apply the page break. The grouping on the expression is correct though. (see attachment)

Enter of page break not working
Could you please help me understand what I'm missing or what new approach I should have to solve this issue.

Regards,
0
Hi,
We have a DB driven application where all the business logic written in Microsoft SQL.
We need to create a bunch of dynamic PDFs (Fillable PDFs) as a result of the calculations. We are now creating the PDF using .Net API and PHP.
Is there any easy way to create the PDF using SQL Reporting Services or any other ways?
Once the PDF is generated, we will use Angular ng pdf viewer to show the preview in browser.
Please help?

Thanks in advance :)
0
I had this question after viewing Displaying group data from a table in a textbox outside the table in SSRS 2008.

I followed the steps, and I'm at the point where I have a text box named Textbox52 in the header and it has the value I want, but when when I try to use Textbox52 to display the Value using the expression ReportItems!Textbox52.value  somewhere else in the report (I have several rectangles in the report), I get the error "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. Letters in the names of report items.
0
CompTIA Network+
LVL 12
CompTIA Network+

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

I am in the process of converting a large number of Crystal Reports to SSRS. I have hit a snag with a number of Running Total issues.

In a number of reports, A running total evaluating on change group is used as a grand total outside of the group. As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.

Any ideas on a way to get this to work?
0
Hello All:

I need help in figuring out how to print a subreport on the last page of it's main report with one parameter passed between them in SSRS.

I need to print a purchase order and every time it prints a follow up terms sheet prints after the main PO prints with the purchase order number printed on both the main and sub report.

I need assistance on the best approach for this.

Any ideas on the best approach for this would be very helpful.

Thank You

Rm103
0
I have a list report that basically looks like

ReportProblem1.jpg      
What I would like is

ReportProblem2.jpg
If there are 2 jobs on a given date, they would be stacked otherwise all jobs are on the row.  Sorry for the crudeness.  I wasn't sure how to ask this questions.
0
I need to create a SSRS report from a list and document library in SharePoint 2016. They are joined by an ID called "BMSId"
What I need is a list of both data points and be able to filter (Parameter) the data. The filter needs to filter both a start date and an end date.
I am not looking for a report that has a sub report, but a straight line item with all the related data based on the ID number.
Being able to sort and group would be great, but just to get a list that has all the date would be great.
I do not have access to Visual Studio.
Thank you so much.
0
Using Report Builder in SQL Server 2016 and I cannot get group contents to split across multiple pages.  Just a simple group with headers and footers, no subreports or fancy images, just a footer full of text as a test because the fancy stuff was broken :(.  The report is grouped by quote number and then line, the details of the line are in the detail section and then a group footer on the line with a bunch of text that will print after each line.  The issue I am facing is the second line jumps to a new page becuase there is not enough room after the first line to fit the group header, detail, and footer for the next line.  All KeepTogether Properties are set to false.  The group is contained within a tablix.  I scanned the xml of the RDL to ensure every last KeepTogether option was set to false.  I have been through every static property in the groups changing the KeepWithGroup option from None, to Before, to After, all three produce the same result.  My goal is to have the header, detail, and begninning of the footer for line 2 begin on page 1 and roll to page 2 as needed and so on.  Anyone have any ideas why this is automatically rolling to the next new page, why is it trying to keep the group together for quote lines when all options are set to false?  I have attached a sample print out of the report in PDF format and the RDL file for report builder just in case you need to examine the properties for yourself, the file is there.
0
SSRS- Created a blank report and used a DB dataset to just drag and drop a text field onto it.  It only shows one row.  This was a limitation of SSRS and that field.  
Solution was to create a Tablix and go through the following to display a separate row per page:
https://www.mssqltips.com/sqlservertip/3493/display-a-fixed-number-of-rows-per-page-for-an-ssrs-report/
The problem is that when I place the Tablix field in my report it is in the position I want.  But due to the page breaks I lose my position and the field ends up on the top of my report with my report being broken up.  Is there a way or how do I get the position of the Tablix field to not move when traversing through my data?
0
Need the IIF statement for the following.  [SUBJECT_SIGNED_DATE] is the Matrix Text Box Value that displays.

In Simple Terms with the 2 Fields and Values Needed to determine Fill Color

If the [SUBJECT_SIGNED_DATE] is null and the [SYSTEM_SUBJECT_STATUS] is <> 'Screen Failed' or 'Early Terminated' or 'Completed'
Set the Fill Color to Red otherwise Set the Fill Color to White
0
I have the below dataset. I am trying to find an SSRS expression to check the value of it and return 0.00 if it equals 100.00. If it does not equal 100.00, the expression should return the value of the dataset. Any guidance is appreciated.

=Sum(Fields!Compliance_Rate.Value, "Failure_Rate_Month")
0
while exporting my report data to excel it is running 10 -15 minutes after that im getting server Runtime error in  ssrs report .I have 3 lakhs data and 49 columns in excel.
There is no exceeding limitations problem with my excel data and im trying to export it into .xlsx format only.Please suggest what i have to do
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

The following command string exports a PDF to be saved or opened.

start explorer "<http://ServerName/ReportServer$NamedSQLInstance?%2fPurchasing%2fPurchaseOrder&PoNumber=103290&rs:Format=PDF"

Need to NAME and SAVE the file Automatically, from a Delphi desktop application.
0
I'm trying to recreate a report for a client, and in that report they have three fields Night, Weekend, Holiday (0 or 1) which need to be displayed as check-boxes.
In Visual Studio, I've made the column for these values .2" wide, have set the font to Wingdings, and have the expression which looks like:

= IIF(Fields!Night.Value = 0, chr(0168), chr(0254))
& IIF(Fields!Wknd.Value  = 0, chr(0168), chr(0254))
& IIF(Fields!Holiday.Value = 0, chr(0168), chr(0254))

In VS, this displays like the following image:2018-09-05-SSRS-Checkbox1.jpgBut after I deploy the report and run it in SSRS it looks like the following, making it difficult for the client to review the data in the report.2018-09-05-SSRS-Checkbox2.jpgIf my client exports this from SSRS to a .pdf file, it renders properly, but the client needs to export the report to Word (so that it can be edited) and when they do, that column automatically resizes itself so that these stacked checkboxes display horizontally.  My client recognizes that they can simply resize the column, but would prefer not to have to do so.2018-09-05-SSRS-Checkbox3.jpgI've tried inserting a carriage return/line feed between each character in the report textbox expression, but that results in extra spacing between the checkboxes.

I've also tried using 'X' and 'o' with the wingdings font, which places an X inside the checkbox, but the formatting issue still persists.

Does anyone have any recommendations?
0
=Sum(IIF(Fields!GROUP4CODE.Value>=3, CDbl(ReportItems!Textbox132.Value), CDbl(0)))

this expression gives error: aggregate functions can be used only on report items contained in page headers and footers.
0
What is best configuration to have SQL Server 2014 Standard & SSRS component run on a normal Windows OS ( 7 or 10) and send reports by email ?
0
How to add the textbox values of columns? these textboxes are not the part of the dataset. I have tried several options but something seems to be missing.

tried #1
=sum(ReportItems!Textbox22.Value)
Error: aggregate functions can only be used on report items contained in page headers and footers

tried #2
=RunningValue(ReportItems!Textbox22.Value,Sum,"Dataset1")
same error as above:   aggregate functions can only be used on report items contained in page headers and footers

tried #3
I wrote a vb function like this given in this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/002c459a-2260-437a-a53c-a380bc2299d0/sum-column-gives-error-aggregate-functions-can-be-used-only-on-report-items-contained-in-page?forum=sqlreportingservices

Public Total_lookup_Sum As Integer = 0
Public Function Lookup_Sum(ByVal value As Integer) As Integer
    Total_lookup_Sum = Total_lookup_Sum + value    
    Return Total_lookup_Sum
End Function

and then wrote this expression:
=Code.Lookup_Sum(Reportitems!textbox22.value)

the output shows me the last value of the textbox instead of aggregating all the textbox values.

if I write the expression
=Code.Lookup_Sum(Reportitems!textbox22.value)
=Code.Total_lookup_Sum

the output shows true a boolean value.

I tried cint also but no success.

I can't use =sum(Fields!......Value) because textbox is not the part of the dataset.
I can't hardcode like textbox1.value+textbox2.value.

I have googled a lot of …
0
I need to install SQL Server Standard 2014 , the main usage will be to activate the SSRS ( Reporting Services) and have it send automated scheduled emails to client. The main point I need t clarify is : can i install this edition of SQL server and have the SSRS running, on a normal windows OS ( Widnows 7, or Windows 10) .... or must it be a Windows Server OS ?
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.