Export Tab Delimited text file in SSRS 2008 R2

Locate rsreportserver.config in the server, and add the below lines of code

Code Snippet
  1. <ExtensionName=TABType=Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering>
  2.                 <OverrideNames>
  3.                      <NameLanguage=en-US>TAB (Tab Delimited Text File)</Name>
  4.                 </OverrideNames>
  5.                 <Configuration>
  6.                           <DeviceInfo>
  7.                                <FieldDelimiterxml:space=preserve>    </FieldDelimiter>
  8.                            <UseFormattedValues>False</UseFormattedValues>
  9.                                <NoHeader>False</NoHeader>
  10.                       <FileExtension>Txt</FileExtension>
  11.                           </DeviceInfo>
  12.                       </Configuration>
  13.             </Extension>

I added it right below CSV extension

I did a few things with the TAB extension, I named the Extension “TAB”, I Named in  TAB (Tab Delimited Text File) this the name that is displayed in the reports webpage when you click the export option

image

I wanted the file extension to be .txt, so I mention <FileExtension>Txt</FileExtension>
I wanted the Column Header in the tab delimited file so <NoHeader>False</NoHeader>

SSRS Warm Up Script

In SSRS 2005, after a period of inactivity (no calls to Reporting services), the first call to RS is very slow. This is more of an IIS issue than reporting services. The app pool generally winds down if there is no activity for a certain period of time. When the subsequent (first) request comes into reporting services, it has to restart everything hence the slowness.

In order to overcome this issue I wrote a SSRS Warm Up script. This is a windows service which executes at regular intervals, on the time elapse event I call the SSRS web service which executes and retrieves the PDF for a dummy report. This way if the app pool recycles, the warm up services will kick in and keep the server ready for subsequent requests.

I tested this by recycling the app pool to simulate cold start.

Test Result :  Executing my dummy report on cold start took almost 20 sec. After Warm up took 6 sec

Code

Windows Service

Imports SsrsWarmUp.Core
Imports System.Timers
Imports System.Configuration

Public Class SsrsWarmUpService
    Private _warmUpTimer As Timer = Nothing
    Private _warmUpCore As WarmUpCore
    Private warmUpLock As New Object

    Public Sub New()
        InitializeComponent()
        _warmUpCore = New WarmUpCore
        _warmUpTimer = New Timer()
        _warmUpTimer.Interval = Convert.ToDouble(ConfigurationManager.AppSettings(“ExecuteInterval”))
        AddHandler _warmUpTimer.Elapsed, New System.Timers.ElapsedEventHandler(AddressOf Me.warmUpTimer_Elapsed)

    End Sub

    Protected Overrides Sub OnStart(ByVal args() As String)
        Try
            _warmUpTimer.Start()
        Catch ex As Exception
            EventLog.WriteEntry(“SsrsWarmUp Service”, ex.Message)
        End Try
    End Sub

    Protected Overrides Sub OnStop()
        Try
            _warmUpTimer.Stop()
        Catch ex As Exception
            EventLog.WriteEntry(“SsrsWarmUp Service”, ex.Message)
        End Try
    End Sub
    Protected Sub warmUpTimer_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs)
        SyncLock warmUpLock
            _warmUpCore.ExecuteReport()
        End SyncLock
    End Sub

End Class

WarmupCore Class

Imports System.Configuration
Imports System.IO

Public Class WarmUpCore

    Public Sub ExecuteReport()
        Dim rs As New ssrs.ReportingService()
        rs.Credentials = New MyICredentials()
        Dim ResultStream() As Byte = Nothing
        Dim OptionalParam As String = Nothing
        Dim optionalParams As ssrs.ParameterValue() = Nothing
        Dim optionalWarnings As ssrs.Warning() = Nothing
        Dim StreamIdentifiers() As String = Nothing
        ResultStream = rs.Render(ConfigurationManager.AppSettings(“ExecuteReport”), “PDF”, Nothing, “<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>”, Nothing, Nothing, Nothing, OptionalParam, OptionalParam, optionalParams, optionalWarnings, StreamIdentifiers)
        WritePdfBytes(ResultStream)
    End Sub
    Private Sub WritePdfBytes(ByVal pdfBytes As System.Byte())
        Dim filePath = “c:\ssrsWarmUp.Pdf”
        Dim theFile As FileStream
        If File.Exists(filePath) Then
            File.Delete(filePath)
        End If
        theFile = File.Create(filePath)
        GetStreamFromByte(theFile, pdfBytes)
        theFile.Flush()
        theFile.Close()

    End Sub
    Private Sub GetStreamFromByte(ByRef theFile As FileStream, ByVal docData As Byte())
        theFile.Position = 0
        theFile.Write(docData, 0, docData.Length)
    End Sub
