[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 have an ssrs report that displays open help desk tickets.   I have a parameter called resolved.   The column that resolved points to in the database is either populated with a code of  'res' or a blank.  

I want the user to be able to choose 3 options for this parameter,
Resolved  parameter
option 1 - Yes  - this one works
option 2 - No - this one works
option 3 - both - this one doesn't work


I can get option 3 to work.  Refer to option123.jpg attached

Here is how I have the parameter set up in ssrs.    refer to parameter.jpg
as you can see I tried to put 2 values separated by a comma.  I have no idea if this is the right way to tackle this?

any ideas
parameter.jpg
option123.jpg
0
OWASP: Threats Fundamentals
LVL 12
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

We create VMs with the latest build of our code on. One VM has a weird ASYNC_NETWORK_IO error when running a SQL SELECT statement. It isn't on any other VM.

I have tried googling for the error - clearly something failed to start or get set when this VM was created.

Just for peace of mind it would be really useful to know what might be the cause of this and if there is some setting I can adjust to fix it.

The symptom is that a SQL SELECT statement just seems to time out with this ASYNC_NETWORK_IO error, but the strange thing is that it must have somehow returned some results because it then blocks an INSERT statement that has used the results of the SELECT statement.

Everything necessary for the application is on the VM - the ASP.NET application and the SQL Server database so there is no actual network access outside of the VM.

There is no antivirus running on the VM either which is one possibly cause I came across.

The server is Windows Server 2016 and the SQL Server is 2016 also.

I've attached a picture of what is happening.

Just to repeat this doesn't occur on any other VMs with the same software on.

I have tried restarting IIS and restarting the VM.

The VM is Hyper-V.

ASYNC_NETWORK_IO error
0
I just upgraded a series of SSRS reports from SQL 2014 to SQL 2017. For some reason on two of these reports SSRS is inserting page breaks in between the groupings. There are no breaks and there were none when displayed using SQL 2014. What can I can examine that would explain this? I am using VS 2017 Community to deploy the reports.
0
Hi,

I have found the following code to render an SSRS via a SSIS script. This script works as it is intended, e.g. saves the rendered report to disk.

But I would like to send the rendered report as an email attachment, without saving it to disk. Please could someone help? I have tried to render it to a memory stream but I can't quite get it to work.

Thanks, Greg

 Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
        Try
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub
    

Open in new window

0
I have series of SSRS projects that I have moved to a new server. I am using VS Community 2017.  For the vast majority of the reports I get the prompt to upgrade and all goes well. For a couple of these reports all I get is this project is incompatible and The application is not installed. The few where this does not work tend to be newer projects that were fine using VS 2015. What can I check to get these reports to upgrade so I can deploy them? I tried deleting the rptproj.user fie but that did not help.
0
Hi,

We have MS SSRS 2016 configured on Windows Server 2012 R2. Quite frequently we have to end the "ReportingServicesService.exe" process via the task manager and then restart the service because the reports just show the loading circle when rendering. The SSRS portal itself is OK and the end users can browse through folders and see all of the reports but when they select ANY report it just hangs at rendering.

If we try to stop the "SQL Server Reporting Services" service via the SQL Server Configuration Manager it does not stop. We have to end the process via the task manager and then restart the service. Normal service then resumes for a few days.

I have had a look through the logs in "C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles" but I cannot find anything useful, but then I do not really know what I am looking for.

Has anyone come across this issue before?

Thanks, Greg
0
I want sum of Count column, can you please share expression for this or how to do this? the number of rows of date and count column is not fixed, it will vary every day.Untitled.png
0
What is the proper combination of SQL Server roles and User Mapping in order to grant a user the ability to run SSRS reports? I only want them to be able to run reports not make any changes to the reports.
0
I have a one SSRS report in that there is filter with start date and end date. Now I want to change the format of the calendar which opens when I select date.

Can any one suggest me how I can do that ,now the Date time format of the calendar is "dd/MM/yyyy" I want this should be "MM/dd/yyyy"

Thanks in advance.
0
Has anyone seen this SSRS report function?   "intSuppress".  It is used in an expression.

Example:  =Code.rptFunctions.intSuppress(Fields!HEALTHNBR.Value, Join(Parameters!prmCardIncludes.Value))

I am modifying an older 2012 SSRS report and trying to find some information, documentation or examples on the internet and
seem to cannot find any.

Trying to find a definition of this before trying to modify and not to break it!

Thanks
0
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I got the screenshot from my SSRS builder for MS SQL Server.
Inside of the textbox. Can I run sql statement like the following?
all I need is to get the value from =Fields!newleadfirstname.Value and try to run another select statement.

= select lead.firstname from leads where lead.firstname=Fields!newleadfirstname.Valuexxx
0
My report is to display the CPU Activity Percentages of a server at different times.  The user picks the start time and the duration of time to cover and a stored procedure gets the metrics for the report.  I have two parameters, StartTime and Duration.  I have a date picker for the Start Time and have available Durations from 10, 20 up to 120 minutes.

I want to limit durations that when added to the Start Time, will not produce a date beyond the present date/time.

I have the report in a rectangle that is not displayed whenever the user selects a date/time beyond Now().

But I am uncertain how to write the expression for the default and for the available times for the parameter, Duration.
serveractivity.png
0
when we use exists in a query

what is meaning of select x means

select count(order_nr) from xyz where ord_cd="10"
exists(

select 'x' from ABC a where ef=ge
and
a.some_cd='zz'
and
((datediff(...)or (datediff.....))


i checked below link
https://www.geeksforgeeks.org/sql-exists/

how to validate my results are correct or not

how to break above query to see if difference of time is actually correct?
please advise
0
Msg 1013, Level 16, State 1, Line 1
The objects "XYZ" and "ABC" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

wondering what it means by above error

please advise
0
I have billing records that are created on the same day once a month, usually on the 17th of the month.  With Experts Exchange help I created a report that compares the previous two months amounts.  The problem is that I need compare dates where Current Month is any date within the previous 30 or 31 dates instead of Dateadd(month,-1... etc. (see query below).

select ClaimAccount,
           PriorMonth,
           CurrMonth,
           (CurrMonth-PriorMonth) as Difference,
           ((CurrMonth - PriorMonth) / PriorMonth) AS PercentChange
from (
          select ClaimAccount,
                      sum(case when month(BillingDate) = month(dateadd(month, -1, getdate())) then Quantity else 0 end) as PriorMonth,
                      sum(case when month(BillingDate) = month(getdate()) then Quantity else 0 end) as CurrMonth
          from   PlatoReporting.dbo.LaserficheBackupReport
          group by ClaimAccount
          )
         where PriorMonth<>0
         
How can I rewrite this to group dates where month is the last 30 days?

For example if the report is run today and today's date is Dec. 16, 2018 and the most recent records in the database are dated Nov 17, the Current Month's totals would include the NOv. 17 records.  But if today's date is Dec. 18 and the report is run, then the Current month's totals would include records that are dated December 17.
0
SELECT  Professor_Name , Department , Salary FROM SALARIES GROUP BY (Department ) ORDER BY MAX (Salary ) DESC  LIMIT 1
how to modify above query to return either top 3 or bottom 3 earners only ?
please advise
0
https://www.youtube.com/watch?v=IVMfDpCGwK4

as given above video when to use Distinct and when to use group by seems bit does same job
please advise
any good links resources appreciated
0
i am trying below kind of query which should give count less than 30 minutes of difference of time. not able to execute below. How to modify to make it work

select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd <30 minutes
please advise
0
In my stored procedure I am setting the following parameters:
@site varchar(4),
@employeeid INT,
@CommissionLevelID INT


In SSRS, my parameter setting are:
CommissionLevelID is set to "integer" and I have checked "Allow multiple values"
employeeid is set to "integer" and I have checked "Allow multiple values"


But, I get an error message "Error converting data type nvarchar to int" when I select more than one employee.
It works fine when I select just one employee from the drop down.
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

SharePoint 2016 Farm - 16.0.4771.1000

After installation of recent SharePoint update KB4461501, when a new site collection is provisioned, although the feature "Report Server Integration Feature" is active, the SQL Server Reporting Services Content Types are not provisioned / installed. Any ideas about how to correct this situation?
0
Hello EE,

I wondered if anyone has any experience with SSRS report generation using powershell .  I simply need to pass some login credentials and generate the report as an xml . Loaded as a subscription. I tried following below url but having issues. Is there a working sample someone has that they are using that they can share or another powershel module they are using that may be easier.

https://blogs.msdn.microsoft.com/cdndevs/2015/10/14/automate-ssrs-report-generation-using-powershell/
0
Hi EE,

We have recently upgraded our SSIS Server from 2008 to 2014, SSIS services v10 has been kept for some of our older packages. All the SSIS packages on our server are running except for 1, error message below:

Executed as user: BUCR\svc_0070. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:28:10 PM  Error: 2018-11-27 14:29:21.73     Code: 0xC0202009     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E09.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC02020E8     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: Opening a rowset for "[dbo].[View_Aurion]" failed. Check that the object exists in the database.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004706B     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: "view_Aurion" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004700C     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC0024107     Source: Cleanse Aurion employee data for import to Procura      Description: There …
0
i have below query

select * from xyz_table where item_id='12345';

i got results with 10 columns

3rd column is purchase_activities


now i need to sort or order based on ascending date to see first activity at top to last activity at bottom

how to do it
please advise
0
Hi EE,

We have just installed SSRS 2014 upgrade from 2008 and we start the reporting service we receive the following error:

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

Attached is the reporting log error messages.

I have no idea about SSRS, any assistance is appreciated.

Thank you.
ReportServerService__11_21_2018_19_5.log
ReportServer-error.PNG
ReportServer-Error-2.PNG
0
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

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.