Friday, October 23, 2009

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
      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, '')
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)

Monday, October 19, 2009

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

Friday, October 16, 2009

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: