Youcalc Reference Guide

youbuilder Reference Guide Reference guide for the youbuilder.

Copyright youcalc Software A/S Denmark

Table of Contents Components .................................................................................................................................. 4 Grid & Cell ................................................................................................................................. 4 Link.......................................................................................................................................... 12 Frame ...................................................................................................................................... 13 Text Note ................................................................................................................................. 13 Charts ...................................................................................................................................... 15 Action button .......................................................................................................................... 18 Explorers, Icons, Menus ............................................................................................................... 19 Explorers ................................................................................................................................. 19 The Help Function ....................................................................................................................... 20 Icons & Icon Menus ................................................................................................................. 21 Menu items ............................................................................................................................. 23 Functions ..................................................................................................................................... 24 Using the function builder........................................................................................................ 24 FUNCTIONS ................................................................................................................................. 27 SQL .......................................................................................................................................... 27 Webservices ............................................................................................................................ 28 XML ......................................................................................................................................... 29 Filters ...................................................................................................................................... 29 Introduction to filtering ............................................................................................................... 29 Quick building filters ................................................................................................................ 30 How to filter on one column? .................................................................................................. 30 How to filter on multiple columns? .......................................................................................... 31 Notes for filtering .................................................................................................................... 33 Grouping ..................................................................................................................................... 36 Target .......................................................................................................................................... 36 Time and Date ............................................................................................................................. 38 Logical ......................................................................................................................................... 41 Mathematical .............................................................................................................................. 44 Utility .......................................................................................................................................... 48 Labels .......................................................................................................................................... 49 Text ............................................................................................................................................. 50 Chart properties .......................................................................................................................... 51 Cell .............................................................................................................................................. 51

youbuilder Reference Guide

Page 2

Copyright youcalc Software A/S Denmark Matrix ......................................................................................................................................... 51 Financial ...................................................................................................................................... 52 DefGrid........................................................................................................................................ 55

youbuilder Reference Guide

Page 3

Copyright youcalc Software A/S Denmark

Components This section contains a component description and properties of all components in youbuilder.

Each component type is described in detail The components are: Grid & Cell Settings for grids and cells Link settings for link component Frame Settings for the frame component Text Note Text note settings Charts Chart reference Action Button Settings for the action button

Grid & Cell The difference between a grid and a 'single cell'.

A 'single cell' is an absolute reference, in the calculation (like using the $ signs in Excel formulas).

You can change a 'single cell' to a normal grid, with one cell in the grid properties.

A grid with single cell property will affect all rows and columns.

A grid with one cell is really a grid that only has a value in the first cell. The rest of the cells are NULL. Therefore only the first row will have a value and the rest will return a zero.

youbuilder Reference Guide

Page 4

Copyright youcalc Software A/S Denmark

Setting grid type The grid type is set by right clicking a grid and selecting the desired grid type in the utility menu.

IMPORTANT! Changing grid type may delete your formula, data and other grid settings.

Default Grid.

All new grids you add to the work area are default grids. In a default grid you can type in any kind of data. The data you have typed in the grid will be published in the dashboard and will be reloaded every time you open the dashboard.

Excel Grid.

If you have remote file connections to one or more remote files, you can change a default grid into an Excel grid.

An Excel grid is always updated from the source (the Excel file). If the Builder cannot find the source file it will act as a default grid.

Formula Grid.

All formula results are displayed in formula grids.

You cannot type in a formula grid. It is automatically updated whenever any of the providers for the function are changed.

youbuilder Reference Guide

Page 5

Copyright youcalc Software A/S Denmark Database grid.

Enable the ability to load and save the data from a database embedded in the youcalc Server.

The project must be published as a *.jsp page.

Reference calc grid.

Reference calc is used with filter and sort functions to minimize memory requirements.

It is only possible to select this option on grids that have no sub-functions. The result will be the same as when using a normal formula grid, but the speed of the dashboard will dramatically increase when working with large amounts of data.

Balanced Scorecard Grid.

Balanced scorecard grids are used for calculating KPIs and can be displayed in speed charts and traffic light indicators.

A balanced scorecard grid is always one cell.

The balanced scorecard grid will automatically create a formula that needs two inputs: A target value and a realized value (both single numbers).

IMPORTANT! Changing an existing grid to a balanced scorecard grid will delete the data and formula of the grid.

youbuilder Reference Guide

Page 6

Copyright youcalc Software A/S Denmark

Grid Properties Display tab.

Set the number of actual and visible rows and columns.

Some functions automatically calculate the actual number of rows and columns. The visual number of rows and columns is always set manually.

Cell width is the default column width if no column width has been set.

General tab.

Change name, location and find component ID (unique ID for each single component in the dashboard).

General number format settings include number of decimals, and some number formats.

Themes change the layout of the grid.

Use invisible to make components invisible in the HTML page.

Decide when and if scrollbars are needed. If the length of a list can change set scrollbar to 'always'.

Advanced.

Change between single cell and normal grid.

youbuilder Reference Guide

Page 7

Copyright youcalc Software A/S Denmark

Styling.

Change the style of the grid.

youbuilder Reference Guide

Page 8

Copyright youcalc Software A/S Denmark

Grid Content Menu Rename, copy and paste.

Rename: will edit the name of the component.

Copy: can copy either the component or the content to the clip board. If you have selected the cells inside the grid, you copy the content. If the component is selected (click on row labels or column labels) you copy the component.

Move to: is used to move the grid to another page. Moving a grid will not affect any formulas.

Add to function and Column to function.

Adds the component as an element to the function builder.

Use these when editing a function.

Utility menu: For information about the grid types, please see the grid type section about this subject.

Paste into column/row labels: Paste clipboard content to column or row labels.

Set months/Quarters to Column(s)/Rows: This is used when building quick mock-ups to react quick inputs for charts. Your calculations are not affected.

Copy column/Row labels: Copy labels from one grid and paste them into another.

Paste style: Copies property settings from one grid to another.

Column settings

See separate section about column settings.

Delete, group, properties Delete: Will permanently delete the grid including the attached formula. There is no undo to this function.

Group: Gives you the ability to group several components.

Grouped components can have synchronized scrolling (e.g. if you have several columns located next to each other).

Properties: Opens the property panel youbuilder Reference Guide

Page 9

Copyright youcalc Software A/S Denmark

Column Settings Youbuilder has a variety of column settings. They are divided into general column settings and column styles.

The most commonly used column settings (fit, width... and remove column settings) have been placed directly in the grid content menu.

Column settings Column settings allows you to set alignment, bold/italic style, background color, define a combo box with values or set user defined date format.

Column Style General

Percent Currency

Default text and number setting result will be displayed as a whole number (without decimals and thousand separators) Data is displayed in percentages Adds the current locale currency in front of a number.

Date Long date

Short date format (standard format).

Long date format (standard format).

Whole number

Trend Bool Radio CheckBox Click for date Rating

Picture format used when dividing a realized result with a target. Any number above 1 is displayed as a green arrow up. Any number below 1 is displayed as a red arrow down. 1 is displayed as a question mark.

Displays a checkbox. A marked checkbox is 1, an empty checkbox is 0.

