We help IT Professionals succeed at work.

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.

I have a client that runs a number of reports on SSRS.  These reports have been created and tested over the past two years, and have been running great for the last 6 months without problem.

Enter Covid19 and everyone working from home.  Users are now logging into the network via RDS and running the same reports, but many of them are now returning this error message after entering parameters SSRS Error message
The IT guys tell me that they have not done anything to the server, and when I log into the server I am able to run all of the Visual Studio reports without error.
Strangely, when I log into RDS and open the SSRS reports menu, the same way that the other users are doing, I don't get this error.

I do not know how to enable remote errors to get more info about the error.

Would appreciate any recommendations anyone might have.
0
I have a SSRS report and the cells in one column (MarketPrice) can be a number or text "NA". Is there a way in SSRS to have numbers in raw excel show as numbers even though another cell value in that column is text (NA)? Right now the raw excel dump shows the "Number Stored as Text" green triangle for numbers.Column with Number and Text values.
Thanks in advance.

Rob M.
0
I have a SQL table with the following fields:

Customer ID |Fiscal Year | September Amount | October Amount| November Amount ....

I need to change the format so I can produce an SSRS report in the following format by customer:


 Months down the left hand column and Fiscal Years across the top. For example:

 Customer: 1

                         Fiscal Year1 |  Fiscal Year 2| Fiscal Year 3|
September        100.00       |     200.00       | 150.00         |
October              250.00      |     300.00       | 180.00         |
November
December
etc.

For some reason every approach I take leads to a dead end.

Thanks!
0
I have an ssrs report (sql server 2014) that takes county in as a parameter (10 possible counties)

I have a file share set up for each of the counties

Would like to write a single data driven subscription that will write the output for each county to the file share dedicated to the county

is this possible?

All the searching Ive done seem to be for emails
0
Hi,

I'd like to filter my existing SSRS report on creation date. Found the article below, but I am a bit lost on howto implement.
An example of my datetime format is 03/06/2020 10:17:46

Tried to change the query with this
select  Cast('9/10/2013  8:19:29' as datetime)  DateCreated
UNION
select  Cast('10/10/2014  8:19:29' as datetime)  DateCreated
UNION
select  Cast('11/10/2015  8:19:29' as datetime)  DateCreated
where Year(DateCreated)=@Year and Month(DateCreated)=@Month

Then I got the error:
Must declare the scalar variable "@Year".
----------------------------
Query execution failed for dataset 'MyDataSet'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)







---source which I used
https://social.msdn.microsoft.com/Forums/en-US/afdaa9d9-e5fd-4a15-94e2-3a87b332c9b5/how-to-filter-the-ssrs-report-by-year-and-month?forum=sqlreportingservices

select  Cast('9/10/2013  8:19:29 AM' as datetime)  LogTime
UNION
select  Cast('10/10/2014  8:19:29 AM' as datetime)  LogTime
UNION
select  Cast('11/10/2015  8:19:29 AM' as datetime)  LogTime
where Year(LogTime)=@Year and Month(LogTime)=@Month
0
Hi,

I have this query

select  Name,Entitlements, LastWriteTime
from table1
join table1 ON table2.Name = table1.Name

This gives values with multiple timestamps

F.e.
Name Entitelments,LastWriteTime
Item1 xyz            02/02/2019
Item1 xyz            03/02/2019

How do I get the LAST write time only, so that would be Item1 03/02/2019
0
Hi Expert,
I am getting stuck after restore the SSRS Database from 2008 to 2016. When i attempt to get into SSRS on the new Server it mentions the database is at lower version.

Thanks

Zahid
0
I have a sql server 2014 a separate ssrs report that returns the following sales info with region (hard coded) as a parameter with a standard subscription that writes the file to a file share

Region, salesperson_firstname, Salesperson_Lastname, Sales_total

Currently have 5 regions but being told new region numbers could be added

was thinking of creating a new ssrs report that did not take in region as a parameter (just create single report for all regions)

then Try to write a data driven  subscription that will write the information for each region in an excel file to a file share with a file name that indicates the region

for example, region 1 records would be in a file named region_1_sales_info.xlsx,

Region 2 records would be in a file named  region_2_sales_info.xlsx and so on.



Just wondering if this is doable?

Hopefully I have described the situation clearly

Thanks!
0
Lost our DBA so now during transition, trying to fumble through some things.  One being modifying existing SSRS reports.  I have a report that I simply need to add a new column for postal code next to service address.. I think i found the right table in the DB.  Trying to use report builder.  see attached
1.PNG
2.PNG
3.PNG
4.PNG
0
What are the proper database roles to be granted to users to access reports being delivered via SSRS? I have an application that deploys a series of SSRS reports. The client wants security setup using their domain credentials.
0
I have a SSRS report where in one text box I want to only display part of the FIELD that populates the textbox.
For example, if the text in the field is "LASTNAME, FI - ID", I want to display only "LASTNAME, FI". So in the query I have Select LASTNAME + ', ' + LEFT(FIRSTNAME,1) + ' - ' + ID.
I can't just change the SELECT statement to leave off ID because I need that for grouping and sorting.
I changed the placeholder properties to recognize HTML tags
Something like:
Select LASTNAME + ', ' + LEFT(FIRSTNAME,1) +<font size= "3">' + ' - ' + ID + '</font>'
works, but I can't find a way to hide the ID
I tried:
Select LASTNAME + ', ' + LEFT(FIRSTNAME,1) +<div style="visibility:hidden">' + ' - ' + ID + '</div>'
References say that only certain inline CSS attributes are supported, so visibility must be one that isn't.
Any ideas, other than using one field for sorting and another for display.
0
Hi,

