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,

I am using the "iif"-function in SSRS. How is it possible to show nothing, if the logical test is wrong?

I.e.
IIf(Parameters!totaldager.Value=0,Parameters!FVDager.Value,"")

I am getting the right values if the logical test is true, but I am just getting #Error - messages if it is false.

Any suggestions are much appreciated.

Thanks
Daniel
0
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I have a web application that uses ssrs 2016 for reports.  I am looking at moving the sql database to a nosql database, but don't know how to maintain the reports I have created.  Any ideas?
0
Hi,

when setting a parameter I chose "allow multiple values". I took this parameter as the basis for the filter in the report, but did not manage to choose multiple values in the report. Whenever I choose more than 1 value (or "select all"), the report shows only the 1st value of the filter.

In my case (see attachment) the report is showing only the value "500".

Thank's for your help
Daniel
0
I have created a link to a SQL report and passing a parameter that is hard coded.
The URL is working and the parameter is being passed successfully.
rs:Command=Render&username=NHC

Instead of hard coding the username I want the userID to be picked up automatically.
Then the link will work for any of my users.
0
I had this question after viewing SSRS:  How to add a group header/footer to an already existing group.

Hi, there is the answer on how to add the group header/footer. Can you explain how to delete it too?
Many thanks
Daniel
0
I'm trying to write a expression in the fill to  get colors to appear in 2 different groups. If a person has been here more than a year I want there date to turn white if they have been here less than  year I want there date to turn Orange. When I run my expression I keep getting errors or no colors will show. Below is my Expression

=IIF(DateDiff(DateInterval.Month,Lookup(Fields!AgentID.Value, Fields!AgentID_FON.Value, Fields!BLStartDate.Value, "BLDate"),
Parameters!OverallStartDate.Value)>=12,"White", "Orange")
0
Good Morning Experts!

My dilemma does not "seem" to difficult to solve.  Unfortunately I have spent too much time trying to find a solution. Can you help?

I have a drop down list for the the User to select a numerical value or "All" to get data for all of the numerical values.   I am envisioning the coding for this happening at the end of the where clause.

...AND   If @year <> 'All' (meaning a year has been selected) then y.year = @year
              If @year = 'All' then I don't want to restrict by the year...just get all records

This "feels" like a case statement but I have had no luck.  Do you have any suggestions?

Thanks,
jimbo99999
0
I’ve installed SQL Server 2016 Standard Edition on Windows Server 2012 R2.  I’m trying to install Management Studio on the same server but I keep getting the following message:

Setup Failed
Fatal error during installation (0x80070643)
Microsoft SQL Server Data-Tier Application Framework (x86):  Error writing to file:  C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\Microsoft.SQLServer.Management.AlwaysEncrypted.AzureKeyVault Provider.dll.  Verify that you have access to that directory.

I have full access to all directories, however there is no C:\Program Files (x86)\Microsoft SQL Server\130\DAC folder.

I’ve tried multiple versions of Management Studio and they all come up with a variation of the above message.  The above message was 16.5.3

.Net Framework 4.6.1 is already installed.
0
Hello experts,

in SSRS 2008 r2, I used to be able to create data-driven subscriptions. As a matter of fact I have quite a few reports with and without existing subscriptions that I can use data-driven subscriptions.
Now, after getting the failed error message, I decided to have a very simple query in the report.. something in the line of    select top 10 * from ACME.
And using a functional shared data-source.
here it comes.

When I upload this functional report to the server and getting to "manage/subscriptions" I cannot use the data-driven.
The error is :
data-driven subscriptions cannot be created because the credentials used to run the report are not stored, the report is using ser-defined parameter values, or if a linked report, the link is no longer valid.

> do not have parameters
> it is not a link
> using a good valid data-source that 100+ reports are running off of

Any ideas what can cause the server to act up like this?
0
I have a WPF MVVM application that I'm porting from a WINFORMS existing app. The reporting of the WINFORMS app uses both SSRS & CrystalReports for reporting.  Looking to convert these report in the MVVM pattern. I want to produce the reporting output on the server thru the Web Service and send back a link to the client ReportViewers. Does anyone know if possible and if so any examples?

Thanks
Chuck
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: 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.