Displays a radio button (NB! in the builder a checkbox is displayed). A marked radio button is 1, an empty radio button is 0. Only one radio button in a column can be selected.

Displays a checkbox. A marked checkbox is 1, an empty checkbox is 0.

Date input format that allows the user to manually write a date (e.g. for filtering). When the user clicks a cell, a small calendar automatically pops up. The date is saved in standard format.

Displays the value as number of stars. The number must be a whole number. Smallest value is zero, largest value is five.

youbuilder Reference Guide

Page 10

Copyright youcalc Software A/S Denmark Link Image

The text in the cell is converted into a link. Use if you have a list with web addresses.

Image format is used in the performance tracker. It displays an image instead of a value.

Image Format The image format displays an image instead of a value.

The value in each cell is used to determine the position of the image (number of pixels the picture should be moved to the right counting from the left border of the cell).

The grid must be a formula grid. A Default grid cannot display image format.

The grid name must end with /// and the component ID of the definition grid (see below).

You find the component ID in the grid properties (called 'model ID just under the component name in the tab 'general'.)

The Definition Grid To determine which image should be displayed in the grid you must configure a Performance Tracker Definition Grid

The grid must have the following columns: Column 1: Name (choose any) Column 2: Sound file name in wave file format Column 3: Picture file name in PNG format Column 4: Picture width in pixels.

HINT: Use the first column as ID column for the pivot calculations for the target and the realized result. This way you ensure that the correct image is applied to the correct person.

The pictures and sounds must be stored in a folder called 'pf' in the inde_files folder of the report.

Example: ..\webapps\dashboards\PerformanceTracker\index_files\pf All sound and picture files must have a lower case extension.

Background The background is automatically configured to use an image called 'linje.png' in the 'pf'-folder.

It can easily be changed by right clicking the workspace, selecting properties and inserting the desired changes.

youbuilder Reference Guide

Page 11

Copyright youcalc Software A/S Denmark

Link A link can link to another page in the dashboard or link to external pages (other reports, intranet pages, etc.)

The name of a link will always be the same as the display name.

Properties Select the page that you wish to link to, or check 'external link'. You can manually edit target frame and other tag properties.

Colors and font settings are made in the display tab.

youbuilder Reference Guide

Page 12

Copyright youcalc Software A/S Denmark

Frame A frame can be a visual square used to divide your dashboard or work area into smaller boxes, or it can be an iframe displaying content of an external html source.

To use iframes simply check the 'iframe' check box in the frame properties and write URL.

Text Note The text note component is used for all texts in the report. Headlines, comments, explanations, etc.

Add a text note by using the icon.

--> Double click the component to add edit text. The component will automatically resize to fit the text.

Styling You can edit the style in the content menu.

Please remember that you should only use fonts that you can expect the receivers of the report to have on their computer (courier, Arial, veranda, Tahoma, times new roman).

youbuilder Reference Guide

Page 13

Copyright youcalc Software A/S Denmark Properties

youbuilder Reference Guide

Page 14

Copyright youcalc Software A/S Denmark

Charts For info how to add a chart to your dashboard and adding data to a chart look under 'general chart properties'.

For detailed settings regarding each chart type, they are listed below.

General Chart Properties Bar and Line Chart Pie Chart

General chart properties Creating a chart Quick icons to the most common charts can be found in the icon menu.

Other carts are located in the top icon menu.

This icon (Other charts) will open a new dialog with all available charts.

Adding data to a chart Data is added to all charts using drag'n'drop from the component explorer.

Simply drag the grid to the chart and release the button on top of the empty chart.

To ad data from more providers, simply drag them to the chart one by one. They will appear as different series in the chart.

For most charts a series of data must be a column of data.

Using a chart as a link All charts are displayed as images in the dashboard and can therefore be used as normal links to other pages. To use a chart as a link, go to advanced tab and select the page you wish to link to.

youbuilder Reference Guide

Page 15

Copyright youcalc Software A/S Denmark

Bar Chart and Line Chart The bar chart and the line chart have similar properties and is therefore both covered in this section. Some settings may apply to only one of the chart types.

You can select different variations of bar charts in the chart menu.

Data format and series naming Each series of data must be stored in a column. You can have multiple columns in one grid or you can add more grids to the same chart. Each column will be added as a separate series.

The chart uses the component name as the series name for components with only one column.

The chart uses a combination of the component name and the column header as the series name for components with more than one column. You can choose to use column header as the series name by checking the checkbox 'Exclude grid name in Series label' in the tab 'General'.

Labels The row labels of the first provider is automatically added as domain labels.

Displaying labels in a 45 degree angle makes it easier to fit long label texts into smaller chart.

To change or delete the axis labels simply delete or edit them in the 'axis' tab.

You can also set the maximum or minimum scale and use the first provider as labels (instead of using the row labels of the first provider).

youbuilder Reference Guide

Page 16

Copyright youcalc Software A/S Denmark

Changing the order of the series The order of the series can be changed in the "series" tab. To delete a series, mark it and click "delete" once.

Please note that even though the series does not disappear from the list, it will be deleted when you click 'OK'.

Pie chart A pie chart displays the distribution of one series (one column) in a pie chart.

You can add only one column of data. The row labels of the provider will be displayed in the labels.

To show/remove labels go to chart properties.

youbuilder Reference Guide

Page 17

Copyright youcalc Software A/S Denmark

Action button The action button is used to 'fire events' such as updating a query or saving data to Excel.

You can add a button by using the action button icon.

The display name of an action button is always the same as the name of the component.

Make sure the button is wide enough to fit the text.

Events An action button can have different events. An 'event' can be Update query (updates all queries in report except cashed queries) Save Excel (saves all data to Excel) Submit (recalculates entire dashboard) You select the event in the property panel.

youbuilder Reference Guide

Page 18

Copyright youcalc Software A/S Denmark

Explorers, Icons, Menus youbuilder has several utilities to help the user navigate the application including: Explorers Icons Menus

Explorers Using explorers In the view menu you can select which explorers should be shown in the youcalc Explorer window.

The youcalc Explorer window can be hidden by using the small arrows between the work area and the explorer.

Component Explorer Gives you a complete overview of all pages and components in your report.

Right click any component to rename, copy, move, delete or set properties.

HINT: If you want to locate a component simply double click the icon of the component. This will highlight the component and move the work area to the page and position with the component.

youbuilder Reference Guide

Page 19

Copyright youcalc Software A/S Denmark

Dependency Explorer

It is possible to get a graphical overview of the relations between the different grids and components by using the Documentation element in the youbuilder.

The element illustrates where the calculated data originates from, with the use of arrows.

Sheet Explorer Sheet explorer gives you a clear overview over your sheets. Use it to easily navigate the pages of your dashboard.

The Help Function The Reference Guide and the Getting Started guide are accessible from Help in the Menu Bar. The reference guide (also accessible by pressing F1) contains specifications on all the components and functions.

The Getting Started guide is a quick rundown of the basics of the youbuilder.

Furthermore, a help function for the Function Library is activated in the menu bar in the Function Library. By pressing a small window, containing the specifics of the chosen function, will appear.

youbuilder Reference Guide

Page 20

Copyright youcalc Software A/S Denmark

Icons & Icon Menus If you are looking for help using the component icons, please go to the component description.

This section holds information only about the top icon menu.

New, open, save, copy and paste The first five icons are new, open, save, copy and paste.

Note that 'save' just saves the project file. This is not the same as publishing a project.

Snap to Grid Snap to grid helps you align components as you build your reports. All components are aligned using a grid (16 by 16 pixels) that is shown as dots in the work area.

TIP: Make all components in a size dividable by 16 to make is easy for you to move and align components.

Documentation Shows the documentation layer. Click any formula grid or combo box to see providers and dependents.

HINT: Providers and dependents on other pages will be shown as a dotted line pointing to nowhere.

Align Aligns selected components.

HINT: To select several components draw a square around the entire area of all the components. Text notes may have a larger actual area than the visible text.

Chart menu Opens the menu to more charts. See the component section for more information about chart types.

youbuilder Reference Guide

Page 21

Copyright youcalc Software A/S Denmark

New function Opens the function builder with at new empty function.

HINT: Select the component(s) you need in your function before clicking this icon. Then the components will automatically appear in the function Builder.

Quick functions The quick function icon contains shortcuts to commonly used functions. Select the input grid(s) before selecting your function type.

Report utilities This icon contains definition grid templates and other small utilities for quickly building dashboards.

HINT: When a function states "use a definition grid for this function" you will find a grid inside this menu with explanatory row labels.

youbuilder Reference Guide

Page 22

Copyright youcalc Software A/S Denmark

Menu items This section contains information about selected menu items.

Publish Project This will publish the project to youcalc.com.

Recalc all Recalculates your entire dashboard. All queries are updated and the content from all excel files will be reloaded.

Project Settings

Set language settings for dates and currencies and write the global value for "show all" in combo boxes and filters.

youbuilder Reference Guide

Page 23

Copyright youcalc Software A/S Denmark

Functions Below are descriptions of the most common functions in youbuilder.

Using the function builder Creating new functions You start a new function by clicking the 'new function icon'.

HINT: Select the component(s) you need in your function before clicking this icon. Then the components will automatically appear in the function Builder.

Show & Hide function builder for a formula You can show or hide the formula builder by clicking the small arrows at the top left corner of the function builder (on top of the function library).

HINT: To display the function of a specific grid, simply click inside the cell of that grid.

Function library To use the functions simply drag and drop them into the function builder area.

HINT: A function must be active before dragging anything to the function builder. To activate the function builder click inside an existing formula grid or create a new function.

Short cuts to the most commonly used mathematical functions are located abode the function library.

If you need a constant in your formula use the 'constant icon' .

Drag and drop it to the function builder the same way as you add functions.

IMPORTANT! A function that only has constants will never be calculated. At least one of the providers must be a grid or a combo box.

youbuilder Reference Guide

Page 24

Copyright youcalc Software A/S Denmark

Adding components to function Right click any component to add it to the active function.

If you only need one column in the function use 'column to function'.

NOTE: that if you change the order of the columns in the provider, you affect the result of the calculation.

Building functions Functions and grids are combined in the function builder to calculate your data.

To attach a function to a new formula grid draw an arrow from the function (in this example a sum-function) to the new formula.

Now attach the grid(s) to the function the same way.

A complete function has as minimum one provider and one function.

youbuilder Reference Guide

Page 25

Copyright youcalc Software A/S Denmark

Right click a function to expand the formula, disconnect the function or change calculation type.

Special parameter functions A few functions needs extra input or provides features that affect other grids. These properties are configured using parameters.

If a function needs parameters it will have two extra options in the content menu: show and hide parameters.

To add a parameter you must drag the component from the component explorer to the parameter window.

youbuilder Reference Guide

Page 26

Copyright youcalc Software A/S Denmark

FUNCTIONS This is a rundown of the specifics of the functions in the youcalc builder. It describes what type of input and which parameters the different functions require.

SQL Query Simple SQL query without Input:.

Input: 1. Data Input.

2. SQL Query.

Drag-and-drop from the functions window to the formula builder and attach it to the "New" box, to insert a query. To specify the criterions for the query in SQL format, drag-and-drop the Constant value box onto the red spot on the query box.

The criterions in this box has to be in text format. The parameter, against which, the query should be run, is a database connection. This database connection is conducted by importing a database. This is done by choosing: Insert -> Database connection -> The database. Follow the instructions for the desired database. The database is now an element in the youcalc Explorer Window (just above the Function Library Window) under the database tab.

Drag-and-drop the database to the green handle on the query box in the formula builder.

NOTE! This function can also be applied to a grid, list or single column.

QueryNoCache Simple SQL query without Input. In addition, this function will conduct a query in the database, every time a change is made in data Input.

Input: 1. Data Input.

2. SQL Query.

Drag-and-drop the function icon, from the functions window to the formula builder and attach it to the "New" box, to insert a query.

To specify the criterions for the query in SQL format, drag-and-drop the Constant value box onto the red spot on the query box.

The criterions in this box have to be in text format. The parameter, against which, the query should be run, is a database connection.

This database connection is conducted by importing a database. This is done by choosing: Insert -> Database connection -> the database. Follow the instructions for the desired database. The database is now an element in the youcalc Explorer under the database tab.

Drag-and-drop the database to the green handle on the query box in the formula builder.

NOTE! This function can also be applied to a grid, list or single column.

SQLFunctionAutoUpdate This function will check if the database has been updated.

This is useful for wide screen reports that automatically updates when the database is updated.

The query can return only one result (e.g. number of sales in the database). Drag-and-drop the function icon into the function builder and attach it to the "New" box.

youbuilder Reference Guide

Page 27

Copyright youcalc Software A/S Denmark Right click the SQLFunctionAutoUpdate box and choose Edit SQL Query to define the SQL query for the data output so desired. The parameter, against which, the query should be run, is a database connection.

This database connection is conducted by importing a database. This is done by choosing: Insert -> Database connection -> the database. Follow the instructions for the desired database. The database is now an element in the youcalc Explorer under the database tab.

NOTE! This function can also be applied to a grid, list or single column.

Webservices Rest Connect to a Webservice provider and retrieve data Input: in XML format.

Input: 1. URL of webservice provider.

To connect to a webservice provider, simply type in the URL in a single cell in the Workspace (If it may change at some point) or a Constant Value box in the Function Builder.

Soap Connect to a webservice provider and retrieve data Input: in XML format.

Input: 1. End Point URL. 2. WSDL. 3. XML Header definition grid.

4. XML Call definition grid.

SoapFunction Manipulate data Input: from webservice provider.

Input: 1. End Point URL. 2. WSDL. 3. XML Header definition.

4. XML Body definition.

youbuilder Reference Guide

Page 28

Copyright youcalc Software A/S Denmark

XML XPathQuery Manipulate data Input: from webservice provider.

Input: 1. Data Input: in XML format.

2. Xpath Query.

To manipulate the data Input: from the webservice Provider, simply type in the query in a single cell in the Workspace (If it may change at some point) or a Constant Value box in the Function Builder.

E.g.: <XML> <Item> <Column 1> <Column 2> <…> <...> This is an example of a query: //*[local-name()='Item']

Filters Introduction to filtering In this document, the element of filtering will be investigated in relations to existing data. The scenario is this: A dashboard has been created and the user needs the opportunity to “filter” data out of the dashboard. If it is a “sales-dashboards” then the viewer may only be interested in seeing one of the sales persons.

Filtering is a vital feature that any youcalc “Solution builder” should master. The element of filtering is evident because any viewer of the youcalc dashboards would want is able to “drill into” the data displayed. Other scenarios could be sales amount, product type, product stage, region etc.

This opportunity is what filtering is all about and the feature can be used in two ways.

Filtering can be done on either one column (se examples above) or on multiple columns (ex: sales and business) First, this document will provide you with a complete walk-through of filtering on one column.

Here, specifying which features, functions and data extracts to use.

Afterwards, the process of creating a filter that filters on two columns will be addressed.

youbuilder Reference Guide

Page 29

Copyright youcalc Software A/S Denmark

Next, filtering on one column will be elaborated and explained.

Quick building filters 1: Get Data (SQL) 2: Create Defgrid 1. (Located under Report Utilities).

Insert Column No. Insert Filter Type. (1=equal to) 3: Create Data provider for Combo box.

4: Data Provider is inserted into the combo box and “Global Show all” is added to the combo box. This is done under “properties” of the combo box.

5: Merge Columns function is created (Filter criterions) Here the combo box(s) is added.

(TIP! Place DefGrid two under DefGrid one in order to get the best overview) 6: Create “Get Show all” function. (Located under Report Utilities).

7: Create filter function: Elements to use: 1: SQL 2: Defgrid 1 3: The Merged Columns 4: Show All function.

The list is generated and only needs to be expanded with visible rows/columns/(column labels/renamed e.g.

How to filter on one column?

In this section of the document, the concept of filtering will be introduced where the task is to filter on one column.

In order to be able to filter on one column it is important that the data that you want to filter is available. A rule of thumb is to have a look at the main SQL query and specify which column should be filtered on. When this has been specified an additional query has to be made that explicitly focuses on the column that has to be filtered.

This leaves you with two query extracts and both of them are mandatory.

This was task one (creating main SQL and a column specific SQL-extract).

Task No. two is to create a definition grid that specifies which column should be used in the main query and selects which filter type should be used. This one is found under “report Utilities” in the top menu bar. In the first box the filter is stated. In the query, for this example, sales person is located in column 1 and therefore the number “1” is inserted. Next the filter type is inserted. For simplistic reasons, “1” is inserted. (1 representing filter type “equal to”).

Now you have built defgrid 1 for the filter formula. See screenshot : Note, that if other values were needed – an overview of the different values that can be inserted can be found on page 5 in this document.

Now the SQL query for the column that you would like to filter on should be used as a data provider for the combo box that you would like to use for filtering. The reason why this list should be generated by SQL and not just by a manually written list is that if the list is created manually it will be a static list that needs to be updated, whenever a sales person is added or deleted. The list of sales people could look something like this:

youbuilder Reference Guide

Page 30

Copyright youcalc Software A/S Denmark So you right-click the combo box and choose the SQL-extract as data provider to the combo box.

Remember to add “Global Show all” to the combo box. This is done under “properties” of the combo box. This combo box has to be used as Filter criterions in the “filter function”. This enables the user to change the data that is being displayed via the combo box.

The last function needed in the function is a “Get Show All”. This function element can be found under report utilities in the top menu bar in the youbuilder.

Now you are ready to build the filter function. Select “New Function” in the top menu bar in the youbuilder and go to the functions library and drag and drop the “Filter” function to the function. Here you add the following function elements to the function: 1: SQL 2: Defgrid 1 3: The combo box 4: Show All function.

- And click “insert function” A new list that is similar to the main SQL is generated and only needs to be expanded with visible rows/columns/(column labels/renamed e.g.

Now you have a combo box that can sort this new list.

How to filter on multiple columns?

In this section of this document, the concept of filtering multiple columns will be addressed.

The process is somewhat similar to the process of creating a filter that can filter on one column.

The difference will be explained in the total walk-through, below.

The object is to create a filter that filters on two columns: The filter functions need the following filter elements: 1: Main SQL 2: Defgrid 1 3: The Merged Columns 4: Show All function.

These functions will be built below: To begin with, you need three SQL-queries.

1: Main SQL query 2: Column 1 SQL query (Sales person) 3: Column 2 SQL query (Amount of sales) The main SQL will be used in the filter function, while the two SQL’s will be utilized as data provider for the combo boxes that will specify the sales persons and the amount of sales.

TIP! When done like this, you will be sure that whenever a sales person or a sale is added or deleted, the combo boxes will take this into consideration. The Combo boxes are created generic when created like this.

youbuilder Reference Guide

Page 31

Copyright youcalc Software A/S Denmark

The next feature to build is the Defgrid 1. This grid should have two columns and two rows.

This grid will specify which columns should be focused on in the main grid (Sales person=1 and Amount = 2) and in addition which filter type should be used.

In this case, filter type has been chosen because we are looking for elements that are “equal to” the values that the filter has to have.

Now the two combo boxes should be created. Here you simply drag and drop two combo boxes from the left menu into the dashboard and use the two SQL grids as data providers individually for the combo boxes. Remember to add a “Show all” element to the combo boxes. This can be found by right clicking the combo box – clicking properties – and clicking “Use Global show all value”.

These two combo boxes have to be connected to one another, because they are interrelated to one another. In other words, when one of the filters is set to a specific value or subject, then the other must take this choice into consideration.

For this, the function merge columns is needed. This function is found under Grid in the Function library.

On the right, you can see exactly how the two combo boxes should be merged: This is your Filter criterions in the filter function.

The last element you need for the function is the “Get Show All” function element. This is Located under Report Utilities in the top menu bar of the Youbuilder.

Now you have created all the elements that the filter function needs: 1: Main SQL 2: Defgrid 1 3: The Merged Columns 4: Show All function.

When the function has been built, it may look something like this: Be aware that the naming of the elements can aviate from this example - to yours.

The list is generated and only needs to be expanded with visible rows/columns/(column labels/renamed e.g.

Now you have created a grid that you can filter by using the two combo boxes.

In general what is different from filtering on one column and multiple columns is listed below:

1: All the relevant columns must be included in DefGrid 1. No. of rows is fixed.

2: Data providers must be created for all the columns that need filtering.

3: Combo boxes for the corresponding data providers must be created.

youbuilder Reference Guide

Page 32

Copyright youcalc Software A/S Denmark

Notes for filtering Types of filters: 1: (Equal to) 2: (Between two values) 3: (In a list) 4: 0 = false, greater than 0 = true 5: (Greater than) 6: (Less) 7: (Simple bool) 1 = true, 0 or empty = false 8: (Not In a list) 9: (Not NULL)

Filter Filters a grid according to two definition grids.

DefGrid 1 defines the columns to filter (row1) and type of filter (row2). Filter criterions specifies the value(s) the filter is based on.

Input: 1. Grid to filter 2. DefGrid 1 3. Filter criterions 4. Grid with SHOW ALL. Types of filters: 1. (Equal to) 2 .(Between two values) 3. (In a list) 4. 0 = false, greater than 0 = true 5. (Greater than) 6. (Less) 7. (Simple bool) 1 = true, 0 or empty = false 8. (Not In a list) 9. (Not NULL)

MultiConditionFilter The same as Filter, but with the ability to add multiple conditions on the same columns.

This function is slightly slower than Filter and should only be used if there is a column with more than one condition.

Input: 1. Grid to filter 2. DefGrid 1 3. Filter criterions 4. Grid with SHOW ALL

QuickFilterEqualTo Filters a grid by comparing a column to a single value.

youbuilder Reference Guide

Page 33

Copyright youcalc Software A/S Denmark Input: 1. Grid to filter 2. Column number to compare 3. Value to compare to.

QuickFilterGreaterThan Filters a grid by comparing a column to a single value.

Input: 1. Grid to filter.

2. Column number to compare.

3. Value to compare to.

QuickFilterLessThan Filters a grid by comparing a column to a single value.

Input: 1. Grid to filter 2. Column number to compare 3. Value to compare to

QuickFilterBetween Filters a grid by comparing a column to two single values. Bounds are not included.

Input: 1. Grid to filter 2. Column number to compare 3. Lower bound4.Upper bound

QuickFilterBetweenIncludeBounds Filters a grid by comparing a column to two single values. Bounds are included.

Input: 1. Grid to filter 2. Column number to compare 3. Lower bound4.Upper bound

QuickFilterIn Filters a grid by comparing a column in the grid to a list of values.

Input: 1. Grid to filter 2. Column number to compare 3. Column with values to filter

PrepareDataforFilter Creates a grid with all unique values of a range of columns.

youbuilder Reference Guide

Page 34

Copyright youcalc Software A/S Denmark This is an easy way of preparing many combo boxes based on a single data grid. It is recommended to only use this function on small amounts of data (less than 5000 rows).

Input: 1. Grid with data (typically a query) 2. Rolegrid with number of the columns that should be prepared for filtering. The role grid has 1 row and the same number of columns as the grid with data.

3. Cell with value for 'Show all'.

ExcludeDoubles Filters the data in a column. Each value will only appear once in the result grid. This function can only be used on a single column.

Input: 1. Column to filter

ExcludeDoubleRows Filters the data in a grid by a single column. Each value in the column will only appear once in the result grid (the first grid with a unique value is returned).

This function can be used on a grid with more than one column.

Input: 1. column to filter 2. the column to use for excluding doubles

SelectedComboBoxIndex Returns the index of the selected value in a combo box. E.g. if a combo box holds values a, b and c (in that order), then Input: a=1, b=2 and c=3.

Input: 1. Combo Box

Hierarchical Filters a grid with hierarchical data (data in a tree data structure).

A tree data structure has a parent-child relationship with parent IDs in one column and child IDs in another column.

This function can filter output to children (1st level) or to the entire hierarchy below the selected node.

Input: 1. Grid to filter 2. Column number of parent ID 3. Column number of child ID 4. RoleGrid with column numbers of: 1) root id ("top parent"), 2. levels to display (-1 = all), 3. include root in output (1 = Yes, 0 = no)

Sort Sorts a grid by a specified column.

Input: 1. Grid to sort 2. Sort column (number of the column) youbuilder Reference Guide

Page 35

Copyright youcalc Software A/S Denmark 3. Direction (0=ascending, 1=Descending)

Grouping PivotOneDimension Pivot function that calculates sum, count or average per ID. IDs can be the column with sales persons, customer categories, businesses, etc. E.g. sale per salesperson, meetings per type of customer or average size of a sale in different businesses.

Input: 1. The grid on which the calculation is based 2. Column labels (Not mandatory) A grid with the labels can be used to determine which IDs should be used in which order (not mandatory).

Use a definition grid to setup this function.

PivotTwoDimensions Pivot function that calculates sum, count or average per two IDs. IDs can be the column with sales persons, customer categories, businesses, etc. E.g. sum of open sales per stage per salesperson or meetings per customer category per type of meeting (task type).

Input: 1. The grid on which the calculation is based 2. The DefGrid 3. Row labels (Not mandatory) 4. Column labels (Not mandatory) Two grids with the row and column labels can be used to determine which IDs should be used in which order (not mandatory).

Use a definition grid to setup this function.

TopX Selects the largest values in a pivot result and makes a sum of the rest. E.g. sale in top five countries plus the rest. You can select how many categories to show (e.g. top5, top10, etc.) and the label for "the rest".

This function automatically copy the row labels for the top categories.

Input: 1. The grid on wich the calculation is based 2. Number of categories to show 3. Cell with text for "the rest" (e.g. "other").

Target BudgetConvert

youbuilder Reference Guide

Page 36

Copyright youcalc Software A/S Denmark TBudgetConvert.0=Converts a standard budget table (year, associate, months/quarters) to a table that can be filtered using the normal Filter and TimePivot functions. A date for each budget period (first date of the period) is set to standard date format.

You can find a layout template for the budget grid in the definition grid menu.

Input: 1. The grid with the budget.

2. Time Unit (1=Month, 2=Quarter)

TargetConvert Used to convert the target data from an easy-to-write into easy-to-filter-and-calculate.

Input: Datagrid.

Number of dimensions.

Number of settings per target.

The structure of the datagrid must be the following: year 2008

{Dimensions} Team, person Etc.

{Settings} Sale, products, currency etc.

Time unit Day/week/ Month/quarter/ Year

Targets The targets.

Always 12 columns

The year always consist of 4 characters.

There can be an unlimited number of dimension columns.

There can be an unlimited number of settings columns.

When time unit is day or week, only the first target column is read. The value is spread into all weeks of the year or all days of the year respectively.

When time unit is month, the 12 following columns correspond to a month.

When time unit is quarter, the 4 following columns correspond to a quarter.

When time unit is year, the first column corresponds to a year.

NOTE! A row can have an unlimited number of targets (with settings, time unit and 12 target columns). The year and dimensions correspond to the entire row.

youbuilder Reference Guide

Page 37

Copyright youcalc Software A/S Denmark

Time and Date Now Returns the current time of the server in standard date format. To get the time in SuperOffice Format you must divide by 1000.

GetStartofTime Returns the start date in a period.

Input: 1. Now 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year) 3. Period (-1=last period, 0=this period, 1=next period etc.)

GetStartDate Returns the start date (standard format) of a corresponding combo box.

Input: 1. Now (a grid with current time in standard format) 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year) 3. Start Combo Box with Start Period (-1=last period, 0=this period, 1=next period etc.)

GetEndDate Returns the end date (standard format) of a corresponding combo box.

Input: 1. Now (a grid with current time in standard format) 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year) 3. Start Combo Box with Start Period (-1=last period, 0=this period, 1=next period etc.)

