Aspose.Cells template is an Excel workbook that contains Smart Markers. Smart Markers acts as data placeholder for report items and will be replaced with the corresponding data at rendering time. There are five types of smart markers, summarized as below. All the markers can be inserted into template by Aspose.Cells.Report.Designer. Users may also edit the markers manually.
The following is a summary of the smart markers.
The format of data markers is “&=DataSetName.FieldName”, e.g., &=SalesDetail.sales, where “SalesDetail” is the name of a DataSet or Query and “sales” is the name of one of its fields. At rendering time data markers will be replaced with the values of dataset provided by Reporting Services.
- Reporting Services Formulas Markers
The format of Reporting Services formulas markers is “&=expression”, e.g., &=sum(SalesDetail.sales)/100. The expression consists of function, fields of dataset, operator and so on. At rendering time Reporting Services formulas markers will be replaced with computed values.
- Reporting Services Global Variables Markers
The format of Reporting Services global variables markers is “&=Globals!VariableName”, e.g., &=Globals!ExecutionTime, where “ExecutionTime” is the name of a global variable. Global variables markers will be replaced with the value of global variables at rendering time.
- Reporting Services Parameters Markers
A report parameter has two attributes: value and label. Consequently, Parameters Markers have two formats: “&=Parameters!ParamName.Value” and “&=Parameters!ParamName.Label”, which indicate the name of parameter and the label of parameter respectively. At rendering time, Parameters Markers will be replaced with the parameter values entered by the user.
In order to make calculations on the rows that are inserted, you must use dynamic formulas. Dynamic formulas allow you to insert MS Excel's formulas into cells even when a formula must reference rows that will be inserted during the export process. And they can be repeated for each inserted row or use only with the cells where the data markers are placed for them.
The format of Dynamic Formulas is “&=&=RepeatDynamicFormula”.
Dynamic Formulas allow following additional options:
{r} - Current row number
{2}, {-1} - Offset to current row number
The following illustrates a repeating dynamic formula and the resulting Excel worksheet.
