10.3.5. Data composition schema wizard

<< Prev   Next >>

Data composition schema wizard is the DataCompositionSchemaWizard 1C:Enterprise language object. Use it to visually create the data composition schema. In addition, the data composition schema wizard is used in Designer during data composition schema editing.

For query data sets, the wizard automatically gets nested data sets from the query text and handles them as data set fields ‑ nested data set.

For object data sets, the wizard allows adding fields ‑ nested data sets.

Below, you can see an example of opening the data composition schema wizard window and subsequent serialization of the resulting composition schema in XML.

Procedure ReportEditorCommandBar (Button)

Wizard = New DataCompositionSchemaWizard;

Wizard.SetSchema(GetDataCompositionSchema());
Wizard.Edit(ThisObject);

EndProcedure

Procedure ChoiceProcessing(SelectionValue, Source)

If TypeOf(Source) = Type("DataCompositionSchemaWizard") Then

DataCompositionSchema = Source.GetSchema();
XMLWriter = New XMLWriter;
XMLWriter.SetString();
XDTOSerializer.WriteXML(XMLWriter, DataCompositionSchema, "dataCompositionScheme", "http://v8.1c.ru/8/data composition system/scheme");
Controls.DataCompositionSchemaText.SetText(XMLWriter.Close());
EndIf;

EndProcedure

Operation with the data composition schema is divided into the following steps, which include editing of:

• Data sets
• Data set fields
• Data set links
• Calculable fields
• Resources
• Parameters
• Templates
• Nested settings
• Data composition system settings

10.3.5.1. Editing data sets

The data composition system supports editing of the following objects:

• Query data set
• Object data set
• Union data set
• Query fields

When you add a data set, the application automatically generates a name and data source (if the data source does not exist).

The data composition schema entry field ‑ nested data set can be described in the query, as well as in the data set ‑ object.

You can use nested tables in the query text of data set ‑ object in the {SELECT} and {WHERE} expressions.

SELECT
GoodsReceipt.Date,
GoodsReceipt.Number,
GoodsReceipt.Vendor,
GoodsReceipt.Warehouse,
GoodsReceipt.Goods.(
LineNumber,
Product,
Price,
Count,
Sum
)
{SELECT
Date
Number,
Vendor.*,
Warehouse.*,
 Goods.(
 LineNumber,
 Product.*,
 Price,
 Count,
 Sum
 )}

FROM
Document.GoodsReceipt AS GoodsReceipt
{WHERE
GoodsReceipt.Date,
GoodsReceipt.Number,
GoodsReceipt.Vendor.*,
GoodsReceipt.Warehouse.*,
 GoodsReceipt.Goods.(
 LineNumber,
 Product.*,
 Price,
 Count,
 Sum
 )}

In the example above, query fragments describing the fields of nested tables available for settings are shown in bold.

10.3.5.1.1. Editing a data set ‑ query

To edit a data set ‑ query, you need to create a data query. To do this, you can use the query wizard or edit the query text directly in the data composition schema wizard window.

If the AutoFill check box is selected, the data composition system automatically fills the data composition schema fields based on the query.

10.3.5.1.2. Editing a data set ‑ object

To edit a data set ‑ object, you need to add/edit the name of the object containing data or add/edit fields and groupings.

10.3.5.1.3. Editing a union data set

To edit a data set ‑ union, you need to edit merged fields from the fields that are subordinate to the data set. You can add created data sets ‑ queries and objects to a data set ‑ union by dragging them with the mouse pointer.

10.3.5.2. Editing data set fields

When you edit fields, you can specify:

• Field title
• Field availability restriction
• Attribute fields availability restriction
• Field role
• Field presentation
• Ordering expression
• Hierarchy check method (data set and parameter)
• Field value type
• Field appearance

Note that you can edit the field role, the presentation, the ordering expression, the parameter, the value type, and the field appearance only at the top level of the data set hierarchy.

If the data set field does not have a title set, the data composition system attempts to generate a field title based on the synonym of the query field. If there is no synonym for the field, the field data path is used as the field title, supplemented with spaces using the algorithm to convert names to synonyms. Synonyms for query fields are obtained only for fields that either have no alias set or are not different from the default alias for this field.

