Outputting diagrams into a spreadsheet document and related problems. Outputting diagrams into a spreadsheet document and related problems Creating a diagram in SKD 1S

Let's collect the remaining questions from section 11 of testing, with explanations in the text.

Question 11.06 of exam 1C: Platform Professional. When used in a chart data composition system (received in output form), it is characterized by:

1. You can only include one chart in the output form
2. you can include any number of charts in the output form, but they must be of the same type
3. You can include any number of charts in the output form, but they must display data for one resource
4. you can include any number of diagrams in the output form without limitation

The correct answer is fourth; for example, you can add your own grouping for each diagram. One:

and the second:

Question 11.14 of exam 1C: Platform Professional. In what case are the conditions from the selection specified in the data composition system settings not placed in the request text?

1. Selection set for grouping
2. The condition uses a calculated or custom field that contains expressions that cannot be represented in a query language
3. The condition uses fields from multiple data sets
4. All of the above is true

The correct answer is the first one. In general, the request text does not imply imposing a condition on any one group of output data; therefore, this cannot be passed as a parameter. Other options are possible.

Question 11.15 of exam 1C: Platform Professional. Is it possible to use native functions in the expression language of a data composition system?

1. It is forbidden
2. It is possible, but only when working programmatically with a data composition system. Functions must be described with the keyword "Export" and must be located in a global common module
3. It is possible, but only when working programmatically with a data composition system. Functions must be described with the keyword "Export" and located in any common module
4. It is possible, but only when working interactively with the data composition system. Functions must be described with the keyword "Export" and must be located in a global common module
5. It is possible, but only when working interactively with the data composition system. Functions must be described with the keyword "Export" and located in any common module
6. It is possible both during interactive and programmatic work with the data composition system. Functions must be described with the keyword "Export" and located in any common module

The correct answer is six. It’s not clear what the picture is for.

Question 11.18 of exam 1C: Platform Professional. A column in the report, the value of which will be calculated using some expressions using the fields of the source data set, can be created in the layout scheme designer window:

1. In the Calculated Fields section
2. In the "Options" section
3. In the "Layouts" section
4. In the "Settings" section on the "Custom fields" tab
5. All of the above are true
6. Answers 1 and 4 are correct

The correct answer is fifth. Expression in calculated fields:

In Options:

In Layouts:

And in Custom Fields:

Question 11.19 of exam 1C: Platform Professional. Data composition schema parameter can be created

1. Automatically, based on the request text
2. Interactively, in the data composition schema designer window in the "Options" section
3. Interactively, in the data composition scheme designer window in the "Settings" section on the "Parameters" tab
4. Programmatically
5. All of the above are true
6. Answers 1, 2 and 4 are correct

The correct answer is sixth - in the third option the parameter can only be changed, not created:

Question 11.20 of exam 1C: Platform Professional. Which control displays the output of a report in a spreadsheet-like format?

1. Spreadsheet document field
2. Summary table field
3. Diagram
4. Pivot chart

The correct answer is the first one. Definitely not a diagram :)

Question 11.38 of exam 1C: Platform Professional. Where can you configure the relationship between the fields of an external and nested schema?

1. Spreadsheet document field
2. In the settings of the nested circuit itself
3. In the settings of the external scheme
4. In the special window "Nested diagram settings"
5. All options are correct
6. Options 1 and 3 are correct

And so, the purchasing manager needed a report showing the dynamics of changes in the balance of goods in the organization’s warehouses in the form of a diagram.

The programmer decided to create a report in a data composition system (DCS) because it would take less time. Let's look at the steps to create a report step by step.

Solution

Let's create an external report "Dynamics of changes in product balances" and add a basic data layout scheme to it. In the layout scheme designer, on the "Data Sets" tab, add a new set and write a query for product balances by warehouse and frequency "Day".

Since the report always displays the dynamics of item balances for only one item in the directory, we will add selection by item as a parameter so that the report will not be generated without filling it out.

