Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B) (rsRPCError)

Rajesh has the answer, simple and to the point:

This error means that your Reporting Servers configuration for the Encryption key has been messed up. From the Reporting Server Configuration Manager you have to restore the encryption key. If you have lost it( it is always good to have a backup) then you may have to delete those databases(reportserver & reportservertempdb). Then you have to delete the reportserver and reports site on IIS. Then you have to start over the configuration again.

Got from his blog at:
Blog of Rajesh Meenrajan - MCSD.NET /MCTS SharePoint: Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B) (rsRPCError)

Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B) (rsRPCError)

Using DateAdd Function in SSRS

Good links on topic: Primarily help with the DateInterval start point:
Using the DateAdd() Function in SSRS « SSRS Learning Curve by SSRS Learning Curve.

Additional links are at:

• msdn
• russell christopher
• http://www.lukehayler.com
• msdn SSRS Function help
  

quick reference:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year (same as Day)
d	Day
w	Weekday (same as Day)
ww	Week
h	Hour
n	Minute
s	Second

How to Create SQL Users that Restore well.

In our Test and Development environments we are always restoring our production databases.  We use a couple SQL Server Users, which means that when we restore a database we have to always drop the database user and reassign the Server User.

Sometimes, we had difficulties in removing one or two users.  As a part of our application we started log shipping for reporting purposes, so reports wouldn't take down production.  When we did this I found this article on the issue with SQL Server User account.  It has a unique SID on each server, so we needed to recreate the user with the same SID for us to be able to assign SQL Server Users to our database.

How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server

Once I got this working for log shipping I realized that I could create this same user/SID on each of my Test/Development boxes and we wouldn't have to fix the users every time we did a restore from production.

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