how to generate SSRS report /consume in WEBAPI with parameters
 ssrs report  in web api and display in angular js
0
User come up to me this morning and told me that he can access http://server/ReportManager. When you got the webpage, you get 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

I logged into the server and tried to access the reporting services from SSRS and get the error below:
Unable to connect to the server at Server. The specified URL might not be valid or there might be a problem with the report server version or configuration. Specify a different URL, or contact your server administrator to verify that the report server runs SQL Server 2008 or later. Additionally, if you are trying to connect to a SharePoint-integrated report server, verify that SharePoint is installed on the server and that the report server uses SharePoint integrated mode. (Microsoft.SqlServer.Management.UI.RSClient)


I tried the following to fix:
1)      Reboot – didn’t do anything
2)      Restart services – did nothing
3)      Log files showed an error: library!DefaultDomain!5ac!05/04/2018-08:43:12:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Cannot open database "Report_Server" …
0
Hi Experts,
I need to upgrade my reporting services instance to SSRS 2017 and also require anonymous authentication.  I believe this is not possible with the 2017 SSRS architecture.  Can anyone point me in the direction of a work around to access reporting services with no authentication or no perceived authentication from the web browser perspective.

Thanks Chris
0
I have an 2014 SSRS report Server.  I am not sure about the best way to tackle this so please advise if my thought process is wrong.

I have some android tablets that are hooked up to a hotspot and vpn to our network.  When i browse to the report server in chrome on the tablet, it prompts me for active directory credentials.   I place my AD credentials in there and it doenst work.

My question is this.  Is there a way to set up a folder on my report server that requires no authentication and will allow anyone to use?  Or is there a way to have your android tablet be active directory integrated in some way.
0
Experts,
We use Visual studio to create and edit SSRS reports.
All reports were written using VS 2012 and published to a single SSRS Instance on a SQL 2014 server (InstanceA).
I was required to create a second instance on the same server (InstanceB), as part of our ERP upgrade
The report files are stored on a network file share.

I am trying to accomplish the following:
- Copy my report files to a different file share
- Upgrade the report files from VS 2012 to VS 2017
- publish the upgraded reports to Instance B
- Keep all original reports active and working on Instance A (until upgrade is completed)

I am sure there is a very easy way to do this. So far I have found about a dozen ways that dont work.
In most attempts I am in the new instance attempting to add existing reports.

Any guidance would be appreciated.
0
Hello,
I have legacy code that is using UNION at least 12 times.  It is joining on the same tables. The performance of this query is over 10 mins.  I need to make it more efficient.  Any thoughts??

-- set first day of week to Monday
SET DATEFIRST  7 ;

DECLARE @sof
SET @sof = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

SELECT Plant,
      Part,
      4 AS Demand,
      DATEPART(yyyy, CASE WHEN DATEDIFF(dd, Due_Date, @sof) > 0 THEN GETDATE() ELSE Due_Date END ) AS MRPYear,
      DATEPART(ISO_WEEK, CASE WHEN DATEDIFF(dd, Due_Date, @sof) > 0 THEN DATEADD(wk, -1, @sof) ELSE Due_Date END) AS MRPWeek,

FROM Demand_Forecast mrp
INNER JOIN  MasterPart x ON mrp.plantID = x.plantID AND mrp.PartID = x.PartKey
INNER JOIN  Parts dp ON x.PartKey = dp.PartKey
INNER JOIN  Plants dpl ON mrp.plantID = dpl.Plant_No
      AND dpl.PlantID NOT IN (1, 2, 3)
WHERE mrp.demand_key = 1

The UNIONs only change demand and date column
Demand = 1; Date = Due_Date
Demand = 2; Date = Ship_Date (instead of Due_Date in MRPYear and MRPWeek)
Demand = 3; Date = Receive_Date

and so on.  What would be the best way to optimize this query?  Currently we are looking into a million records multiple times.  It is also returning million + records to efficiency is needed!!!  Would IF statements be better than UNION's?  But we will still have 10+ IF statements.

Thank you for your help in advance.
0
We have SSRS reports that pull from our data warehouse.  The IT team Active Directory group has Read permission to the data warehouse.  However, there are some reports that are going to be used by other staff, not just the IT team, but I don't want to grant Domain Users permissions to the data warehouse.  I see when setting up SSRS data sources I can store a username/password, but it says the 'Credentials stored securely in the report server'.  Is it talking about the Report Server database?  Because Report Server is a different database than the data warehouse, however they are on the same server.  

