Data Connections in Excel 2007

Every workbook that imports data from an external location uses a data connection to retrieve data from the data source.  There are two types of connections in Excel 2007 – Embedded, and Linked connections.

---

Embedded connection
Embedded connections exist within the file definition of the containing Excel document – they are bundled together.  This is useful in scenarios where there is no central data connection library available, or for when you want to deploy a completely standalone solution.

Linked connection
Linked connections are connections that are stored in a separate file with a .odc (Office Data Connection) extension.   Using ODC files is good for scenarios where you have many different workbooks that utilize identical data connections.  You can deploy the connection file to a central location, where multiple workbooks can access it.  If the connection details ever need to be changed, only a single file needs to be updated, rather than updating the connection within every single workbook.

---

Within Excel both types of connections are utilized in the exact same way, there is no functional difference.

A single connection within an Excel workbook can be both embedded and linked at the same time.  Whenever you use the data connection wizard to setup a new connection file, the file is referenced by that connection by default.  However if you go into the connection settings within Excel and change any part of the connection, you will get a warning that the connection in the workbook will no longer match the linked connection, and the link to the external connection will be severed.

Optionally, the workbook can be forced to use the linked connection exclusively by checking the “always use connection file” option within the connection properties menu  (see screenshot to the right).  By doing this, Excel will always reference the external connection- even when it differs from the connection within the file.

The benefit of leaving “always use connection file” unchecked is that if one connection fails for whatever reason, the workbook will try the other one before giving up completely.  This gives you two shots at making the connection!

The VBA script below is really useful if you need to flatten a pivot table output into a regular table with repeating labels.  Select a column, run the macro, and the blanks are filled!

source http://www.contextures.com/xlfaqPivot.html#Repeat 

The row headings show once in a Pivot Table, and there's no setting you can change to force them to repeat. To create a table with a heading on each row, you could copy the pivot table, paste it as values in another location, and fill in the blanks, using the technique shown here: http://www.contextures.com/xlDataEntry02.html.

Fill Blank Cells Programmatically

If you frequently have to fill blank cells, you may prefer to use a macro. The following code will fill blank cells in the active column:

Sub FillColBlanks() 'by Dave Peterson  2004-01-06 'fill blank cells in column with value above Dim wks As Worksheet Dim rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks    col = activecell.column    'or    'col = .range("b1").column    Set rng = .UsedRange  'try to reset the lastcell    LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row    Set rng = Nothing    On Error Resume Next    Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _                   .Cells.SpecialCells(xlCellTypeBlanks)    On Error GoTo 0    If rng Is Nothing Then        MsgBox "No blanks found"        Exit Sub    Else        rng.FormulaR1C1 = "=R[-1]C"    End If    'replace formulas with values    With .Cells(1, col).EntireColumn        .Value = .Value    End With End With End Sub

With Reporting Services 2008 came a new report authoring tool geared towards business & power users.  The tool is called Report Builder 2.0, and is available as a standalone download. I have to say this tool works really well with Analysis Services 2008 cubes. You can browse the cubes just like you do in SQL Management Studio cube browser, and can even view/copy the MDX generated by the designer.  Report Builder has been a nice addition to my toolbox.

You can download Report Builder 2.0 here: http://www.microsoft.com/downloads/details.aspx?familyid=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

And here is some good but outdated background info from one of the members of the SSRS team at MS: http://blogs.msdn.com/bwelcker/archive/2007/12/11/transmissions-from-the-satellite-heart-what-s-up-with-report-builder.aspx

After running these scripts you can easily view the underlying MDX query for any pivot table that uses a SSAS cube for its datasource:
 

Use the following script to add a show MDX Query option when you right click a pivot table in Excel 2007:

Private Sub Workbook_Open()    Dim ptcon As CommandBar         Set ptcon = Application.CommandBars("PivotTable context menu") insertDisplayMDX:    Dim cmdMdx As CommandBarControl    For Each btn In ptcon.Controls        If btn.Caption = "MDX Query" Then GoTo doneDisplayMDX    Next btn       ' Add an item to the PivotTable context menu.    Set cmdMdx = ptcon.Controls.Add(Type:=msoControlButton, temporary:=True)       ' Set the properties of the menu item.    cmdMdx.Caption = "MDX Query"    cmdMdx.OnAction = "DisplayMDX"        doneDisplayMDX: End Sub

And put this in a seperate module:

Sub DisplayMDX()     Dim mdxQuery As String     Dim pvt As PivotTable     Dim ws As Worksheet        Set pvt = ActiveCell.PivotTable     mdxQuery = pvt.MDX         ' Add a new worksheet.     Set ws = Worksheets.Add     ws.Range("A1") = mdxQuery End Sub

Works great!

The code comes from http://sqljunkies.com/WebLog/sqlbi/archive/2007/01/18/26875.aspx