Designing Reports

An introduction to concepts and usage of the Noviship reporting system

A report in Noviship is a query that produces a table of information in the form of rows and columns. The query can be run at any time, manually or automatically, and can be visualized in a number of formats.

Understanding report concepts

The output of a report is called a dataset and while it is tabular in shape, it can also be visualized in a graph. At its heart, however, the dataset consists of rows and columns. Each row in this table represents some bucket of information. It could be a single object, such as a customer, or some group of objects (although always the same type). Each column is a single type of data and represents some property of the bucket. For example, one column could represent the customer’s name. Another could represent the number of shipments belonging to that customer created in the last week.

To better understand this let’s consider how the report is designed.

We design a report with a very simple process. First we choose the base object on which we will grow the report (a transaction, a shipment, and so on) and then we define how we want to extract data from those objects. To help explain this we are going to build a report as the concepts are introduced. This report will show us which customers are spending the most in the last week. For good measure let’s also find out who their sales agents are (for the bonus, of course).

The report we are going to build: Which customers spent the most last week and who are their sales agents.

The shape of the data

Objects are related to each other in the form of a hierarchy of relationships. We can imagine this in the form of a tree. The trunk of the tree is the Partner. Each partner relates to many Associates which in turn relate to resellers, in turn to customers, users, shipments and finally to transactions. In this example you can consider the transaction the leaf with the intermediate objects being branches. When you create a report the first thing you choose is the leaf from a menu of options. You aren’t choosing a specific leaf, but rather the type of leaf. Having chosen a leaf you have a single path all the way back to the trunk. The content of the report will consist of data taken along this path.

Let’s consider an example: you might choose a Transaction report which means the leaves are transactions. While there are many leaves, as you trace your path back towards the trunk from a specific leaf you are joined by many other leaves following their own paths back to the trunk.

You might ask which leaves (transactions) we are counting. Each report actually considers every leaf available to you, the operator. If you are a Reseller, every transaction that passes through your Reseller organisation as it makes its way to the root will be considered. In most cases that’s way too many leaves to be useful, but that’s what filters are for and we’ll get to that in a bit.

What about the data the data we want to extract from the report? This is where columns come in and in our tree analogy you can think of them as attributes not only of the leaves but also every branch on our way to the trunk.

In the illustration we have specified some columns, which are attributes of the objects on the path. As soon as we choose a type of leaf, we have access to all the attributes along the path. Columns represent an attribute but they also come in different flavours. Let’s examine the columns.

We have three flavours of column: Dimensions, Metrics and Descriptors. Some attributes are suited to Dimensions, some to Metrics, and all to Descriptors. When you choose an attribute to be a column you also choose one of these types. But what do these terms mean?

Dimensions

The dimension is a column that identifies the buckets that will form rows. Recall from earlier that each row in our dataset can be thought of as a bucket into which data is poured. In practice the dimension (or dimensions if we want more than one) will be the first column(s) in our table. Let’s say we choose the customer name to be a dimension. What this means is that each row will have a unique customer name and therefore the bucket will represent properties that relate in some way to a specific customer. (If two customers have the same name, they will be grouped into one bucket). We can have more than one dimension. If the next dimension is the day of the week, then each row will have a unique combination of customer name and day of the week. In other words, while each dimension can have duplicate values, the combination of all your dimensions is unique.

Metrics

Next come metrics. Metrics are some quantity accumulated in the bucket. If, for example, we choose the price of the shipment as a metric each row will have a value representing all the prices of shipments in that bucket. If only one object is in bucket then the metric represents a single value of that object. If there are many objects on the bucket then the metric will need an Aggregation. We’ll talk about aggregations in a moment.

Descriptors

Descriptors are the third type of column and simply contain information taken from the bucket. If the bucket contains multiple instances of the descriptor property, the system will make an arbitrary choice as to which one to display. In practice for most descriptors you could have chosen a dimension or metric instead but by choosing a descriptor you are telling the system a bit about the shape (which comes in handy for graphs and making the reports work faster).

Aggregations

We mentioned aggregations with respect to metrics. An aggregation is a means to turn multiple metrics into a single value when the bucket contains multiple objects. Examples of aggregations would be Sum (the numerical total of all the objects) or the Average and so on. Remember that you get one row per unique combination of dimensions, not metrics. So when more than one metric is ending up in a bucket you need some way to turn these into a single value.

Filters

If you choose a Shipment Report then every shipment you have access to will be considered and could potentially represent a row. Using descriptors you group the shipments into buckets but the report still represents every shipment. Using filters you reduce the shipments in the report based on the criteria you choose. Otherwise your reports are massive and not very helpful.

Filters are formed as a tree of conjunctions (branches) and comparisons (leaves). OK we already used this analogy for the shape of the report so let’s instead consider the roots. The same (very useful) principle of starting from one point (the trunk) and splitting off into more and more roots. Let’s go to an example.

If you want to consider all delivered shipments within the last week you need two comparisons (Status Is Delivered and Date Within Last Week) combined with an And conjunction.

What this means is that each leaf being considered must be true if you evaluate this filter. Each transaction must relate to a shipment whose attribute Shipment Date is within the Last Week and (this is the conjunction) also whose attribute Shipment Status is equal to Delivered.

We have almost finished designing our report.

Recall that our report is going to show us which customers spent most during the last week. What if we have thousands of customers? Well, lucky you. But that also means you probably don’t care about most of them, just the top few. We don’t want to give out too many bonuses after all. So how do we trim off say, the top 10? Through ordering and limiting.

