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

GUys,
I have sql 2014 ent edition that installed with SSRS, this version of sql servers was actually an upgrade version from sql 2008 R2. Today I have a report that having a row size more than 65536 and somewhat report subscriptions failed to sending this report with following errors.  from what I understood this sql 2014 ssrs should have bigger row limit size.


 
 An updated rendering extension for Microsoft Excel 2007–2010, supported both by Report Builder and the SSRS web rendering controls (both covered in this chapter). The maximum number of rows you can export from an SSRS report to an Excel worksheet is now 1,048,576, the maximum number of columns you can export is now 16,384, and the maximum number of colors supported for generated content is 16 million. The export format is Open Office XML (OOXML), and files are generated as zip-compressed *.xlsx archives.
 
 
 
 
 ▶
 
 An updated rendering extension for Microsoft Word 2007–2010 (and 2003). It also supports the OOXML standard, generating zip-compressed *.docx files. Note that in order to open *.docx and *.xlsx files, Microsoft Office clients must be at version 2007 or later, or you must install the Office Compatibility Pack (available free from Microsoft’s download site)


Failure sending mail: Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 105676. Maximum rows: 65536.Mail will not be resent.
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Hello,

I am preparing exam results reports in SSRS using Visual Studio 2008, and the actual report is visually very simple.

I have attached a simplified version here, and it simply lists subjects, grades, and the total of each grade.

I am trying to add a column at the end, to show the percentage of both A and B grades together, within all grades (A,B,C,D).

So, for example, in English, out of a total of 25 candidates, 14 had A or B, so the percentage of A,B is 56%

I'm struggling to write an expression to do this. I would normally do a expression, consisting of a calculation, and reference textboxes in the report grid
to get the values, but in this case the data is grouped, so I'm not sure what to do.

I can't find any similar example on the web.

I have attached sample of the report preview, rendered report, and the SQL output grid showing all the field names.

Can anyone help, please?

Many thanks,

Jim Dorans

the report in design modethe rendered reportthe SQL output showing file names
0
What I am trying to do is when I change the build/deploy from Production to Test, I want to be able to also change the shared data source reference for all the RDL files.

