Opening new windows within SQL Server Reporting Services (SSRS) 2005

In reporting services you have the ability to create a jump to link under the navigation tab. This is to help understand how the link works from the Reporting Services site and from your own hosted site.

example  jump to link contruction in SSRS:

=Parameters!Root_URL.Value.ToString()
& "?SourceID=42cde449-6c46-48c4-97dd-5830c3727c88"
& "&Key=" & rtrim(Fields!Key.Value.ToString())

In our scenario we wanted our form details to look the same as it does from our website.  So, there is a redirect within that page that makes it so if the user hits the back button it doesn't work.  So we decided to open the link in a new window. So we added a javascript wrapper around the link to open it in a new window:

example link construction with JavaScript in SSRS:

="javascript:void(window.open('" & Parameters!Root_URL.Value.ToString()
& "?SourceID=42cde449-6c46-48c4-97dd-5830c3727c88"
& "&Key=" & rtrim(Fields!Key.Value.ToString())
& ",'_blank','menubar=no,titlebar=no,status=no,resizable=yes,scrollbars=no,width=800,height=900'))"

This worked well when we were just using The Reporting Services portal to render the report, but we also deploy our reports to another website, but using an iFrame that points back to the report.  The iFrame URL is captured from a link that is appended to the current URL using QueryString parameter. We ran into an issue where the report server parameters were not making it to the iFrame,  make sure that you URL encode the URL in the QuerySting, if you use this method. Good online encoding tool.

In researching this issue we found a couple good links where people discussed solutions to this problem:

sql server reporting services : Open New Window from Iframe
SSRS: HTML Device Information Setting "LinkTarget" is ignored.

LinkTarget seemed to be the answer.  The way that LinkTarget works is that any links on your report will be opened in the window name defined in the LinkTarget.  In our case we chose "_blank". the full QueryParameter name is:  rc:LinkTarget=_blank.

Microsoft definition:

LinkTarget

The target for hyperlinks in the report. You can target a window or frame by providing the name of the window, like LinkTarget=window_name, or you can target a new window using LinkTarget=_blank. Other valid target names include _self, _parent, and _top.
Article: SQL Server BOL: HTML Device Information SSRS

So you can add the LinkTarget to the URL you use on your own hosted site.

Example Host iFrame URL:

http://YourReportServer/ReportServer?/YourReportPath/YourReportName&rs:Command=Render&rc:LinkTarget=_blank

This does work however, it does not give you any control of the window when it is opened. So, if you use the JavaScript in conjunction with the above link it will then open two windows, but your window should show up with the correct windows parameter settings.  You cannot tell the Report URL the size of the window, but you can the JavaScript.  So all we need to do now is close that extra window. We noticed that the SSRS windows opens first and then the javascript runs and opens a new window from there. So, we used more JavaScript in our Jump to Link construction in SSRS.  window.close() will close the current window where the JavaScript is . 

Example adjustment to the link construction in SSRS:

="javascript:void(window.open('" & Parameters!Root_URL.Value.ToString()
& "?SourceID=42cde449-6c46-48c4-97dd-5830c3727c88"
& "&Key=" & rtrim(Fields!Key.Value.ToString())
& ",'_blank','menubar=no,titlebar=no,status=no,resizable=yes,scrollbars=no,width=800,height=900'));window.close"

SSRS 2005 Custom functions show #error

I created a custom function to convert UTC date times to the Local DateTime. When doing this I found that the if the field was nothing the textbox expression returned #error.

example expressions that produced error in text box:

Orginal Code with Error:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)

Tried checking if it is a Date first:
=IIF(IsDate(Fields!completed_dttm.Value), Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value), nothing)

Tried checking if the value was nothing and using string values for each output:
=IIF(IsNothing(Fields!completed_dttm.Value), "", Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value.ToString())

I looked for solutions on the internet but didn't find my exact issue.

So, I decided to just try another approach and I within the DataSet, I Added a new Field by right clicking on the dataset and choosing Add...

Name the field, choose Calclulated and enter the original expression from above:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)

Deploying a custom assembly: http://msdn.microsoft.com/en-us/library/ms155034%28v=sql.105%29.aspx
Referencing custom assemblies: http://msdn.microsoft.com/en-us/library/ms155974%28v=sql.105%29.aspx
and: http://msdn.microsoft.com/en-us/library/ms159238%28v=sql.105%29.aspx#Custom

