In Chapter 4, I told you that the PivotTable component embeds connection information into the XMLData property when you use its built-in ConnectionString and CommandText properties. This is true, but it does not embed this information if you use a DSC and bind the PivotTable component to it. This chapter's solution does just that, and it uses this technique to allow you to switch between an online OLAP server and an offline cube file. You can open the same reports while connected to either source because the connection information is not embedded in the XMLData property.
By default, this solution attempts to connect to an online server when you first load the page. If you have an OLAP Services server, you can enter the name of it in the input box and the solution will use that server as its data source. You can later click the Work Offline radio button, and the solution will prompt you for the location of the Sales.cub file included in the Data directory on your companion CD. The code that resets the connection is the SetConnection method, shown here:
Function SetConnection(dsc, fOffline) If fOffline Then ' If no offline cube file path exists yet, prompt ' for the location of the offline cube file If Len(m_sOfflinePath) = 0 Then ' Show the FindCubeFile.htm dialog box. This lets the user ' specify a file path or browse for the cube file. m_sOfflinePath = window.showModalDialog("FindCubeFile.htm",, _ "dialogHeight:160px;dialogWidth:400px") ' Check whether the user entered something If Len(m_sOfflinePath) = 0 Then SetConnection = False Exit Function End If 'User cancelled End If 'No offline cube path yet ' Set the connection string to use the cube file dsc.ConnectionString = "provider=msolap;" & _ "data source=" & m_sOfflinePath Else ' Prompt for the name of the online server if needed If Len(m_sOnlineServer) = 0 Then m_sOnlineServer = InputBox( _ "Enter the name of your online server:" & _ String(2, vbcrlf) & _ "(Must be an OLAP Services server with a Foodmart" & _ " sample cube)", "Foodmart Sales Analysis System", _ "(Foodmart Server)") ' Check whether the user entered something If Len(m_sOnlineServer) = 0 Then SetConnection = False Exit Function End If 'User cancelled End If 'No online server yet ' Set the connection string to indicate an OLAP Services server dsc.ConnectionString = "provider=msolap;" & _ "data source=" & m_sOnlineServer & _ ";initial catalog=foodmart" End If ' Return success SetConnection = True End Function 'SetConnection() |
The only difference between the connection string for an online server and the connection string for an offline cube file is that the data source attribute names a server in the online case and a path to a cube file in the offline case. This code prompts the user for a server name or a cube file location if not already specified and sets the DSC's ConnectionString property to the appropriate value.
When loading a report that uses totals, fieldsets, or fields that no longer exist in the current data source, the PivotTable control silently ignores those parts and drops them from the report. Therefore, if you build a report using a fieldset not available in your offline cube, the PivotTable control will display the portions of the report that it can and ignore the rest. But if you then save the report, the PivotTable control will return information for the parts of the report definition it was able to reload and will not return the information that is no longer valid for the current source.