Ordering and Limiting the dataset

To consider only the ten greatest shippers we will need to order the rows in descending order and start from the top. The column we sort on must be one of the columns represented by the data and in our example transaction amount will do nicely. Next we set the limit to 10 rows and as a result we will ignore all the riff-raff.

Building the report

Now that we have designed our report let’s get in to actually creating it in the system. To create a report go to Management | Reports. Here you will find all the reports you have created and those you have access to.

Reports exist in the database as a set of instructions on how to generate data, reports are not the data itself. In other words each time you view a report you may see a different result.

Click Add to start creating a new report.

You will see a page where you can choose the type of report. What you are choosing here are the leaves of the tree we discussed earlier. A Shipment Report starts with shipments (whether or not they have been invoiced) while a Financial Report starts with transactions (whether or not they are even for shipments). Although shipments have a price, that price changes over time (as the shipment is affected by the reconciliation process). Transactions, however, do not change.

Generally when your report answers financial questions, you should start with a Financial Report. Let’s do that by clicking on the Create button in the Financial Report panel.

The Report Editor

Next you will see the report editor which is used to construct the report and edit it later.

On the left you will see the properties of the report and on the right you will see the available pool of columns suited to the type of report you are creating. In this example you see columns for the final report, grouped according to topic.

In the Name field you should describe your report so you can find it later.

In Maximum Rows you can optionally limit the report to a fixed number of records. This is useful if you want the “top ten”, as in our example. In this field enter “10”.

The Subject Company determines the constraints of the report. If left blank, the operator of the report becomes the Subject. That means if you run the report, it is constrained to the records you can see while if someone else runs the report it is constrained to their company. This constraint doesn’t mean you only see shipments for that company, rather you see shipments that company has access to. A reseller will see all their customers.

The Currency determines the currency for all monetary values. In effect any monetary value not stored in the selected currency will be converted using the current daily rate.

The Time Zone determines the time adjustment for any date and time values. Some data is stored in Local time (such as the Shipment Date) while others are stored in Global time (such as the Transaction Date) which will shift according to timezone.

Configuring Columns

Below these fields we see the column sets: Dimensions, Metrics and Descriptors. To put a column in these sections, select the column from the pool on the right and choose which set to add it.

To start, select Customer Name from the Customer group and choose Dimensions. This will add the column to the Dimensions set of the report.

You can now give the column a caption. On the right you can choose a grouping operator. Groupings are mostly used for Time dimensions (for example, to group the date by month or year).

Next, let’s add the Sales price to the Metric set. Choose Sales (Gross) from the Sales section of the column pool and choose Metric.

In our system Net and Gross refer to taxes. Net means the price before any taxes are added while Gross means the price including taxes. If you are considering profit or commission calculations you should use Net as it is not possible to make a profit on tax.

Once you have added the Sales price metric you should choose your caption (Sales in our example) and select an aggregator. If each final row will contain only one transaction, it doesn’t matter. But in all other cases you’ll need to decide how to handle multiple values. In most cases you want the Sum of all the values - so choose that now.

Next, let's add the Sales Agent. This will be a descriptor as we don’t want it to affect the report structure. In this case it doesn’t matter (as there is only one agent per customer name) but in general you should keep the dimensions and metrics to a minimum and make the rest descriptors.

Now that we have chosen the columns in the report, it is time to filter it. Without a filter, the report will contain every piece of data the subject has access to. In our example, we want to consider only the previous week.

Defining the Filter

Filters are a logical hierarchy of conjunctions (branches) and comparisons (leaves). To filter the report to only shipments in the previous week, we need just one comparison (is the shipment date within the previous week).

So click on the empty filter dropdown and select Comparison. The other options (Not, And and Or) are conjunctions.

Each comparison consists of a field, and operator, and a value. The available operators are determined by the field. Some only make sense for dates, some only for numbers, and so on. Choose Shipment Date as the field. This will make the operators After, Before and Within available.

Choose Within as we want the range of shipments to be “within” the previous week. Next you can choose a value.

The Within options for dates are always relative to the time the report is run. To specify a fixed date range you would use the conjunctions Before and After.

Since we don’t want to show all the customers who shipped in that week, just the top ten, we need to consider sorting. Sorting is only important if you are limiting the number of rows as it will determine which rows are cropped. The limit is always applied from the start so if you specify 10 rows, it will be the first 10. Therefore which those first 10 are will be determined by the sorting column and direction. You can only choose from columns in the report, so select the Sales column and Descending.

This means the data will be sorted from largest value downwards and the top 10 will be selected.

Now that you have defined the contents of your report, you should choose its presentation. Select Table from the Format options.

You can now add an optional Notice which is usually a description of the report. Notices are displayed along with the data when the report is emailed so it is a good idea to explain what the receiver is looking at.

You can now save the report and it will (providing you haven’t left something out) join your existing collection of reports.

Let’s try it out.

Running the Report

Find your report in the list and click View from the action list. You will see something like the result below:

Now we can quickly see who spent the most last week, and which agents brought them in.

You can click the column headers to change the sorting and you can even filter the data by typing in the search column. As you filter data you will see new footer summaries:

Conclusion

You should now have an understanding of how reports work and how you can apply the concepts in day-to-day work. The reporting system will continue to evolve so check for updates from time to time.