Synonyms for the query field are obtained as follows.

• If the query does not contain unions:
  • If the query field expression consists of a single field, the synonym is obtained from that field.
  • If a field expression is an aggregate function over a single field, the synonym for the field is derived from this field. For other expressions, it is considered that a synonym cannot be obtained.
• If the query contains unions:
  • In all unions, the system searches for a field for which a synonym used as a field synonym can be obtained. If no such field is found, the synonym cannot be obtained.

In the data composition schema wizard, there is an additional column with a check box indicating that the field title is set manually. The check box is automatically cleared if the field does not have a set title.

When filling fields based on a query, the wizard automatically fills titles of fields without synonyms and of those fields whose alias in the query differs from the default field alias. This way, the check box will be automatically selected for such fields.

If this check box is cleared, the Title column displays the title to be shown to the user. At the same time, the title in the field is not filled. The Title column for fields that have the check box cleared is not edited and is displayed in the disabled text color.

If the developer needs to change the title, they select the check box, and the field title text is filled based on the text that was previously displayed in the Title column. If the check box is cleared, the wizard clears the title text and starts displaying the automatically generated title in the title.

The field role is initially defined in the query, but you can change it in a separate dialog box.

For the count field, explicitly specify a reference to the count type in the View parameter.

For the dimension field, specify the path to the parent dimension data in the Dimension parameter.

If the Ignore NULL check box is selected, group records for this field will not be included in the result if it contains the NULL value.

In the resulting data set, fields with selected Required check box will always be present if at least one field from its data set is used in the settings. For example, you need to get expanded balances by extra dimensions. In this case, if the Extra dimension field is not used in the query, the balances will be collapsed.

You can also edit field ordering expressions, value type, and appearance in a separate dialog box.

10.3.5.3. Editing data set links

In case of multiple top-level data sets, you can set up links between them by one or several fields.

Link source and destination are data sets. Source and destination expressions are data set fields.

10.3.5.4. Editing calculable fields

On the Calculable Fields tab, you can create and edit the following properties/characteristics of calculable fields:

• Data path
• Expression
• Title
• Availability restriction
• Presentation expression
• Ordering expression
• Value type
• Appearance
• Available values

Ordering expressions are edited in a separate dialog box.

10.3.5.5. Editing resources

You can calculate resources by all fields of all data sets and by calculable fields. The left tabular field shows a list of available and unused fields. The right tabular field displays the fields by which the totals and their calculation expressions will be generated.

By default, the Sum function is set for numeric fields, and the Count function is set for non-numeric fields. Click >> to add all fields of the Number type to resources. Note that you can add multiple lines for a single resource. When the template composer gets an expression for a resource, it checks the target grouping and generates the expression for that grouping.

In the editing dialog box of the Calculate by column, strings with the field name and the Hierarchy keyword are added for fields that can be hierarchically grouped. The expression will be used for hierarchical grouping records by the field specified before the word. When you select a grouping broken down by which a resource can be calculated, you can select both a regular and hierarchical field at the same time.

If you have set the resource to be calculated only broken down by a certain grouping (you have selected at least one grouping field in the Calculate by column), this resource is output to the results only for this grouping and its nested groupings.

10.3.5.6. Editing parameters

Parameter editing includes:

• Parameter name editing
• Title editing
• Editing of available parameter types and values
• Defining the value and availability of the parameter value list
• Defining the expression
• Defining the parameter as an available data composition setting field
• Availability restriction
• Defining whether the parameter is required
• Defining whether the parameter is used
• Specifying editing parameters

If the parameter value is undefined, it is assumed that the value is a zero reference to the specified type. Parameters can use predefined data and enumerations (Designer mode).

Values that can be selected by the user as parameters of the data composition schema are edited in the Available values column. If the check box in the Value list is available column is selected, several parameter values can be used.

Here is an example of using the required parameter of the data composition schema. Imagine that the Company parameter must be always specified in a report. In the data composition schema, for the Company parameter, the Use property is set to Always, and the Prohibit empty values property is set to True.

