Data composition schema wizard

How to create data sets, data sources, queries, and query fields
How to specify a data composition schema
How to specify report structure
Settings

Use the data composition schema wizard to build a new data composition schema or edit an existing one.

On the Data Sets tab, you can add or edit data sets and data sources, as well as edit queries and query fields.

A data composition schema may contain multiple data sources. A data source is a source to retrieve data from. For example, you can use a 1C:Enterprise infobase as a data source.

Data sets in data composition schemas contain information on what fields can be retrieved from this set, what data set fields can be used for filtering, and so on. A data composition schema can use multiple data sets. A data set can contain descriptions of the fields available for this data set. Each data set field has the following properties:

  • Field. The name of the data set field.
  • Path. The string containing the data path of the field, which will be used to refer the field in settings and expressions. Note that the field appears with this exact name and not with the name specified in the Field property. If multiple data sets have fields with the same data paths, the field that belongs to the parent data set is used. You cannot specify identical data paths for unrelated data sets.
  • Title (field title). A string displayed in the user settings and in the title of the field composition result.
  • Field restriction. Specifies how this field cannot be used in the settings. The field usage is determined by the data set itself. In this property you can only add restrictions, but you cannot allow anything that is denied in the data set part description. For example, if a query indicates that a field can be selected but does not indicate that this field can be used in a filter, the absence of a filter restriction in this property does not make this field available for use in the filter. You can restrict the following usage scenarios:
    • Field. The field cannot be output to composition results (the field cannot be a selected field of the data composition system).
    • Condition. Filtering by the field cannot be performed (the field cannot be included in the data composition system filter).
    • Group. Grouping by the field cannot be performed (the field cannot be included in the data composition system grouping fields).
    • Ordering. Ordering by the field cannot be performed (the field cannot be included in the data composition system order).
  • Attribute restriction. Indicates how field attributes (fields that can be obtained from this field using "." (dot)) can be used in user settings. The usage scenarios that are subject to restriction are identical to those of field availability restrictions (Field, Condition, Group and Order)
  • Role (field role). Indicates how to handle the field.
  • Presentation expression. The expression used to calculate field presentation. It can be used to redefine a default field presentation.
  • Order expressions. Descriptions of expressions that are used when ordering by the field.
  • Hierarchy check. If a filter criterion for the field requires custom processing, you can specify a data set to obtain data for checking whether the reference belongs to a certain value's hierarchy.
    • Data set. A data set for the hierarchy check.
    • Parameter. A parameter where the value is assigned in order to get its child items.
  • Value type. A value type of the data set field.
  • Appearance. The appearance parameters of the data set field.

On the Data set links tab, you can specify links between various data sets, by a single field or multiple fields. Each data set link has the following properties:

  • Link source. The name of the source data set.
  • Link target. The name of the target data set.
  • Source expression. The expression that defines the source link value.
  • Destination expression. The expression that defines the target link value.
  • Parameter. The name of the parameter that stores the source link value. This parameter can be used in a target data set query, so that only data with the specified parameter value is retrieved.
  • Parameter list. Defines whether a value list can be used as a parameter value. A value list is allowed when a parameter is used in the IN operation.
  • Link condition. The expression whose result is checked before creating a link. The link is created only if the expression result is True. A link condition can be empty, in this case a link is always created.
  • Initial link value. The expression whose value is always used as the link source initial value. It is used to specify the initial value link when linking a data set to itself is required

On the Calculated fields tab, you can create and edit calculated fields (fields calculated using certain expressions that contain data set fields). Each calculated field has the following properties:

  • Data path. The display name of the field.
  • Expression. The expression for calculating the field value. Expressions can only include data set fields. Using calculated fields in expressions is not allowed.
  • Title. The field title displayed in user settings and in composition results.
  • Availability restriction. Specifies how this field cannot be used. The restriction is described similarly to data set field restrictions.
  • Presentation expression. The expression whose result is output to the field composition result. If the presentation expression is not specified, the value of the field expression is output to the field composition result.
  • Order expressions. The expressions used for field ordering. They are specified similarly to order expressions of a data set field.
  • Available values. Contains a list of available values.
  • Appearance. Contains the field formatting that can be applied to calculated fields.