End Class

Note: In Sql Server 2008 this issue may not exist as there is no IIS to control.

SSRS Explorer

Installing SSRS Explorer

image

image

 image

image

image

To Start SSRS Explorer go to Program Files –> SSRS Manager

image

In order to connect to any existing SSRS server we need to first configure the server settings

Click Application–> Servers

image

I generally remove all the default servers that show up and click add

image

Enter the server URL. For SSRS 2005 the url will be http://<<servername>>/reportServer/ReportService2005.asmx

If you’re current windows login id has access to SSRS server then check the windows authentication check box, else enters the credentials for SSRS

image

Click Save –> Close

To Connect to Devl SSRS server, click connect –> devl.

image

At this point the explorer queries the SSRS web service and constructs the tree view structure

image

How to move report from one folder to another on the same server

Right click on the report and select move

image

Select the destination folder

image 

Click Ok

image

To move multiple files select all of them and click move

image

Moving Reports or objects from one server to another (Typically moving from devl to test or Prod)

Here I am moving the reports from my localhost to the development server. I want to move the all the files in My Reports folder to devl server

Step 1: Download the files you want to deploy to devl server from localhost, to your local drive.

Step 2: Connect to the destination server which is devl here

Step 3: Upload them

Downloading files

image

image

You may want to preserve folder structure if you are downloading folder with subfolders

To upload the files to the development server connect to the Devl server as we did earlier, then select the destination folder and upload all the files or the entire folder

image

image

If you do not want to overwrite existing files on the destination server choose no, most of the times you want to overwrite the old versions with the new on.

image

 

image

Associate Data Source for the Report

image

 

Select a single report or multiple reports right click and select set data source

image

From the list of available data source select the one

You can do almost all the admin activities using the ssrs explorer. Like adding a data source, modifying a data source. You would even administer security from the explorer. But I prefer to do security related stuff from report manager website

Deploying SSRS Reports from Development Server to the Test Server or Production

When developers deploy report to SSRS server, they right click the report project set the deployment properties.

image

Then the select the report, right click deploy.

But in many organization SSRS Security is setup such that no developers can get on Test and Production servers. DBA’s are the only people who can deploy the reports to these servers. Below I explain the process for migrating reports from one server to another.

The information to be sent to DBA’s for Moving a report or other SSRS object from one server to another

 

1. SSRS Object to be deployed (Source)

· Object Type: Report \ Report Model

· Location : __________________________________________

· File attached :

2. SSRS Object Destination

· Location : __________________________________________

· Data Source to be used: ________________________________________

DBA logs into the Source Reports site

image

Click on the Report to be migrated in this case Report1, Click Properties tab

 

Click on the Edit link button in Report Definition section

image

Click the save button. This saves the RDL file locally

Login to destination server, Navigate to the location mentioned

image

Click Upload File button

image

Browse to the location where you saved the rdl file. It automatically populates the name in the 2nd textbox. If there already exists a report with the same name in the current location you have the option overwrite it. Click OK

Now when you click on the report, you will get an error

image

The reason is we have not mentioned what data source the report needs to use during execution. To set the data source click on the properties and then select Data Sources

image

If you already have a shared data source you can reuse that by clicking the browse button and selecting the same. Or you can setup a new data source for your report.

How to enable My reports

Click on Site Settings

image

Check the Enable My reports to Support User-owned folders for publishing and running personalized reports and click apply

image

To grant a user access to publishing reports to my reports folder

Go to home Click on the Properties tab, click on edit link next to the username. Make sure My reports Role is checked.

image

How to Enable Report Builder tab in SSRS

Many of the SSRS administrator I have interacted are a little confused about enabling Report Builder tab. The reason is when the get on the box where they have installed reporting services, they browse to http://localhost/reports they are able to see the report builder tab, but when the try to access the site remotely they cannot see it. To confuse matters future, the administrator keeps granting access to the Report Builder Role and nothing happens.

image

Below is the step by step instructions on how to enable Report Builder

1. Remote in or log into box where you have installed SSRS

2. Go http:\\localhost\Reports

image

3. Click on site setting in the Right hand corner, you will get the below from

image

4. Click Configure system-level role definitions,

image

Click New Role

image

5. Add a new role name: Report Builder, Description “This role will make report builder visible to users”

Same as shown above click OK

6. Select Site Setting

7. click Configure site-wide security

image

Click Ok.

When User1 access the site remotely should see the Report Builder Tab.

2 Tiered SQL Report Server Installation