4. End Combo Box with Periods to Roll

GetStartValue Converts a date (standard format) to the start value of a TimePivot funtion.

Input: 1. Start Date (standard format) 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year) 3. Now (a grid with current time in standard format)

GetEndValue Converts a date (standard format) to the end value of a TimePivot funtion.

Input: 1. Start Date (standard format) 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year) 3. Now (a grid with current time in standard format)

youbuilder Reference Guide

Page 38

Copyright youcalc Software A/S Denmark

GetTime Calculates the period number of a date field (SuperOffice format).

Input: 1. Date (SuperOffice format) 2. Time Unit (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year)

TimeFillStart Creates a combo box provider with time periods.

Use a definition grid to configure this function.

Input: 1. Now (a grid with current time in standard format) 2. Definition Grid 3. Default Value (optional)Definition Grid: Cell 1. Type (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year)Cell 4. Start Period (-1=last period, 0=this period, 1=next period etc.)Cell 3. Number of Periods to roll (minimum 1)

TimeFillEnd Creates a combo box provider with time periods.

Use a definition grid to configure this function.

Input: 1. Now (a grid with current time in standard format) 2. Definition Grid3. Start Combo Box4. Default Value (optional)Definition Grid: Cell 1. Type (-1=Day, 0=Week, 1=Month, 2=Quarter, 3=year)Cell 3. Start Period (-1=last period, 0=this period, 1=next period etc.)Cell 3. Number of periods to roll (minimum 1)