In ssrs I get diff from two dates.
How do I get also hours and minutes (now some results show 0 since diff is hours minutes only.

Thanks!
0
Hi,

I'd need a sql query to color code a date:
Items = status NOT equal to 'Status01', 'Status02'
For all items which have date less then a week => green
older then a week => orange
older then 2 weeks => red
Items which have Status Status03
and have date
older then 2 weeks orange
older then a month red

Thanks for helping with the approach!

J
0
Hi,

I mail my ssrs report on a daily basis. Opening it in mail (mobile device) takes 30 seconds, so people think the mail is not working and delete it.
Also loading it on Windows, it still takes like 3 seconds to load.
How can I make my report more leightweight?
Suggestions: show less items on a page (there are a few 100 rows now, howto?), leighter formatting (like I have gridelines for better visibility ... howto?) ...?

J
0
Hi,

I have an SSRS KPI in which I have totals like the following expression:

=Sum(IIf(Fields!StatusName.Value = "Status01", 1, 0))

Now, how do I make more difficult queries: like
sum of all items where statusname.value = status02 and status03 but not empty status but also status06,status07

J.
0
Hi,

I have an ssrs report for a ticketing system which has names, ticket number etc.

I d like to add several filters:
-show my tickets = filter on username, all tickets
-environment: UAT/PRD
-statuses: open/not resolved, closed

This way a user of the form can see/filter on only specific data like.his requests which are open in prd

Please advise.
J
0
I am using this data set query that is generating error , The dated diff function requires 3 arguments(s). Incorrect syntax near Label 1. Incorrect syntax near 'Group Order'. The Query generates data for a DemographicsPar2(parameter. I am building a ssrs has board that uses this parameter.

SELECT DISTINCT 
                  'Age' DemographicGroup, 
CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN '<=25'

 WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN '26-35' 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND   45 THEN '36-45' 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN '46+' END Label, CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN 1 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN 2 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND 45 THEN 3 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN 4
END GroupOrder

INTO        #Demo2
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Marital Status', MaritalStatus, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Race', Race, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Sex', Sex, 1
FROM     [dbo].[Dim_Borrower]
                      SELECT *, DemographicGroup + ' - ' + Label RealLabel
                      FROM     #Demo2
                      WHERE  DemographicGroup IN (@Demographics1)
                      ORDER BY DemographicGroup

Open in new window



When I run this query on the SSRS query Designer, the output shows Three columns. DemographicGroup / Label / GroupOrder/ Real Label ..
0
Hi,

I have an ssrs report which I would like to make dynamical (like a Powershell Crusties): when you click ok a column of sorts, when you search, it filters, ideally have also search filters and some nice formatting too (now it is a boring formatting).
One more thing: can I link to another ssrs report (clickable link)?

J
0
Hi,

I have a GUI which talks to a sql db backend via powershell and sql queries.

I execute queries remotely but now discovered stored procedures and views.
Created some views and a stored procedure.

What is the specific purpose of each item, the difference and what should I use for my gui?
Also, can I use the view for ssrs report?

J
0
Hi, is there anyway to Stop SSRS from Building ALL reports when Previewing in Visual Studio ?

Many Thanks
0
I recently moved SSRS to a new server.  The migration went fairly well and most of the reports are running fine on the web server.  I've been going through each project to point the project  to the new report server location and then updating the connection string on shared data sources to again point to the new server.  Almost all of my data sources point to one of 2 catalogs (one for finances and one for fundraising) and I have successfully updated many of the projects without an issue for both catalogs.  However, I have a folder that uses both data sources.  I've made all the changes to point to the new server and when I test the connection string, it is successful.  However, if I try and run the query for the financial data source, it errors out with the following error (last paragraph).  If I turn on the old SSRS server, it works fine.  I've checked and double checked that everything points to the new server and it does.  Again, this data source is used in other projects and works fine there - just not in this one project.  I've even deleted and recreated the data source and it still won't work.  I can't figure out what the problem is... and feel the error is misleading.  

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - …
0
Hi Experts!

Have a simple report that display from a sproc. The sproc is like this (the fields are varchar(16) for SomeTable:

SELECT FirstName,
       LastName,
       '0' AS MealCost,
       ISNULL(CAST(SUM(Field02) AS VARCHAR(16)), '0') AS Field02,
       ISNULL(CAST(SUM(Field03) AS VARCHAR(16)), '0') AS Field03,
       ISNULL(CAST(SUM(Field04) AS VARCHAR(16)), '0') AS Field04
FROM dbo.SomeTable

Open in new window


Here's the report layout: The Total is added after the group.
Report Layout
Have tried these for the MealCost and the total comes out as #ERROR:
=SUM(Fields!MealCost.Value)
=IIF(IsNothing(Fields!MealCost.Value), 0, Fields!MealCost.Value)
=IIF(SUM(Fields!MealCost.Value) IS NOTHING, 0, Fields!MealCost.Value))

Open in new window


Here's how the report should look like (I'm using Excel as an example)
Error In Todal
any ideas?

TIA!
0
How to convert sqlserver database schema to no sql database such as Cassandra or mongodb. Is it some api can be used to convert or use database structure to do that or any other way.
0
I was having a heck of a time trying to create a report in Visual Studio 2017.  I removed references to version 9 and installed 150.1400 from nuget and added the references back.  I was never able to get a report viewer onto a form.  It always ended up at the bottom as a component like the timer or backgroundworker.  I uninstalled 2017 and installed 2019 after hours of unsuccessful google fu trying to fix it.  2019 allows me to drop a report viewer onto a form but I am missing the add new item - report option.  So now I can drop a report viewer onto a form but I can't add an RDLC to my winforms project.  I may go back to VS 2010 where things just worked...This is crazy frustrating.

Any ideas on getting the report creation option into the menu?

Thanks
screen shot references
0
Hi,

I d like to have a daily or weekly sql ssrs report of ALL software users have on their pc (add/remove programs?) by querying our sccm 2012 server (latest version) plus number of installs per item/preferably also clickable that it shows the list of pcs per software.
Nice would also be to map it to sccm.itself: do.we have it in sccm (or was it installed nu user manually).

I d like to be able to exctact the data in Powershell so I can work with it. F.e. $alllsoft | get top 10 foreach ...

Thanks!
J
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.