• A connection can be used by multiple SQL or OLAP reports.
• Select Type:
  • "SQL OLEDB" for SQL report type
  • "SQL Server" for SQL report type
  • "SQL ODBC" for SQL report type
  • "XMLA URL" for OLAP reports type
  • "Reporting Services" for SSRS report
• To delete a connection or connections select the connection and click the "Delete" button. Please note that the connection cannot be deleted if it is still used by a report or a ROLAP cube. The number next to the name of the connection represents the number of reports used by the connection. Click on the number to see the reports that are using this connection.
• To view database schema for the connection click DB Admin button.
• Click Test button to test the connection

To temporally disable a connection click the "Disable" button. All reports that are using this connection will be disabled. Connection disabling can be useful when access to OLAP reports need to be suspended while the cubes are being processed or a database is being restored during log shipping. The following SQL can be used is a SQL Server Agent job to disable a connection:

update dbo.Connection
set Disabled = 1--, Disabled = 0--to enable
where ConnectionName = 'xmla'

When opening a report with a disabled connection the user will see following message text:

Connection [ConnectionName] used by this report is temporarily disabled.

The text can be customizable by admin by clicking on the edit button next to the message or by going to Admin > Localize > Language = English, Phrase ID = 978. Note that [ConnectionName] is a variable that will be replaced by the actual connection name.

• SQL Server 2000
• SQL Server 2000 and 2008
• MS Access
• Oracle
• IBM DB2
• MySql
• Other

To create a new connection:

• Select Schema. This option is especially useful for Oracle when Database field is not available. To select all schemas select All.
• Enter Server. If the database has an instance, please use this format: Server1\Instance1
• Enter Database
• Enter Port Number. Optional field. The default SQL Server port is 1433.
• Windows Security - applicable to SQL Server. When this field is checked, the application will use the Windows user based on the Authentication mode (Anonymous, Basic or Windows) of ReportPortal virtual directory. User name and password will not be used when this field is checked.
• Enter User name. In case of SQL server, make sure that SQL Server is in mixed security mode.
• Enter Password
• Connection String will be automatically updated when the information in the above filed is changed. However, Connection String can be overwritten by a custom connection string generated manually or by a UDL file.
• Command Timeout is used to set the maximum seconds a database command can run unil it times out
• Click the "Add" or "Update" buttons to add or update a connection.
• The connection string is stored in the database in an encrypted format

XMLA URL

A XMLA URL connection should already be setup during the installation. If it is not, you can setup XMLA connection on the web server automatically using "Application Setup" (Start > Programs > Application Setup) utility.

It is also possible to setup XMLA service and create XMLA connection manually. Here are few articles that explain how to do this:

• Windows Server 2003
• Windows Server 2008

Once the XMLA service is installed you can register it within ReportPortal by creating XMLA Connection:

• Select Language. This is useful when SSAS has transitions for a specific language. This field will be overwritten if the user has Language field selected instead of the default option (Use site settings)
• Enter the URL to XMLA service in the Connection String field. Analysis Services 2000 should use the URL to XMLA SDK (xmla/msxisapi.dll). Analysis Services 2005 and up should use URL to msmdpump.dll.
• The URL can be full (http://localhost/xmla/msmdpump.dll) or it can be partial (xmla/msmdpump.dll). You should use partial URL if the web site can be accessed via multiple aliases (like http://www.reportportal.us/ReportPortal, https://www.reportportal.us/ReportPortal or https://75.127.117.119/ReportPortal)

To improve performance in connecting to your cube, please click the "Cache" button to cache the cube schema information. Please note that no cube data will be stored - just schema. This option might be useful if you wish reduce the amount it takes for a cube designer to connection to a cube to design a report. Whenever cube schema is changed the Connection Cache should be reset. Please click the "Reset Cache" button t recreate the cache or Delete Cache button to delete it completely.

Properties field allows for additional properties for XMLA connection:

• CommitTimeout - Determines how long, in seconds, the commit phase of a currently running XMLA command waits before rolling back. The commit phase corresponds to XMLA commands such as Statement or Process. A value of zero (0) indicates that the instance waits indefinitely.
• Timeout - Specifies, in seconds, the maximum time that the Analysis Services instance should wait for a request to be successful before returning an error. This property also determines the maximum time that the instance should wait for an update to a writeback table to be successful before returning an error, equivalent to the connection string property, Writeback Timeout.
• ForceCommitTimeout - Determines for how long, in seconds, the commit phase of a currently running XMLA command waits before forcing previously issued commands to roll back. The commit phase corresponds to XMLA commands such as Statement or Process. A value of zero (0) indicates that the instance waits indefinitely.
• SspropInitAppName - Contains the name of the client application.
• DbpropInitMode - A bitmask specifying access permissions.
• DbpropMsmdFlattened2 - Outputs all members of a parent-child hierarchy in a single table column in the flattened result, unless the parent-child hierarchy is requested on Axis 0. The Level template for output columns is not used.
• Dialect - You can use the Dialect property when you expect that most of queries will use one particular dialect over any other. Query syntax can be similar for language dialects, such as DMX and SQL. Because the syntax can be similar, Analysis Services may not be able to infer the dialect from the query syntax. If a query does not run in one dialect, the Analysis Services instance may try to run the query again in a different dialect. If the Dialect property is set, Analysis Services returns query execution errors in the dialect that has precedence, even if the provider tries to run the query again in another dialect. For example, the Dialect property is set to MDGUID_DM. The provider first tries to run the query as a data mining query, but this query fails. The provider then resubmits the query as an SQL query. However, this SQL query also fails. Because the value of the Dialect property is MDGUID_DM, Analysis Services returns a data mining error message, not an SQL error message. If the Dialect property is not set, Analysis Services returns query execution errors in the dialect last used. For example, the Dialect property is not set, and a data mining query fails. The provider then resubmits the query as SQL. The SQL query also fails. Because the Dialect property is not set, the provider returns an SQL error message instead of a data mining error message.
• DisablePrefetchFacts - When set to True, the engine stops trying to pre-fetch values for the length of the session.
• MdxMissingMemberMode - Indicates whether missing members are ignored in MDX statements.
• RealTimeOlap - When set to TRUE, indicates that all the partitions listening for table notifications are to be queried in real time, bypassing caching.
• SafetyOptions - Determines whether unsafe libraries can be registered and loaded by client applications. The value of this property also determines whether the PASSTHROUGH keyword is allowed in local cubes.
• SecuredCellValue - Specifies the error code and the values for the Value and Formatted Value cell properties to be returned when it tries to access a secured cell.
• SQLQueryMode - Determines whether calculations are included in SQL queries.
• VisualMode - Indicates whether the provider is to calculate visual totals, which dynamically totals child members of parent members specified in a set. When visual totals mode is on, displayed aggregate values are equal to the sum of the displayed values being aggregated.

This type of connection is used by Reporting Services reports.

For Connection String please enter URL to SSRS ReportServer service. The URL typically has this format: http://ServerName1/ReportServer. If SSRS has an instance then URL will have this format: http://ServerName1/ReportServer_Instance1

There are also global settings that applies to all Reporting Services connections. It is accessible via Admin > Settings > Reporting Services

You might need to configure SSRS Web Service URL first. On SSRS server, go to Start > Programs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager.