During one of my assignments a request for a report was made containing only certain data from a datasource and joining it with another datasource. The best way would be to create views from existing datasources instead of filtering in the report itself. In this blog I want to show you how to create a view from a data source. This works very well but should only be used for data that does not change very often.
Create a data source that acts as a view
We start with a data source containing data about products. From this datasource, I would like to create a view that only contains a subset of products I want to use in reporting. The steps to create this view are:
- Create a cloud data source “Cloud Product ID” and upload the ID’s of the products you want to use
- Create a new data source “Product data source view” where you join the “Product data source" (based on ID) with the “Cloud Product ID data source"
You can now use the "Product data source view" as any normal data source in your reports where it will act like a view. Now that the basic idea is clear, let's have a look how it was used in a report the customer needed.
Using multiple "views" in one report
The customer required specific data regarding marketing attributes to be shown in a report. In the normal selection you make in the marketing attribute report the data is shown as follows (below example is based on fictional data):
The requirement is to put the values of the 2 different marketing attributes next to each other as separate fields. This made it easier to compare the data between customers. The end result should look like:
What needed to be done is to create 2 new views based on the marketing attribute data source. One of them containing only data related to the "Size" attribute and the other containing data about the "Favorite" attribute.
- Create a cloud data source "view" for marketing attribute Size containing all the values defined for the marketing attribute. In the example I use I only have 100, 200 and 400. In reality there could be more values (100,200,300,400,500,etc..) so add (upload) all of them to the cloud data source.
- Create a cloud data source "view" for the marketing attribute Favorite. Here also you need to add all the possible values that have been defined for the Favorite marketing attribute (not only Plate, Cup and Fork)
- Join the “Size” and “Favorite” cloud data sources with the customer data source baased in customer_id. This part can be a bit tricky depending on how many joins you need to make.
Below is a dramatic over-simplification of the data sources created and joined to deliver the report as required
Please take into account that creating these kind of views could have impact on performance so try to limit the amount of views. In normal circumstances try to use the filter options that are available in standard reporting.