In order for the quantitative balance indicator to be displayed on the chart, add the "QuantityRemaining" field to the resource.

Let's configure the report parameters. For the "Nomenclature" parameter, we will set it to mandatory use so that the user always fills it out to generate a report. And we will limit the parameters “Beginning of Period” and “End of Period” for editing, and their values ​​will be taken from the values ​​of the “Period” parameter with the “StandardPeriod” type.

The report is almost ready. All that remains is to configure the report output structure and its fields. Let's use the settings designer.

Having selected the “Diagram” report type in the designer, we need to select the settings:

1. Chart fields.

2. Series and point diagrams.

3. Chart type.

At this point, the task of creating a report can be considered completed!

Result

The task was completed. The report was created on the access control system in a very short time. In practice, creating such a report takes less than five minutes. External report created in the article, you can download from link.

One of the most important areas of business software is reporting. The fate of a business can depend (and not in a figurative sense!) on how easy it is to customize an existing report to the changing needs of business (and legislation) or create a new one, be it a report for the tax office or a diagram of the dependence of demand for goods on the season and other factors . A powerful and flexible reporting system that makes it easy to extract the necessary data from the system, present it in an understandable form, allowing the end user to reconfigure a standard report to see the data in a new light - this is the ideal that every business system should strive for.

In the 1C:Enterprise platform, a mechanism called the “Data Composition System” (abbreviated as DCS) is responsible for generating reports. In this article we will try to give short description ideas and architecture of the ACS mechanism and its capabilities.

ACS is a mechanism based on a declarative description of reports. The access control system is designed for generating reports and for displaying information with a complex structure. By the way, in addition to developing reports, the ACS mechanism is also used in 1C:Enterprise in a dynamic list, a tool for displaying list information with rich functionality (displaying flat and hierarchical lists, conditional design of rows, groupings, etc.).

A little history

1. One or more queries were written in the 1C query language (SQL-like language, more about it below).
2. Code was written that transferred the results of executed queries to a spreadsheet document or chart. The code could also do work that could not be done in a query - for example, it calculated values ​​using the built-in 1C language.

One of the ideas that formed the basis of the access control system was the flexibility and customization of reports, which was accessible to both the developer and the end user. Ideally, I would like to give the end user access to the same set of report design tools as the developer. It would be logical to create a single set of tools available to everyone. Well, since the tools require the participation of the end user, it means that the use of programming in them should be reduced to a minimum (it is best to eliminate it completely), and visual settings should be used to the maximum.

Formulation of the problem

Two languages

Query language

It is easy to see analogues of sections standard for SQL queries - SELECT, FROM, GROUP BY, ORDER BY.

At the same time, the query language contains a significant number of extensions aimed at reflecting the specifics of financial and economic problems and at maximizing the reduction of efforts to develop application solutions:

• Accessing fields using a dot. If the fields of a table are of a reference type (they store links to objects of another table), the developer can refer to them in the text of the request through “.”, and the system does not limit the number of nesting levels of such links (for example, Customer Order. Agreement. Organization. Telephone).
• Multidimensional and multilevel formation of results. Totals and subtotals are formed taking into account grouping and hierarchy, levels can be traversed in any order with summing up, and the correct construction of totals according to time dimensions is ensured.
• Support for virtual tables. Virtual tables provided by the system allow you to obtain almost ready-made data for most application tasks without the need to create complex queries. Thus, a virtual table can provide data on product balances by periods at a certain point in time. At the same time, virtual tables make maximum use of the stored information, for example, previously calculated totals, etc.
• Temporary tables. The query language allows you to use temporary tables in queries. With their help, you can improve query performance, in some cases reduce the number of blockings and make the query text easier to read.
• Batch requests. To make working with temporary tables more convenient, the query language supports working with batch queries - thus, the creation of a temporary table and its use are placed in one query. A batch request is a sequence of requests separated by semicolons (";"). The requests in the batch are executed one after another. The result of executing a batch request, depending on the method used, will be either the result returned by the last request in the batch, or an array of results from all queries in the batch in the sequence in which the queries in the batch follow.
• Retrieving representations of reference fields. Each object table (in which a reference book or document is stored) has a virtual field - “View”. This field contains a textual representation of the object and makes the report creator's job easier. So, for a document, this field contains all the key information - the name of the document type, its number and date (for example, “Sale 000000003 from 07/06/2017 17:49:14”), saving the developer from writing a calculated field.
• and etc.

