Reporting Services 3: Report Model Project

Reports designed by developers cannot cover all the content to be displayed in the Organization. End users often need to design their own reports, but they are faced with the difficulty of not understanding the structure of the business database. In this case, there is naturally a division of labor between developers and end users: the developers provide an explanation (or translation) of the business database structure ); based on explanations (or translations), end users can create ad hoc reports that meet their own needs.

First, let's take a look at how developers can interpret or translate the business database structure.

The interpretation or translation of the business database structure is completed through the report model project. A report model project can be considered as a container, which consists of one or more data sources, data source views, and report models:

1) the data source is used to provide connection information to the Business Database. The data source file extension in the Report Model Project is. Ds;

2) the data source view (dsv) can be viewed as the "View" of the previous business database, that is, in the data source view, you can use the structure of objects in the business database and make certain modifications to meet the needs of report presentation, such as defining the relationship and primary key between tables, creating name queries, and calculating columns, the extension of the data source view file in the project is. dsv;

3) The report model is a semantic model. It describes the metadata of the database referenced by the data source view and is the most important component of the report model project. The report model file extension in the project is. smdl, and smdl is the abbreviation of semantic model Definition Language (semantic model definition language). Like RDL, smdl is also based on XML syntax.

Note that the data source can only be SQL Server 2000/2005 or Analysis Services 2005, this is because the query translator used by the Report Server to process the semantic model needs to convert operations on the semantic model into corresponding SQL statements, and other data sources may be supported in the future; in addition, each report model can reference only one data source or data source view.

Figure 1 and figure 2 are instances of a report model file. One or more objects, folders, and pivoting can exist in a model. The entity is composed of roles,Attribute. The role describes the relationship between the object and other entities.AttributeThere are multiple forms: If bindingAttributeIf it is specified as a column name in dsvAttributeIt is called a source field. IfAttributeBy its expressionAttributeDecide, thenAttributeIt is called an expression. For example, the wizard will set a date for different date components.AttributeCreate a variant and a valueAttributeCreate sum, AVG, Min, and Max aggregation. IfAttributeIsfilterAttributeTrue and filterAttributeIs an expression that returns the logical value.AttributeIt is called a filter.

Figure 1 objects contained in the Report Model

Figure 2 objects contained in a report model object

Bids provides easy-to-use Wizard for the development of report model projects. The Wizard uses a data source view as the input, A report model file is output after calculating the table row count, calculating the column uniqueness, calculating the column width, and generating the processing rules for tables and columns, and relationships. The report model file is directly displayed in front of end users. Therefore, all objects, roles, and attributes must have unique and easy-to-think business names.

In this case, the data source view is the middle layer between the report model and the business database, and the report model is the middle layer of the report designer used by the Business Database and end users.

Note that each entity's identifyingattributes attribute set must have at least one member. That is to say, each entity must have an attribute set that identifies this entity. Otherwise, a compilation error occurs.

After a report model project is created, you must deploy it for access to the Report Server.

Figure 3 deployment options of a report Model Project

Next, let's take a look at how end users use the report model to create ad hoc reports.

When an end user creates an ad hoc report, the client must first have a report designer.

SQL Server 2005 reporting services report builder is a client report designer. It is a clickonce windows application that is easy to centrally manage and can be accessed from the Report Server.Program. The physical path of report builder on the server is X: \ Program Files \ Microsoft SQL Server \ mssql.3 \ Reporting Services \ reportserver \ reportbuilder. application. On the client, you can use the following methods: http: // (servername)/reportserver $ (InstanceName)/reportbuilder. application URL access, and download to the client similar to c: \ Documents ents and Settings \ Administrator \ Local Settings \ apps \ 2.0 \ lcqc7wwz. 25z \ vphj3nw5. the physical path of the ADG.

End users use the preceding URL to start the report designer, and in three available report la S (templates): [Table (Overview), matrix (cross table) and chart] and select a data source that is displayed with the report model name or perspective name, the report builder interface shown in Figure 4 appears: the Resource Manager manages the entities and fields of the preceding report model. When designing a report, you can customize the report.

Figure 4 Report Builder

in figure 4, we can see that the report has a filter option. What is the relationship between the filter here and the filter in the report model? In the report model, the source fields and expressions used as derived fields are easy to understand. They will eventually appear in the field list of the report designer, for filters, the first step is to take the filters as follows: developers Define "filters" in the report model ", this filter specifies that end users with different roles and permissions can only browse their own permitted data. In fact, this is not the case. The report model only responds to business-related objects in dsv in the eyes of end users and does not support data filtering for different user groups (if you need to filter data for different user groups, you can create a name query in dsv and display it to end users using different pivoting methods ). As mentioned above, the filter in the report model is a variant of the attribute. Its isfilter attribute is true and Its filter attribute is an expression that returns the logical value, if its hidden attribute is not true, It will be displayed in the report designer like other attributes. In fact, this provides end users with a better way to filter data: for example, we stipulate that the conditions for departing employees to meet are: [employees. birthday >=1980-1-1 (young people may prefer to try different jobs) ] | [employee. marital status = false (no one can hold him, huh, huh ......) and employee. gender = 'M' (since he is, of course, a male) ], this is a complicated logic (of course, the example may not be very appropriate). End users may not be clear about this logic, in this case, developers need to set the above expression in the report model as a filter "filter _ is it possible to leave ". The filter option in the report designer is used to limit the data to be displayed in the report, end users only need to set the filter to "filter _ is it possible to leave = true" for the report to show only the data of employees who may leave the report, in the report model, "filter _ may resign" is represented as a filter, while in the report designer, it is represented as a report available field, that is, the filter in the report model is used to make it easier for end users to filter data in the report designer.

Figure 5 filter in Report Model Designer and report designer

After using report builder to design a report, end users can use "Run Report" to preview the report execution and save the Report on the Report Server, you can use this server-side report to display data by specifying the correct report path in the application (figure 6 ). Because this kind of ad hoc report is designed by end users, end users are not responsible for referencing the report in the application. This work should be automatically completed by the application, in future articles, we will introduce how to implement this task.

Figure 6 ad-hoc reports displayed in web projects (click a thumbnail to view the larger graph)