After we told the Data Composer where to take the data from, we need to tell it how to output them to a spreadsheet and what kind of functionality the report has to have. This is done over here - on the Settings page. And this is what we’re going to discuss today.
But first let me show you the brand new sales tracking functionality I added in the meantime. First of all there is a new Sales accumulation register. It has the Turnovers type, and remembers how many items were sold and how much money was paid for each product over a period of time.
The register has the only recorder - the Sales document, and here is how the doc posts its data into the register. It cycles through the Products tabular section, adds a new record to the register, assigns the document’s date to the register’s Period attribute and then copies the Product reference, quantity and total price.
After this was done, I’ve reposted all Sales documents, and here is my register with all the products I’ve ever sold.
Now I want us to build a real-deal report out of this data, so let’s get started. I’m adding a new report called Sales and including it in the Other subsystem.
Now, I’m creating a DCS template and these are my Data Sets.
Now. I want to see how many items of each product were sold and for how much money. One way of doing this would be to get the ready-to-go results from a query, that should look something like this. Then I can just display this data using these simple settings, and here we go.
These are all the products we ever sold along with the summed up prices and quantities. But what if I want the weekly sales dynamic instead of the overall sums? There is no way for me to somehow drill down and dig out the date information from these query results. As soon as we aggregated these records we lost all transactions’ timing data.
So, here is another solution for you. Instead of aggregating with the query, I’m feeding the report with the raw data like this, and asking the Data Composer to aggregate them for me like this.
How come it works the same as the GROUP BY in the query? This is how. Every field in this list can have a role. When you use a query, DCS figures out the appropriate role automatically, although you can change it if you need. When you use an object-type data set, you need to specify roles manually.
This Dimension role means that this field can be used as a GROUP BY field when Data Composer aggregates data. As for the aggregate functions, we already defined them here on the Resources pages. And this is what it looks like in the runtime. Which is exactly the same report, right? So, we get the same result using the Data Composer aggregating features instead of the query’s ones. And we didn’t lose any raw data on the way, so now we can play with them.
I want a weekly sales dynamic report, so I’m telling a register to aggregate the turnovers weekly, and it turns on this Period field that will be set to the beginning of each week. I’m dropping it in the selected fields, and here is my query.
But. The report doesn’t look the same, does it? Somehow, the data aren’t grouped by the Product anymore. And here is why. The Data Composer has automatically given this Period field the same-name role. This role is also deemed to be suitable for grouping by, so now the Data Composer has two group-worthy fields and cannot choose between them automatically.
So, I’m going to the Settings over here and telling the Data Composer that this reports section should be grouped by the Product field. And here we go. Our report is back.
And of course We can group the data by the Period instead, and the report will look something like this. Which doesn’t make a lot of sense by itself, right? It tells us that during the week started on April 21st 2019 we sold ten items of something overall. Um… Thank you, I guess?
But. Wouldn’t it be great to somehow combine these two reports into one and see the revenue and turnover broken down both by Products and by Period. Basically, I want to get something like this, but filled out with revenue and quantity sums.
OK, let’s do it. Let me fix this report section first. Now, I’m adding a new section down here, but this time it’s not the grouping we used before. It’s a table - basically a two-dimensional grouping just like the one I want.
I want my Products to become rows, so I’m adding a new Products grouping down here. And I want my weeks to become columns So, I’m adding another grouping here. And… Ta-da. These are our weekly revenue and quantity turnovers in the flesh.
Now, let’s look at this Settings section down here. I know, looks confusing. But let me help you out. First of all, these settings depend on the level of the tree they were set on. So, here I see the Report settings, and here - the Products grouping settings inside of this Table. And this is just the full path to the settings you’re now in.
But you can also use it to switch between different levels of settings like this. This Auto flag means, that all this setting was inherited from the level above. Meaning, that the Data Composer will just take these Selected fields and use them down here. But if you want, you can switch this flag off and set selected fields (or any other setting) differently.
One last thing.
Remember this beginning and end of period settings? The Data Composer is ready to use them as parameters for the report, but I need to approve it by switching them on like this. I can also add them to so-called quick settings in which case the Data Composer will bring them up right to the report form like this.
And now I can have the same report for a specific period of time. So, these were the DCS report settings - the last episode of the last module of the 1C:Junior Developer learning course.