Recently I have been helping out one of my customers with SQL Reporting Server 2005. It all started with my customer wanting to install SQL Reporting Server on a failover cluster. SQL Server Reporting services 2005 is not cluster aware. Meaning you don’t want to install both SQL Server Report Services and the reporting services databases on each nodes in a failover cluster. I also found was there was a quite a bit of confusion amongst some of the other people whom I knew. So I decide to try out each deployment type myself and publish my experience.

Microsoft in its Deployment Topology documentation mention a few deployment types

Single Server Deployment  – Here all the report server components including database are on the same box. I will not deal with this as it is pretty straight forward

Standard Server Deployment – This is the focus for this post. Here we install Report server on one box and Report Server Database another

Standard Scale-Out Server Deployment – Here we will have 2 Report server machine in a load balance configuration  and Report Server database will be on a separate machine

Standard Scale-Out Server Deployment  With Database on Failover Cluster – Here deployment will be same as Standard Scale-Out Server Deployment  but the Report Server Database will be on a failover cluster.

There is also Advanced Scale-Out Server Deployment, If I have enough time I will discuss this as well.

Standard Server Deployment

image

The picture is pretty self explanatory, so I will just get to the details on how I went about to achieve this.

Environment

In my environment, I use Virtual PC to create these machine and test them out.

Agni-Ssrs-Web machine, which is my Report server running windows 2003

AgniSql machine, which is my database server running windows 2003, SQL server 2005 enterprise edition

My Active Directory Domain is AgniNet.local

I have a AgniNet domain user sqldba for whom I have granted admin privileges on both the boxes

I also have AgniNet\RprtService user – just an ordinary domain user. I will use this user to run my Report Services

Deployment

On my AgniSql box, I logged in as sqldba user and Installed only SQL database server named instance “sqlinst2”. I did not install SSAS, SSRS, SSIS  or another thing Just database server

On my Agni-ssrs-web all I did was enable IIS before starting Report server installation

One thing I must admit I hate to type and write long sentences, so I will try to put more picture and type less.

Logged in as sqldba on to Agni-ssrs-web box, started SQL server installation

Report Server Installation

image

image

image

This took a while for me, (I doing this on 2 virtual pc’s), so please wait.

image

image

image

image

image

The next step is to configure Report Server and Create Report server Databases on AgniSql Database box

Configuring Report server

Click Start –> All Programs, –> Microsoft SQL Server 2005 –> Configuration Tools, click SQL Server Surface Area Configuration

image

Click In Surface Area Configuration for Services and Connections, verify that the Report Server Windows service is running.

image

Back in SQL server Surface Area Configuration, Click Surface area Configuration for Features

verify that Scheduled Events and Report Delivery is enabled. Verify that HTTP and Web Service Requests is enabled. Verify that Windows integrated security is enabled.

image

image

When I click windows integration Security I get error “Argument value was not passed for the argument Reporting service URL”

image

I just ignored it and went forward

Then I started Report server Configuration on Agni-ssrs-web box

image

Clicked Connect

image

I probably should have configure Report Server Virtual Directory (VD), then Report Manager Virtual Directory, then web service identity, but I attacked web service Identity first, got a small error setting web service Identity, that was because I had to have created the VD’s first. But it does not matter you can go back and create VD’s and it will just work fine.  I will just write what I did

On the web service Identity, I clicked new for creating a new application pool for the Report server

image

image

image

Selected New again for Report Manager Application Pool

image

Clicked Apply got the error, I mentioned earlier

image

Clicking on the link got “ReportServicesConfigUI.WMIProviderException: A Virtual Directory must first be created…”

image

So I am off to create virtual directory, I click Report Server Virtual Directory, click new

image

Click ok

image

Click Report Manager virtual directory

image

image

Back in web service identity. I see ASP.Net Service Account as got NT Authority\NetworkService

image

Database Setup

Open the Database Setup page, I have created AgniSql\SQLINSt2 instance. There are no report database in this instance.

image

Click new in database name field, I changed server name from AgniSql to AgniSql\sqlInst2

image

image

Database successfully created. Changed the server name from AgniSql to AgniSql\Inst2 and clicked apply

image

image

image

Looks like after the error happened it retried and went thru successful

I left the encryption keys thing as is

Verify reporting server installation

image

The 2 databases were created, on AgniSql\Sqlinst2

image

The Reporting Services was started successfully on Agni-ssrs-web

I reset IIS

image

browse to http://localhost/Reports

image

Got the current identity (..) does not have write access to c:\widows\Microsoft.net\framework\v2.0.50727\Temporary asp.net files

So I ran CMD

cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

aspnet_regiis -ga “agninet\RptService”

By doing this I am granting access to agninet\RptService account to IIS metabase and other directories used by ASP.net

image

iisreset, browse to http://localhost/Reports

image

Report page came up successfully