Reports are all about data. You get the data you need, you process them, output them to the spreadsheet, and here is your report.
When we build the template-based report, we have to write the code, that reads and processes the data and outputs it to the spreadsheet.
DCS reports are different. They retrieve, process and output data without a single line of code, using only the schema settings.
Today, we’ll look at how we tell the Data Composer what data to retrieve and how to process them.
So, let’s create a new empty report and see what’s where.
The most of the data retrieval and processing is going on right here in the Data Sets. There are three Data Set types we can use. First of all, there is a query - our good old friend from the last module.
We tell it what we want and where it lives. The query goes to the database and grabs the data for us.
Second of all, there is an object. This object is accessible to you from the source code, where you can fill it out with literally anything you want. For example, you can call an external HTTP service published anywhere on the web and grab some data from there.
As you can see there may be as many Data Sets as you need. So, the question is: how do these recordsets get combined into a single one for the Data Composer to process?
Let me show you.
Our first option is to connect them like this, which is, basically, nothing else but the LEFT JOIN between these two Data Sets. So, it just adds the MyObject data columns to the MyQuery result columns.
The JOIN condition - the one that looks up the matching right table records - lives over here - on the Data Links page, and we will look at it later on.
Our next option is to connect recordsets like this. This is done by using the third type of Data Set we have here: the Union one. So, I’m creating one, dropping these two guys down there, and this is it.
The question here is: how does the Data Composer know how to bind columns from these two recordsets? The answer is simple: it looks at the column names.
Columns with the same names are deemed to be the same column, and the columns with different names get added individually, so it looks something like this.
So, these are our Data Set types. Let’s see what else we have here. I’m removing my data sets from the Union one and deleting it for now.
So, this is my query data set. As soon as I add any query down here, the Data Composer parses it and fills out this Fields list over here.
Let’s take a look at some of these settings. Here you can change the default field title just like this. And here you can change the default field appearance settings like this.
As for the object data set, the Data Composer has no idea what fields it is to have, so you need to tell it. Let’s say this data set calls a web service and grabs the amount of products left in my remote warehouse.
So, these are my fields, and this is where I left-join my data sets just like this. Sometimes (pretty much all the time actually) I want to let a user decide what data they do and don’t want to see.
And this is the place where it’s done. The Data Composer already added these two parameters to the list because it knows that this virtual table has them.
I can add my own parameter to the query like this. And the Data Composer will add to the list as well. I can also hide or unhide any parameter like this, so a user will or will not be able to set its value from the interface.
This is how we set up what data go to our DCS report.