This lesson is a step by step guide on how to create calculated columns in a report.
Calculated columns are columns you add to a table in your database. The data in calculated columns is derived from the data in one or more columns in the table and can contain operators and constants.
For instance, if you have “sales price” and “tax” in separate columns in your table, you could create a calculated column for “net price” which would contain the difference between “sales price” and “tax”.
We will be using the AdventureWorksDW.ndb database in this tutorial.
Load the AdventureWorksDW.ndb
Open the Vizubi database called “Vizubi AdventureWorksDW.ndb” saved at the end of the Vizubi AdventureWorks tutorial 2: create a report. Or you can download it from here.
Open excel.
(1) Click on the Vizubi tab.
(2) Click on the Open button in the Database group of the ribbon and open the ndb file.
Select the table where you want to add the new column
1) Click on the Manage Tables button in the Navigation pane.
2) Click on Tables.
3) Click on FactInternetSales tab.
4) Click on the Column Tools tab.
Add a new column
Suppose you want to create a formula that calculates Netsales ( [Sales] – [Taxes]):
1) Click on the Add Column button in the Column Options group of the ribbon. The Add Column window opens.
2) Insert NetSales as the Name and Label of the new column.
3) Click on the Select field button to the right of the formula text box. The Select fields window opens.
Insert a formula for the new column
1) Double click on the first field you want to insert as part of the formula (select SalesAmount).
Complete the formula
1) Insert an operator in your formula (insert “-“. for a full list of operators and descriptions, go to the key at the end of this tutorial).
2) Click on the Select field button again and select a second field to complete your formula (select TaxAmt).
NB: You can type fields directly into formulas if you want. Be sure to follow the syntax of [fieldname].
Check syntax of the formula
1) Now your formula is complete.
2) Click on the Check syntax button on the right on the formula box, to check the syntax of the formula.
3) The Information window opens. If you see “Formula syntax is correct” your formula is ready to be used. Click on OK.
4) Click on Confirm to save the settings and generate the new column.
View the results
1) Drag the scroll bar to the right to view the last column in the table.
2) The NetSales column has been added to the table and is ready to be used in your report.
3) Click on the Home tab in the ribbon.
4) Click on Switch to Workbook to go back to Excel.
Open the “Number of orders and sales by country” report
1) Click on the upper part of the Report button. The Select report window opens.
2) Double click on the Number of orders and sales by country report.
Use the new field in the report
1) Go to the FactInternetSales table in the Field Section. The NetSales field is in the list of fields and you can add it to your report.
2) Remove the flag from the “OrderQuantity” field to remove that column from the report.
3) Flag the box to the left of NetSales to add the column to the report.
4) The new column will be added to the report.
Save the new report
1) Click on the Save As button in the Reports group of the ribbon.
2) Insert NetSalesbyCountry as the Name of the report
3) Insert Amount of netsales by country as the Label of the report.
4) Click on OK to confirm.
NB: Download the list of formulas currently available for Vizubi from here.
Stay in Touch