When the report is being used, the Use property is unavailable to the user (it is absent), and the empty value is displayed in the usual way marked by a wavy red line. If the user does not fill the parameter value and runs the report for execution, the system will prompt the user to fill the parameter value.

10.3.5.7. Editing templates

To add templates, click Add template on the command bar.

You will be prompted to select one of the following template options:

• Field template
• Grouping template
• Grouping title template
• Table resources template

For the grouping template and grouping title, you can specify the grouping name/grouping fields, and template type.

For the table resource template, you can specify templates for two groupings at the intersection of which it is located.

The Area tabular field column indicates template area coordinates in the spreadsheet document.

To edit spreadsheet document areas, press Alt + Enter to go to the properties panel. You can edit both cell appearance and contents, as well as its drill-down parameter.

You can edit templates in all languages supported by the application. When you specify a parameter or template for a spreadsheet document cell, the parameters are added to the template and are displayed in the Parameter name column of the Template parameters table field. You can edit the template parameter expression. When loading, template areas are separated by empty lines.

10.3.5.8. Nested schemas

On the Nested schemas tab, you can create and edit nested data composition schemas. Nested schemas edited by the data composition schema wizard can serve as a schema.

10.3.5.9. Settings

The data composition schema contains default data composition settings that can be specified by the developer.

The way you set up the Auto resource position report defines how resource fields will be displayed:

• Do not use. In this case, resources are displayed in the order fields are listed on the Selected fields tab.
• After all fields. In this case, resources are displayed after all fields.

For groupings (or table groupings), the Grouping use case parameter controls the display of additional information after resource fields. If this parameter is not set or is set to Auto, the grouping is responsible for displaying the detailed records.

If the parameter is set to Additional information and is specified in the column grouping, one column will be displayed in the resulting table in this column. It will contain fields specified on the Selected fields tab of this grouping and fields available for display in row grouping. If grouping fields are specified for the column grouping, an error occurs when composing the template. The algorithm for the row grouping is similar, but only one row is displayed instead of a column.

For example, Products are displayed in table rows. Warehouses are displayed in table columns. You want to generate a report on goods inventory in warehouses. You need to display product SKUs after columns with remaining goods. To do this, add grouping without group fields to the table columns and set the Grouping use case parameter to Additional information in the grouping settings. On the Selected fields tab, specify the Product.SKU field.

When this grouping is displayed outside the table, all fields that are available in the grouping are displayed.

For example, if a report contains a grouping with multiple nested groupings If you use a grouping with the Grouping use case parameter set to Additional information, the parent grouping data between groups can be displayed.

For groupings set to Additional information, the following parameters are ignored:

• Location of grouping fields. Titles of fields displayed in groupings are always displayed at the beginning of the row or column.
• Number of records.
• Percentage of records.
• Placement of totals.
• Placement of grouping fields.
• Placement of groupings.
• Placement of overall totals.

During template composition, if there is a field in a grouping set to Additional information that is unavailable in the grouping or table, an exception is thrown.

10.3.5.9.1. Nested fields

Numeric fields

The application automatically generates nested fields for numeric resources. These fields are calculated automatically and facilitate data retrieval. For example, you can use them to get the percentage ratio of a resource value to the resource value total in all the report data. These fields are listed below.

ID:

PercentInHierarchy

Description:

This field contains the percentage of a resource value in the current hierarchy group. In nontable output, it is always 100%.

ID:

PercentInColumnOrPointHierarchy

Description:

This field contains the percentage of the resource value in the current cell to the value of the resource total at the current hierarchy level of the current grouping for a column or for a point. In nontable output, it is equal to the % in hierarchy group field value.

ID:

PercentInRowOrSeriesHierarchy

Description:

This field contains the percentage of the resource value in the current cell to the value of the resource total at the current hierarchy level of the current grouping for a row or for a series. In nontable output, it is always 100%.

ID:

GroupPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the resource total in the current grouping. In nontable output, it is always 100%.

ID:

ColumnOrPointGroupPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the resource total in the current group for a column or a point. In nontable output, it is always 100%.

ID:

RowOrSeriesGroupPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the resource total in the current group for a row or a series. In nontable output, it contains the % in grouping field.

