Word/Excel: Tips on moving objects

In word <=2003, to fine tune the object movement, hold the "Alt" key when moving objects.
However, It's seems that this feature was removed from word 2007. (Please correct me if it is wrong) Every tune treat as fine tune...

For excel, the "Alt" movement could be used to keep an object close to grid lines

When moving with holding the "shift" key , the objects will move in straight line (horizontally/vertically)

The "ctrl" key revering to copying the objects by dragging

Excel 2007: Multiple Object Selections

Where does the Object Select Pointer in Drawing gone?

It's here: [Home] > Find & Select > Select Objects

Since it allows multiple objects selection, it's my best friend in drawing with Word.

When the first time on using the Word, I just lost this old friend for a few hours...

SSAS:EXCEL 2007: Data Filter in Pivot Table

If the filters in Excel 2007 SSAS Pivot Tables could not be found,
try installing the SP2 of the SQL05.
Yes, it's problem of SQL, not Excel.

However, the feature is solely for Excel 2007 (pivot table 12). if you save the format as <2003 (.doc format), the filter feature will be loss.

It's one of BI feature as "mentioned" technet library:
What's New in SQL Server 2005 SP2
http://technet.microsoft.com/en-us/library/bb283536(SQL.90).aspx
The 2007 version of Microsoft Office requires the installation of SQL Server 2005 Analysis Services SP2 to support all its business intelligence features

The BI features in Excel 2007 is highlighted under the Excel Team Blogs
http://blogs.msdn.com/excel/archive/2007/05/06/excel-2007-and-sql-server-analysis-services-2005-service-pack-2.aspx
following filter types are enabled in PivotTables connected to Analysis Services:

• Label Filters, including the ability to filter based on member properties
• Date Filters
• Value Filters, including enhanced Top 10 Filters, evaluated in the context of the PivotTable (ex.: Top 5 customer list per year)
• Expanding/collapsing items of attribute hierarchies placed next to each other on rows or columns

Just don't know why it's not mentioned in the specification of Excel or SQL...
I found this feature accidentally when trying the Performance Point Service(PPS). The PPS has a minimum requirement of SQL service pack 2.

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

Excel XP/2003/2007 中文支援&簡繁轉換

EXCEL 2003簡繁轉換
Office 2003 簡繁轉換增益集 (也適用於 Access2003和 Powerpoint 2003)
安裝後會多出這個東西:

(中文Excel)

(英文Excel)


選好CELL,然後選"簡->繁" 或 "繁->簡"就ok
---------------------------------------------
EXCEL XP簡繁轉換
Office XP 簡繁轉換增益集
也是選好CELL,然後選"簡->繁" 或 "繁->簡"就ok

---------------------------------------------
EXCEL 2007簡繁轉換
選好CELL,然後在"校閱"(REVIEW)Tab下按上,"簡->繁" 或 "繁->簡"就完成

英文機方面,如果出現
"Word is unable to proceed. More than one file necessary to do TC/SC translation is missing",
就需要另行安裝中文Language Pack, 詳見此編

--------------------------------------------

Microsoft Office XP/2003/2007 Language Setting

如Excel內容出現亂碼情況(如繁/簡 問題), 可嘗試到這裡加設繁/簡支援,或設定預設語言:

程式集/所有程式->Microsoft Office->Microsoft Office 2003/2007 語言設定



最下方為預設語言

Excel MCAS Syllabus

I will try to fill in the belows with video/step by step screenshot

