How to Pass Multi-Valued Parameter to SSRS Sub Reports

I'm working on a report that contains about 10 subreports. Some of the subreports need multi-valued parameter passed from the main report. After struggling with it for awhile I came across a good post that explains three different ways to do that. All three worked from me.
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/ae5ac615-1076-428d-9fcf-6c27e5fee1f0/

Create a Report of the Reports

After some digging, I found this post that talks about how to get report usage information:
http://p2p.wrox.com/content/articles/creating-report-server-usage-report-sql-server-reporting-services

SQL Server 2008 Email Notification Setup

I was trying to set up an email notification for a SQL Server agent job. I set up the Database Mail profile under Management, created operators under SQL server Agent, selected an operator under the notification section of a job. After the job is completed, guess what, there is no email. After diggin around I found this post(http://social.msdn.microsoft.com/Forums/en/sqltools/thread/839b5c27-9bc5-4467-a9d4-e78f9934a761). I tried restarting the agent serice and then realized that the mail profile was not enabled under SQL Server Agent Properties -> Alert System. After enabling that and restarting the agent service, it worked. The key here is to enable the mail profile first, then restart the agent service.

An Interesting Report Caching Issue

I was working on caching a report and encountered an issue. It simply didn’t work. All caching settings were tried. But it just doesn’t cache the damn report. I looked around on the Internet and no one seemed to have the same issue. One unique thing about this report is a big multi-valued parameter called Feature. The drop down has 150 – 200 entries depending on the selection of other parameters.

The next thing I did was looking into the [ExecutionLog2] view in the ReportServer database. I compared the parameters used in two different report runs. And I noticed that on the big list of features, one feature was converted to lower case (test feature) while the other report run has the same feature in regular cases (Test Feature). The data set that generates the feature list has the feature listed as Test Feature. And the lower case issue is on different feature everytime. I don’t know why SSRS converts it into all lower case. But because of that, it thinks these two report runs contain different parameter selections. Therefore it is not cached.

To fix that, I did a upper() on the query that generates the feature list and the query that generates the final report data. The report now takes a little longer to run. But the caching issue is fixed.

A good article I found when I troubleshoot the issue is at:
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

SSRS Report Caching

I came across a good article that talks about report caching.
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

TestIng from my mobile phone

Testing

User Permission for Creating Linked Servers

I created a stored proc that drops and creates linked servers. The following system stored procs are called in my this stored proc:
master.dbo.sp_dropserver
master.dbo.sp_addlinkedserver
master.dbo.sp_addlinkedsrvlogin

It works fine in my dev environment. But when I ran it on the prod db server, the following errors came up:
Msg 15247, Level 16, State 1, Procedure sp_dropserver, Line 20
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

After looking around, I found out that the account that runs the stored proc needs the "ALTER ANY LINKED SERVER", "ALTER ANY LOGIN" permission and the "dbcreator" server role. These were not setup on the SQL authentication account that runs the stored proc. So I did the following permission granting in addition to manually set the "dbcreator" role.

USE master;
GRANT ALTER ANY LINKED SERVER TO WarehouseAdmin;
GO

USE master;
GRANT ALTER ANY LOGIN TO WarehouseAdmin;
GO

Now it runs fine.

Tips on Deploying and Scheduling SSIS 2008 Packages

1. Use Windows authentication.
2. Deploy as File System instead of MSDB.
3. The account that runs SQL Server Agent needs to have sufficient privileges if connection to remote database server is included in the package.
4. Expose as few item as possible in the configuration file.

To repeat rows with column headings for a table with row groups

To repeat rows with column headings for a table with row groups
________________________________________
1. In Design view, select the table. The Grouping pane displays the row groups.
2. On right side of the Grouping pane, click the down arrow, and then click Advanced. The Grouping pane displays static and dynamic tablix members for each group. You can only set properties on a static tablix member.
3. In the Row Groups pane, click the static tablix member for the row that you want to repeat. When you select a static tablix member, the corresponding cell on the design surface is selected, if there is one. The Properties pane displays the properties for the selected tablix member.
4. Set the KeepWithGroup property in the following way:
o For a static row that is above a group, click After.
o For a static row that is below a group, click Before.
5. Set the RepeatOnNewPage property to True.
6. Preview the report. If possible, the row repeats with the group on each vertical page that the row group spans.
The post is at:
http://msdn.microsoft.com/en-us/library/cc627566.aspx

TFS 2008 to TFS 2010 Report Migration Notes

1. SSAS 2008 doesn't support "-" anymore.
"
everal Team Foundation pre-upgrade reports, and specifically the Scenario Details and Unplanned Work reports, show one of the following errors when run on SQL Server 2008:

The set must have a single hierarchy to be used with the complement operator.

The above messages appear because the WHERE clause in the report query is using a minus or complement operator (-) to exclude a specific attribute from the query. For example, the Scenario Details report includes a WHERE clause with the following syntax:
"
More details at:
http://msdn.microsoft.com/en-us/library/ff452590.aspx#REMComplement

Challenge in Excluding Weekends in TFS Reports

I have a burn up report that runs fine in SSRS 2005. This report excludes weekends. The MDX code block that does that is:

{FILTER([Date].[Date].[Date].ALLMEMBERS,
[Date].[Year Week Date].CURRENTMEMBER.Properties( "Day of Week" )<> "0"
AND [Date].[Year Week Date].CURRENTMEMBER.Properties( "Day of Week" ) <> "6")
}

However when I was trying to make this report work on TFS 2010, which is running SSRS 2008, it didn't work. The MDX code block was:

{FILTER([Date].[Date].[Date].ALLMEMBERS,
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER.Properties( "Day of Week" )<> "0"
AND [Date].[Year - Week - Date Hierarchy].CURRENTMEMBER.Properties( "Day of Week" ) <> "6")
}

I got an error that says "Query (13, 9) The Day of Week dimension attribute was not found." After Googling around with no finding, I opened up the TFS 2010 SSAS database. Guess what, the Date demension doesn't have the "Day of Week" attribute. In TFS 2008 SSAS 2005 the Date dimension has the following attributes:
Date
Day of Month
Day of Week
Day of Year
Month
Month of Year
Week
Week of Year
Year

However, in TFS 2010 SSAS 2008 the Date dimension only has the following attributes:
Date
DateSK
Month
Week
Year

So we have to find out another to exclude the weekends. I used the WTD() function and came up with the following MDX code, which works:
{FILTER([Date].[Date].[Date].ALLMEMBERS,
count(WTD([Date].[Year - Week - Date Hierarchy].CURRENTMEMBER)) <> 1
AND count(WTD([Date].[Year - Week - Date Hierarchy].CURRENTMEMBER)) <> 7
)
}

Who Locked My Table?

I have a couple of big stored procs that process data to a data warehouse. Sometimes they lock up the destination table and makes the reports hang. So I need to find out who locked the table and kill the process. Our DBA is kind enough to provide a query to find that informatoin:

select CASE WHEN tl.resource_type = 'OBJECT' THEN object_name(tl.resource_associated_entity_id)
WHEN tl.resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id) END as 'entity_name'
, tl.*
from sys.dm_tran_locks as tl
LEFT JOIN sys.partitions as p
on p.partition_id = tl.resource_associated_entity_id
where resource_type <> 'DATABASE'

