Cell Palette, Formats, Alerts, & Report Cell Types

[synoptix]

Desktop Client Cell Types & Other Report Definitions

---

Cell Palette

Purpose

Accounting and other types of cells are accessed through the Cell Palette, located to the left of the spreadsheet in the Report Designer. Note the two small icons at top right of the Palette. You can click the icons to hide and expand the palette.

   


Definitions

Company: The Company list box allows you to select which company you would like the balance to be retrieved from. In the event only one company is available for use, the list box is not displayed. Instead, the company being used is listed at the top of the Company section of the Cell Palette.

Functional Area: If both Accounting and Operational modules have been purchased, this area may be set to either Accounting or various Operational spheres.

Numeric Cells:
Accounting Cell: This cell allows you to retrieve balances from your General Ledger and have that balance automatically populated in the report.

Advanced Accounting Cell: This cell allows you to have account numbers and/or descriptions automatically entered into the report based upon your chart of accounts. This feature is very useful in cases where you have many accounts you want to list on the report and need to have individual accounts listed.

Reference Cell: This cell allows you to retrieve account information from another accounting cell within your report.

Budget Cell: This cell allows you to enter a budget balance, or refer to another cell that contains a budget balance, and save that balance back to a budget table.

Offset Cell: This cell allows you to offset a budget by allocated amounts, to avoid increasing or decreasing approved budget amounts.

Detail Cells: The Detail Cells list box contains cells for working with criteria other than dates and balances. At a minimum, these include Account # and Account Description, but can encompass a wide variety of criteria depending upon the accounting package being used by Reporting Suite.

Total Cells: The Total Cells list box contains drag and drop cells for use in automating sums at report run time. These include the Subtotal, to be used in a Detail Area for subtotaling, and the Total. Total Cells can be used both in and outside of a Detail Area.

Other Cells:
Spreadsheet Cell: This cell allows you to pull data to a single cell from an external spreadsheet.

Condition Cell: This cell allows If, Then comparisons between cells.

Image Cell: This cell allows you to place an image, from various locations, onto your report.

Linked Cell: This cell allows you to retrieve data from a cell on another report.

Cell Format: Alerts

Purpose

The Alerts tab of the Cell Format dialog box allows the report to perform an action when a cell's value is outside a specified value or range of values at runtime. Alerts are typically used with numeric cells.

View the Alerts tab by selecting desired cells, then clicking the Cell Format button from the Format tab of the toolbar and choosing Alerts from the resulting menu.

   

You can also access the Alerts tab by right clicking selected cells and choosing Cell Format...

   

...then selecting the Alerts tab.

To use the Alert function, enter a minimum and/or maximum value for the cell. If the value of the cell at runtime falls outside the values entered, Reporting Suite executes selected alerts.

Definitions

Min. Value/Max. Value: Enter a minimum or maximum value, or, enter both to create a range.

Play Sound: An audio alarm sounds if the cell's value at runtime falls outside values entered.

Change Font Color: A change to the cell's font color occurs if the cell's value at runtime falls outside values entered.

Flash Cell Border: Alternating colors flash around a cell's border if the cell's value at runtime falls outside values entered.

Send Email: An e-mail is sent to selected recipients if the cell's value at runtime falls outside values entered.

   

Cell Format: Alignment

Purpose


To horizontally and/or vertically align text within a cell.

By default, general text is left-aligned, and values and dates are right-aligned. However, you can change both the horizontal and vertical alignment of cell data. To do this, use the icons in the Alignment group of the Format tab to alter the alignment.

   


Definitions

Vertical Alignment: Top, Middle, Bottom
Horizontal Alignment: Left, Center, Right


Alternately, you can set the alignment of cells by selecting them, then right clicking and choosing Cell Format...

   

...then selecting the Alignment tab.The Alignment tab duplicates options available from the buttons in the Alignment group on the Format tab of the toolbar.

   

Note: Wrapped text is automatically top-aligned. You cannot change the vertical alignment of wrapped text.


Cell Format: Border

Purpose

To add borders to a cell.

View the Border tab by selecting desired cells, then clicking the Cell Format button from the Format tab of the toolbar and choosing Border from the resulting menu.

   

You can also access the Border tab by right clicking selected cells and choosing Cell Format... 

   

...then selecting the Border tab.

   

Definitions



Line Style: Select border thickness and whether it displays as a single or double line.

Line Color: Select border color.