ExcelSerialNumber Converts a standard date format into Excel date format. Excel uses a special serial number format.

Input: 1. Date(s) to convert.

DateFill Retunes a list for a comboBox with • Label (e.g. months names in local language) • DATE • Default selected.

Input: 1. Time Unit (day/-1; week/0; month/1; quarter/2; year/3).

2. Period back (relative to now)(-1=last, 0=this etc.).

3. Periods forward (relative to now) (-1=last, 0=this etc.).

4. Start or end (0=start, 1=end).

5. Optional: Default selected (default NOW).

youbuilder Reference Guide

Page 39

Copyright youcalc Software A/S Denmark

DatePivotOneDimension Returns a sorted table over a user defined period of time.

Input: 1. Main grid.

2. The column with dates in standard java format .

3. The column with the values to calculate.

4. The calc type (Sum/s/1; count/c 2; avg/a/3).

5. The time unit interval in the pivot (day/-1; week/0; month/1; quarter/2; year/3) 6. The start date in standard java format .

7. The end date in standard java format.

NOTE! The time unit (provider 5) defines the row labels. If it is set to Month, the row labels will be eg, May 2008, Jun 2008, Jul 2008.

The start date defines the FIRST row label, but does not need to be the first millisecond in the period. If for example the date 7/5-2008 is used as provider 6, the function will automatically detect what period (defined in privoder 5) it belongs to, and set the start label to May 2008.

