How to decide when to use Value Source or Excel formulas?

How to decide when to use Value Source or Excel formulas?

Created by: Johann Du Toit

Modified on: Fri, 14 Feb, 2020 at 9:41 AM


Warning! This is a confusing subject, but please bear with us.

If you've done some report designs and created formula columns, then you would be familiar with "Value Source" or "Data Source" formulas:

For instance let's say you have a column called "Total Value", with the formula: [Quantity] * [Line Value]

But confusingly Wink also has something called "Excel formulas".

Like in Microsoft's Excel product, an Excel formula is just a text string starting with "=".

These are different from the value source example we gave above. The "data source formulas" are calculated row by row in the database itself as we retrieve the data. The calculation is done before any processing or aggregation is performed - you are in effect defining a new database value.

But an Excel formula is just a string - no calculation happens while we fetch all the rows of data.

For example if you export the report to Excel, Wink doesn't do any calculations - we just put that formula string like that into Excel, and Excel will do all the calculations when you open the sheet. (give it a try!)

But if you view the report on our website we do detect if there are Excel formulas in the final rendering of the report and then calculate those values as well. So just how Excel will only calculate these after all the other data is already there, similarly they are only calculated as the very last step when showing it on the screen in the browser.

This means if you make a formula column in Wink, and put in "=A1", you will see the contents of A1 in every cell of that column. It's a pain to figure out what the column letter for each of your columns are though, so in wink you can also put in "={Total Column}1" and it will replace {Total Column} with the excel column letter, eg A or B or C. Similarly you can also replace the "1" with {row} and it will substitute the current row number in there... so to go back to our original example:

We have the calculated column [Total Value]. If I wanted to create a new column called "Half of Total Value", then I can put that column's value as "={Total Value}{row} / 2" and you should see the right values calculated. Using Excel formulas are very useful if you need to calculate things after aggregation or pivoting has happened, because it's calculated after everything else has been rendered.

You can't use Excel formulas or columns in filters.

The best way to get a feeling for it is to try a "Data source formula" and an "excel formula" in a demo report and then exporting it to Excel. You'll then notice that one is just the value, whereas the other will have the formula in there.
    • Related Articles

    • Report Designer Excel Style Formulas

      Created by: Gavin Hodge Modified on: Fri, 14 Feb, 2020 at 9:43 AM Most formula transformations can be done using value source formulas, but the report designer tool also supports using literal Excel style formulas.  While value source formulas are ...
    • Import an Excel file

      Created by: Johann Du Toit Modified on: Tue, 3 Dec, 2019 at 12:46 PM How to Import Your Own Excel Data and use it to create a Report Note: This functionality is only available on Professional and Enterprise plans.    Set up a Connector to Excel Click ...
    • No totals when viewing in Excel

      Created by: Gavin Hodge Modified on: Mon, 7 May, 2018 at 9:55 AM Spreadsheet reports are exported to Excel format when sent via email, and also when 'Save As Excel' is used while viewing a report. When you first open an Excel attachment it will show ...
    • Designing a conversion rate report

      Created by: Gavin Hodge Modified on: Tue, 18 Sep, 2018 at 2:06 PM A conversion rate report is one of the more advanced uses of the report designer. It requires use of many features of the designer including value formulas, Excel style formulas, ...
    • Report Designer Formula Reference

      There are two places where formulas can be used in the report designer tool: Row Filter formula for reports Value Source formula for cells Both have the same syntax, but are used in different ways. The general formula language is inspired by the ...