Formula
Formula
Enter any supported Excel formula or function in the textbox. Though Excel formulas start with an "=" equal sign, you do not need to add one in the provided textbox, it will be added automatically if not present.
A simple Excel formula would look something like "=F10*G10" where F and G are column positions and 10 the row number. Since we do not know the column position until the data is imported to Excel, we would write that formula by using Dictionoary names instead.
={QTY}*{SALESUNITPRICE} where QTY and SalesUnitPrice are columns defined in the Data View Manager. The curly braces '{' and '}' indicate a dictionary name and the report engine will find it's Excel column equivalent at runtime.
Column QTY and SALESUNITPRICE are automatically converted to H2 and I2 respectively and the formula =I2*H2 is added to column K.
Any Excel formula and functions can be used, ex: AVERAGE, MAX, ABS...
The folowing example uses the IF function to convert data on the sheet. The data returned by the database in column URGENT is a number 0 or 1. To the report reader a 0 or 1 is not as meaningful as a Yes or No. To convert 0 and 1 to No and Yes we create a new field and assign it the Excel IF function.
The formula reads: IF({URGENT}="0","No","Yes")
The sheet on the right shows the original URGENT column and the new UrgentYesNo column. At this point the original URGENT colum is only required by the IF function and can be "hidden" from the user.
Check "Hidden Column" to hide the column from the user. The data is still exported to Excel because it is required by the IF function.
Column M is used in the IF function but hidden from the user.