The end date defines the LAST row label, but does not need to be the last millisecond in the period. If for example the date 9/5-2008 is used as provider 7, the function will automatically detect what period (defined in privoder 5) it belongs to, and set the end label to Jul 2008 NB!!

The Labels for Quaters must be "Q1 2008".

DatePivotTwoDimensions Returns a sorted table over a user defined period of time a a category.

Input: 1. Main grid.

2. The column with dates in standard java format .

3. The column with the categories(the column labels in the pivot table).

4. The column with the values to calculate.

5. The calc type (Sum/s/1; count/c 2; avg/a/3).

6. The time unit interval in the pivot (day/-1; week/0; month/1; quarter/2; year/3) 7. The start date in standard java format .

8. The end date in standard java format.

9. (Optional) The category labels. Row wise!!!

NOTE! The time unit (provider 5) defines the row labels. If it is set to Month, the row labels will be eg, May 2008, Jun 2008, Jul 2008.

The start date defines the FIRST row label, but does not need to be the first millisecond in the period. If for example the date 7/5-2008 is used as provider 6, the function will automatically detect what period (defined in provider 5) it belongs to, and set the start label to May 2008.

The end date defines the LAST row label, but does not need to be the last millisecond in the period. If for example the date 9/5-2008 is used as provider 7, the function will automatically detect what period (defined in provider 5) it belongs to, and set the end label to Jul 2008 NB!!