Select Individual Borders: Select one or more individual borders. Affects all selected cells.

Select All: Select all four borders on all selected cells.

Clear All: Clear all four borders on all selected cells.


To add a border to selected cells, choose a Line Style and select the individual border check boxes corresponding to the cell border you’d like displayed. You can also click the Select All or Clear All buttons, located just above these check boxes. Below is an example to illustrate formatting a single line border along the bottom of selected cells.

   

Note: Some line styles may not display the same way they print. For example, it's possible for a double line to look like a heavy single line on some extremely low resolution monitors, but it will print as a double line.


Cell Format: Cell Color



Purpose



The Cell Color tab of the Cell Format dialog box allows you to set the background color of the cell, using the Cell Color Palette.

View the Cell Color tab by selecting desired cells, then clicking the Cell Format button from the Format tab of the toolbar, and choosing Color from the resulting menu.

   

You can also access the Cell Color tab by right clicking selected cells and choosing Cell Format...

   

...then selecting the Cell Color tab.

   

Definitions

Cell Color: Select the desired cell background color from the palette. Choose More Colors to see an expanded palette.


Cell Format: Font

Purpose

The Font group on the Format toolbar allows you to set the font characteristics of cells.

You can set font characteristics of cells by selecting the cells, then selecting desired properties on the Format tab of the toolbar.

   

Definitions

Font: Click the drop down list to select which font is used in selected cells.

Font Emphasis: Click the drop down list to select the font's emphasis-style. Options include Plain, Bold, Italic, and Bold-Italic.

Font Size: Click the drop down list to select the font's size.

Font Color: Click the drop down list to select the font's color.


You can also access the Font tab of the Cell Format dialog box by right clicking selected cells and choosing Cell Format...

   

...then selecting the Font tab.

   

The Font tab allows you to set the Font, Typeface, Size and Color of one or more selected cells. While these options are also available from the Format tab of the toolbar, here, as you change attributes, you’ll see Sample Font text to give you an idea of how text in the cell will look.


Cell Format: Number Format

Purpose

To set the number format within a cell.

Numeric data is typically returned from a database unformatted. Depending on the field, it can be returned with as many as 8 or 10 digits to the right of the decimal. You may want to show that type of precision in some cases, but generally, some formatting is recommended to make the numbers easier to read.

On the Format tab, there are five buttons that allow cells to be quickly set to the standard number formats defined in the Reporting Suite’s general preferences. You can format the output of cells by highlighting the cells then clicking one of the Standard Format buttons shown below.

   