Custom ReportFunctions Class:

using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;

namespace RWF.Report.Custom
{
    public class ReportFunctions
    {
        ///

        /// enum of Time Zone Names
        ///

        public enum TimeZoneName
        {
            Pacific = 1,
            Eastern,
            Central,
            Mountain,
            Arizona
        }
        ///

        /// used to translate to local time zones
        ///

        /// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
        /// UTC Date/Time
        /// Local Date/Time        public DateTime ToLocalTime(TimeZoneName timeZone, DateTime UtcDateTime)
        {

            switch (timeZone)
            {
                case TimeZoneName.Pacific:
                    return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
                case TimeZoneName.Eastern:
                    return ToLocalTimeFromOffset(UtcDateTime, -5.0, true);
                case TimeZoneName.Central:
                    return ToLocalTimeFromOffset(UtcDateTime, -6.0, true);
                case TimeZoneName.Mountain:
                    return ToLocalTimeFromOffset(UtcDateTime, -7.0, true);
                case TimeZoneName.Arizona:
                    return ToLocalTimeFromOffset(UtcDateTime, -7.0, false);
                default:
                    return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
            }
        }

        ///

        /// used to translate Local Time to UTC Date Time.
        ///

        /// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
        /// Local Date Time
        /// UTC Date Time        public DateTime ToUtcTime(TimeZoneName timeZone, DateTime LocalDateTime)
        {

            switch (timeZone)
            {
                case TimeZoneName.Pacific:
                    return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
                case TimeZoneName.Eastern:
                    return ToUtcTimeFromOffset(LocalDateTime, -5.0, true);
                case TimeZoneName.Central:
                    return ToUtcTimeFromOffset(LocalDateTime, -6.0, true);
                case TimeZoneName.Mountain:
                    return ToUtcTimeFromOffset(LocalDateTime, -7.0, true);
                case TimeZoneName.Arizona:
                    return ToUtcTimeFromOffset(LocalDateTime, -7.0, false);
                default:
                    return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
            }
        }

        ///

        /// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
        ///

        /// Local Date Time
        /// UTC Offset for time zone
        /// Time zone observers daylight savings time (DST)
        ///
        public DateTime ToLocalTimeFromOffset(DateTime UtcDateTime, Double Offset, bool ObservesDST)
        {
            DateTime LocalDateTime;
            bool bDateIsInDST;
            LocalDateTime = UtcDateTime.AddHours(Offset);

            // add 1 to offset if the date is in daylight savings time and it is observed
            if (ObservesDST)
            {
                bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
                if (bDateIsInDST)
                    LocalDateTime = LocalDateTime.AddHours(1.0);
            }


            return LocalDateTime;
        }

        ///

        /// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
        ///

        /// Local Date Time
        /// UTC Offset for time zone
        /// Time zone observers daylight savings time (DST)
        ///
        public DateTime ToUtcTimeFromOffset(DateTime LocalDateTime, Double Offset, bool ObservesDST)
        {
            DateTime UtcDateTime;
            bool bDateIsInDST;

            // add 1 to offset if the date is in daylight savings time and it is observed
            if (ObservesDST)
            {
                bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
                if (bDateIsInDST)
                    Offset = Offset + 1.0;
            }

            UtcDateTime = LocalDateTime.AddHours(-Offset);

            return UtcDateTime;
        }


        ///

        /// Returns true if the date falls within DaylightSavingsTime
        ///

        /// Datetime to check
        /// true/false        public bool IsInDaylightSavingsTime(DateTime dateTime)
        {
            int year = dateTime.Year;
            DateTime startDate = GetDaylightBeginDate(year);
            DateTime endDate = GetDaylightEndDate(year);

            //TimeSpan delta = new TimeSpan(startDate.Ticks - endDate.Ticks);
            //DaylightTime daylightTime = new DaylightTime(startDate, endDate, delta);
            bool isDST;

            //isDST = TimeZone.IsDaylightSavingTime(dateTime, daylightTime);

            if ((dateTime > startDate) && (dateTime < endDate))
                isDST = true;
            else
                isDST = false;

            return isDST;
        }