The Labels for Quaters must be "Q1 2008".

youbuilder Reference Guide

Page 40

Copyright youcalc Software A/S Denmark

Logical If If is used to test a logical statement (an if-sentence) on each row in a grid. E.g. If a sale is too old (if sale date is greater than current date) or if a sale is large and needs extra attention (if sale amount is greater than 1000 euros).

Use the logical functions (EqualTo, GreaterThan, Between, etc.) in this function category as Input: for the if-function.

Input: 1. The logical statement (use the logical functions) 2. The true result 3. The false result

ExecutionIf ExecutionIf is used when you create a logical statement (an if-sentence) that returns only one outcome (true or false).The output for true or false can be any value or formula you can imagine. The size of the true and false outputs grids can be different.

Use the logical functions (EqualTo, GreaterThan, Between, etc.) in this function category as Input: for the if-function.

Input: 1. The logical statement (use the logical functions) 2. The true output (grid or formula) 3. The false output (grid or formula)

Not Use to build logical statement in an If or ExecutionIf function. Use this to reverse a logical statement.

EqualTo Use to build logical statement in an If or ExecutionIf function. Will test if two values are equal to each other. Note that this function is case sensitive.

Greater Use to build logical statement in an If or ExecutionIf function. Will test Input: 1 is greater than Input: 2.

Less Use to build logical statement in an If or ExecutionIf function. Will test Input: 1 is less than Input: 2.

youbuilder Reference Guide

Page 41

Copyright youcalc Software A/S Denmark

NotEqual Use to build logical statement in an If or ExecutionIf function. Will test if two values are different from each other. Note that this function is case sensitive.

NotGreater Use to build logical statement in an If or ExecutionIf function. Will test Input: 1 is less than or equal to Input: 2.

NotLess Use to build logical statement in an If or ExecutionIf function. Will test Input: 1 is greater than or equal to Input: 2.

Contains Use to build logical statement in an If or ExecutionIf function. Will test if a value is contained in a cell string. Note that this function is case sensitive.

In Find out if a value is contained in a grid.

The In function will check the reference of choice, to see if the set value is contained in this reference. Attach the In function to an If function (If or ExecutionIf). Attach the set value and the reference of choice (Grid, database, lists, etc.) to the Input: variables.

Input: 1. Provider, the set value (the cell to test against the grid).

2. Provider, the reference of choice (grid, list of values(row wise)).

ContainsIgnoreCase Use to build logical statement in an If or ExecutionIf function. Will test if a value is contained in a cell string. Note that this function is case sensitive.

Between Logical between test.

Input: 1. Provider Provider to test 2. Provider Lower bound 3. Provider Upper bound.

And Use to build logical statement in an If or ExecutionIf function. Use this function to apply multiple conditions to your logical statement. All conditions must be true to return the 'true' value, else the 'false' value is returned.

youbuilder Reference Guide

Page 42

Copyright youcalc Software A/S Denmark

Or Use to build logical statement in an If or ExecutionIf function. Use this function to apply multiple conditions to your logical statement. If any one of the conditions are true, the function will return the 'true' value, else the 'false' value is returned.

CountIf Counts the number of rows that meet the logical test. E.g. number of sales larger than 1000 Euros. The result of this function is always a single number (the number of rows where [logical test]).