ID:

ColumnOrPointPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the resource total for a column or a point. In nontable output, it contains the % total field.

ID:

RowOrSeriesPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the resource total for a row or series. In nontable output, it is always 100%.

ID:

OverallPercent

Description:

This field contains the percentage of a resource value in the current cell to the value of the overall resource total in the table. In nontable output, it is always 100%.

Fields that contain the type Date as part of their types

The application automatically generates nested fields for fields that contain the Date type among their types. These fields are calculated by the application, which simplifies display of date- and time-related data. For convenience, these fields are divided into three groups: Start dates (BeginDates), End dates (EndDates), and Date parts (DateParts). These fields are listed below.

"Beginning dates" group

ID:

BegOfDay

Description:

Date and time of the beginning of the day.

ID:

BegOfWeek

Description:

Date and time of the beginning of the week, considering the First day of the week property of the regional settings of the infobase.

ID:

BegOfTenDays

Description:

Date and time of the beginning of ten days.

ID:

BegOfMonth

Description:

Date and time of the beginning of the month.

ID:

BegOfQuarter

Description:

Date and time of the beginning of the quarter.

ID:

BegOfHalfYear

Description:

Date and time of the beginning of the half year.

ID:

BegOfYear

Description:

Date and time of the beginning of the half year.

ID:

BegOfMinute

Description:

Date and time of the beginning of the minute (if the date includes time).

ID:

BegOfHour

Description:

Date and time of the beginning of the hour (if the date includes time).

"End dates" group

ID:

EndOfDay

Description:

Date and time of the end of the day.

ID:

EndOfWeek

Description:

Date and time of the beginning of the week, considering the First day of the week property of the regional settings of the infobase.

ID:

EndOfTenDays

Description:

Date and time of the end of ten days.

ID:

EndOfMonth

Description:

Date and time of the end of the month.

ID:

EndOfQuarter

Description:

Date and time of the end of the quarter.

ID:

EndOfHalfYear

Description:

Date and time of the end of the half year.

ID:

EndOfYear

Description:

Date and time of the end of the year.

ID:

EndOfMinute

Description:

Date and time of the end of the minute (if the date includes time).

ID:

EndOfHour

Description:

Date and time of the end of the hour (if the date includes time).

"Date parts" group

ID:

Day

Description:

Day of the month (number).

ID:

WeekDay

Description:

Number of the day of the week (as the WeekDay() function returns, the week always starts on Monday).

ID:

WeekDayName

Description:

Weekday presentation (1 ‑ Monday, 2 ‑ Tuesday, and so on). Day number is obtained by the WeekDay() function.

ID:

DayOfYear

Description:

Number for the day of the year.

ID:

WeekOfYear

Description:

Number of week in the year, considering the First day of the week property of the regional settings of the infobase.

ID:

Month

Month

Description:

Number of the month of the year.

ID:

MonthName

Description:

Month name presentation (1 ‑ January, 2 ‑ February, and so on).

ID:

Quarter

Description:

Number of the quarter.

ID:

Year

Description:

Year value in 4-digit view (number).

ID:

Minute

Minute

Description:

Number of the minute.

ID:

Hour

Hour

Description:

Number of the hour.

10.3.5.9.2. System fields

In the list of selected fields, the application generates a dedicated group of system fields (SystemFields) used to define the sequence number of records both in the entire report and within groupings. Remember that system fields are not included in the list of fields that are obtained by expanding an auto field. Therefore, they must be added manually.

ID:

SystemFields.SerialNumber

Description:

Contains the sequence number of the row in the report. Numbering starts with 1.

ID:

SystemFields.GroupSerialNumber

Description:

Sequence number of the row in the current grouping. Numbering starts with 1.

ID:

SystemFields.Level

Description:

Current record level. Numbering starts with 1.

ID:

SystemFields.LevelInGroup

Description:

Current record level in relation to the grouping. Numbering starts with 1.

10.3.5.9.3. Parameter fields

The selected fields include a dedicated group named Parameters used to include data composition schemas with the Include in available fields check box selected in the report.

<< Prev   Next >>