        private DateTime GetDaylightEndDate(int year)
        {
            if (year < 2007)
            {
                //last Sunday in October
                return GetNextSunday(new DateTime(year, 10, 25, 2, 0, 0));
            }
            else
            {
                //first Sunday in November
                return GetNextSunday(new DateTime(year, 11, 1, 2, 0, 0));
            }
        }

        private DateTime GetDaylightBeginDate(int year)
        {
            if (year < 2007)
            {
                //first Sunday in April
                return GetNextSunday(new DateTime(year, 4, 1, 2, 0, 0));
            }
            else
            {
                //second Sunday in March
                return GetNextSunday(new DateTime(year, 3, 8, 2, 0, 0));
            }
        }

        private DateTime GetNextSunday(DateTime dt)
        {
            double dow = Convert.ToDouble(dt.DayOfWeek);
            if (dow == 0)
            {
                return dt;
            }
            else
            {
                return dt.AddDays(7.0 - dow);
            }
        }

        public DateTime GetUtcDate()
        {
            return DateTime.UtcNow;
        }
    }
}

Implementing Data Security in SSRS Report Model

This is a good blog on explaining the Data Security within SQL Server Reporting Services (SSRS)

Bob's SQL Reporting Services Blog : Implementing Data Security in a Report Model

We are also looking at doing our own custom data security, so we can do it through our own groups and permissions within our application.

Why aren't my CHM Files working?

The CHM viewer component is not properly registered on your PC
The system file <WINDOWS>\system32\hhctrl.ocx may get missing, corrupted, or unregistered.

Solution: Run "regsvr32 hhctrl.ocx" command from the command line to register the library in the system.

A security update for Windows XP® may block access to CHM files
Windows XP® security update blocks active content in CHM files to protect your system security. Additional information is available here.

Solution: Run Windows Explorer, right-click on the CHM file, and select Properties from the popup menu. Click on the Unblock button immediately below the Advanced button on the General page. Click Apply to show the content. Once the CHM file has been unblocked, the Unblock button disappears.

Go here for other issues/solutions

When using IIS 6.0 with Integrated Security, get Access Denied from IE 6.0

This happened when setting up SQL Server Reporting Services.  It has many different places where you can tell the applications to run as a service.  My administrator gave me an account for running my SQL Services: MYDomain\SQLAgentSrvc

After setting up my applications, I setup new App Pools for each site and I used the same account to run them.

When opening the site with IE 6.0/7.0 I got the popup window for authentication and normally it passes through for NTLM.  So, I tried FireFox and it logged me in just fine.  I found a web page that suggested a couple tools to help solvle this "Access denied in Internet Explorer" issue.

In my research I found two tools that helped me to figure this out:

Fiddler 2:
This helped me to see what FireFox was doing to make it work and what IE was doing that wasn't working.  In the captures I noticed that FireFox was using straight NTLM and IE was using Kerberos. 

And

Microsofts Authentication and Access Control Diagnostics 1.0 (AMD64)
This helped me to see some issues with the active directory account that I was running the AppPools as, especially when tested the Kerberos Configuration. 

So, I changed the account that the Applicaiton pools ran under to "Network Service" and poof.  Issue was fixed.

Solution to Log Shipping and Problem SQL Users

The issue is that the SQL Users that are transfered with a log shipped database do not have the same SID as the SQL user on the destination server.  In order to make them compatible, you must create capture the SID/Login of the user from the Source Database Server and recreate the User on the destination Database Server.

This makes it so you don't have to modify the database on the destination server.


See this article on Microsoft Support:

How to transfer the logins and the passwords between instances of SQL Server 2005


This also helps if you are always restoring your development and test environments from production.  So, What I did was capture the user off of my production database server and populated it to my other databases. 

Reporting Services Styles & Templates

Reporting Services Styles in a Database Table

This article is about storing colors for headers, footers and body text in the database and using the DataSet to store at the top level report and passing down to subreports through parameters.

Reporting Services Styles in a Database Table - SQLServerCentral



Create Report Template

This article describes how to create a template you can use to maintain your company template starting point.  Logo, Header, footer, date, page counters etc.