0.Office->1.New Worksheet Tab ->right click->Insert
0.Office->2.Open
0.Office->3.Save
0.Office->4.Save as
0.Office->5.Print
0.Office->6.Prepare->1.Properties
0.Office->6.Prepare->2.Inspect/Encrypt/Signature/Final/Compatibility
0.Office->7.Send
Q.Quick Access Toolbar->2-3 Undo Redo
Q.Quick Access Toolbar->Compare and merge workbooks
1.Home->1.Clipboard
1.Home->2.Font
1.Home->3.Alignment
1.Home->4.Numbers
1.Home->5.Styles->1.Conditional Format
1.Home->5.Styles->2.Format as Table
1.Home->5.Styles->3.Cells style
1.Home->6.Cells->1.Insert/2.Delete
1.Home->6.Cells->3.Format->1.Cell Sizes
1.Home->6.Cells->3.Format->2.Visibility
1.Home->6.Cells->1.Insert/2.Delete
1.Home->6.Cells->3.Format->2.Visibility
1.Home->6.Cells->3.Format->3.Organize Sheet
1.Home->6.Cells->3.Format->4.Protect
1.Home->6.Cells->3.Format->4.Format Cell
1.Home->7.Editing->1.Autosum
1.Home->7.Editing->2.Fill
1.Home->7.Editing->3.Clear
1.Home->7.Editing->3.Sort & Filter->1.Sort
1.Home->7.Editing->3.Sort & Filter->2.Filter
1.Home->7.Editing->4.Find
1.Home->7.Editing->4.Find
2.Insert->1.tables->1.Pivot Tables
2.Insert->1.tables->2.Table
2.Insert->2.Illustration>1.Picture/2.Clipart/3.Shape
2.Insert->2.Illustration>4.Smart Arts
2.Insert->3.Chart
C.Chart
2.Insert->4.Hyperlink
2.Insert->5.Text->1,3,4,5
2.Insert->5.Text->6-object
2.Insert->5.Text->2.header&footer H.header&footer
3.Page Layout->1.Themes
3.Page Layout->2.Page Setup->1.Margin
3.Page Layout->2.Page Setup->2.Orientation/3.Size 3.Page Layout->3.Scale to Fit
3.Page Layout->2.Page Setup->4 Print Area
3.Page Layout->2.Page Setup->5 Page break
3.Page Layout->2.Page Setup->6 Background
3.Page Layout->2.Page Setup->7 Print titles
3.Page Layout->4.Sheet Options
3.Page Layout->5.Arrange
4.Formula->1.Function Library Formula Bar
4.Formula->1.Function Library->Autosum/math
4.Formula->1.Function Library->math
4.Formula->1.Function Library->Lookup&Ref
4.Formula->1.Function Library->logical
4.Formula->1.Function Library->Text
4.Formula->1.Function Library->Financial
4.Formula->1.Function Library->Date&Time
4.Formula->1.Function Library->Insert function
4.Formula->2.Defined Name->1.Name Manager/2.Define Name/4.Create from selection
4.Formula->2.Defined Name->3. Use in formula
4.Formula->3.Formula Auditing->1.Trace Precedent/2.Dependents/3.Remove Arrows
4.Formula->3.Formula Auditing->4.Show Formula
4.Formula->3.Formula Auditing->5.Error Checking
4.Formula->3.Formula Auditing->6.Evaluate formula/7.watch
5.Data->1.Get External Data->1.From Access 4.From other Sources
5.Data->2.Conenction->3.Properties
5.Data->1.Get External Data->4.From other Sources 5.Data->2.Conenction->3.Properties
5.Data->1.Get External Data->2.From Web
5.Data->2.Conenction->3.Properties
5.Data->1.Get External Data->2.From Text
5.Data->4.Data tools->Text to Columns
5.Data->2.Conenction->4.Edit Links
5.Data->3.Sort&Filter->1.Sort
5.Data->3.Sort&Filter->2.Filter->1.Filter,Clear/Reapply
5.Data->4.Data tools->2.Remove Duplicates
5.Data->3.Sort&Filter->2.Filter->4.Advanced Filter
5.Data->4.Data tools->3.Validation
5.Data->4.Data tools->4.Consolidate
5.Data->4.Data tools->5.What-if->1.Scenario
5.Data->4.Data tools->5.What-if->2.Goal Seek
5.Data->4.Data tools->5.What-if->3.Table
5.Data->5.Analysis->Solver
5.Data->6.Outline->1.Group
5.Data->6.Outline->2.unGroup"
5.Data->6.Outline->3.Subtotal
6.Review->1.Spelling
6.Review->2.Research/3.Treasure/4.Translate"
6.Review->2.Comment
6.Review->3.Changes->1.Protect worksheet/2.Protect workbook/5.Allow user to edit ranges
6.Review->3.Changes->3.Share workbook 4.Protect and share workbook
6.Review->3.Changes->6.Track Changes
7.View->1.Workbook Views
7.View->2.Show/Hide
7.View->3.Zoom
7.View->4.Window
7.View->4.Window->3.Freeze/4.Split
7.View->4.Window->9.workspace

Excel VBA performance

A nice guidelines on VBA performance
http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx#comments

Just would like add one more point:
Instead of re-inventing the wheel, Make use of the built in function.
For example, making use of pivot table is much faster than writing aggregation by mutliple for-loops.
Application.WorksheetFunction does good performance over looping too.

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.

R1C1

試驗性質Blog. 大家請隨便交流一下意見~

R1C1是Excel其中一個定位方法, R1C1也就是試算表上第一格的意思
另一個定位方法就是用字母為欄標的A1.
選R1C1為第一編的另一個原因,是因為每星期總會有人問我為什麼欄標變成數字.
大家如對EXCEL有任何疑問,請隨便發信息給我~

把R1C1還原成A1的方法:

• EXCEL 2003:
  [Tools]->[Options]->General Tab 左上角R1C1 Reference Type


• EXCEL 2007:
  Office Button->Excel Option(menu右下方)->Formula Tab->(working with formula) R1C1 Reference Type