Reporting console for 1c accounting 8.3. Universal reporting console. Why do I need a query console?
The query console is very convenient to use when you need to debug some complex data or quickly obtain a sample of certain data.
You can find many different consoles on the Internet. For regular and managed forms, these are usually completely different consoles. Using this you can download the universal query console 1C 8.3 and 8.2, which will work in both cases. Just keep in mind that the console on the managed interface will only open in the thick client.
Working with the Query Console
In the 1C program, the query console opens in the same way as any external processing: menu “File” - “Open”, or using the hotkey combination “Ctrl + O”. If used frequently, it can be added to external processing of the infobase itself.
The left side of the request console displays the list of your requests. This allows you to store not just one request, but several at once. The query tree has a hierarchical structure, which is very convenient for grouping them.
By clicking on the “Add” button, you can add a new request, giving it a name yourself. Also, the built-in functionality allows you to create queries with copying, move up or down the list and much more.
The request text itself is displayed on the right side of the processing form. It can be written manually or using a query builder. The query builder only works in the thick client.
To the right of the designer button is the “Insert Query Statements” button. It will immediately add an empty query structure with condition, grouping, order and totals.
To the right are very useful buttons that will help if you use this request when developing solutions in 1C.
The button allows you to convert your written request into text. In our case it will look like the figure below.
You can save the requests themselves to a file or load them from it. This is very convenient for future use.
Before directly executing the request, you must set its input parameters (if any), then click on the “Run request” button. It is also possible to execute requests not as a whole, but separately as a package.
The viewing mode determines how the query result will be displayed: as a list, hierarchical list, or tree.
The execution result will be displayed at the very bottom of the request console. It also displays the number of rows received and the execution time. You can move query result rows if necessary.
Very often, users ask to select some data and save it in Excel. You may need this too. To complete the task, you just need to select “Display list” in the “More” menu. The program will prompt you to mark the required columns.
In addition to specifying a list of columns for output, you can also specify specific rows here. This is implemented by highlighting the rows in the query itself. Next, in the column selection form, just set the flag in the “Selected Only” setting.
In our example, we displayed all the query result data. You can save them to an external file by copying them from the opened spreadsheet document, or using the hotkey combination “Ctrl+O”. There are many options for saving formats; we chose “*. xlsx".
As you can see, working in the query console is very convenient and useful, so we strongly recommend downloading it and putting it into practice.
In the previous article, we looked at how to use the “Report Console” to get a report without correcting the algorithms in the configuration. It should be noted that using the “report console” you can both display “corrected” reports and expand the set of reports with new reports. Very often clients ask for some kind of report, and the question always arises of how to include it in the system. If you enable it through the configurator, you must always remember about future system updates and the possibility of “accidentally overwriting” the desired report. Therefore, when ordering a report for programmers, consult with him in advance. It may be better to immediately include it in the query console. Then the problem of updating the configuration will not be so acute. Of course, if the 1C company changes the structure of storing information in the database, then the report from the reporting console may stop working, but experience shows that the structure of the database does not change often.
Let's add a couple of simple reports to our reporting console.
Let's say we need a report at hand that would reflect information about employees, their birthdays, dates of hiring and dismissal, tariff salaries, tariff salaries with all allowances, as well as the latest departments and positions of employees.
Let's open our Report Console.
And let's add a new report. Let's call it "Dates of birth/hiring/dismissal". Please note that the program already has reports that can provide such data. We do this to show the IDEA itself, that the system can be expanded with reports without making changes to the configurator.
After adding a report, the report design form will open.
We have already prepared an auxiliary file of report generation algorithms. You can download it from our website "acceptance-dismissal dates.xml". Remember where you saved it and now click the icon for downloading the report algorithms file at the bottom of the designer form.
After downloading the file, just click the "OK" button and our report is ready for use.
Click "Generate" and see what happens
Very good. Now let's experiment with the report
Click on “Full name, date of birth, date of admission..” and on the right click on the “Grouping fields” tab. Or double-click on “Name, Division, Position”. All fields that are displayed in our report are marked here. Uncheck all fields except “Name, division, position” and click “Create”.
We have received a list of the latest departments and positions of our employees. But all employees, both working and fired, are present here. Let's keep only working employees.
To do this, use the “Selection” tab of the report. Those. First, click on “Report”, and then select the “Selection” tab.
Working employees are employees whose termination date is blank. Let's put a tick in the selection. Let's also select only women. In the line “Gender” - “Equal”, select “female” and click “Generate”.
Everything seems to be correct, but for control, let’s return the “Date of dismissal” column to the report.
Click on “Name, Division, Position” and on the “Grouping Fields” tab. We put a tick on the “Dismissal date”. Or double-click on “Name, Division, Position”.
And click "Generate".
And indeed, working women are on our list.
Let's now see which women quit in 2014.
There is only one difficulty here - how to indicate that the date of dismissal is greater than 01/01/2014 and less than 01/01/2015.
We already know that to clarify the selections, you need to click on “Report” and on the “Selection” tab
Now in “Date of dismissal” select “Greater than or equal to” and set 01/00/2014
Now let’s click on the “Dismissal Date” in the “Available Fields” table and DRAG it to the selection table.
All that remains is to set the dragged dismissal date to Less than or equal to and the date 01/01/2015. Click "Generate" and enjoy the result.
Thus, by manipulating the output data and selection values, various useful information can be obtained.
Let's consolidate the result. Let's add another report to our console.
Let's add a new report and name it "Personal accounts of employees"
Download the algorithm file for the report "Personal accounts of employees.xml" and load it into the report. Save the report.
Please note that in the selections the “Date of dismissal” is indicated in advance - “Not filled in”, i.e. The report is displayed for currently working employees.
It’s worth noting right away that the selections include the “Bank” field. This means that you can select information for a specific bank. Or, if you specify “Not filled in” as a selection for the Bank, you can display information about employees for whom the bank in their personal accounts is not filled in.
If we remove the checkmarks from the “Date of admission” and “Date of dismissal” fields, we will receive the same report, but in which there will be information only about the individual, bank and personal account.
Try it, experiment. We wish you good work.
Best regards, "Afalina" company
P.s. All reports described in the previous and current article can be downloaded to the reporting console if you download the file from the link from our website and upload it to the reporting console (in the reporting console you must select “File” - “Open” and select the report description file)
You have probably noticed the rapid development of technology in all areas of life. Almost every person has a device such as a computer, tablet, smartphone, we now cannot imagine our life without software products: email, Yandex cards, etc.
Business does not lag behind progress. It is impossible to imagine that a journal-order form of accounting is now used, unless, of course, this is a vegetable stall. And there is a difference between stall and stall. Every company has huge amounts of information and tools that use these amounts. A significant share of these funds is occupied by programs on the 1C platform. In particular, .
All these trends clearly hint to us: in order to become a sought-after and competent specialist, we must understand digital technologies better and better. So, for those who want to increase their efficiency in working with 1C software products, this article will be useful. These could be economists, financial managers, analysts and, to a lesser extent, accountants.
Have you often had to save 1C reports in the format and then process them in a Microsoft software product? I'm sure this is not uncommon. I did it this way myself...
Although this approach has the right to life, it is not at all effective. And here's why:
- Any large data array in Excel takes an unbearably long time to process.
- As a rule, already assembled information is stored in table cells downloaded from 1C, and you want to see it in different cells for filtering.
Of course, one could argue that there are ACS reports that give the user room to maneuver, but setting up an ACS is also not an easy task.
But there is a tool that allows you to avoid all the difficulties, and it is not new at all, every programmer uses it. This is a processing called “1C Query Console”. This wonderful thing is stored on ITS disks. Varies for different platforms.
Get 267 video lessons on 1C for free:
This thing is simply necessary for effective work in UPP. It simply inspires you, you become the ruler of the information base. You do not depend on programmers; you are able to extract almost any information in a matter of seconds.
But, of course, for successful work you need to know something about the 1C configuration. It's not as difficult as it seems at first glance, buy any book that describes the configuration, or take the "Developer's Guide" from the yellow box and look through it. In less than a couple of weeks you will be able to write queries well enough.
How to work with processing
Let's start processing "" in the 1C UPP software product. Open by clicking on the panel: “File – open – path to the processing file.”
You will see a processing window with three windows. In the upper right window, enter the text:
CHOOSE
Organizations.Name,
Organizations.TIN,
Organizations.KPP
FROM
Directory.Organizations AS Organizations
And click the Run button on the control panel. The lower window will display a list of organizations for which records are kept in your UPP database, their Taxpayer Identification Number (TIN) and KPP. Well, you have completed your first request - congratulations.
Now right-click on the upper right window and select the “ ” menu item. In the left field we see all the configuration elements, in the middle - what we turned to (in this case, the “Organizations” directory), in the right - what will be displayed as a result of the request.
Other tabs allow you to group, set conditions, display results, and many more useful things.
Using the query console, you can access any configuration element, set conditions for displaying information, combine data from different sources, and perform mathematical operations with data. And all this will be done incredibly quickly compared to Excel.
Here is a brief overview of the most useful tool in 1C UPP, without which I cannot imagine working in this program.
Why do I need a query console?
Here you may be indignant: “I’m not a programmer, I’m an economist, an analyst, a manager, why do I need this?!” Wait to be indignant. Let's start with the fact that, having mastered the 1C Query Console and learned how to use it in UPP, you will not become a programmer, and don’t count on it. BUT!!!
- You will save your time, and these are not just words. With this processing you will get the job done perhaps 10 times faster. If you analyze a table from period to period, using various filters and functions for sampling; do not know how to write Excel macros; spend a couple of hours or a couple of days on it... By writing the request once and saving it, you can receive this data in seconds, changing only the date parameters. All that remains is to sit and think about the request code.
- Processing “1C Query Console” is especially useful if the company does not yet have a clear system of key indicators. Every time management asks to add new data to the report or calculate an indicator using a different formula. Here you can show off your knowledge. And who knows what heights this may lead you to.
- Removes your dependence on the programmer. Of course, the result of a query from the console is not a full-fledged report. But it is not the form that is important, but the content. The console will give you any configuration information. And you can start operating with data while the report is at the programming stage.
- Using the console will inevitably force you to deepen your knowledge of SCP. And this will not be the knowledge of a typical user, A knowledge of the configuration architecture of the soft starter. If these are empty words for you, go ahead to the “working” sites and see how much UPP specialists cost.
We live in a digital world in which the ability to work with information increases the chances of becoming more successful and richer!
And quickly obtain information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.
Let's take a closer look at this tool.
Download 1C query console
First of all, to start working with the query console, you need to download it from somewhere. Processing is usually divided into two types - controlled forms and conventional (or sometimes called 8.1 and 8.2/8.3).
I tried to combine these two types in one processing - the required form opens in the desired operating mode ( in managed mode the console only works in thick mode).
You can download the 1C 8.3 (8.2) query console for managed and regular forms using the link: .
Description of the 1C query console
If you are interested in how queries are written in the 1C system, I recommend that you read the article and.
Let's start looking at the query console with a description of the main processing panel:
In the header of the query console, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.
The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.
On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.
Get 267 video lessons on 1C for free:
- Execute request— simple execution and obtaining results;
- Execute package— allows you to view all intermediate requests in
- Viewing temporary tables— allows you to see the results returned by temporary queries in the table.
Request parameters:
Allows you to set the current parameters for the request.
In the query parameters window, the following is interesting:
- Button Get from request automatically finds all parameters in the request for the convenience of the developer.
- Flag Common parameters for all requests— when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.
Set a parameter with a list of values It’s very simple, when choosing a parameter value, just click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:
Also in the top panel there is a button for calling up the query console settings:
Where you can specify query autosave parameters and query execution parameters.
The request text is entered into the console request field. This can be done by simply typing a query test or by calling a special tool - the query designer.
Called from the context menu (right mouse button) when you click on the input field:
This menu also has such useful functions as clearing or adding hyphens (“|”) to the request, or getting the request code in this convenient form:
Request = New Request; Request. Text = " |SELECT | Currencies.Link |FROM | Directory.Currencies AS Currencies"; RequestResult = Request. Run() ;
The lower field of the query console displays the query result field, which is why this processing was created:
Also, the query console, in addition to the list, can display data in the form of a tree for queries containing totals.