Definitions

   
[b]Standard Currency Format:[/b] Sets selected cells to the default currency format. Defaults can be changed from the General Setup screen.

   
Standard Number Format: Sets selected cells to the default number format. Defaults can be changed from the [/url]General Setup screen.
 
   
[b]Standard Percent Format:[/b] Sets selected cells to the default percent format. Defaults can be changed from the [url=http://generalpreferencessetup.html/]General Setup screen.

   
Increase Decimal Place: Adds one decimal place each time it is clicked. For example, if your current format is 100.00, clicking once results in 100.000.

   
Decrease Decimal Place: Removes one decimal place each time it is clicked. For example, if your current format is 100.000, clicking once results in 100.00.


Advanced Number Formatting

More advanced number formatting options are available from the Format tab of the Cell Format dialog box.

View the Format tab by selecting desired cells, then clicking the Cell Format button from the Format tab of the toolbar, and choosing Format from the resulting menu.

   

You can also access the Format tab by right clicking selected cells and choosing Cell Format...

   

...then selecting the Format tab.

   

The Format tab allows you to format numbers, dates, times, phone numbers, and zip codes, using the Format Type option buttons located near the top of the dialog box.

As mentioned, often numbers coming from a database must be formatted to increase their readability. To do this, select the Number option button, then click the drop down list just beneath it. From this list, select the type of number you are formatting. Choices are Number, Currency or Percent.

   

With the type selected, the Format list box is populated with available choices.To set the format you want, select it from the list. As you select a format from the list, example results are displayed on the right.

   

With the Date, Time, Phone # and Zip Code options, select the corresponding button, then select the desired format in the list. As with Number formats, example results for these formats are displayed on the right.



Definitions

Number: This option requires you to select None, Number, Currency or Percent as an additional sub-option. Then select the desired number format from the list.
       
Date:  Select Date to format cell as a date.  Then select desired date format from the list.

Time: Select Time to format cell as a time.  Then select desired time format from the list.

Phone #: Select Phone # to format cell as a Phone#.  Then select desired Phone# format from the list.

Zip Code: Select Zip Code to format cell as a zip code.  Then select desired zip code format from the list.

REPORT CELL TYPES

Accounting Cell

Purpose
An accounting cell is used on a Financial report. In using the Accounting Cell, you select parameters for it, such as Company and Account Numbers. Accounting Cells return a single number. The place holder for an Accounting Cell is 9999.99.

To see Accounting Cell Properties, select Accounting Cell from the Numeric Cells list box on the Cell Palette, then drag it onto the report. Its Cell Properties appear on the Properties Panel of your report and the Account Numbers dialog box appears. Here you can manually enter your account numbers or click the magnifying glass to choose account numbers from a list. Click Validate to confirm the account numbers are correct and then Apply those account numbers to the cell's properties.

   

Properties for the cell are set using its Cell Properties.

   

Definitions
Companies: Displays the company(s) selected from the Cell Palette.

Accounts: When you click Add Accounts, the Account Numbers dialog box appears, wherein you can look up account numbers by clicking the magnifying glass icon, typing them into the box separated by commas, or entering a range separated by a colon. Then Validate to make sure account numbers are correct before applying. Hold the Control key to select multiple account numbers.

Calculations:
Source of Calculations: The type of calculation for your report, Actual, Budget, and so on.

Calculation: The period of your calculations, for example, MTD = month to date. 

Adjustment: Adjusts the calculation, or period, by the number entered. For example, if your calculation is MTD and you have an adjustment of -1, it will retrieve the previous month to date. If your calculation is YTD and your adjustment is -1, it will retrieve the previous year to date.

Reverse Sign(+/-): Reverses the positive/negative sign for your calculations.

Currency:
Use Currency Conversions: Determines whether or not currency conversion will be used for the cell.

Use Account Defaults: The conversion type set in Account Conversion Setup for each account will be used.

Override Account Defaults: Override any defaults that have been set and all balances for the cell will be converted based upon this setting.

Conversion Type: There are several conversion types, each of which affect the conversion differently. A discussion of the various types is found in Reporting Suite's Advanced Financial Tutorial.

Target Currency: The Currency to which you want the Account Defaults currency to be converted.

Notes: You can add notes to individual cells to give your reader additional context for the data it contains. When a cell has a note, in either the Report Designer or Viewer, a yellow icon appears in the corner of the cell. When you rest the pointer on the cell, the note displays in the tooltip.

BI Cell

Purpose

Like a detail report, a BI Cell uses a Sphere, but rather than placing the Sphere into a Detail Area for all the data to be displayed, the BI Cell totals a numeric field from the Sphere and displays a single number.

Using a BI Cell is very much like creating a detail report and totaling a field in the report; it just doesn't require you to display all of the detail on the report. Because the BI Cell is using a Sphere, and the Sphere actually contains the details behind the total, you can always Drill Down into a BI Cell to get the details of what makes up the number.

To add a BI Cell to a report, select the desired Sphere from the Functional Area list box on the Cell Palette, then drag the appropriate BI Cell from the Numeric Cells list box onto the desired cell on the Report Designer spreadsheet.

   

The BI Cell's properties are displayed on the Properties Panel.

   

Definitions

Companies: Allows you to select the desired company. Hold down the Ctrl key for multiple companies.

Sphere: Indicates the sphere from which the cell retrieves its data.

Source of Calculations: This field indicates which of the Sphere’s numeric fields you’re going to total. Typically a Sphere has several numeric fields which can be totaled. If a Sphere doesn’t contain any numeric fields, it can’t be used to populate a BI Cell. After selecting a Sphere, the field dragged onto the report from the Numeric Cells list box is used as the BI Cell’s Source of Calculation.


Date Preference: Allows you to select the date on which your calculations will be based.

Calculation: Allows you to select the date range for your report (MTD = month to date; YTD = year to date.)

Starting Adjustment: Adjusts the calculation based on the number entered.  (If your calculation is MTD and you enter -1, it will show the previous MTD. If your calculation is YTD and you enter -1, it will show the previous YTD.)

Ending Adjustment: Uses the starting and ending adjustment to provide a date range.

Reverse Sign: Reverses the (+/-) sign.  For example, if a number returns from the database as negative (-) but you want it to display as positive (+), check this box.

Sphere Details: Shows SQL statements created after selecting the group by fields.

Notes: You can add notes to individual cells to give your reader additional context for the data it contains. When a cell has a note, in either the Report Designer or Viewer, a yellow icon appears in the corner of the cell. When you rest the pointer on the cell, the note displays in the tooltip.

BI Cell: Filters

Purpose

Using filters in a BI Cell is similar to using Area Filters in a Detail Area. However, in a BI Cell you only affecting the final number. Filters allow you to limit the number of records coming from the database. If you don't use any, you'll get every record encompassed by the Sphere you're using. Cell Filters allow you to limit this number by specifying records you want to see, don't want to see, or any combination of the two. Select as many filters as needed to obtain desired results.

To see Business Intelligence Cell Filter Properties, drag a Numeric Cell onto a report to view its properties on the Properties Panel.

   

Click the green plus sign (+) to the right of Filters.

   


Definitions

Select Criteria: Clicking the blue text displays a dialog box, allowing you to select the desired criteria for the filter.

And/Or: If more than one filter is used, you can affect how results are returned by using the AND & OR operators. The AND operator, which is selected by default, will return all records that satisfy the conditions of all filters in the Detail Area. Selecting the OR operator will return all records that satisfy the conditions of any of the filters.

Variable: Selecting the filter's Variable check box allows a filter's criteria to be defined in the Report Viewer when the report is run, using the Variable Criteria Panel. When selecting the check box, you can either leave criteria blank and add all criteria at runtime, or select some criteria and have the option of adding or subtracting additional criteria at runtime.

SQL Phrase: Selecting the SQL Phrase check box gives you the ability to write a small portion of the SQL statement that is run against the database.

Exclude: Selecting the Exclude check box will, as its name suggests, exclude the filter's criteria from results, rather than including it.

Description Cell

Purpose

Any cell into which you directly enter data is called a Description Cell. Data can be entered by clicking the empty cell and typing. Clicking a non-empty Description Cell and typing replaces any existing text.

Description Cells allow you to define an alternate Drill Down Target. This can be used when your Description Cell contains information but you would like its Drill Down to open another cell.

To assign an alternate Drill Down Target:

1: Define the Description Cell (in this case a formula) by either typing the formula or using point and click method.
2: Highlight the Description Cell and select its Drill Down Target in the Properties Panel.

   

Condition Cell

Purpose

A Condition Cell allows you to make and evaluate the results of comparisons, then execute actions according to the results. It works similarly to an IF statement. A cell reference can be compared to a value, or two cell references can be compared to each other. If the result of the comparison is true, one or more actions can be executed. If the result is false, a different set of actions can be executed.

   

Drag a Condition Cell onto a report to display its properties in the Properties Panel. The placeholder value for a Condition Cell is 7777.77, and appears in the cell where it is dropped. Use the Properties Panel to define the comparison and its results.

   

To define your comparison, click the blue text “If comp 1 = comp 2”. The Condition dialog box displays.

To the right of Comparison #1, enter the cell designation or information to compare. For example, enter "=C3" to use the value in cell C3. Select the type of comparison you'd like to make, such as EQUALS or GREATER THAN. To see all available comparison operators, click the down arrow at right. To the right of Comparison #2, enter the cell designation or information to compare, such as another cell reference or a numeric value. Click Apply.

To the right of True, enter the desired value to display if the comparison evaluates to true. To the right of False, enter the value to display if the comparison evaluates to false. Click apply to save your changes.

In the example below, if the value in A3 is greater than the value in C3, the value from B4 will be displayed. Otherwise, the value from B6 will display.

   

If you need to create more than one condition for a cell, do so by clicking Add Condition at the top of the cell's properties, then selecting And or Or to indicate how to combine the conditions.

   


Definitions

Comparison #1: Enter the first cell reference or value for the condition cell.

Operator: Select a relational operator from the drop down list. Options include Equals, Greater Than, Less Than, Greater Than or Equal To, Less Than or Equal To, Not Equal To, Equals Infinity, Equals NaN, Equals Infinity or NaN.

Comparison #2: Enter the second cell reference or value for the condition cell.

Condition Results: If the condition proves to be true, the cell will display the value or cell reference entered in the True field. If the condition proves to be false, the cell will display the value or cell reference entered in the False field.


Please note this guide applies to Synoptix versions 7 and 8