I love DBAs.

T-SQL Error Handling

http://www.sommarskog.se/error-handling-II.htmlc

Merged colums in Excel exporting

I ran into a situation today that when I export a report to Excel there are unwanted merged columnns. After some digging I came across this post. It was very helpful.

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

Compare Strings in MDX

"using the MDX filter () function and the vba text functions you have plenty of search possibilities

filter (DimName.children, instr(DimName.currentmember.Properties("Name"),"MyValue")>0"

The full post is at:
http://www.developmentnow.com/g/112_2004_3_0_0_407650/MDX-equivalent-to-SQL-LIKE-operator.htm

Warehouse and Cube Changes in TFS 2010

The over view:
http://blogs.msdn.com/sunder/archive/2009/05/16/team-foundation-server-2010-relational-warehouse-and-cube-schema-changes.aspx

How the existing reports are affected:
http://blogs.msdn.com/aaronbjork/archive/2009/05/18/team-foundation-server-2010-where-are-my-reports.aspx

Reports update from 2008 to 2010:
http://www.socha.com/blogs/john/2009/05/upgrading-visual-studio-team-foundation.html

Pass Parameters via URL

I had to do some research to find out why my URLs did work. In short, the URL should point to http://localhost/ReportServer/ instead of http://localhost/Reports/, which is the Report Manager. Here is the post that helped:
http://dobrzanski.net/2008/08/11/reporting-services-problem-with-passing-parameters-directly-in-the-url/

Here is another good post about passing multivalued parameters:
http://bobp1339.blogspot.com/2007/10/passing-ssrs-report-parameters-in-url.html

Execution Policy Issue When Running a PowerShell Script from Team Build

I was trying to run a Powershell script as a step in a Team Build. To do that, I created the Powershell script, and called it in Team Build via an EXEC task. The execution policy was set to Unrestricted on the build server manully. I kept running into the permission error when the build ran ("cannot be loaded because the execution of scripts is disabled on this system. ").

After strugging with it for two days, I decided to put the set-executionpolicy in the Team Build to set the execution policy on the fly. And that fixed the problem.

Why did that work? After some digging (basically adding another powershell command to find out what the current user is.), the Team Build uses the same service account as the one we remote desktop in and set the policy manually. So the only theory that can explain that is the same account has different security settings in team build and remote desktop in. I still need to prove that.

PowerShell Stuff

Ping a server and get results:
***************
$ping = new-object System.Net.Networkinformation.Ping
$result = $ping.send("ServerName")
write-host $result.status
***************
Or
***************
$ip =”ServerName”
$qry = ('select statuscode from win32_pingstatus where address="' + $ip + '"')
$rslt = gwmi –query “$qry”
if ($rslt.StatusCode –eq 0) {
write-host “ping worked” -BackgroundColor "Green"
}
else {
write-host “ping failed” -BackgroundColor "Red"
}
***************

Using Custom Assembly in the Report

I was working on a report that requires getting information from a txt file. To do that, I planed to use the custom assembly reference feature in SSRS 2008. And I found a realy simple but useful post that walks through how to get thing setup(http://geekswithblogs.net/shervin/archive/2008/04/28/121712.aspx).

--Update--
1. The rssvPolicy.config file is not located in the Bin folder but one level up. The above post was wrong.
2. Here is a better post.
http://www.c-sharpcorner.com/UploadFile/balajiintel/CustomAssemblyinRS06302005081435AM/CustomAssemblyinRS.aspx

So I followed the steps in the post and create a Class Library C# solution. The custom assembly worked fine within its own solution. But after I copied the dll to the Report Designer bin folder and granted FullTrust access in the rssvPolicy.config file, the report couldn't find the file. This was frastrating because there is no error or exception that provides more information for debugging. After hours of digging on the Internet with no progress, I created a VB code snippet that does the same thing as the C# custom assembly. This time I embed the code in the report, hoping to get some different results. And indeed, I got an error:
********************
Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Preview complete -- 0 errors, 1 warnings
********************

The above error led me to another Microsoft post (http://support.microsoft.com/kb/842419), which solved my problem.

Solution:
Add the following code to the class
********************
FileIOPermission filePerm = new FileIOPermission(FileIOPermissionAccess.Read, "C:\TestFile[Put in the actual location of the file.]");
filePerm.Assert();
********************

split().GetValue()

The GetValue() method of the Split() function is cool.
E.g.
If the Version number is 5.4.6. I can get the release number by using:
Split(Fields!Version.Value, ".").GetValue(0)
and iteration number by using:
Split(Fields!Version.Value, ".").GetValue(1)

This is very neat.

Adding Line Feed in A Text Box

If you need to have multiple lines in a SSRS text box, VbCrLf is what you need.

Example:
="adbc" + VbCrLf + "xyz"
Result:
adbc
xyz

More details at:
http://stackoverflow.com/questions/26567/how-do-i-set-a-textbox-to-multi-line-in-ssrs

Solving Blockings with Dirty Read

A very nice article about dirty read.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx

Linked Server Name Trick

I created a report that pulls data from Oracle databases via OpenQuery() agained SQL Server linked server. I initially set the linked server name with periods (.), i.e. Environment.Schemaname.abc. Then my SQL query failed because OpenQuery() doesn't like period in the linked server name. After replacing the (.) with (_) everying worked just fine.

Strang Right() Function Behavior

I have a query that has been working for 6 months. All of a sudden it started giving me the error "Invalid length parameter passed to the RIGHT function". That query contains serveral select queries that are UNIONed together. A RIGHT() function is in one of those select queries. When I ran the select queries seperately, they all worked fine. But when I ran them together with the UNION, I got the error. This is very confusing.

The error went away after I put additional filtered in the query with Right() function. The additional filter is not necessary. But it got rid of the error.

My theory is that when SQL server parses the query, the Right() was verified against data that will be filtered out by the WHERE clause. I hope there is a way to verify that.