In SSRS Report How to hide or collapse detailed rows or sub-grouped rows

In SSRS Report how to show classic pivot like Excel report.

In other words how to hide or collapse detailed rows or sub-grouped rows.

Say we have report which shows Estimated Sales and Actual Sales by State, Region and Country.

We have report without any groups as:

Country  Region  State  Estimated-Sales  Actual-Sales.

In this report we need to create Groups as follows:

Country  => Region  => State.

NOTE: While creating groups you can select for Country Parent group and for remaining Child group.

When by default the SSRS report loads what we want is to hide entire row with Country is collapsed and will show when it is expanded.

Make sure it is Tablix.

Except parent’s group properties, navigate to each of the child’s group properties => Visibility section and DO AS FOLLOWS:

  1. select Hide option.
  2. Check on Display can be toggled by this report item. Please select the PREVIOUS ITEM.

 

 

Advertisements

Implementing a custom Rendering Extension in SSRS in SharePoint integrated mode without coding

A rendering extension is a component or module of a report server that transforms report data and layout information into a device-specific format. SQL Server Reporting Services includes six rendering extensions: HTML, Excel, Word, CSV or Text, XML, Image, and PDF.
We can create additional rendering extensions to generate reports in other formats.

We can modify the rendering format of a Reporting Services report by making changes to the RSReportServer.config file located at :

In native mode the files are located at:
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER

In SharePoint integrated mode the files are located at:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

More details can be found here

<Extension Name=”TXTspaceDelimited” Type=”Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering”>
<OverrideNames>
<Name Language=”en-US”>TXT (Space Delimited Text File)</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter xml:space=”preserve”>?</FieldDelimiter>
<NoHeader>true</NoHeader>
<FileExtension>txt</FileExtension>
<ExcelMode>False</ExcelMode>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>

If we want to add or modify the rendering format in a Reporting Services report in native mode, the RSReportServer.config file is still the way to go as described above.

But for Reporting Services in SharePoint-integrated mode, this can be done by using the PowerShell cmdlets for Reporting Services SharePoint Mode, specifically, the New-SPRSExtension cmdlet. The powershell details:

New-SPRSExtension -identity <GUID ID value of the Reporting Services service application> -ExtensionType “Render” -name “TXTspaceDelimited” -TypeName “Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering” -ServerDirectives “<OverrideNames><Name Language=’en-US’>TXT (Space Delimited Text File)</Name></OverrideNames>” -ExtensionConfiguration “<DeviceInfo><FieldDelimiter xml:space=’preserve’>?</FieldDelimiter><NoHeader>true</NoHeader><FileExtension>txt</FileExtension><ExcelMode>False</ExcelMode><Encoding>ASCII</Encoding></DeviceInfo>”

We have to change the double quotes to single quotes in XML tags because the parameter values used in the PowerShell cmdlet are surrounded by double quotes.
This eliminates parsing errors when running the command.

TheGUID value comes from the ID value of the Reporting Services service application when you run the Get-SPRSServiceApplication PowerShell cmdlet as follows:

Get-SPRSServiceApplication

We need to restart IIS on the application server running the Reporting Services service application for the changes to take effect.
To verify, reload the Reporting Services report and check the list of Export options.
The rendering format we’ve added should now be listed as an option.

Reference 1

Reference 2

SharePoint List Attachments over 50MB need more than an increase in Maximum Upload Size – httpRuntime maxRequestLength

Reference

The SSRS report (SharePoint integrated mode) was giving error. While researching for this error came across the above article.

1.Connect to Central Admin
2.Navigate to Central Admin > Application Management > Web Application General Settings
3.Select your web application
4.Set the Maximum Upload Size value to “X” MB and hit OK.
5.Repeat steps 6-7 for all zones for your web application on all servers hosting the web application role
6.Open the web.config
7. Replace the following line:<httpRuntime maxRequestLength=”51200″ />with<httpRuntime maxRequestLength=”{X * 1024}” />

if you’re using SQL storage for your SharePoint content, the maximum useful value for the maxRequestLength should be 2097152 (2GB in kilobytes = 2 * 1024 * 1024). Keep in mind that other limits may affect large uploads, most notably:

SharePoint 2013 correlation id not found on SharePoint farm – Enable job Diagnostic Data Provider: Trace Log and query view ULSTraceLog

Today for one of the SSRS report (SharePoint integrated) the export to Excel failed and it gave the correlation id which we were unable to find on the various nodes of SharePoint farm.

So using Central Admin we enabled job “Diagnostic Data Provider: Trace Log” by navigating to Monitoring => Review Job definitions. On SQL server execute following query against view Logging.dbo.ULSTraceLog:

SELECT [MachineName],
[Area],
[Category],
[Level],
[EventId],
[Message],
[CorrelationId]
FROM ULSTraceLog
WHERE [CorrelationId] = ‘9990009d-cccc-xxxx-07xx-xx99f9999xx9’

Powershell script to list all the SharePoint 2013 integrated SSRS 2012 subscription details

MSDN reference

Using PowerShell script we can find all the SharePoint 2013 integrated  SSRS 2012 subscription details:

Script:

# Parameters
# server – server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)

Param(
[string]$server,
[string]$site
)

$rs2010 += New-WebServiceProxy -Uri “http://$server/_vti_bin/ReportServer/ReportService2010.asmx&#8221; -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site); # use “/” for default native mode site

Write-Host ” ”
Write-Host “—– $server’s Subscriptions: ”
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status

powershell c:\scripts\ListAll_SSRS_Subscriptions.ps1 "[server]/_vti_bin/reportserver" "http://[server]"