VBA Challenge

Whew, quite busy this week. Just keep posting on...

http://blogs.msdn.com/officepalooza/

A nice exercise on VBA.
I seldom use Powerpoint/Access VBA actually,
let's have a review lesson with it.

"Thrid Place" in Novice Challenge:

Novice Challenge Summary:

1.Excel: Range / Formating
2.Word: Edit:Replace
3.Excel: Editing(Formula,Range )
4.Word: Formating(FontName, Style)
5.Excel: Formating(Sheet Name)
6.PowerPoint: Slides(Edit Content0/Shapes
7.Excel: Edit(Paste Special)
8.Word:Reference:Insert from another word
9.Word:Editing(Page Break)/ statistics(Page Count)
10.PowerPoint:Custom Slide show

Experienced Challenge summary:
1.PowerPoint:Shapes
2.Word:Edit(Word)FileSystemObj(Or Read multiple doc)
3.PowerPoint:Slide(Content),Control Objs
4.Excel:Edit(Formula),Charts
5.Access:Form,DAO
6.Word,Access:Edit(Word),use DAO in Word
7.Access:DAO
8.Excel,PowerPoint: application.ontime, Lanuch powerpoint
9.PowerPoint:Random,Shapes,WAIT
10.Excel:Edit(Range,Sorting),Random

SSRS: SQL Function for handling multiple value parameter

CREATE FUNCTION dbo.fn_MVParam(@RepParam nvarchar(4000), @Delim char(1)= ',')RETURNS @Values TABLE (Param nvarchar(4000)) AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

For reference

From "Pro Sql Server 2008 Reporting Services"- APress

SSIS : Dynamic Excel Destination File Name

In suitation that you may need to dump logs periodically, dynamic expression of SSIS could help you through this.
For example, by using the date as name to create a daily log from query.

A simple solution could be done by the 'isqlw -o' or 'sqlcmd -o' command with bat scripting. However, the log is only available in text format. To obtain an Excel log, you may use the following sample.

1.Open the BIDS and create a blank SSIS project.
2.select the SSIS export and import wizard under "Project Menu"
3.Follow the steps in the wizard with entering the exact data source and an arbitrary Excel destination.
4.After the creation of package, add a "package" variable (e.g. DirectoryPath) for storing the destination path and file name string. (View->Other window->Variables)
5.Select the "expression" in properties of the Excel destination connection.
6.Add an expression for the property "ExcelFileName"
@[User::DirectoryPath] + "\\" + (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + ".xls"
7.When back to the properties listing. The ExcelFieldName was changed to date name style.
8.Turn the delayValidation property of the control flows and excel connection destination to false.
So to suppress the validation on non-existing file source.
9.Save, Deploy to SSMS job scheduler and Done!

The expression is provided from Zulfiqar.
http://zulfiqar.typepad.com/zulfiqars_web/2006/11/ssis_dynamic_fi.html
However, after adding the expression for the "Connection" property, the dtsx has became corrupted after run/reopen the project. By the way, The ExcelFileName was already fit enough for the purpose.

Check your DSV

29542=28562?

If you found something like 1+1 not equal to 2 in your SSAS cube results,
try checking the query of DSV in the first time.

The query builder today is too easy to use. On the other hand, it's too easy to make mistake with. For the problems that I encountered in a project, there are around 1/2 of the problems are come from the wrong SQL in the DSV.

Before checking the Dimension usage, Dimension, Role Settings... Try looking at the problem in the very back end first.