There are also special query language extensions for access control systems. Expansion is carried out using special syntactic instructions enclosed in curly braces and placed directly in the request body. Using extensions, the developer determines what operations the end user will be able to perform when customizing the report.

For example:

• CHOOSE. This sentence describes the fields that the user will be able to select for output. After this keyword, aliases of fields from the main query selection list that will be available for configuration are listed, separated by commas. Example: (SELECT Item, Warehouse)
• WHERE. The fields on which the user can apply selection are described. This proposal uses table fields. The use of selection list field aliases is not allowed. Each part of the union can contain its own WHERE element. Examples: (WHERE Item.*, Warehouse), (WHERE Document.Date >= &StartDate, Document.Date<= &ДатаКонца}
• and etc.

Data Composition Expression Language

Example:

The process of creating a report on the access control system

• request text with instructions for the data composition system;
• description of multiple data sets;
• detailed description of available fields;
• describing relationships between multiple data sets;
• description of data acquisition parameters;
• description of field layouts and groupings;
• and etc.

For example, you can add a query to the data composition scheme as a data set and call the query constructor, which allows you to graphically create a query of arbitrary complexity:

The result of launching the query designer will be the query text (in the 1C:Enterprise query language). This text can be adjusted manually if necessary:

There can be several data sets in a data layout scheme, data sets can be linked in the layout in any way, calculated fields can be added, report parameters can be specified, etc. It is worth mentioning an interesting feature of the query mechanism in 1C:Enterprise. Queries are ultimately translated into a dialect of SQL specific to the DBMS with which the application directly operates. In general, we try to use the capabilities of DBMS servers to the maximum (we are limited by the fact that we use only those capabilities that are simultaneously available in all DBMSs supported by the 1C:Enterprise platform - MS SQL, Oracle, IBM DB2, PostgreSQL). Thus, at the query level in calculated fields, we can only use those functions that are translated into SQL.

But at the level of the data composition scheme, we can already add custom fields and use functions in them in the built-in 1C development language (including those written by us), which greatly expands the capabilities of reports. Technically, it looks like this - everything that can be translated into SQL is translated into SQL, the query is executed at the DBMS level, the query results are placed in the memory of the 1C application server and the SKD calculates for each record the values ​​of calculated fields whose formulas are written in the 1C language.

Adding Custom Fields

You can add an arbitrary number of tables and charts to the report:

Report designer

Runtime report

Using SKD, the user can add complex selections to the report (which will be added to the request in the right places), conditional design (allowing the displayed fields to be formatted differently - with font, color, etc., depending on their values) and much more. .

The process of constructing and generating a report can be briefly described as follows:

• The developer in design time with the help of a designer (or in runtime using code) determines the data layout scheme:
  • Text of the request/requests
  • Description of calculated fields
  • Relationships between requests (if there are several of them)
  • Report Options
  • Default settings
  • Etc.
• The above settings are saved in the layout
• User opens report
  • Possibly makes additional settings (for example, changes parameter values)
  • Clicks the “Generate” button
• User settings are applied to the data composition scheme defined by the developer.
• An intermediate data composition layout is formed, containing instructions on where to receive data from. In particular, the queries specified in the layout are adjusted. Thus, fields that are not used in the report are removed from the request (this is done in order to minimize the amount of data received). All fields that participate in calculated field formulas are added to the query.
• The data composition processor comes into play. The layout processor executes queries, links data sets, calculates values ​​for calculated fields and resources, and performs grouping. In a word, it makes all the calculations that were not performed at the DBMS level.
• The data output processor launches a request for execution and displays the received data in a spreadsheet document, chart, etc.

