Getting Percentage Over Subtotal in Matrix

I worked on a matrix report that needs percentage of each row against the subtotal of the second row group. There are 3 row groups. I looked around and tried different things like InScope(), Sum() with scope, etc. Nothing worked.

The problem was solved by a work around. So instead of trying to get the subtotal from the dataset, I used the ReportItems! Expression to get the subtotal directly from the subtotal cell. This can only be done in SSRS 2008 because in SSRS 2005, the subtotal field is not exposed. Here is the expression used to get the percentage:

=Iif(reportitems!txtEnvironmentSubTotal.Value is nothing, nothing, Format(SUM(Fields!Test_Check_Count.Value)/reportitems!txtEnvironmentSubTotal.Value, "0.0%"))
-- Ke

Data Warehouse Resources

A good article about general Data Warehouse concepts:
http://www.gantthead.com/content/processes/9076.cfm

Hide Duplicates Property

The Hide Duplicates property comes in really handy after I struggled on how to format the table to hide the duplicate cells. And its ability of formatting each column separately works perfectly.

SQL 2005 Scheduled Job Failed Due to Linked Server Access Error

There are two things I did to fix the error. I'm not sure why. But it worked.

1. On the linked server configuration screen select Be made using this security context. Then put in the SQl Authentication user name and password setup on the remote server.
2. Do NOT use the Run As User option. For some reason, eventhough the specified user can excute the stored proc without any problem, when run it in a scheduled job. It will fail.

Install Oracle Drive on Microsoft OS

Here is what I found out:

"
I ran into the same issue and this is how I resolved it. Go to http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/winsoft.html. Save Oracle Database 10g Release 1 (10.1.0.2) into your dard drive. In the saved directory under "Disk1" you will find an install directory. Under install, click on setup.exe. This will download the latest Oracle Universal Installer in your machine. This will only update your Oracle Installer to the latest version but will not install the latest database. Hope this helps. "

More info on the oracle forum
http://forums.oracle.com/forums/thread.jspa?threadID=285669

SSAS Process Error

I encountered an error when trying to process a SSAS cube. I haven't found out the root cause of it yet. But turning off the error reporting allowed me to get around it and get the cube processed. Details at:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/971e3e6b-a1a7-4a19-95af-78ef21d11678/

SQL Server Linked Server Windows Authentication Config

There is a good blog post about Winodws Authentication config for double-hop linked server.
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

If Windows Authentication setup is too much trouble, an alternative would be using SQL Server Authentication.
http://itknowledgeexchange.techtarget.com/itanswers/linked-server-error-message-on-a-sql-server-2005-machine/

Performance Improvement on Recursive CTE

One big finding for today is that when using recursive CTE, the leaner the recursive member table is the better. This basically means getting only the fields that are necessary for the recursive CTE. Any other fields such as Tile, Description are to be pulled after the recursive CTE. It also means doing the recursive as early as possible in the big overall query.

This practise brought a SQL query running time from 30 seconds to 1 second. Huge, huge difference!

Convert UTC Time to Local Time

To convert a UTC time to local time, we first find out the difference between the UTC and local time by getting the current UTC time and local time.

DECLARE @UTCtoLocalTime AS INT
Set @UTCtoLocalTime = DATEDIFF(hour, GETUTCDATE(), GETDATE())

Then we just need to add the difference back to the UTC time to get the local time.
DateAdd(hour, @UTCtoLocalTime, [UTC TIME])

The benifit of this approach is that the US daylight saving is considered.

Multi-Selection Parameter Reset to Blank

I encountered a situation where a multi-selection parameter is reset to blank sometimes by the Reporting Services.

Here is the reason that the Feature parameter was reset to blank or giving out error message:

Somehow the Reporting Service re-evaluates and invalidates the parameter values when a parameter is dynamically generated based on other parameter. In our case, Feature list is dynamically generated based on Primary Workstream selection. The work around is to use a table variable instead of straight SQL query to generate the dataset that populates the parameter values. For some reason the Reporting Services doesn’t re-evaluate the values if a table variable is used.

After some digging, I found a work around:
http://stackoverflow.com/questions/684250/ssrs-asp-net-reportviewer-parameters-reset-to-default-when-clicking-view-report

The following query is used:

DECLARE @FeatureList TABLE (Feature NVARCHAR(256))
INSERT @FeatureList
SELECT DISTINCT Cobalt_Common_Feature AS Feature FROM dbo.[Work Item]
WHERE Cobalt_Common_Feature IS NOT NULL
AND (@PrimaryWorkStream='_All Workstreams' OR Cobalt_Common_PrimaryWorkstream=@PrimaryWorkStream)
UNION SELECT 'ZZZ Unknown' AS Feature

SELECT * FROM @FeatureList ORDER BY Feature

How to create new SQL Server Instance

1. Run the full setup.exe. Ignore the version warning message is Service Pack is installed.
2. Add a new DB instance there
3. Upgrade to the current SP version

T-SQL Pivot / UnPivot Command

This is a very useful and efficient command for pivoting data.

E.g.

select * from dbo.tbl_BuildInformationField
pivot (
min(FieldValue)
for
FieldName in ([Name],[Message],StartTime,FinishTime)
) as p
where name = 'FirstTarget'

More information at:
http://www.unboxedsolutions.com/sean/archive/2004/08/30/302.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

Also UNPIVOT is equally handy in certain situations:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Here is how to unpivot multiple columns:
http://pratchev.blogspot.com/2009/02/unpivoting-multiple-columns.html

Take Advantage of the Calcuated Field

Get most of the detail level calculation done using the Calcuated Field helps simplify the Layout design.

Fixing The Custom MDX Lost in Report Designer Problem

Recently I encountered a problem with the SSRS 2005 Report Designer. When I opened a report with custom MDX code and go the data tab. The dataset view screen automatically went to the design view and wiped out the custom MDX code. The problem started occuring when I uninstalled SQL 2005 Express edition and installed the Enterprise edition. I found a post on the Internet that talks about similar situation. (http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fca63464-cffb-4208-99e4-c0a53c3686a0/)

Because of the issue mentioned in the post, the Report Designer couldn't parse the MDX code. It automatically went back to the design mode when that happends because that's the "Previouse State" of the data set.

Installing SQL Server 2005 SP2 fixed this problem.

Compare Just the Date Portion of DateTime

datediff(day,@d1,@d2)=0

TFS Reports

A good resource about SSRS and SSAS on TFS.
http://weblogs.asp.net/vblasberg/archive/2008/06/04/tfs-reporting-architecture-notes.aspx

MDX + T-SQL: Combining relational and multi-dimensional data into one query result set

Quite interesting
http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx

Complicated WHERE Clause

Goal:
The filter is as followed.

_All Releases
Overflow
Release 1
Release 2
Release 3
Release 4
Release 5

When user selects _All Releases, the filter should only filter out Overflow. When user selects Overflow, the filter should only take Overflow. When user selects Release 1, the filter should take Iteration 1 through 9. When user selects other releases, the filter should take the selected release.

Solution:
((@Release='_All Releases' AND (i.[Iteration Path] <> '\Cobalt Product Backlog\Overflow'))OR (@Release = 'Overflow' AND i.[Iteration Path] = '\Cobalt Product Backlog\Overflow')OR (@Release <> '_All Releases' AND @Release <> 'Overflow' AND (i.[Iteration Path] LIKE '\Cobalt Product Backlog'+ CASE @Release WHEN 'Release 1' THEN '\Iteration%' ELSE '\'+@Release+'%' END)))

Thanks to the following post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54046

MDX Using SUM() to Convert A Query to A MDX Statement

Using SUM() function allows you to convert a regular MDX query to a MDX statement. So it can be used in for calculated member. The post below talks about it:

http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/7c2b58e1-4b20-4f8b-866f-7c328add916d/

If more than one set if required to slice the data, wrap the sets and measure with SUM() function. It's like using a tuple that allows slicing by sets instead of members. See example:

SUM(([Measures].[_Microsoft_VSTS_Scheduling_CompletedWork], [WorkStreams], [Work Item].[System_WorkItemType].&[eScrum Sprint Task]))

MDX Dynamic Sets

Interesting stuff
http://www.sqljunkies.com/WebLog/mosha/archive/2007/08/24/dynamic_named_sets.aspx

MDX Iterative Calculations

I found this topic interesting. Will look at it later:

http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/84a04c2c-a758-4092-9ef7-74f51116cbb0/