In the screenshot attached, I have ImpresarioProd as my Data source and ImpresarioProd as my Shared Data source.  I would like to change all the Data source in the rdl files in my sln to ImpresarioTest.  (Minus the bad naming convention of the Data Source, is it possible to do this without going into each rld file and making the change manually?

Screenshot of Data Source and RDL files
Thank you,

Rich
0
Using Firefox I still get asked for username and password after saving username and password when trying to access local SSRS reports. I can see the credentials on the passwords and security section. I am running the latest version of Firefox and we do have an SSL certificate.
0
Hi,
I am using reporting services 2008 and trying to create  a Data Source from  the Report Manager   URL.
My connection string has the correct servername and database name (see below)
However I get the error displayed when I click "Test Connection"
I have selected Windows Integrated security and I know my credentials  are in the SQL_Admin domain group which has all roles for this Data_Source
Any guidance appreciated
thanks
dse
0
Hi Team,

  Having an issue with an SSRS report I've nearly completed. When converting a single value parameter to multi value, I'm receiving the following error:

The Hidden expression for tablix 'table1' contains an error: Overload resolution failed because no Public '<>'  can be called with these arguments (screenshot attached).

I do have a few parameter and visibility filters setup on the tablix and columns (also attached; each address column has this filter).

I'm just not sure what I need to change to make multi-value parameters work properly.

Here is the dataset query I'm using as well:

IF @QueryParameterType=1
(
SELECT Customer_Name
      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    


      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code

           
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = …
0
I have a SSRS Pick List report for my shipping department. It is time consuming to print one at a time. I was tasked in creating a batch to generate a single PDF and the user only needs to send the print job once. I successfully created this data driven report. The only issue is that I get 100(example) emails with one pick report in each. I need one email with one PDF containing all  pages. Is it possible to get this in one file instead of individual ones? I'm using SSRS 2017.
0
Hello Experts,

I need to create a report in MS Reporting Services connecting to a Calendar in SharePoint and get the fields, EventDate, EventID, Event Start Time, Event End Time and Description. If this is a recurrence then I need the recurrence events as well. I tried connecting using SharePoint List but it is not giving the recurrence data.

Please help.

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>Altiris_Calendar</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="Location" />
    <FieldRef Name="EventDate" />
    <FieldRef Name="EndDate" />
    <FieldRef Name="Description" />
    <FieldRef Name="fAllDayEvent" />
    <FieldRef Name="fRecurrence" />
    <FieldRef Name="RecurrenceData" />
  </ViewFields>
</RSSharePointList>
0
Is it possible to make an SSRS column fully conditional? I have tried using the hidden property and the Column Visibility property and the best it does is leave the column blank. I want the column width to go to zero and have all subsequent columns shift to the left. Is this possible?
0
I am setting up my laptop to use SQL Server Management studio, MS visual studio, and SSRS/SSIS. Can someone suggest the best combination of these tools for me to download and install.  I am NOT looking for enterprise/paid/trial version, only something like express version. Thanks.
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hi,
I would like to email an ssrs report to an end user only when there is any record on the report, the report should not print when no data to show.
I am using SQL SP and SSRS.
Thanks.
0
I have the following query
SELECT max([Name])
      --,[Path]
      --,[TimeStart]
      --,[TimeEnd]
      --,[UserName]
      --,[day#]
        --,Created_Date
      ,COUNT(Name)
  FROM [AnalysisReport].[dbo].[SSRSReportRunTime]
  GROUP BY NAME

I have to have the result - how many count each report run total with one time each time cpount although maybe a report may run more than once a day
0
Hi,

I am upgrading a SQL SERVER 2008R2 to SQL SERVER 2016.  I have the DEV SQL SERVER 2016 running.

There are a lot of apps (SSIS, vbscript, SSRS, vb.net)  that call the stored procedures and use FQDN file paths.

QUESTION:
1. How do I test these stored procedures in the DEV SQL SERVER 2016 because they specify FQDN to text files in PROD?

Example:   a stored procedure is executed
Prod imports text file \\prod\import_file1.txt
Prod exports text file \\prod\export_file1.txt
Prod archives the text file by moving   \\prod\import_file1.txt to \\prod\Archive\import_file1.txt

2. an SSIS calls a stored procedure that does DML/CRUD (delete, update, insert).

How do I test the SSIS without going through and changing the CONNECTION MANGER (to text files) and the SCRIPT TASK (for any references to a text file in the PROD folder)?
0
Our DBA did an in-place upgrade of reporting server from SSRS 2014 to 2016. Since then, I am able to access
 https://<rshost>/reportserver/Pages/ReportViewer.aspx ?<folder path with report name as params>.

But I am unable to go to folder directly like https://<rshost>/reports/Pages/folder.aspx?ItemPath=<folder>&ViewMode=List.

It says ' Service is not available The report server is not configured properly.. ' . I was able to previously
0
I am grouping column data by expense_code but sometimes there some subcodes have no records.  

How do I make that column still display and show zero?

In the example (Crystal Report) there were no records that had subcodes 2110, 5155 and 5156.  I want those columns to still display with zeros.
Desired-table-report-output.jpg
report-table-definition.jpg
currentreportformat.jpg
0
I am using SQL server 2012
I want to have a query output with the following columns

report name
report path
folder name
user last run time
name of the user
how many days not run

I am running the following query, but don't find some fields,

SELECT
    C.[Name],
    C.ExecutionTime,
    MAX(EL.TimeStart),
    MAX(EL.TimeEnd),
DayDIFF ( day, MAX(EL.TimeEnd), getdate()) #Day
FROM
    dbo.ExecutionLog AS EL JOIN
    dbo.[Catalog] AS C
        ON EL.ReportID = C.ItemID
WHERE
    c.[Name] = 'Your Report Name'
GROUP BY
    C.[Name],
    C.ExecutionTime

Open in new window

Can someone help?
0
I am using following 2 parameters on my SSRS report:  

@status   ( Current or Closed)
@Fromdate and @Todate

My first query looks for                      status = Current without Date Range
My second Query looks for               Status = Closed with a date range.  

I would like to default null to the date range when a user selects Status = current,  and if they select Status = Closed then provide them an option to select a date range.
Can someone please help.  Thanks.
0
I have a SSRS report with one source, multiple datasets and report parameters.  I added a blank table to the report and started selecting dataset fields for the text boxes but I receive an error that says only fields from the current dataset can be added.

How do I associate this table to the correct dataset?
0
2 separate reports from 1 stored procedure...

I have a Detail version of a query with roughly 8 columns and a 'D' qualifier Column to show its detailed
and a summary version of a query with roughly 4 columns and a 'S' qualifier Column to show its Summarized

What I am hoping is to use 1 single Stored Procedure to maybe call a #TempTable for either the Input of a 'S' or 'D'...is this even realistic?

I am not against creating multiple Stored Procedures that pass results to each other...

Kind of new at this
0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Hi, Just dropping this question again with some more detail to see if i can get a work around.

I am in the process of converting a large number of Crystal Reports to SSRS. I have hit a snag with a number of Running Total issues. we have over 25 reports that need to been converted but they all have a running total as a grand total, typically evaluating on group, outside of the group. In some cases these running values are used in other calculations. It is a nightmare.

As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.

I have read through many links but i can't see how they will work, but i might just have a skill gap, none of them cover where the running total evaluating on group are used as a total only.

I have attached an example of one of the reports that has a running total, it is only one of the possible layouts and ways the previous reports used the running totals. but does highlight the issue i am hitting. If possible i would rather do this within the ssrs report but i am at a loss on how to get it to work.

We are on SQL 2014 but are about to migrate to 2016

Any ideas on a way to get this to work?

Example-of-Running-total-in-Report.JPG
0
I need to create a report based on a sharepoint online list. When our sharepoint server was in house, this was not a problem but I cannot get the connection string to work for sharepoint online. I do not know power BI and frankly don't have time to learn it. If I can get SSRS to connect and I can get my report done in 1/2 hour. Anyone know the connection string to make this work?
0
I am trying to write a ssrs report based on a sharepoint online list. I have tried about 100 different variations of the connection string with no luck. Can anyone point me in the right direction?
0
Hi Experts,

I am using SSRS and I want to create a subtotal on based on the first 7 character of the AccountID field.

So the example attached, I would have a subtotal for the account 101-002-xx,  101-007-xx and 102-002-xx.

Screenshot_1.png
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
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

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.