On the Resources tab, you can describe the resource fields whose values are calculated for group records. Each resource field has the following properties:

  • Field. The path to the data of the field for which the total calculation formula is provided.
  • Expression. The expression used to calculate totals by field. Example: Sum(QuantityClosingBalance).
  • Calculate by. Allows you to select groupings for which totals are calculated (used when it is not necessary to calculate totals for all groupings)

To manage the list of fields, use the move buttons >, >>, <, and <<.

Use the >> button to add all fields of the Number type to the resources. Note that you can enter multiple rows 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.

If you have set the resource to be calculated only for a certain grouping (i.e. 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.

On the Parameters tab, you can describe data composition schema parameters. Each parameter has the following properties:

  • Name. The parameter name.
  • Title. The parameter title displayed in the user settings.
  • Type. The parameter type.
  • Available types. A set of available types.
  • Available values. A list of available values.
  • List of values is available. A check box showing whether the list of available values is used.
  • Value. The default parameter value.
  • Expression. The expression used to calculate the parameter value (for calculated parameters).
  • Functional option parameter.The functional option parameter name, as defined in Designer.
  • Include in available fields. Specifies whether the parameter is available as a field.
  • Availability restriction. Specifies whether a user can can set the parameter value.
  • Disallow incomplete values. A check box showing whether a fill check is performed for the parameter.
  • Usage. When set to Auto, indicates that the parameter usage is defined by data composition settings. When set to Always, indicates that the parameter is always considered to be used and its value defined in the composition settings is always used.
  • Edit options. Defines the editing parameters.

On the Templates tab, you can describe templates that are used for displaying the field, grouping, or grouping title. Each data composition schema template description has the following properties:

  • Name. The name that identifies the template.
  • Template type. The template type (header/footer/hierarchical header/hierarchical footer).
  • Area. A spreadsheet document area that is used as a template.
  • Template parameters. The template parameters. Each template parameter has the following properties:
    • Parameter name. The parameter name.
    • Expression. The expression used to calculate the parameter value.

On the Nested schemas tab, you can describe nested data composition schemas. Each nested data composition schema has the following properties:

  • Name. The nested schema name.
  • Title. The text displayed to a user when they select the nested object.
  • Schema. The data composition schema for the nested object.
  • Settings. The settings that are applied to nested object settings when the nested object is added to a parent object setup.

If required, you can also specify the data composition schema settings on the Settings tab.

Nested Fields
Number-based fields

The platform automatically generates nested fields for number-based resources. These fields are calculated automatically and facilitate the retrieval of various values. For example, one can use them to get the percentage of a resource value to the resource value total in all the report data. These fields include:

% in hierarchy group

Identifier:

PercentInHierarchy

Description:
Contains the percentage of a resource value in the current hierarchy group. It is 100% if output outside of a table.

% in column or point hierarchy group

Identifier:

PercentInColumnOrPointHierarchy

Description:
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. It equals the % field in the hierarchy group when output outside of a table.

% in row or series group

Identifier:

PercentInRowOrSeriesHierarchy

Description:
Contains the percentage of a resource 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. It is 100% if output outside of a table.

% in grouping

Identifier:

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 group. It contains 100% if output outside of a table.

% in column or point grouping

Identifier:

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. It contains 100% if output outside of a table.

% in row or series grouping

Identifier:

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. It contains the % field in the group when output outside of a table.

% in column or point

Identifier:

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. It contains the total % field if output outside of a table.

% in row or series

Identifier:

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 a series. It contains 100% if output outside of a table.

overall %

Identifier:

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. It contains 100% if output outside of a table.

Date-based fields

The platform automatically generates nested fields for fields that contain Date type. These fields are calculated by the platform, which simplifies getting various pieces of date- and time-related data. For your convenience the fields are divided into three groups: Beginning dates, End dates, and Date parts. These fields include:

Beginning dates group

Beginning of day

Identifier:

BegOfDay

Description:
This field contains the date and time of the beginning of the day.

Beginning of week

Identifier:

BegOfWeek

Description:
This field contains the date and time of the beginning of the week.

Beginning of ten days

Identifier:

BegOfTenDays

Description:
This field contains the date and time of the beginning of a 10-day period.

Beginning of month

Identifier:

BegOfMonth

Description:
This field contains the date and time of the beginning of the month.

Beginning of quarter

Identifier:

BegOfQuarter

Description:
This field contains the date and time of the beginning of the quarter.

Beginning of half year

Identifier:

BegOfHalfYear

Description:
This field contains the date and time of the beginning of the half year.

Beginning of minute

Identifier:

BegOfMinute

Description:
This field contains the date and time of the beginning of the minute (if the date includes time).

Beginning of hour

Identifier:

BegOfHour

Description:
This field contains the date and time of the beginning of the hour (if the date includes time).

End dates group

End of day

Identifier:

EndOfDay

Description:
This field contains the date and time of the end of the day.

End of week

Identifier:

EndOfWeek

Description:
This field contains the date and time of the end of the week.

End of ten days

Identifier:

EndOfTenDays

Description:
This field contains the date and time of the end of the 10-day period.

end of month

Identifier:

EndOfMonth

Description:
This field contains the date and time of the end of the month.

End of quarter

Identifier:

EndOfQuarter

Description:
This field contains the date and time of the end of the quarter.

End of half year

Identifier:

EndOfHalfYear

Description:
This field contains the date and time of the end of the half year.

End of minute

Identifier:

EndOfMinute

Description:
This field contains the date and time of the end of the minute (if the date includes time).

End of hour

Identifier:

EndOfHour

Description:
This field contains the date and time of the end of the hour (if the date includes time).

Date parts group

Day

Identifier:

Day

Description:
This field contains the number of the day in the month.

Day of the week

Identifier:

WeekDay

Description:
This field contains the number of the day of the week (the week starts from Monday, same as in the WeekDay() method).

Day of the week name

Identifier:

WeekDayName

Description:
This field contains the presentation of the day of the week (1 means Monday, 2 means Tuesday, and so on).

Day of the year

Identifier:

DayOfYear

Description:
This field contains the number of the day in the year.

Week of the year

Identifier:

WeekOfYear

Description:
This field contains the number of the week in the year.

Month

Identifier:

Month

Description:
This field contains the number of the month in the year.

Month name

Identifier:

MonthName

Description:
This field contains the presentation of the month (1 means January, 2 means February, and so on).

Quarter

Identifier:

Quarter

Description:
This field contains the quarter number.

Year

Identifier:

Year

Description:
This field contains the 4-digit year number.

Minute

Identifier:

Minute

Description:
This field contains the number of the minute.

Hour

Identifier:

Hour

Description:
This field contains the number of the hour.

System fields

In the list of selected fields, the platform generates a dedicated group of system fields that are intended to define a sequential number of a record both in the report as a whole and within the groups. Remember that system fields are not included in the list of fields that are obtained by expanding an auto field, and therefore they should be added manually.

Ser. #

Identifier:

SystemFields.SerialNumber

Description:
Contains the sequential number of the row in the report. Starts from 1.

# in group

Identifier:

SystemFields.GroupSerialNumber

Description:
Contains the sequential number of the row in the current group. Starts from 1.

Level

Identifier:

SystemFields.Level

Description:
Contains the current record level. Starts from 1.

Level in group

Identifier:

SystemFields.LevelInGroup

Description:
Contains the current record level in relation to the group. Starts from 1.

Parameter fields

The selected fields include a dedicated group named Parameters, which is intended to include data composition schemas with the Include in Available Fields check box selected in the report.

 

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

You can specify the following settings that apply to the entire report on the subtabs of the Settings tab:

  • Selected fields. A set of fields that are output to composition results. Each selected field of the data composition system has the following properties:
    • Usage. If the check box is selected, the field is used.
    • Title. The path to the field data.
    • Location. For a group of selected fields, specifies field positions.
  • Filter. The filter that is set for the entire report. Each filter item of the data composition system has the following properties:
    • Usage. If the check box is selected, the filter item is used.
    • Left value. The value in the left part of the condition.
    • Comparison type. The type of comparison used to compare left and right values.
    • Right value. The value in the right part of the condition.
  • Sorting. Describes how to order the output records (the default setting for all composition items). Each order item of the data composition system has the following properties:
    • Usage. If this check box is selected, the order item is used.
    • Order. The path to the order field data.
    • Sort direction. The direction for sorting: ascending or descending.
  • Conditional appearance. Describes how to format output fields. Each conditional appearance item of the data composition system has the following properties:
    • Usage. If this check box is selected, the conditional appearance item is used.
    • Formatted fields. The fields to which the formatting is applied.
    • Condition. The condition that defines whether the formatting is applied.
    • Format. Appearance that is applied.
  • User fields. A set of user-defined fields. User fields can be used to specify settings together with ordinary fields, but they cannot be used to define other user fields. The data composition system supports two types of user fields: expression field and selection field.
  • Other settings. Contains an additional list of settings for the selected report structure item.
Data composition schema settings

Data composition schema settings can contain a structure that includes available configuration items. Such items can include:

  • grouping,
  • table,
  • chart,
  • nested report.

For a grouping, the following settings are available:

  • Use Grouping flag. If checked, the grouping is used for composition.
  • Grouping Fields. A set of fields used for grouping. Each grouping field of the data composition system has the following properties:
    • Use Field flag. If checked, the field is used.
    • Grouping Field. The path to the data of the field used for grouping.
    • Grouping Type. The type of this grouping (Hierarchy, Only hierarchy, Items).
    • Addition Type. Indicates that dates are to be added in the period.
    • Start Date. The start date of the period. It can contain a constant value or a field value.
    • End Date. The end date for the period. It can contain a constant value or a field value.
  • Selected Fields.
  • Filter.
  • Order.
  • Conditional Appearance.
  • Output parameters. Chart grouping does not have output parameters.

For a table, the following settings are available:

  • Use Table flag. If checked, the table is used for composition.
  • Columns. The collection of values that determines table columns.
  • Rows. The collection of values that determines table rows.
  • Selected Fields.
  • Conditional Appearance.
  • Output parameters.

For a chart, the following settings are available:

  • Use Chart flag. If checked, the chart is used for composition.
  • Points. The collection of values that determines chart points.
  • Series. The collection of values that determines chart series.
  • Selected Fields. The selected fields that determine resources output to the composition chart.
  • Conditional Appearance.
  • Output parameters.
Report Structure Table Box

You can generate report content in the report structure table box on the Settings tab of the data composition schema wizard. You can select a checkbox near each structure item. If checked, the item is output to the report results.

To add a new grouping, use the New Grouping command. In the dialog that opens, select the grouping field and grouping type. If no grouping field is specified, the grouping will display detailed records and be called <Detailed Records>.

When you add a new grouping, the autoorder and autoselect fields are added to its parameters automatically. When you add a new chart, autoselect is added.

To group selected records for the specified field, select the Group command of the report structure item context menu, and then select the grouping field and grouping type in the dialog (you can choose from hierarchical grouping and grouping by items). Note that if the grouped item already contains the autoorder field, this field is included in the new grouping as well. To abort when creating a group, click Cancel.

If a field is grouped hierarchically or only hierarchically, the hierarchy type is displayed in the report structure in parentheses after the field header.

To change the current grouping, use the Edit command. This command allows you to change the grouping field and type.

To delete a report structure item, select it and press Del, then confirm the deletion.

To switch data setup mode, use the Current Report and Current Report Element commands.

Data Composition Schema Setup Options

The range of setup parameters depends on the type of the report structure item selected. When you add ordering, sorting, conditional appearance, etc., a corresponding icon is displayed in the current row of the report structure table box. In data composition schema settings, wherever the fields are used, field data paths are displayed by default instead of fields.

Current Report Setup Mode

The following tabs are available in this mode: Parameters (if at least one parameter is specified for the schema), Selected Fields, Filter, Sorting, Conditional Appearance, User Fields, Other Settings.

Current Settings Structure Item Setup Mode

In this mode, the following tabs are available:

  • For the Grouping item:
    • Grouping Fields
    • Selected fields
    • Filter
    • Sorting
    • Conditional appearance
    • Other settings
  • For the Table and Chart items:
    • Selected fields
    • Conditional appearance
    • Other settings

If parameter fields are set on the Parameters tab of the data composition schema wizard, they are displayed in the list of available fields in the Data Parameters folder. When the report is run, corresponding parameter values are substituted in these fields. You can use parameter fields anywhere in settings except when filtering and grouping. If conditional appearance is applied to a parameter field, it applies to the parameter when its value is output in composition results.

You can use predefined values and enumerations as parameters in the Designer mode.

Note: For the parameter values that are not available to the user but are defined in the default settings, the system will retrieve values from the settings instead of the data composition schema.

On the Selected Fields tab, you can select fields to include in composition results. You can add a new field, field folder or auto field from the menu or context menu.

On the Grouping Fields tab, you can select fields for grouping and the grouping type. You can add a new field or auto field from the menu or context menu.

On the Filter tab, you can select fields for filtering composition result records. You can add a new item or folder from the menu or context menu. In this tab's columns, you can set a value for filtering composition result records for the item that you have added. You can use predefined values and enumerations in the Designer mode.

On the Sorting tab, you can select fields for sorting the report results. You can add an order item or auto order item. For an auto order item, the Expand command is available in the context menu. When items corresponding to grouping fields are added to the order and an auto item is expanded, the system checks whether there are any attributes of those fields in the global order. If so, they are added to the order instead of the grouping field.

On the Grouping Fields, Selected Fields, Filter and Sorting tabs, you can drag fields or groups of adjoining fields from the available fields list to a selection window. You can also use the available field list context menu to select the current field or all fields (Select All command) in order to add them to a corresponding collection.

Available data composition settings fields are displayed in the following order: non-resource fields sorted by headers alphabetically, then resource fields sorted by headers, and then system folders.

On the User Fields tab, you can set user fields (selection or expression) to be output in composition results. For a selection field, specify the header, filter expression, value and presentation. For an expression field, you can specify the header and expression for detailed and total records.

If a field or a parameter has a list of available values, you can select a value from this list in parameter data composition settings or in field filter settings. When a value of a parameter or field that has a list of available values is output, the value presentation from that list is used in data composition results instead of the value itself.

Note: By default, field data paths are displayed instead of field headers wherever the fields are used.

On the Conditional Appearance tab, you can specify special formatting for data composition items.

On the Other Parameters tab, you set report output parameters in the Current Report mode, and special output parameters for report items in the Current Item mode.

On the Filter and Conditional Appearance tabs, you can use the Detailed button to display the Presentation column where you can specify names for the fields displayed. These names will be used in report results.

The Standard Setting button cancels all user report settings and restores the default settings.

Note 1: If you have changed a nested report on the Nested Schemas tab, delete the old report and add a new one in order to apply changes to the current report.

Note 2: When a composition template is generated, the system displays an error message if the report has a top-level field for which it is impossible to define a value. This happens when the report is nested in the grouping by this field or in detailed records.

Note 3: When you place a pie chart in a report structure, note that if the points are set and the series are not, the system automatically uses points as series. For a line chart, if the series are set and the points are not, series are used as points.

To set up a data composition schema, you can also use the settings wizard.

1C:Enterprise Developer's Community