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/