So at the report level, I will only give permissions via the report security so certain non-IT staff will have access to the report.  But how do I get the report to be able to pull from the data warehouse without adding those certain staff (and the list may grow of who they are) to the permissions of the data warehouse database?
0
Hi I have the following requirement. I am having trouble breaking it down into tasks and would appreciate any input from Power BI and SSRS experts here to do so. I am new to PowerBI and am having trouble even figuring out how to get started on this.

"Initially we want to setup an existing SSRS report via SSRS/ w/Power BI integration

 Using new authentication methods (not sure what’s best, but other people are doing this so no need to re-invent the wheel)
http://www.itprotoday.com/microsoft-sql-server/using-reporting-services-internetextranet-environment

We want to be able to have an extranet user authenticate, Have parameters applied to their user object that only allows them to see data they’re associated with by individual and groups.


This may require an Azure instance of SSRS/IIS to pass credentials (which I’m fine with as we can treat that as a DMZ) we should have plenty of dev azure space with our MSDN accounts.

Some of the things to consider as you’re doing this,..
We eventually want to make a lot of this “self-service”
So a Customer Admin” could add/remove e-mails for trend users get notifications, add/remove logins (restricted to trends data obviously) add/remove those users from groups, add remove pages from groups etc…"

HELP! I don't even know where to begin with this.
0
I have reports displayed on the Reports Manager Web page.
Some of these reports are currently not used and I would like to hide them rather than delete in case they are needed in the future.
There is a Delete option in the menu bar, if I use this does this delete all the code for creating the report?
If it does, how can I just hide the report?
Thanks
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Hello all, I am trying to create the attached dummy file in SSRS. I have the data that populates into a table already. The problem I am having is getting it into the format shown. We are moving to a new platform and our teams have requested that the format remains the same as in the previous system...I have the Stored Proc setup accordingly with parameters I pass to return GL Period and Year along with Departments. I need the report to give me the detail and sum of the different sections(Revenue, Salaries, Other Expense) as shown.  It needs to be separated by Department. So when I produce the report it will show Department A , its related data(Revenue, Salaries, Other Expense, then begin a new page with the next department and so on...Any help out there ?
EE-PL.JPG
0
  1. I need to allow my user to select a column name and its value from a parameter (or two params) to filter the result.
  2. I have a text parameter with a few column names that are listed in my dataset Column1, Column2, Column3. Each of those columns has only two values 1 and 0.
  3. I would love some help in getting an idea how to filter my dataset based on the column name listed in the parameter and a selected value (1 or 0)
  4. I assume it has to be related to a dynamic sql but, not sure how to incorporate that in either the WHERE clause or the actual dataset filter.
  5. Thanks for any points guys!! :)
0
SSRS 2016, and I'm trying to create a subscription.  I keep getting an error that there are no stored credentials.  I've provided credentials to the data source and to the report; SA as a matter of fact; but keep getting the same error.  I've looked at the same report I have on a 2012 instance and that works fine.
0
SSRS 2008 Margin problems when downloading report to MS Word.
Hello, I have some reports that users generate and then they download into MS Word.  When this report is downloaded and
rendered in MS Word, it 'cuts off' the bottom of the data within the report.
I first thought it would be the page, table, header or footer settings, so I changed those or modified those to see if the data
would show.
Interesting facts:
When I try other formats or download, like .pdf, .xlsx, these files render just fine and show all the data, as expected.
Also, if I open the MS Word file after downloading, and then view it in Read Mode or Web Layout, the data also all shows.
Thanks, and the SSRS version is 2012.  (Sql Server 2012)
0
How to add Index and page # for SSRS report. I want to add an Index page as a last page where group by as index name and page #.
0
Is it possible in SSRS 2016 to dynamically set date parameters ?

I want to have two Date Parameters with no default value, Start Date and End Date.

I would like the user to perhaps from a drop down list, Select Yesterday to Today, Week to Date, Month to Date, Year to Date, Inception to Date and have the selection update the Start Date and End Date.

Thank you.
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.