The process of generating a report using the ACS mechanism

We try to minimize the amount of report data transferred from the server to the client application. When displaying data in a spreadsheet document, when opening a spreadsheet document, we transfer from the server only those lines that the user sees at the beginning of the document. As the user moves along the lines of the document, the missing data is downloaded from the server to the client.

Custom Settings

• Fast access. The setting will be displayed directly at the top of the report window.
• Ordinary. The setting will be available through the “Settings” button.
• Not available. The setting will not be available to the end user.

Setting display mode in design time

Display the setting in Quick Access mode at runtime (under the Generate button)

Development plans

It just so happens that in more than 10 years of working with the 1C:Enterprise platform, I have never had to work closely with diagrams in a spreadsheet document. Only a couple of times I had to display pie charts in the report, but no difficulties were found.

And now it has come - the time of diagrams. I now work in an investment company, which also deals with the trust management of client funds. The clients are different: from large non-state pension funds to private investors who want to profitably invest their 2-3 million rubles. It must be said that with such an investment you can get income higher than the interest rate on bank deposits, even with a cautious strategy. However, you may win almost nothing. But if you need to get income in a short period of time (a year or two), it’s hardly worth going into real estate or gold for this.

All clients are interested in knowing what assets their money is invested in, what the return on the investment portfolio is for the period and, in general, how external factors influence the prospects for income. Not everyone has an economic education, so it was decided to generate periodic reports for investors in the form of a booklet with a large number of diagrams. Initially, the layout was drawn by our manager in Adobe Illustrator, then it was my turn.

There were no problems with the color palette and fonts - 1C allows you to manipulate them wherever necessary. The output of logos and tables was also not difficult, except that the space for each plate was limited by the layout of the report, so it was necessary to analyze each time which indicators were considered significant and which were not.

The problems started when working with diagrams. But before we identify them, let’s remember how in the language of the 1C:Enterprise platform we work with diagrams in a spreadsheet document.

Each spreadsheet document contains a collection of Pictures. It can include both static drawings (logos, pictograms, photographs) and diagrams. In the case of a diagram, the Picture collection element has an Object property of the Diagram type initialized. It is important to note that if you create a spreadsheet document by sequentially outputting layout areas with diagrams into it, there is no easy way to find the desired diagram in the final document: the index and name of the figure can change each time when the area is output to the document, you can only search by properties An object, such as Title or ChartType.

In Excel, the 1C diagram is uploaded as a picture.

Typically, charts are inserted into a report layout, their properties are configured, and then the chart values ​​are generated programmatically when the report is output. This article does not discuss the output of diagrams to a report using an access control system; it has its pros and cons.

Each chart has a type (pie, histogram, graph, etc.). And it is drawn by setting the values ​​associated with points and series. Series and points (elements of the Series and Points collections) can be specified immediately in the Configurator; this is convenient when the quantity is strictly limited by the scope of the task and/or it is necessary to strictly set the color, text and some other parameters.

By the way, when you “click” on a diagram in the layout, the properties window for the diagram area may open. Click again and the properties window for the Diagram object will open.

Series are what we typically color in a chart with different colors. For example, there are as many multi-colored segments in a pie chart as there are series with non-zero point values. Points are what are located along the axis in the diagram.

Degenerate cases: a pie chart has many series, but one point, and a histogram or graph can be constructed using one series, but several points.

You can define series and points programmatically, but you need to ensure that they are added to the collection and do not forget to set the Text property (especially for points, otherwise we will not see labels on the horizontal axis of the graph). Series and points are accessed through the index of the collection element.

For example,

If Area1.Drawings.Object.Points.Count()< Инд Тогда

Area1.Drawings.Object.Points.Add();

endIf;

Area1.Drawings.Object.Points[Ind-1].Text=”February”;

And to set the value of the chart, the SetValue() method is used, to which the point index, series index and the value itself are passed. Typically this method is called in the value table traversal or query fetch loop.

