Thursday, March 21, 2013

Retrieve report definition .rdl from a SQL 2005 report server

Problem

If you are a developer and confronted with a situation where there is no source for a report that is stored on a SQL 2005 report server, then you can download the report definition from the server itself. although it is not obvious.

Description

A report that was developed by someone else is run on a particular Sql 2005 report server. This report has to be modified and migrated to a new server. There is no source available. But luckily you do have the rights to administer the report site. Then there is a way to download this report definition as a file to your development pc where you can then add it as an existing report to your Visual Studio project.

Solution

To retrieve the .rdl file

  • Open the SQL 2005 report site with Internet Explorer.
  • Browse to the folder with the report.
  • in this folder click on the ‘Show details’ button
    image
  • The reports are now listed line by line with an edit button in front of the name. Click on the ‘Edit’ button of the report that you need.
    image
  • The properties page for that report is opened. Under ‘Report Definition’ click on the ‘Edit’ link
    image
  • The ‘File Download’ dialog window opens. Click on the ‘Save’ button.
    image
  • In the ‘Save As’ dialog window select a location on your pc where you want to save the file. click on ‘Save’

Add the .rdl file to you reporting project

  • Open the Business Intelligence project with Visual Studio.
  • Right-click on the Reports folder and from the dropdown menu select ‘Add / Existing Item…’
    image
  • In the open file dialog browse to the location where you downloaded the .rdl file and select it.
  • The report definition is now in your project ready for modification.

Thursday, March 07, 2013

SSRS reporting with SharePoint integration log file location

Where are the SSRS log files when yu have SSRs integrated with SharePoint?
when integrated with SharePoint the SSRS service is running on the SharePoint App server. the log files will be on that server in :

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

Data driven report subscription send one email to all subscribers in comma separated list

Question

With an existing data driven report solution, a scheduled report is sent to several subscribers by email individually. I;e. if there are 4 users that subscribe to the report in the subscription database then 4 emails will be sent by reporting services. The manager would like to have the report sent once with all the subscribers in the TO section in a comma separated list. that way he can see who else received the email.

Current situation

The subscription database basically looks like this

image

The Subscription table holds the report parameters needed to subscribe to a report

The UserData table holds general information about the Windows users that can be subscribers.

For the many to many relation between subscriptions and users, the relation has been defined in the table ‘SubscriptionUser’ where users can be assigned to one ore more subscriptions.

There is also a lookup table ‘ReportFormat’ that holds the name of various export formats available in SSRS.

The original statement used by the data driven subscription in reporting services was:

SELECT a.SubscriptionName, d.EmailAddress AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat
    , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody
FROM dbo.Subscription AS a
INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID
INNER JOIN dbo.SubscriptionUser AS c ON a.SubscriptionId = c.SubscriptionId
INNER JOIN dbo.UserData AS d ON c.UserId = d.UserId
WHERE a.SubscriptionId = 3 AND d.IsActive = 1
This statement will yield a number of lines, in this example 4 lines
jack@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
tom@abc.com true Excel Weekly sales report 0 1 Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
pete@abc.com true Excel Weekly sales report 0 1 Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
mike@abc.com true Excel Weekly sales report 0 1 Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>



Solution


We need to have only one row returned from our data driven select statement. The Email addresses need to be concatenated in one semicolon (not comma!) separated string that will serve as the TO column.



  • Select email addresses for the given report


    SELECT EmailAddress 
    FROM dbo.UserData AS dd
    INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
    WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
  • jack@abc.com
    tom@abc.com

    pete@abc.com

    mike@abc.com

  • This will yield 4 rows again, now these rows need to be concatenated into a single string. For this the T-SQL FOR XML PATH can be used

    SELECT  ';' + EmailAddress
    FROM
    (
        SELECT EmailAddress 
        FROM dbo.UserData AS dd
        INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
        WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
    ) AS e FOR XML PATH('')


    ;jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com

  • Now the first semicolon needs to be trimmed from this string, this can be achieved with the STUFF function in T-SQL. The complete select statement result needs to b used as argument to the STUFF function.
    SELECT STUFF(
                (
                    SELECT  ';' + EmailAddress
                    FROM
                    (
                        SELECT EmailAddress 
                        FROM dbo.UserData AS dd
                        INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                        WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                    ) AS e FOR XML PATH('')
                ) ,1,1,'') AS EmailAddress



    jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com

  • Finally the result has to be selected together with the other report subscripion parameters for the report with id 3.
    SELECT STUFF(
                (
                    SELECT  ', ' + EmailAddress
                    FROM
                    (
                        SELECT EmailAddress 
                        FROM dbo.UserData AS dd
                        INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                        WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                    ) AS e FOR XML PATH('')
                ) ,1,1,'') AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat
        , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody
    FROM dbo.Subscription AS a
    INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID
    WHERE a.SubscriptionId = 3



  • The result is one line and a semicolon separated [TO] column

    jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com    true    Excel    Weekly FER report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly FER report. <br/><br/>Regards <br/>

Monday, March 04, 2013

Rollback VS 2008 SSIS package changeset in TFS

Problem

For some reason a package that was changed last week gave a script task error validation error. The package had been tested and was workiing fine. Only after the package had been chaecked into TFS last week and reopend with VS2008 this monday did the the Script task produce validation errors. Trying to edit and open the C# script code failed.

Solution

Since the change in the script task was minor and well documented I decided to revert to the previous version in TFS. But rolling back in TFS with VS 2008 does not seem as simple like it used to be with Visual SourceSafe.

There is an option in Visual Studio (Tools / Options / Source control / Visual Studio Team Foundation) that says ‘Get latest version of item on check out’. The option on my machine was turned of, so one would think it might be possible to use this feature to get a previous version of the file during check out. But I could not find a menu choice like that.

image

Therefore I abanonded looking further into this problem and opted for a pragmatic solution:

Get previous version of package – copy to separate folder – check out, this will get the latest version again. Close VS 2008  and overwrite the checked out version with the copy of the previous version. Load VS 2008 and modify package, check in.

  1. Open the SSIS solution in VS 2008
  2. Rightclick on the package in question and from the dropdwon menu select Get Specific Version…
    image
  3. In the ‘Get’ dialog, in the Version box, select Type ‘Changeset’ and click on the button with 3 dots
    image
  4. In the find changesets dialog window, set Ragne to ‘All changes’ and click on the Find button. The results are shown in the listbow at the bottom. Select the previous changeset (2nd in the list) and click OK. In my examplet this is set 495
    image
  5. The Get window has 495 in the Changeset textbox . Click on the Get button.
  6. Now visual studion has loaded the file with the previous version of the package.
  7. Close the soloution.
  8. Copy the package file with windows explorer to another location, and make the copy writable.
  9. Open the Solution.
  10. Check out the package. This will get the latest version.
  11. Close the solution.
  12. Copy the writable previous version to the project folder and overwrite the latest version.
  13. Open the solution.
  14. Edit the package and check in.