This topic describes how to create and manage data models in the DevExpress Report and Dashboard Server.
The Report and Dashboard Server separates the document presentation and data layer. You should first define a data model that provides access to your data provider (in the read-only mode) and obtains the specified tables and/or views from it. Once you created a data model, you can supply its data to any number of documents (reports and dashboards).
The Report and Dashboard Server supports the following data providers:
Note that the Report and Dashboard Server does not install any data provider (except Microsoft SQL Server). You should install data providers and register them in GAC.
See How to create a correct connection string for XPO providers for information on how to construct a connection string for each supported provider.
Use the following best practices when you create data models in the Report and Dashboard Server:
Click Data Models on the Report and Dashboard Server's toolbar to switch to the data models view.
This screen is available for users that have permissions to access and customize data models.
Click Add Data Model to invoke the New Data Model dialog.
Select the data provider and specify its connection settings.
You can specify connection parameters for Microsoft SQL Server, MySQL, Oracle, PostgreSQL and SyBase ASE. For other providers, enter the connection string in the Connection String field.
Enable the Set Custom Command Timeout option and specify Command Timeout.
The provider terminates the query if it exceeds the specified timeout and the report displays an error message. The timeout settings are available for all providers except XML Dataset.
Note that the Report and Dashboard Server backend uses the Network Service account. If a data model uses Windows authentication to connect to a database, ensure the Network Service account has permission to access the database. See the How To: Use the Network Service Account to Access Resources in ASP.NET MSDN topic for information on how to provide permissions to Network Service.
The created data model appears in the list. Select the model to see its details.
Click Edit to change connection settings.
Click Tables to open the Tables tab in Edit Data Model Schema.
Check tables to include them in the data model. You can also select which columns a table should contain. Use the search boxes to find specific tables or columns.
Click Procedures to open the Edit Data Model Schema dialog's Procedures tab.
Check procedures you want to add to the data model. Select a procedure to see its fields in the Fields list.
Click Queries to open the Edit Data Model Schema dialog's Queries tab.
Click the New Query button to open the Query Builder and create a query.
To add a table or view to a query, drag it from the Available tables and views section and drop onto the design surface.
Check columns that you want to include in the query.
You can join multiple tables within the same query. Drag and drop a subordinate table to include it in a query and create an inner join relation based on a key column.
Click the data relation to display its properties in the Relation Properties section. You can define the join type (Inner or Left Outer) and the logical operator to apply (for instance, Equals to, Is greater than, etc).
Select a column to access its properties. You can sort column records, group data by this column or apply an aggregate function.
You should apply grouping and/or an aggregate function either to all selected columns or to none of them.
Expand Query Properties and specify the query name. Click the Filter property's ellipsis button to invoke the Filter Editor to filter query results against static values, other fields' values, parameters, and/or functions.
Click OK to apply the filter, and save the query.
Use custom SQL queries at your own risk, as they may cause security issues.
Custom queries are available when the Custom SQL Query Execution Mode option is set to Execute SELECT Statements Only or Execute All. See General Settings for more information.
Click New Query and select Write Custom SQL Query.
Write the query and give it a name.