| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Connection Mapping"
(→Example XML file) |
|||
| (8 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| + | =Overview= | ||
XLCubed Web Edition supports the re-targeting of connections for reports published to the web server. | XLCubed Web Edition supports the re-targeting of connections for reports published to the web server. | ||
| − | To enable this feature, the {{Code|ConnectionMapping.xml}} file must be created in the {{Code|Xml}} subfolder of the XLCubed Web installation folder. | + | To enable this feature, the {{Code|ConnectionMapping.xml}} file must be created in the {{Code|Xml}} subfolder of the XLCubed Web installation folder. |
| + | |||
| + | =Using= | ||
| + | |||
| + | The {{Code|ConnectionMapping.xml}} file can contain: | ||
| + | |||
* one or more {{Code|connectionmap}} nodes (for cube connections) each of which must contain a {{Code|from}} connection definition and a {{Code|to}} connection definition | * one or more {{Code|connectionmap}} nodes (for cube connections) each of which must contain a {{Code|from}} connection definition and a {{Code|to}} connection definition | ||
** {{Code|connectionmap}} can map published connections by setting the appropriate {{Code|XL3_CONNECTION_TYPE}} and {{Code|XL3_WEB_CONNECTION...}} properties, as found in the published connection file. These take priority over a standard match on server/database/cube (from [[Version 8.1]]) | ** {{Code|connectionmap}} can map published connections by setting the appropriate {{Code|XL3_CONNECTION_TYPE}} and {{Code|XL3_WEB_CONNECTION...}} properties, as found in the published connection file. These take priority over a standard match on server/database/cube (from [[Version 8.1]]) | ||
* one or more {{Code|sqlconnectionmap}} nodes (for relational connections) each of which must contain a {{Code|match}} Regular Expression element and a {{Code|connectionstring}} to replace it (from [[Version 8]]) | * one or more {{Code|sqlconnectionmap}} nodes (for relational connections) each of which must contain a {{Code|match}} Regular Expression element and a {{Code|connectionstring}} to replace it (from [[Version 8]]) | ||
| + | ** Note that as the {{Code|match}} is a regular expression any {{Code|\}}s must be escaped by using {{Code|\\}}. These often occur in named instances. | ||
| + | ** You can have multiple {{Code|match}} elements, in which case all must match for the connection to be mapped. | ||
** {{Code|sqlconnectionmap}} nodes may contain an {{Code|equals}} element rather than {{Code|match}} (from [[Version 8.1]]) | ** {{Code|sqlconnectionmap}} nodes may contain an {{Code|equals}} element rather than {{Code|match}} (from [[Version 8.1]]) | ||
After modifying this file, IIS must be restarted. | After modifying this file, IIS must be restarted. | ||
| + | * New in [[Version 9]]: {{Code|connectionmap}} and {{Code|sqlconnectionmap}} can optionally include a "path" attribute, so the connection mapping only applies to the path specified. The mapping will apply to sub-folders unless there is any mapping with the same path: | ||
| − | + | <connectionmap path="Folder\Sub Folder"> | |
| − | |||
| − | <connectionmap path="\ | ||
| Line 23: | Line 30: | ||
* matches cube connections to the DEVELOPMENT server and replaces them with connections to the PRODUCTION server | * matches cube connections to the DEVELOPMENT server and replaces them with connections to the PRODUCTION server | ||
* matches SQL connections to any of the servers DEVELOPMENT1, DEVELOPMENT2 and DEVELOPMENT3 and replaces them with connections to the PRODUCTION server'' | * matches SQL connections to any of the servers DEVELOPMENT1, DEVELOPMENT2 and DEVELOPMENT3 and replaces them with connections to the PRODUCTION server'' | ||
| + | * Note that the database and cube names you map to are case sensitive. | ||
<pre> | <pre> | ||
| Line 35: | Line 43: | ||
<variable type="String" name="database"><value>Demo Database</value></variable> | <variable type="String" name="database"><value>Demo Database</value></variable> | ||
<variable type="String" name="cube"><value>Demo Cube</value></variable> | <variable type="String" name="cube"><value>Demo Cube</value></variable> | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
</connection> | </connection> | ||
</from> | </from> | ||
| Line 49: | Line 51: | ||
<variable type="String" name="database"><value>Demo Database</value></variable> | <variable type="String" name="database"><value>Demo Database</value></variable> | ||
<variable type="String" name="cube"><value>Demo Cube</value></variable> | <variable type="String" name="cube"><value>Demo Cube</value></variable> | ||
| − | |||
| − | |||
| − | |||
<variable type="Boolean" name="requirespassword"><value>0</value></variable> | <variable type="Boolean" name="requirespassword"><value>0</value></variable> | ||
| − | |||
<props /> | <props /> | ||
</connection> | </connection> | ||
| Line 66: | Line 64: | ||
</connectionmapper> | </connectionmapper> | ||
</pre> | </pre> | ||
| + | |||
| + | ==Downloading from Web== | ||
| + | You can control whether or not the connection mapping is undone when downloaded as “Live” using a setting in the web.config file, “UnmapConnectionsOnExport”. If true the connections are restored to their original values, if false then the connection mapping is preserved in the downloaded file. | ||
| + | |||
| + | https://help.xlcubed.com/Customising_web_config | ||
| + | |||
| + | |||
| + | ==See Also== | ||
| + | * [[Bulk Connection Editor]] for direct remapping of connections in XLCubed Excel workbooks | ||
| + | * [[Connection Parameter Web Extension]] for controlling connection mapping through [[Web Parameters]] | ||
[[Category:Web Edition]] | [[Category:Web Edition]] | ||
Revision as of 13:46, 8 March 2022
Overview
XLCubed Web Edition supports the re-targeting of connections for reports published to the web server.
To enable this feature, the ConnectionMapping.xml file must be created in the Xml subfolder of the XLCubed Web installation folder.
Using
The ConnectionMapping.xml file can contain:
- one or more connectionmap nodes (for cube connections) each of which must contain a from connection definition and a to connection definition
- connectionmap can map published connections by setting the appropriate XL3_CONNECTION_TYPE and XL3_WEB_CONNECTION... properties, as found in the published connection file. These take priority over a standard match on server/database/cube (from Version 8.1)
- one or more sqlconnectionmap nodes (for relational connections) each of which must contain a match Regular Expression element and a connectionstring to replace it (from Version 8)
- Note that as the match is a regular expression any \s must be escaped by using \\. These often occur in named instances.
- You can have multiple match elements, in which case all must match for the connection to be mapped.
- sqlconnectionmap nodes may contain an equals element rather than match (from Version 8.1)
After modifying this file, IIS must be restarted.
- New in Version 9: connectionmap and sqlconnectionmap can optionally include a "path" attribute, so the connection mapping only applies to the path specified. The mapping will apply to sub-folders unless there is any mapping with the same path:
<connectionmap path="Folder\Sub Folder">
When a user loads a Report to view, the Web Edition will automatically search for any connections matching a from or match definition, and replace them with the corresponding definitions. For cube connections, the match is done using the Type, Server, Database and Cube of the from connection.
The ID number and name for the connection will be left unchanged.
Example XML file
This example:
- matches cube connections to the DEVELOPMENT server and replaces them with connections to the PRODUCTION server
- matches SQL connections to any of the servers DEVELOPMENT1, DEVELOPMENT2 and DEVELOPMENT3 and replaces them with connections to the PRODUCTION server
- Note that the database and cube names you map to are case sensitive.
<?xml version="1.0" encoding="utf-8"?>
<connectionmapper>
<connectionmap>
<from>
<connection id="0" name="">
<type>AnalysisServices</type>
<variable type="String" name="server"><value>DEVELOPMENT</value></variable>
<variable type="String" name="database"><value>Demo Database</value></variable>
<variable type="String" name="cube"><value>Demo Cube</value></variable>
</connection>
</from>
<to>
<connection id="0" name="">
<type>AnalysisServices</type>
<variable type="String" name="server"><value>PRODUCTION</value></variable>
<variable type="String" name="database"><value>Demo Database</value></variable>
<variable type="String" name="cube"><value>Demo Cube</value></variable>
<variable type="Boolean" name="requirespassword"><value>0</value></variable>
<props />
</connection>
</to>
</connectionmap>
<sqlconnectionmap>
<match>Provider=SQLOLEDB;Data Source=DEVELOPMENT[1-3];Initial Catalog=MyDatabase;Trusted_Connection=Yes</match>
<connectionstring>Provider=SQLOLEDB;Data Source=PRODUCTION;Initial Catalog=MyDatabase;Trusted_Connection=Yes</connectionstring>
</sqlconnectionmap>
</connectionmapper>
Downloading from Web
You can control whether or not the connection mapping is undone when downloaded as “Live” using a setting in the web.config file, “UnmapConnectionsOnExport”. If true the connections are restored to their original values, if false then the connection mapping is preserved in the downloaded file.
https://help.xlcubed.com/Customising_web_config
See Also
- Bulk Connection Editor for direct remapping of connections in XLCubed Excel workbooks
- Connection Parameter Web Extension for controlling connection mapping through Web Parameters
