Where Clause and SSAS Calculations

Today I had to change business rule in our SQL Server Analysis Services 2005 solution. It would be hard to explain the scenario so I use an example.
Scenario: A class contains boys and girls. We need to calculate the passing percentage of students that are boys.

Previously the rule was to get passing percentage of boys.
Total number of boys that pass / Total number of students in class
Correct rule should have been:
Total number of boys that pass / Total number of boys
Yeah, I know this can be done in a giffy in SQL Server but Analysis services is a totally different ball game.

Constraint was that we had no dimension for gender like boys, girls. Instead two columns in fact table (yup, not good implementation) boys and girls with value 0,1.
To work around our constraint we created  Named Query in cube which returned all the dimensions along with every record where boys = 1.
Finally we had to filter measure based upon result dimension (P meaning Pass). So in Calculations tab of cube created a calculation as:

Round(([Measures].[Vw Get All Boys Count], [Result Dim].[Result].&[P])  / [Measures].[Vw Get All Boys Count] * 100,0)

And got the result we were expecting.

Alerts on SharePoint List

Today I had to troubleshoot a SharePoint environment where alerts were not working.
Firstly this environment was not configured to send mail. So I started by installing IIS's SMTP client component. Configured the relay server.
Then configured the 'Outgoing E-Mail Settings' in Central Administrator.
Added an alert on a custom list.
And upon adding a new item nothing happens... hmm... googled around... eliminated the fail points since this environment was working with anonymous access. And then got it working by:
Disable alerts on the site

stsadm.exe -o setproperty -pn alerts-enabled -pv "false" -url http://problemsite

Enable alerts on the site

 stsadm.exe -o setproperty -pn alerts-enabled -pv "true" -url http://problemsite

Added a new item in the custom list and it working like a charm. Also works when an anonymous user adds a list item.

External Links:
1. Troubleshooting Alerts
2. Configure outgoing e-mail settings

OLE DB Parameterized SQL Command in Execute SQL Task

There are very good articles on the internet explaining how to use different parameters in 'Execute SQL Task' control of SSIS.

I needed to use the same parameter twice in my query so this is what i did:

Since SSIS treats every other '?' used in SQL statement as a new parameter index I used the same variable twice. And the 'where' clause used is like:

WHERE (? = GetDate() OR CreatedDate > ? ) 

Reads where fromdate is equal to today or created date is greater than fromdate.

External Links

1. Running Parameterized SQL Commands Using the Execute SQL Task – I
2. Execute SQL Task on MSDN

Multi-Value Parameters, Stored Procedures and IN operator

Today, I was adding parameters to my Reporting Services report and made a very common mistake when using multi-value parameters. My assumption:

SELECT * FROM [SomeTable] WHERE ID IN ( @multiValueParameter ) 

No wonder it didn't work since this is interpreted as:

SELECT * FROM [SomeTable] WHERE  ID IN ( '1,2,3,4' )

instead of

SELECT * FROM [SomeTable] WHERE  ID IN ( '1','2','3','4' )

I recalled I had done this before in some other project and after a visit to the archive I found it.

Following is the solution (You will find same logic elsewhere but with use of a Split function to return table whereas I create a table in my procedure in other words skip creating a function):

DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned
DECLARE @TableOfArrayItems TABLE (Item NVARCHAR(100) collate database_default )  DECLARE @separator char(1) --Used in WHERE clause
BEGIN
      SET @separator =',' --Separator A.K.A. Delimiter
      SET @MultiValueFromRS = @MultiValueFromRS + @separator --append ',' at the end
      WHILE PATINDEX('%' + @separator + '%', @OpportunityLevel) <> 0
      BEGIN -- patindex matches the a pattern against a string
             SELECT @separator_position = PATINDEX('%' + @separator + '%',@MultiValueFromRS)
             SELECT @array_value = LEFT(@MultiValueFromRS, @separator_position - 1)  
             INSERT INTO @TableOfArrayItems(Item) VALUES(@array_value)
             
             --Moving to end of array
             SELECT @MultiValueFromRS = STUFF( @MultiValueFromRS, 1, @separator_position, '')
      END 
END    

And now you can use @TableOfArrayItems anywhere in you query/stored procedure like:

SELECT * FROM [SomeTable] WHERE ID IN ( SELECT ID FROM @TableOfArrayItems)         

And success!

External References:

1. The curse and blessing of dynamic SQL 
2. Using Multi-Value Parameters in Stored Procedures 
3. Passing multi-value parameter in stored procedure (SSRS report)

CRM Reporting Service PreFiltering

Today I was given task to enable PreFiltering on our internal CRM 4.0 using Reporting Services.
So I started by creating a very basic report in 'SQL Server BI Development Studio' on a random 'ContactList' table, just to see if I can enable preFiltering on it. After deployment to CRM I was unable to find 'Report Filtering Criteria' dialog box. Moreover I got the message when selecting "More Actions-->Edit Default Filter ..." from toolbar: 

"This report cannot have a default filter."

 After some googling I found out that preFiltering can only be enabled on FilteredEntityViews meaning in your CRM database under 'Views' you will find views pre-pended by 'Filtered' like FilteredEmail, FilteredLeads etc. For such views you can easily enable preFiltering. You just need to pre-pend 'CRMAF_' to the filtered view you are selecting and preFiltering would be available in CRM along with "Edit Default Filter" dialog box.
I think there is a way to created FilteredEntityViews in CRM but, unfortunately, that is out of the scope of this post.

Things I learnt:
1. PreFiltering cannot be applied to any table.
2. Use .
3. Make sure you include CRMAF_ alias name on all fields in query.
Helpful links for the day:
1. Context Sensitive Reporting
2. Inside Report Prefiltering
3. Reporting PreFilter

Change Header Banner of SharePoint Site

Client site is not that much development savvy. What they need to do is change the banner image from their SharePoint 2007 (MOSS) site:
This is the logo that needs to be changed:

Like other site this logo would not change through these settings

I needed a fast, simple and easy approach so what I did is:

1. Rename by banner image to "bl_logo.jpg"
2. Go to the "Site Content and Structure"
3. Take backup of existing "bl_logo.jpg" (precaution)
4. Upload the banner image to this library from step 1. Select "overwrite selected files"
5. The banner image would be visible on site.

This is the easy and fastest method that I found. Surely there are more "recommended" ways of doing the same but unfortunately that is not fast and easy. Nonetheless I would share them here.
External Articles:

1. A Quick Way to Add/Change the SharePoint Header Area Graphic 
2. Microsoft Office SharePoint Server 2007 – Custom Chrome / Branding