Create: Special Column Definitions

Estimated reading time: 9 minutes

Overview

Interject's Special Column Definitions are a convenient way you can customize a cell and provide additional features to your reports. There are a number of options available:

  1. Adding a cell comment
  2. Adding a hyperlink to a web address
  3. Adding a custom drop down validation to each row


You can even add multiple cell values, for example a comment and a hyperlink.

These additional features are added by editing the Column Definition Row. The Column Definition Row is a row that designates, and filters, the placement of information. For example, if your report is presenting the field "Company Name", your Column Definition would have a cell with the value "Company Name". To add one of these features to the cells in a column where the report is populated you use an identifier such as AddComm for comments, hLink for hyperlinks, and Valuelist for a drop down list.

This example uses the Customer Credits report, which is in the Customer Aging.

If you are following the Training Labs, this report file can be found in the Report Library at Training Labs > Lab 5 Advanced Features > Lab 5.4 Special Column Definitions.

Special Cell Value Comment

A comment is often used to note more detailed information about the data in a cell. Comments are useful when the information contained within them are not required but need to be referenced when you put your mouse over the cell. In this example you will add comments to the InvoiceTotal column to note the customer's last unpaid invoice.

To start, unfreeze the panes.


In the Company Name field in cell C21 input Market and Pull the data.


The report looks like this:


Select cell G2 and input InvoiceTotal Addcomm:LastUnpaidInvoice.


Note: Addcomm designates the type of special column that you are using and LastUnpaidInvoice is the actual content in the comment.


Pull the data.


Place your cursor over cell G27 and review the comment that is placed in the cell. Notice that the InvoiceTotal column was pulled as it was before the changes but it has a comment associated with it that now details the last unpaid invoice of the customer.

Select cell C2 and input CompanyName hlink:MapsURL.


Note: The MapsURL segment of the hlink string designates a column built in the SQL stored procedure. The hlink string only links to a whole column of data you cannot concatenate values in Excel, but can in the SQL stored procedure as long as it results in a single column.


Pull the data.


CompanyName is still pulled normally but instead of the normal dataset, it will create a hyperlink formula. The Link Location goes to Google Maps and searches for the full address for that company. This way the location data is accessible but does not overcrowd the information while the Friendly Name of the URL is the Company Name.


Select cell C27 and notice that it takes you to Google Maps.


Note: The addresses may not be valid as they are fabricated data from the Northwind Database.


Special Cell Value Validation List

Inserting data validation dropdown lists are similar to the other special column definitions. When used they create dropdown lists using comma delimited strings, with each value inside the string separated by commas. In this example you will add data validation to the account freeze column to limit possible input values.

For this example, use AccountFreeze in cell K2.

Select the cell, then input AccountFreeze Valuelist:FreezeOptions. Pull the Data again.


AccountFreeze will be shown as a blank value but upon selecting the cell, you can see that there is a drop down list with the options Yes or No. If there was a comma separated list instead of FreezeOptions , the value list would only include what is in the comma separated list.


Through using this feature with Interject, you can have a different validation list for every row in your report. Even though this is not shown in this example, if you wanted to select a city name inside of a state, it is possible to have the validation list only show the cities available within the state which would have to change based on the row the data is on.

Stacking Special Cell Types

Excel allows users to have multiple special cell values attributed to a single cell. This is also the case when using Interject integration. You can add multiple cell value types in the column definitions of the report.

For this example, use Company Name cell C2

Select the cell, then input CompanyName hlink:MapURL Addcomm:AddressLong


Pull the data.


Hover your mouse over cell C27 and it will have a comment attributed to the cell along with the hyperlink.


Click on the hyperlink in cell C27 and notice that the functionality of the hyperlink is unaltered and that the comment brought back the address that is used in the google map search.