Use the logical functions (EqualTo, GreaterThan, Between, etc.) in this function category as Input: for the CountIf-function.

Input: 1. The grid 2. The logical statement (use the logical functions)

SumIf Sums the values in a column if the row meets the logical test. E.g. the sum of sales older than current date. The result of this function is always a single number (the sum of column X for all rows where [logical test]).

Use the logical functions (EqualTo, GreaterThan, Between, etc.) in this function category as Input: for the CountIf-function.

Input: 1. The grid 2. The column to sum 3. The logical statement (use the logical functions)

youbuilder Reference Guide

Page 43

Copyright youcalc Software A/S Denmark

Mathematical Abs Returns the absolute value of a number, a number without its sign, from a range or cells, columns or rows. Supports both columns wise, row wise and grid wise.

Input: 1. Grid a cell, a column or a row

Avg Returns the average of selected values, which can be a range of cells, columns or rows.

Calculation types: columns wise, row wise and all.

Input: 1. Grid a cell, a column, a row or grid(s).

Ceil Returns the smallest (closest to negative infinity) double value that is not less than the argument and is equal to a mathematical integer. Calculation types: columns wise and row wise.

Input: 1. Grid a cell, a column or a row.

Count Counts the number of values in the grid.

Calculation types: columns wise, row wise and all.

Input: 1. Grid a cell, a column, a row or grid(s).

Division Returns the value of one or more cells, columns or rows divided with a constant or another cell, column or row, which has to be different from 0. Supports both columns wise, row wise and grid wise.

Input: 1. Double a value.

2. Double a value.

Floor Returns the largest (closest to positive infinity) double value that is not greater than the argument and is equal to a mathematical integer.

Calculation types: columns wise and row wise.

Input: 1. Grid a cell, a column or a row.

youbuilder Reference Guide

Page 44

Copyright youcalc Software A/S Denmark Note! Special cases:*If the argument value is already equal to a mathematical integer, then the result is the same as the argument.* If the argument is NaN or an infinity or positive zero or negative zero, then the result is the same as the argument.

Ln Returns the natural logarithm of a number, from a range or cells, columns or rows.

Calculation types: columns wise and row wise.

Input: 1. Grid a cell, a column or a row.

Max Returns the largest number in a set of values in cells, columns or rows.

Calculation types: columns wise, row wise and all.

Input: 1. Grid a cell, a column, a row or grid(s).

Min Returns the smallest number in a set of values in cells, columns or rows.

Calculation types: columns wise, row wise and all.

Input: 1. Grid a cell, a column, a row or grid(s).

Minus Returns the value of one or more cells, columns or rows minus a constant or another cell, column or row.

Calculation types: columns wise and row wise and all.

Input: 1. Grid a cell, a column, or a row.

Multiply Returns the value of one or more cells, columns or rows multiplyed with a constant or another cell, column or row.

Calculation types: columns wise and row wise and all.

Input: 1. Grid a cell, a column, or a row.

Power Returns the value of one cell, column or row multiplied to the power of a constant or another cell, column or row.

Calculation types: columns wise and row wise and all.

Input: 1. Grid a cell, a column, or a row.

youbuilder Reference Guide

Page 45

Copyright youcalc Software A/S Denmark

Round Rounds a number to a specified number of digits Supports both columns wise, row wise and grid wise.

Input: 1. Grid the number(s) to be rounded argument 2. grid the number of digits.

Random Returns an uniform distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.

Calculation types: columns wise and row wise and all.

Pi Returns the correctly rounded positive square root of a double value.Input:1. Double a value.Special cases: * If the argument is less than zero, then the result is NaN.* If the argument is positive infinity, then the result is positive infinity* If the argument is positive zero or negative zero, then the result is the same as the argument. Otherwise, the result is the double value closest to the true mathematical square root of the argument value.

Sin Returns the correctly rounded positive square root of a double value.

Input:1. Double a value.

NOTE! Special cases: * If the argument is less than zero, then the result is NaN. * If the argument is positive infinity, then the result is positive infinity * If the argument is positive zero or negative zero, then the result is the same as the argument Otherwise, the result is the double value closest to the true mathematical square root of the argument value.

Sum TSum.0=Adds all the values in a range of cells or grids.

Calculation types: columns wise and row wise and all.

Input: 1. Grid a cell, a column, grid(s) or a row.

SumCulomn Adds all the values in a range of cells or grids.

Calculation types: columns wise and row wise and all.

Input: 1. Grid a cell, a column, grid(s) or a row.

Sqrt Returns the correctly rounded positive square root of a double value.

Input: 1. Double a value.

youbuilder Reference Guide

Page 46

Copyright youcalc Software A/S Denmark NOTE! Special cases: * If the argument is less than zero, then the result is NaN.* If the argument is positive infinity, then the result is positive infinity* If the argument is positive zero or negative zero, then the result is the same as the argument. Otherwise, the result is the double value closest to the true mathematical square root of the argument value.

RelativeSum Sums relative on two grids e.g primo/ultimo cash flows. Supports both columns wise, row wise and grid wise.

SumColumns Returns the conditional sum.

Input: 1. Grid.

2. First column to sum 3. Last column to sum.

Trend Returns values along a linear trend. Fits a straight line (using the method of least squares).

Input: 1. Double a value

Grid ExtractColumns Extracts one or more columns from a grid.

Input: 1. Grid 1 2. Grid 2 3. Definition grid 1 4. Definition grid 2.

A definition grid (one row) holds the numbers of the columns that should be extracted. The role grids must have the same number of columns as the corresponding master grid. E.g. to extract column 3 as the first column in the output grid you need to put the number "1" in the third column of the definition grid.

ExtractTwoColumns Extracts columns from two grids.

Input: 1. Grid 1 2. Grid 2 3. Definition grid 1 4. Definition grid 2.

youbuilder Reference Guide

Page 47

Copyright youcalc Software A/S Denmark Two definition grid (one row) holds the numbers of the columns that should be extracted. The role grids must have the same number of columns as the corresponding master grid.

SimpleExtractColumns Extracts one or more columns from a grid.

A definition grid (one column) holds the numbers of the columns that should be extracted.

Input: 1. Grid with the columns you want to extract 2. The definition grid

RowCount Counts the number of rows in a grid that are not null (empty).

Input: 1. The grid CellCount Counts the number of cells in a grid that are not null (empty).

Input: 1. The grid

Utility Fill Creates a column or row with a specified value in all cells.

The value is in a single cell grid.

Input: 1. Cell with the value.

2. Number of cells to fill.

FixedColumn Fixes the column coordinate of a cell or column(e.g. when multiplying a single number with a number of columns).This function is always used as a sub-function to other functions.

Input: 1. The column to fix.

FixedRow Fixes the row coordinate of a cell or row(e.g. when multiplying a single number with a number of rows).This function is always used as a sub-function to other functions.

Input: 1. The row to fix.

youbuilder Reference Guide

Page 48

Copyright youcalc Software A/S Denmark

GetShowAll Gets the value for Show All set in this project. By using this function you ensure that the value is always the same in all filters and combo boxes.

GetWebAppName Returns the name of the Web Application in the URL bar.

GetClickValue Returns the value of a desired cell (by clicking on the corresponding cell).

Input: 1. Main grid.

2. The value to be extracted.

ParamPutGrid Copies the values from a master grid to a dependent grid. Both master and dependent grid must be added as parameters.

Input: This function is used to trigger calculations in the dashboard. The function is only recalculated when an Input: is changed. You can add any grid or combo box as Input:.

Parameters: 1. The master grid.

2. The dependent grid.

GoalSeek Calculates the values to achieve the necessary goal. This function has multiple inputs.

ProviderGoalSeek Calculates the values to achieve the necessary goal. This function is used when the following calculation is dependent

Labels GetColumnLabels Returns a grid with the column labels from another grid.

Input: 1. Grid with the labels.

GetRowLabels Returns a column with the row labels from another grid.

Input: 1. Grid with the labels.

youbuilder Reference Guide

Page 49

Copyright youcalc Software A/S Denmark

PutColumnLabels Puts a row of values into the column labels of another grid.

Input: 1. Row with the labels.

Parameter: The grid that should have the labels

PutRowLabels Puts a column of values into the row labels of another grid.

Input: 1. Column with the labels.

Parameter: The grid that should have the labels

Text WriteText TWriteText.0=Puts the text of a cell into a combo box. The character "/" creates a line break in the text. use Input: 2 and 3 to set the size of the text note.

Input: 1. Cell with the text 2. Number of lines in the text note.

3. Width of the text note (number of characters).

Parameter: 1. The cell with text note .

2. The text note.

MergeText Concatenates the text of two or more cells. The function automatically puts a space between each value. You can add multiple providers for this function.

Input: 1. Cells with text to merge.

youbuilder Reference Guide

Page 50

Copyright youcalc Software A/S Denmark

Chart properties SetPieChatProperties Sets the headline text in a pie chart.

Input: is a cell with the text.

Input: 1. Cell with headline text Parameter: 1. The chart (pie chart type)

SetChatProperties TSetChartProperties.0=Sets the text for headline, value axis and domain axis in a bar chart.

Input: is a Definition grid (a column with the text).

Row 1 = headline text Row 2= value axis label Row 3= domain axis label Input: 1. Definition grid.

Parameter: 1. The chart (bar chart type)

Cell Integer Converts a cell to an integer. Only works with a single cell.

If the Input: cell contains a text string the function will return 0.

Input: 1. Cell to convert.

String Converts a cell to a string. Only works with a single cell.

Input: 1. Cell to convert.

Matrix MatrixTranspose Returns the inverse of a grid. Rows will be transposed to columns and columns to rows.

Input: 1. Grid to transpose.

NOTE! Labels are not copied to output grid.

youbuilder Reference Guide

Page 51

Copyright youcalc Software A/S Denmark

MatrixTransposeWithLabels Returns the inverse of a grid. Rows will be transposed to columns and columns to rows. Labels are copied to output grid.

Input: 1. Grid to transpose.

CellConvert Converts the value from the desired cell to value in the corresponding in a different grid.

Input: 1. Main cell/column.

2. target cell/column.

Financial Irr Returns the internal rate of return for a schedule of cash flows.

Input: 1. Cash flows.

Parameters: Column-wise and row-wise calculation types.

IrrTime

Returns the internal rate of return for a schedule of cash flows.

Input: 1. Cash flow values.

2. Payment dates.

Parameters: Column-wise and row-wise calculation types.

Pmt Returns the periodic payment for an investment.

Input: 1. The interes rate.

2. Present value.

3. Total number of periods.

Mortgaging Returns the mortgaging table for an investment.

Input: 1. The interes rate.

2. Present value.

3. Total number of periods.

4. Payment each period.

youbuilder Reference Guide

Page 52

Copyright youcalc Software A/S Denmark

YearFrac Returns the fraction of the year by the number of whole days.

Input: 1. Start date.

2. End date.

3. Type of day, count basis.

Nominal Returns the nominal annual interest rate. The nominal interest rate is the interest rate as stated on the face of a security.

Input: 1. The effective interest rate.

2. The number of compounding periods per year.

DB Returns the depreciation of an asset using the fixed-declining balance method.

Input: 1. The initial cost.

2. The salvage value.

3. The number of periods over which the asset is being depreciated.

4. The period for which the depreciation is to be computed.

5. The number of months in the first year.

Ddb Returns the depreciation of an asset using the double-declining balance method.

Input: 1. The initial cost.

2. The salvage value.

3. The number of periods over which the asset is being depreciated.

4. The period for which the depreciation is to be computed.

5. The rate at which the balance declines.

Effect Returns the effective annual interest rate. The nominal interest rate is the periodicallycompounded interest rate as stated on the face of a security.

Input: 1. The effective interest rate.

2. The number of compounding periods per year.

FV Returns the future value of an investment. The future value is the value, at some time in the future, of a current amount and a stream of payments.

youbuilder Reference Guide

Page 53

Copyright youcalc Software A/S Denmark Input: 1. The interest rate.

2. The total number of payment periods.

3. The payment made in each period.

4. The present value.

5. When.(1. AT_BEGINNING_OF_PERIOD. 0:AT_END_OF_PERIOD).

Nper Returns the number of periods for an investment for which periodic, and constant payments are made and the interest rate is constant.

Input: 1. The interest rate.

2. The payment made in each period.

3. The present period.

4. The future value.

5. When.(1. AT_BEGINNING_OF_PERIOD. 0:AT_END_OF_PERIOD).

Npv Returns the net present value of a stream of equal periodic cash flows, which are subject to a given discount rate.

Input: 1. The interest rate.

2. Values of equally-spaced cash flows.

Parameters: Column-wise and row-wise calculation types.

Pmt2 Returns the periodic payment for an investment.

Input: 1. The interest rate.

2. Total number of periods.

3. The present value.

4. The future value.

5. When.(1. AT_BEGINNING_OF_PERIOD. 0:AT_END_OF_PERIOD).

PV Returns the net present value of a stream of equal periodic cash flows, which are subject to a given discount rate.

Input: 1. The interest rate.

2. Total number of periods.

3. Payment each period.

4. The future value.

5. When.(1. AT_BEGINNING_OF_PERIOD. 0:AT_END_OF_PERIOD).

Sln

youbuilder Reference Guide

Page 54

Copyright youcalc Software A/S Denmark Returns the depreciation of an asset using the straight line method.

Input: 1. The initial cost.

2. Salvage value.

3. The number of periods over which the asset is being depreciated.

Syd Returns the depreciation of an asset using the sum-of-years digits method.

Input: 1. The initial cost.

2. The salvage value.

3. The number of periods over which the asset is being depreciated.

4. The period.

DefGrid FilterDefGrid1 Filters a desired column by a user defined parameter.

Input: 1. Column nr.

2. FilterType.

FilterType Parameters: 1. (Equal to).

2. (Between two values).

3. (In a list).

4. 0 = false, greater than 0 = true.

5.(Greater than).

6.(Less).

7.(Simple bool) 1 = true, 0 or empty = false.

8.(Not In a list).

9.(Not NULL).

youbuilder Reference Guide

Page 55