How To Create Templates in Reporting Services 2005

Passing Multi-Value Parameters to Subreports

When passing a multi-value parameter using the Expression Builder it will automatically add the Parameter!Name.Value(0).  If you strip the (0) then it will work.  This expression returns the first selected value. 

Parameter passing from master to subreport : SQL Server Reporting Services : SQL Server : MSDN Forums

Scalar Function Performance...

I found this article which describes Scalar Functions acting as cursors.  Use the Table-Inline Function instead.  Adam found that it was 288 times better in IO.  Read article to understand the difference.

Adam Machanic : Scalar functions, inlining, and performance: An entertaining title for a boring post

SSIS Links to Tips and Blogs

Very good tip on setting package variables from SQL Jobs:

SSIS Tip #1: Set SSIS package variables via SQL Agent jobs - Steve Holstad's "the bright lights"


This is an OK article.  I am still figuring out how to use a DateTime Variable as a parameter in an Execute SQL Task:

Working with Parameters and Return Codes in the Execute SQL Task

Will post a solution later...

Use Cases, Help!!

Here are some helpful links for Use Case Diagrams:

Creating Use Case Diagrams

Use case - Wikipedia, the free encyclopedia

I have a book: "UML Distilled"  by Martin Fowler.

• Chapter 9 - Use Cases

How to Use Custom Assemblies in SQL Server 2005 Reporting Services

Once you have created a custom assembly you will need to copy the .Dll to the following Folders:

[Drive]:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
[Drive]:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

VS 2010 x64 machine:
[Drive]:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies

Reference:
How to use custom assemblies or embedded code in Reporting Services

Automatic Site Translation....

This is an interesting article from CodeProject:

CodeProject: Automatic Site Translation Using HTTPModules and Machine Translation (MT). Free source code and programming help

Is it possible for us to use a similar approach to our WinForms?

Report Drill Through

Links related to the Report Drill-Through Events and methods

ReportViewer.Drillthrough Event (Microsoft.Reporting.WinForms)

How to use ReportViewer Control in Microsoft Visual Studio 2005

Lookup Transformation Caching

Lookup Transformation

If you are working with large tables that are millions of records or more you will probably want to enable cache restrictions. 

The default caching is without restriction.  So the whole dataset will load into memory.  In one of our cases we had 39 million rows which failed because we ran out of memory.  I set it back to 500 mb, this took 5 hours, but it completed.  This is too long and we needed another way to trim down the cache.  Or the size of the table that was being used for the lookup.

To do this we need to figure out an algorithym that will make the lookup table smaller.

Answer:
Stop using the lookup transformation.  Capture the max ID of the primary key column into a variable, using Execute SQL Task.  It is assumed to be an Identity column on the source. 

In order to capture the ID we had to do a workaround with two variables.

1. capture the variable into an Object Type variable
2. Use a script to convert the object variable into an Int64 variable.
3. Use the Int64 Variable in a conditional split data flow task

In the conditional Split compare the max ID to the current ID coming through the dataflow.

1. if the current ID is greater than the max ID found then do INSERT
2. otherwise do an UPDATE

What Business Intelligence Application to Use?

In researching business intelligence applications we have many choices and of these we have look at three different vendor solutions:

• Microsoft SQL Server, includes Database, Integration Services, SOAP Services, Reporting Services and Analysis Services
• SAP Business Objects Enterprise , includes Crystal Reports, Web Intelligence and Xcelsius Enterprise
• InterSystems – DeepSee , Cache Objects, Connector, Analyzer, Architect, and Designer

Feature Comparisons

	MS SQL Server	SAP BusinessObjects	InterSystems DeepSee
Comes with Relational Database Server	Good	None	Fair
Can Use Existing SQL Server Data	Good	Good	None
Cost Extra	No	Yes	Yes
Rate Reporting Tools	Good	Good	Fair
Report Management Server	Good	Good	Good
Integration Services	Good	Good	Good
Analysis Services	Good	Good	Good
End-to-End Data Warehouse Solution	Good	None	Fair
Dash Board Tools (Widgets)	Fair	Good	Fair
Developer Knowledge	Good	Fair	Fair
Business Knowledge	Fair	Good	Bad
Interoperability	Fair	Good	Bad