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 Experts,

We are using Dynamics CRM 2016 on prem.  We have a subject tree built out in our CRM system that contains subject matters for Cases.

Our customer service people are in need of some reporting, and I think to get them the data they need I might need to use SSRS or Visual Studio, but if Advanced Find is an option I'm all for it because it gives them the ability to tweak their results on the fly without involving actually changing queries or reports.  Avanced find is tempting, as it will let me use the UNDER option, which is kinda cool, but I don't think it does enough.  It seems like I can only chose one subject matter at a time to report on.

Anyhow, they would like some reporting that captures each item we have listed as a subject.
They'd like this by year or by month and with a count of occurances.
So for example, I think they want something like this:

YR 2019
       Vendor Issues                     210
       Personnel Issue                    14
       Bad venue experience        45
       Guest Sickness                       6
       Complaint                             178
       Compliment                          57
        etc.


YR 2018
        Vendor Issues              196
       Personnel Issue               13
       Bad venue experience   48
       Guest Sickness               17
       Complaint                       92
       Compliment                   114
        etc.

Yr 2017
       Vendor Issues               …
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

I'm executing a query inside SSRS and receiving an ORA-00933: SQL command not properly ended.  It's just a SELECT statement but the error appears to be in the Where clause.  Am I specifying the parameter, @PaperBill, incorrectly?

Select ACCTUSAGE_MAIN_V.BILLING_DATE
,ACCTUSAGE_MAIN_V.BILLING_CYCE
,ACCTUSAGE_MAIN_V.BILLING_MONTH
,ACCTUSAGE_MAIN_V.BILLING_YEAR
,ACCTUSAGE_MAIN_V.INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER
,ACCTUSAGE_MAIN_V.DESCRIPTION
,ACCTUSAGE_MAIN_V.CNT_LAST_NAME
,ACCTUSAGE_MAIN_V.CNT_FIRST_NAME
,ACCTUSAGE_MAIN_V.CNT_ADDR_1
,ACCTUSAGE_MAIN_V.CNT_ADDR_2
,ACCTUSAGE_MAIN_V.CNT_CITY
,ACCTUSAGE_MAIN_V.CNT_STATE
,ACCTUSAGE_MAIN_V.CNT_ZIP
,ACCTUSAGE_MAIN_V.CNT_ZIP_EXTEN
,ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL
,ACCTUSAGE_MAIN_V.EMAIL
,ACCTUSAGE_MAIN_V.MIN_INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.MAX_INVOICE_NUMBER
From COMIT_CUSTOM.TAMU_ACCTUSAGE_MAIN_V as ACCTUSAGE_MAIN_V
Where ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL = @PaperBill
Order by ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER,ACCTUSAGE_MAIN_V.BILLING_CYCE
0
Hi,
I have opened an RDL file in Visual Studio 8, when I preview the report, I get an error message  “The selected Data Source cannot be found”.
When I go to the DataSource properties, it tells me to verify that the DataSource be included in the Project.
I do not have the rds file.

Within the Report Server Database I can see an entry for the Datasource in a table DataSource.
Is there someway I can extract this database entry into an rds file?
any guidance appreciated.
Thanks
0
Hello All,

I am using the following expression to calculate percentage in SSRS,
=Format(Fields!PCTanswers.Value/100,"0%")
however, sometime the calculated values aren't equal to 100%  they are 99, so the rounding is not working correctly.
Can someone please help?  Thanks.
0
I am designing a SSRS matrix report that shows a list of people and people on their team in categories. For example

                 |CAT1      |CAT2      |CAT3
JOE           |SAM       |BILL        | MIKE
ALAN        |SAM      |ADAM     |  
DOUG      |               |BILL        |STEVE


This much works fine. But sometimes there are two people in a category for one person. So if there are 2 cat2 people for ALAN I need:

|                 |CAT1      |CAT2       |CAT3      |
|JOE           |SAM       |BILL        | MIKE      |

|ALAN        |SAM      |ADAM     |                |
|                  |              |MARK    |                |
|DOUG      |               |BILL        |STEVE     |


How do I get that textbox to expand so that it displays both values?

I am using Visual Studio 2015 and SQL Server 2016.

Here are some details that may make the question clearer. There are four tables involved. Here are the table and the relevant fields:

ManagerTable
ManagerID
ManagerName

WorkerTable
WorkerID
WorkerName
WorkerCategoryID

Category Table
CategoryID
CategoryName

ManagerWorker Table
ManagerID
Worker ID

The query results look like this:

ManagerID, ManagerName, WorkerID, WorkerName, CategoryID, CategoryName
1, JOE, 1, SAM, 1, Cat1
1. JOE, 2, BILL, 2, Cat2
1, JOE, 3, MIKE, 3, Cat3
2, ALAN, 1, SAM, 1, Cat1
2, ALAN, 4, ADAM, 2, Cat2
2, ALAN, 5, MARK, 2, Cat2
0
I am creating a chart inside my SSRS table.  In my table I have ClaimAccount, Quantity, BillingDate and ChargeComments.  I am creating a report that groups on ClaimAccount.  But for a few accounts there are multiple records per month so I want to split out those reports while grouping the others.  ChargeComments has project details that can use for grouping.  Specifically, I want to group on the part of ChargeComments that starts with a slash and all the way to the last letter of the string.  This section of the string is variable length.

How do I add a group in SSRS based upon this section of ClaimAccount that starts with a slash?

Example:

ClaimAccount     BillingDate    ChargeComments
000450                 01012018     DOIXM/agribix2348             this is one group
000450                 01012018     CIXMS/bourlagzip7              this is one group
000520                 01012018     DASFALKG/zip3                    this is one group
000520                 02012018     DASFALKG/zip3                    this is part of the group above because it has the same ClaimAccount and same BillingDate
0
Hi Expert,

I can launch the SSRS url. When I try to open the report I got the following error.

Could not load folder contents
You are not allowed to view this folder. Contact your administrator to obtain the necessary permissions.

Please help

Thanks

Zahid
0
We have a fresh installation of SQL Server 2017 & SQL Server Reporting Services (SSRS) on a Windows 2016 server.

I am a domain admin and have local sysadmin access within SQL.

I am not able to see the tabs when opening a browser to http://servername/ReportServer.  So I am unable to configure the site and reports for users.

The DBA did do a restore from an older database (2008), so I am able to see 2 folders and their reports (content); however; I am unable to configure the site.

I've added to Trusted websites, ensured that UAC is off and local server firewall disabled.  

Any thoughts on how to get SSRS 2017 working on Windows 2016 so I can configure it would be appreciated.
0
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
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
CompTIA Cloud+
LVL 12
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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
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
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
1
Become a Microsoft Certified Solutions Expert
LVL 12
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).

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
1
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
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

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.