Technical Bulletin 9287
BYOR Datasource Guide
Purpose
The purpose of this document is to give users a detailed explanation of the Datasources that exist in Build Your Own Report (BYOR). This document explains in detail the purpose of each Datasource to help the user determine which one best fits their reporting needs.
BYOR Datasources
When creating a report within BYOR, users typically know what data they want to see but choosing the correct Datasource is often challenging. We offer 8 different types of Datasources a user can choose from. Each Datasource type is different, but different types of Datasources can often accomplish the same tasks in different ways. Each Datasource has its advantages, and in this document you will learn when to use each type of Datasource depending on what want to report. The 8 different Datasources offered in BYOR are shown in Figure 1.1 below.
Detail Datasource
A Detail Datasource is used to report on simple sales data that already resides in the database. A Detail Datasource is used to report company information or data about goals, products, profit, quotes, invoices, orders, vendors etc. The Detail Datasource was created to allow the user to display simple, one-row report sections that have one data point to be collected through multiple time periods.
Figure 2.1 shows an example of what a Detail Datasource section might look like. As you can see, there is one data point that is reported through multiple quarters. In Figure 2.2 you can see that in this case, the Count of Lines Detail under BI -> Sales Invoice Lines was used as the selected detail.
Aggregate Datasource
An Aggregate Datasource can be used to group sales or bookings data. In Figure 2.1 we reported on the number of line items in 4 quarters. If we want to drill in even further into this information, it would be appropriate to use an Aggregate Datasource. To accomplish this, you can use a COUNT operation on LineItems and use AcctPkg as the dimension. The dimension property is what the report will look at to form groups. If you don’t choose a dimension, the data will not be grouped. In Figure 3.1 you can see the Operation and the Dimension properties are set accordingly. Using these settings, in Figure 3.2 we report on the same data from Figure 2.1 but it’s now grouped by Account Package. Similarly, you could group by many other fields such as AccountName, AcctRoot, VendorID, VendorName etc.
In Figure 3.2 the COUNT aggregate is used but other aggregates can easily be used such as, SUM, AVG, MAX, and MIN depending on what your need to report on.
Item Datasource
An Item Datasource is similar to an Aggregate Datasource, but deals with item and record data instead of sales and bookings data. When setting up an Item Datasource, you must choose which type of item or record you would like to report on. As shown in Figure 4.1, all of the TDF items and record types are supported.
Figure 4.2 shows a report, using an Item Datasource, on the sum of opportunity values throughout 2017 grouped by which employee created the opportunity.
Below in Figure 4.3, you will see that this report is achieved with the following properties: ItemOpportunity is the ItemType, CreatedBy is the Dimension, Opportunity Value is the Measure, SUM is the Aggregate Operation and Creation is the DateField.
Variable Datasource
A Variable Datasource is a Datasource to hold info that does not reside anywhere in the database. You can enter your own dataset to use and update it periodically. An example of this could be something like headcount of employees.
Note: To set up a new Variable Datasource go to Util -> Variables -> Add.
The first type of Variable Datasources is a snapshot. With snapshot you set values at certain time periods and it is assumed that the data will not change unless you change it in future periods. Figure 5.1 shows an example of a Snapshot Variable Datasource where there have been four data points entered.
Figure 5.2 shows what the snapshot example from Figure 5.1 would look like in report form.
The second type of Variable Datasource is an aggregate. This type is used for values that will accumulate over time. Variable datasources that deal with time or money would most likely fall into this category. Figure 5.2 shows an example of an Aggregate Variable Datasource where you can see that the values accumulate.
The Variable Datasource was created to be able to used data values that you may need for other calculations. For example, if you wanted to measure profit over number of sales employees you could keep track of how many sales employees you have in your company and update it when there are hires or terminations.
A Variable Datasource can be used as a data within a Calculated Datasource. This will be discussed in the next section of the document.
Calculated Datasource
A Calculated Datasource is useful when you want to perform calculations across multiple datasources or come up with new data based on other existing data. You can choose up to 5 other datasources to include in your Calculated Datasource. For example, if you need to perform a calculation that uses 4 different fields from four different datasources, you would include the four datasources as data in your Calculated Datasource and then reference each one in your ValueFormula depending on what calculation you need.
Below in Figure 6.1, notice the highlighted section of the report is a Calculated Datasource that uses two datasources for its calculation. For the purpose of example, the two datasources are displayed above.
In Figure 6.2, DataSourceA is a Detail Datasource of total sales and DataSourceB is the snapshot Variable Datasource from Figure 5.2. 
The ValueFormula in Figure 6.2 is A/B because we are looking to report on company sales based on how many sales employees are on board.
Datasource Wizard
The Datasource Wizard has a collection of pre- joined tables that you can pull data from to include in your report. These pre- joined tables are represented by different categories. Once you select a dataview category, you will get different fields that are related to that dataview category. For example, if you select Accounts, on the next screen you will see all of the fields that are generally related to an account record.
The reason that Datasource Wizard was created was to allow users to report on information about items. More specifically to allow the reporting of multiple fields that aren’t numbers related to items and records. Figure 7.1 shows an example of a contacts sheet created with Datasource Wizard.
This report was created using the Contacts view with the following fields and ordering:
When using the Datasource Wizard you are also able to use data filtering, date filtering, aggregates, and grouping.
BI Summary Datasource
BI Datasource is used when you want similar information like what you see in BI summary within Info Center. You can pull data used in BI group summaries and display it how you see best fit. Using the BI Summary Datasource is appropriate when you would like to see columns that represent time period calculations along with many different rows/values for each time period. For example, Figure 8.1 shows a report that is very similar to an account group summary. This was created using a BI Summary Datasource.
This was easily created by choosing Account as the summary type, ActualAccountRollup for goal type, and choosing the following columns to display:
Build Your Own Query (BYOQ)
BYOQ can technically be used in any circumstance. Users can use a BYOQ Datasource to create very simple or very complex reports. If you need to design a complicated report, BYOQ is the tool you want to use. Also, if you would like to perform reporting on other databases, BYOQ can be used to do this. In fact, the only way you can get access from other servers and databases in BYOR is by using a BYOQ Datasource.
Figure 9.1 shows the query from a BYOQ Datasource. This report has four column from two different tables, a date criteria, and is ordered by 3 fields to keep the data organized. This query is behind the report in Figure 9.2. The report shows alerts that all of the users in TDF_Users table are subscribed to.
Note: While BYOQ can do any reporting that any of the other datasources can, keep in mind that it doesn’t always make sense to use a BYOQ when trying to accomplish simple reporting tasks.
Frequently Asked Questions:
Q: What is the difference between a BYOR and a BYOQ?
A: A BYOQ is a type of BYOR. BYOQ is a Datasource option a user has when creating a section of a BYOR. BYOQ allows users to query Data with SQL commands in their TDF Database or other Databases they have access to. A BYOQ Datasource can accomplish anything any other Datasource can, but it doesn’t make sense to use a BYOQ when trying to accomplish simple reporting tasks.
Comments
0 comments
Article is closed for comments.