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

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.