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

Does anybody have any projects or insight on upgrading the SQL Server source databases to 2017 and still use existing SSRS 2012 .rdl files? We need to upgrade our OLTP environment and do not currently have enough SSRS report developers to get the SSRS part done by the due date. Our thought was to leave the current .rdl files at 2012 revision level and just upgrade the OLTP source database to 2017. We will not be upgrading the SSRS databases or the SSRS engine.

Thanks experts!
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hi all,
I'm trying to build a graphs in ssrs report , when I test to receives this report I'm curious why  I can view all  namesgraphs_ssrs_email.PNG which only ssrs present me 3 names  ( managersemail ( those marked as dark ) where the actual  values I should be able to have 100 of them here.

This is how the report looks like in ssrs report builder graphs_ssrs.PNG
0
Hi Experts,

  I'm receiving the error below after changing a single-value parameter to allow multiple values and I have not been able to get past it for weeks. Oddly enough, I have another parameter (@state) that I've set to allow multiple values, and it works perfectly fine.

Under the error is the primary data set code, as well as the ones used for suppliers (@Manufacturer) and states (@state). I have also included a number of screenshots that show the column visibility expressions, the single tablix filter for the ship2_state column and screenshots with the errors, column filters and parameter details.

I have attempted to use joins - e.g. =IIF(Join(Parameters!QueryParameterType.Value,",").Contains("1"), False,True) or =Switch(

Parameters!QueryParameterType.Value(0) = "2", False,

Array.IndexOf(Parameters!QueryParameterType.Value,"1") > -1, False,

True, True

)

and nothing is working. I really need someone to help take a look and show me what I'm missing. I've outlined everything in the SSRS report.

ERROR:

The Hidden expression for the tablix ‘table1’ contains an error: Overload resolution failed because no Public '<>' can be called with these arguments:
    'Public Shared Operator <>(a As String, b As String) As Boolean':
        Argument matching parameter 'a' cannot convert from 'Object()' to 'String'. (rsRuntimeErrorInExpression)


DataInfo dataset source (main body of report):


IF @QueryParameterType=1
(
SELECT …
0
This is something that I've had trouble with over the years.   In the script below I need to move the date script in the where statement into the select.  so I want to the the last 365 days for po_count and line_count.  This way I see all suppliers even if they have a 0. The issue is the group by as I don't want to add date_created into the group by or it make a different line for each date and I want the sum so one line per supplier.

This is my case statment but requires me to put po_hdr.date_created into the group by which is not what I want.  

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_hdr.po_no) else 0 end AS po_count

Open in new window

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_line.po_line_uid) else 0 end AS po_count

Open in new window



SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name       AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN po_hdr
               ON p21_view_supplier.supplier_id = po_hdr.supplier_id
       INNER JOIN po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  ( p21_view_supplier.delete_flag

Open in new window

0
Hi All,
In Report Manager I have assigned a user to the browser role at the report level  of a single report (and also to the browser role of a folder) but she cannot see the folder or  report in her browser.
Here is what she can see
rs The Folder and Report are both set to  not inherit Parent Security.
The user is assigned to the Browser role in the Home Folder.
If you require any further information please do not hesitate to ask.
Any Guidance appreciated.
0
Hi I have a column named TEST in our database s varchar(20). Now in SSRS report i have added this
column as NUMBER and when i try to export the report to excel , i can see an error in excel called
like "the number in this cell is formatted as text"

i then used the expression =CDbl(Fields!Test.Value) in SSRS for that column and the excel issue
is now resoloved but the numbers starting with 0 , 0 is removed..

for example if a value is like 012435, then in the preview tab i am getting 12435

can any one suggest a solution for this ?

Many Thanks
0

Dynamics 365 9.1,  

We are working on converting SQL SSRS reports to FetchXML for the Dynamics 365 Online environment.  We have completed most of a report but still have the following to convert:


SQL
select Value
from FilteredStringMap
where FilteredViewName ='FilteredFEI_nanoportdemo'
and attributename = 'fei_nano_nnplocation'

Open in new window


FETCH
<fetch mapping="logical" version="1.0">
  <entity name="StringMap">
    <attribute name="Value" />
    <filter>
      <condition attribute="ViewName" operator="eq" value="FEI_nanoportdemo" />
      <condition attribute="attributename" operator="eq" value="fei_nano_nnplocation" />
    </filter>
  </entity>
</fetch>

Open in new window


We can query the Stringmap in SQL and can see it Fetchxml Builder but cant link the stringmap/option set to the report

0
Hi, I am running an SSRS report using SSIS  and i have a parameter in SSRS with 4 values.

UK
germany
france
ALL

Now in the ssis script task, i am succesfully able to generate a report for UK, france and germany
seperately but when i try to use ALL , its failing.

below url works fine for UK --

http://ReportServer?%/&rs:Command=Render&rs:Format=CSV&Portfolio=UK

below is what i am using for ALL in my url which is failing

http://ReportServer?%/&rs:Command=Render&rs:Format=CSV&Portfolio=UK&Portfolio=germany
&Portfolio=france

can someone please give let me know the write expression for ALL so that i can get a report for all
3 countries in one report ?

Thanks
0
I am working on an asp.net c# web application that creates a report and saves the report as a pdf. The report could reach up to 140 pages and is already over 22 MB at page 35. Is there any way to reduce the size of these files as they will then be retrieved and emailed. The application is asp.net webforms using ms reportviewer.

this is the code I used to save the report. I created a LocalReport in the code behind instead of using the reportviewer control beause we don't need to display the report.

            private void SavePdf()
            {
                  var path = GetReportPath();

                  var bytes = _localReport.Render(
                        "PDF", null, out string mimeType, out var encoding, out var filenameExtension,
                        out var streamIds, out var warnings);

                  using (var fs = new FileStream(path, FileMode.Create))
                  {
                        fs.Write(bytes, 0, bytes.Length);
                  }
            }

this is the code to create the report before saving it as a pdf.

            private void RenderReportForSaving()
            {
                  var reportDataSource = new ReportDataSource("DataSet1", base.GetDiscipleshipReportView());

                  try
                  {
                        var path = Server.MapPath($"~/Administration/Reporting/Reports/{_reportType}.rdlc");
                        var report = new LocalReport();

                        _localReport.EnableExternalImages = true;
                        _localReport.ReportPath = path;
                        _localReport.DataSources.Clear();
                        _localReport.DataSources.Add(reportDataSource);

                        AddSubQueriesToLocalReport();

                        _localReport.Refresh();
                  }
                  catch (Exception ex)
                  {
                        …
0
IN SSRS, we have a AD new security group that has users with browser permissions. However when members in this group try to run reports we get this error..

The permissions granted to user 'DOMAIN\testit' are insufficient for performing this operation. (rsAccessDenied)

If we add these same members of the AD group individually with browser permissions, it works fine.  What are we missing on this AD security group?

SSRS is running on SQL2008
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

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

D365 Online 9.1, SSRS FetchXML

We are working on converting a report to FetchXML.  We started with a new report and added a MSFetch datasource.  However, when attempted to create a dataset from the datasource, we get the error:

Invalid URI: The Authority/Host could not be parsed.
----------------------------
The URL "https:\msddev1.crm.dynamics.com" that you specified is invalid. Specify a valid URL, and try again.

We added the organization unique name from Customizations :: Developer Resources but still get the error (i.e. https:\msddev1.crm.dynamics.com;msddev1)

Your help is appreciated.
0
Using SqlServer Db 2012 and ReportBuilder 3.0:
Using an embedded dataset calling a sproc with 7 parameters
Stanard Tablix Report with headings and Columns generated from the fields in the Sproc

Running this produces  3 data row and the heading however the data is blank but running same sproc in SqlServer Management Studio produces the correct 3 rows of data with content.

I know it must be something to do with my definition in the Tablix but I have:
1.  deleted and re-created to no effect.
2. Verified the parameters are passing data - and they must be as it is clearly returning the data but for some reason can't display it
3. Tried running in the report on the Query Designer providing those same parameters and DO get data back there!

obviously I know the problem is in the Tablix but I am at a loss to know what else to do!
Why can I not get data in the Report??

Any Ideas?
Screen-Shot-2019-03-28-at-11.59.46.png
0
Hi Experts

I am using SQL Report Builder to create this report.

It is grouped by the first 3 characters of the Account ID i.e. =Left(Fields!AccountID.Value,3)

I have just been asked to move accounts 102-010-07 and 102-010-13 from the 102 Account Group and have it moved to the 101 Account Group under the "Subtotal - Tickets" where it will have its own subtotal and be part of the 101 total.

How would I accomplish this?

Screenshot_2.png
0
Visual Studio 2012, Report Authoring Extensions


We have VS2012 and VS2015 installed locally with SSDT installed.  We are trying to install the Microsoft Dynamics Report Authoring Extensions to enable Fetchxml as an option in the datasource type.  Regardless of the version of the Report Authoring Extension download, we get an error when trying to add the Report Authoring extensions.

Need advice on which version of the report authoring extensions to install.  We have reviewed the internet extensively for an answer to this problem.

Thanks
0
I am working with an existing .RDL. An additional requirement was introduced. Based on a report parameter a section of the report is not visible. This is leaving a blank spot in the report that want to collapse as it throws off the rest of the report formatting.

Is there a way to completely collapse rows that are not visible?

Example:

This is set at Tablix Row Level:

=IIF(Parameters!RunInLocalCurrency.Value = "0" And Parameters!ConsolidateBroker.Value = "1" And Parameters!UnconsolidateBroker.Value = "0", False, True)

When ConsolidateBroker.Value = "1"  I want to compress those rows.

Thanks,

Robert
0
CRM 2016 8.1 upgrade to Dynamics 365 8.2.3.8

We  have a CRM 2016 v8.1 server that we want to upgrade to D365 8.2.3.8  The system uses the email router and SRS server and this is an in-place upgrade on Production.  We previously upgraded the CRM 2016 Test system but that doesn't user the Email Router (uses Server Side Sync).

To upgrade the 8.1. system fully to D365 8.2.3.8 including the email router and SRS server, are there any conditions or upgrade order that we should be aware of before starting the upgrade?

Thanks
0
Dynamics 365 9.1

We are trying to convert SSRS reports from SQL to FetchXML.  When running the following SQL through the SQL2FetchXML converter, the site crashes.  Note: the site works on other SQL code.

SELECT Cast(right(year(@StartDate),2) as Varchar(2)) +
  CASE
  WHEN Len(datepart(wk, @StartDate)) = 1 Then  '0' + Cast(datepart(wk, @StartDate) as varchar(2))
  ELSE Cast(datepart(wk, @StartDate) as varchar(2)) End WeekStart,
  Cast(right(year(@EndDate),2) as Varchar(2)) +
CASE
  WHEN Len(datepart(wk, @EndDate)) = 1 Then  '0' + Cast(datepart(wk, @EndDate) as varchar(2))
  ELSE Cast(datepart(wk, @EndDate) as varchar(2)) End WeekEnd

Open in new window


Can you see any SQL syntax errors in the above code?

Thanks
0
SQL SSRS subscription fails with "Failure sending mail: An error occurred during rendering of the report.Mail will not be resent."

SQL Standard edition Version 11.3.6020.0

I have 40 SSRS reports that are working and just 2 that give this error, can anyone help?
0
I need to learn how to document permissions on reports in SQL Server reporting services. Are the permissions all detailed in the databases local to the reporting server itself, and if so are there any SQL scripts that can be used to list all reports, and all users who have access to them? From what I can gather we just have a few 'shared' SSRS setup, which are reports based on databases from numerous different SQL Server instances across the network. Being able to document for each report, what data source it uses as well would be very interesting. Knowing which server roles as well exist in SSRS if that is the correct phrase would be useful, to determine who can do what with each report, and where exactly you can get a report of who has was SSRS roles.

second part of my question, from within a SQL instance, which stores usual user/system databases, can you determine which (if any) remote SSRS reporting services the instances/databases are connected to, and if so, how?
0
PMI ACP® Project Management
LVL 13
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Hi,
I have created a report using visual studio and deployed it to the Report Server.
The Data  Source is configured to use Windows Authentication.
I have deployed the Report and can see it in Report Manager.
I can run the report from the Report Manager URL on the Report Manager server.
However when I try to run the report from a desktop I log into on the domain I get an error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source ‘test report'. (rsErrorOpeningConnection)
Login failed for user An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Report_Run_Frequency'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I do not understand why it is trying to run the report using the account 'NT AUTHORITY\ANONYMOUS LOGON' as I have configured the Data Source to use Windows Authentication.
I am expecting  the Report Server to run this report using my windows login which has the Roles Browser, Content Manager, My Reports, Publisher, Report Builder assigned to it
Any guidance appreciated
0
I have a new server that has sql 2017, ssrs 2017 installed and ssdt installed.  I think i goofed something up.  When using ssdt with Visual Studio,  there are no projects available to start for Reporting Services.  I have a feeling i goofed up on the the install.
any suggestions.
screenshot.JPG
0
I have a 2012 Report Server that is currently our production Report Server.  I have spun up a new report server ssrs 2017.  Is there a way to do a mass migration of the reports from one server to the other.  I was able to download one at a time and then upload it one at a time and then reestablish the datasource on the new server.  It was kindof a pain.
0
Use SSRS to send emails.  I have an application that records inspection results.   There will be a column in a table that change.  Inspection result will go from "null" to "pass" or "fail"
when this field change occurs.  I would like for ssrs to send out an email to an email address configured for the customer on that inspection table.  Is there a way to do this.  The application itself has a rudimentary way of doing it but i dont like it.

I'm thinking some sort of trigger needs to be initiated and then ssrs email the user... any insight or document for the ssrs process would be awsome
0
Have an SSRS report with 2 columns that calculate percent using expressions. I'm receiving an #ERROR when dividing 0/0 or 0/1. I've tried a few iif expressions, but nothing worked. It's quite possible I simply had errant formulas. Below are the expressions I'm using and attached is a screenshot of the results with errors.

In advance, thank you for all of your help on this!

=Fields!PreYr_Direct_Line_Count.Value/(Fields!PreYr_Regular_Line_Count.Value+Fields!PreYr_Direct_Line_Count.Value)



=Fields!PreYr_Direct_Line_Values.Value/(Fields!PreYr_Regular_Line_Values.Value+Fields!PreYr_Direct_Line_Values.Value)
Supplier_Line_Sales_Divide_by_Error.png
0
Have a report in SSRS that I've added a link to another report within a textbox using "Go to URL" because wanted to open in new tab. The below code will open the report but doesn't pass the parameters:

="javascript:void(window.open('https://servername/RsMgt/Pages/Report.aspx?ItemPath=%2fInventory+Data%2fActivity+Lifespan&rs:Command=Render & Fields!Location.Value & Fields!SN.Value'))"

Any ideas what I'm doing wrong?
on SQL Server 2008 R2.
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.