So, we need to draw a diagram like this:

Problem 1: 1C does not know how to change the slope of the scale signatures. You can place signatures either horizontally or vertically. We do it vertically.

Problem 2(more serious): I don’t know of a way to force 1C to add a percent symbol to the point values ​​and scale legend. Settings are always made through the format string, and the platform does not allow using an arbitrary character in the format string of a number. The Chart object allows you to work with percentages, but only when analyzing the relationship between values ​​across series. If there is only one series, then choosing a method for displaying values ​​as percentages is pointless. Well, let's add a note to the title (in %).

Let's move on to the following diagram:

Problem 3. The first thing that catches your eye is the dotted increment on the cumulative histogram at the last point. 1C doesn’t know how to build diagrams, so we’ll have to come up with our own background color for the last series.

Problem 4. In addition, the color of the value labels on the diagram is different - for series 1 it is white, for series 2 it is black. 1C can’t do that either. You'll have to use white throughout.

Please note that the ordinate scale starts at 307. This is done in order to clearly see the increase in assets. Because if we display the scale from scratch, then at the top of each column we will see a tiny increment bar, which is a little more than a percentage of the value for Series 1.

Problem 5. It would seem that the Chart object has a MinimumValue property and no one is stopping us from setting it. We install. Oops. It turns out that it doesn't work for histograms. It's a pity. But there is a BasicValue property. Set it to 307 and get what you need.

However, testing the report quickly shows that the base value works correctly only when Series 1 plays the dominant role. If at the first point the value for Series 1 is small, and the value for Series 2 or for any other series different from the first, will be large, then we will get something like this:

I couldn't find a way to display a cumulative histogram normalized relative to the minimum value. After communicating with the developers, it turned out that indeed, the base value is always considered relative to Series 1. It turns out that statically specifying series in the Configurator is an almost useless feature; you need to programmatically analyze the series values ​​before displaying the histogram and reassign as Series 1 the one that has maximum value.

At the partner conference, this issue (non-working properties Minimum and MaximumValue) was raised several times, but things are still there. Perhaps there is a lack of persistence among users who rarely work with diagrams in 1C.

Why rarely? Because the following error clearly showed how serious bugs are found in the 1C diagram mechanism. If there had been a bug of this level in the payroll calculation mechanism or in the operation of the access control system, it would have been identified immediately.

It is very easy to reproduce.

Problem 6. We make a layout from several horizontal sections. We put 2-3 diagrams in each. We display all this in a spreadsheet document, filling in the values ​​of the diagrams programmatically. And suddenly we see that the captions to the horizontal scale of one of the diagrams disappear. In the debugger we see that in the filled layout area there are names of points, but after executing the method

TabDoc.Output(Area1);

The diagram ends up in the TabDoc without the names of the points. The error “walks” from diagram to diagram, but is reproduced consistently. At least on release 8.2.17.

I had to write a piece of code that iterates through the Drawings in the resulting spreadsheet document and sets the correct point names. It is somewhat reminiscent of an ancient platform 7 bug, when the value of a variable was assigned only the second or third time.

Problem 7.

It is impossible to display a transparent 1C chart in Excel. Diagram 1C has the "Transparent background" property and it can be used when outputting to a spreadsheet document. But when exported to Excel, 1C diagrams are replaced with pictures, and opaque ones at that.

I tried using 1C to replace the diagram with a transparent picture. The diagram has a GetImage() method.
Next, I uploaded the BMP file to binary data (possibly to a file)and created a new Image object using the constructor. If, when creating a picture from a source, set the second parameter to True, the picture will turn into transparent. And a transparent picture can be exported to Excel if Picture Size = Change Proportionally.

But here’s the problem - such a conversion greatly distorts the original diagram and has no practical meaning:

But overall the tool is useful and quite multifunctional. Don't be lazy to use it - and your reports will look very clear.

As for solving my specific problem, in the end I drew all the diagrams directly in Excel. And everything worked out.

You can take the library for drawing charts in Excel from my article here: