Reporting Intelligence Excel add-in Keywords

BC Cloud Only - this article only applies to Reporting Intelligence for BC Cloud

Overview

One of the most powerful aspects of using Reporting Intelligenceis that not only are you able to retrieve and analyze your data, but you are also able to format that data in ways that best suit your needs.

The Excel add-in allows you perform such advanced steps as to auto-fit columns, hide rows and columns, allow the user to specify report options, etc.

Many of these capabilities are achieved through the use of Keywords.

These keywords are used in Row 1 and/or Column A - which are reserved in a report for that specific use

Let's take a look at the handful of keywords that provide these advanced features.

Keywords

  1. AUTO+HIDE

    The most fundamental of the Reporting Intelligence Keywords is the AUTO+HIDE that (when the report is run) is automatically placed in cell A1 of any worksheet containing Reporting Intelligence functions.  AUTO+HIDE combines the functionality of the hide keyword to hide a row or column with the keyword of AUTO which tell Reporting Intelligence to automatically perform other keyword commands in row 1 and column A.  As a designer, you will never need to add the keyword of AUTO.

  2. +VALUES

    This keyword can be added to the AUTO+HIDE that always resides in the A1 cells for the sheets in any Reporting Intelligence Report. If you would like to design a report that can be available to people who are not licensed Designers, you can convert the workbook to values by placing "Auto+Hide+Values" in cell A1 of any worksheet. When a report containing this keyword is run, all Reporting Intelligence functions will be converted to values while all other Excel formulas will remain intact and the worksheet will be editable. The Reporting Intelligence functions can be restored (by a licensed Designer) by selecting the Design button on the Reporting Intelligence ribbon.

    More information on this feature can be found in the article Introducing Report Viewers

  3. +LOCK

    You can create a locked worksheet by entering "Auto+Hide+Lock" in cell A1. Locking a worksheet prevents non-Designers from accidentally changing the formulas.

    When a report with locked sheets is run, any worksheets that include the +Lock keyword in cell A1 will be locked.

    More information on this feature can be found in the article Introducing Report Viewers

    Sharing Reports with Excel Users Who Do Not Have the Reporting Intelligence Excel add-in

    Using either +LOCK or +VALUES  allows Reporting Intelligence Viewers to use reports. Both of these features are also useful for sharing your reports with people who use Excel and don't have the Reporting Intelligence Excel add-in.  In this situation, there are several advantages to using +VALUES instead of +LOCK. The first advantage is that +VALUES does not lock the report, so the person receiving the report can edit it. The second advantage is that using +VALUES eliminates any link to the Reporting Intelligence Excel add-in, so Excel will not display a warning message asking the user if they want to update data from external sources when opening the report. This message can be confusing to the person who receives your report.  So it is recommended that you save in report mode with +VALUES to eliminate this potential issue.

  4. FIT

    Some data values, such as customer names, are of variable length. So while you are designing the report, you do not know how wide the column should be. If the column is not wide enough to hold the data, Excel will either display ######## in the cell, or it will only show the section of data that will fit in the cell.

    The Reporting Intelligence Excel add-in can automatically format the column width or row height for the widest or tallest piece of data that you want to display. This operation is very similar to the manual process of using Excel's Autofit feature. The whole column will resize to fit the widest cell.

    To do this with the Reporting Intelligence Excel add-in, you can put the keyword "Fit" in row 1 of the column. For example, if you have a list of customer names in column C, you can put "Fit" in cell C1 to automatically resize the column for the longest customer name when the report is run. If you want to resize the height on a row, you can put "Fit" in Column A of that row.

  5. HIDE  /  HIDESHEET

    Sometimes, it can be very convenient to include information in your report, but not actually display it.

    The HIDE keyword can be used to hide an entire column or entire row once the report is run. Likewise, the HIDESHEET keyword can hide an entire worksheet when the report is run.

    More information on this feature can be found in the article Automatic Hidden Columns Sheets and Rows

  6. HIDE+?

    Sometimes you will want some data returned in your report to be hidden - depending on some aspect of that date. For example, maybe when a particular value returned is zero.

    This is accomplished through the use of the 'conditional hiding" keyword of HIDE+? in either cell B1 (if you wish to hide certain rows) or cell A2 (if you want to hide certain columns).

    More information on this feature can be found in the article Conditionally Hiding Rows, Columns, or Sheets

  7. OPTION, TITLE, VALUE, LOOKUP, TOOLTIP, VALID, MESSAGE

    All of these keywords are used together to allow you do define "Report Options".

    These keywords allow you to create a report options window from which - when the report is run - you can choose specific filters to narrow down the information you want to view in the report.

    Detailed instructions for doing this can be found in the following articles:

  8. DATA, KEY, KEYNAME

    Please note: Stored User Data is a highly advanced feature

    Normally any changes made to a report while it is in Report mode will be lost when the report is run again.

    However, with the stored user data feature, it is possible to designate sections of a report where users may enter and change data which will be stored and kept by the Reporting Intelligence Excel add-in when the report is run.

    Detailed instructions for doing this can be found in the following articles:

    • Introducing Stored User Data in Reports
    • Advanced Stored User Data

Related Articles

Was this article helpful?

We're sorry to hear that.