The sample demonstrates how to create a report that shows the details of single business entity and how to use Dynamic Formulas with Aspose.Cells for Reporting Services.
To design and run the report, follow the steps below:
- Make a directory named “design” and copy the file “Sales Order Detail.rdl” from DataSources folder to design directory
- Open MS Excel.
- Click Open Report
on the Aspose.Cells.Report.Designer toolbar and open “Sales Order Detail.rdl” from design directory.
- Create a sheet named “Sales Order Detail”.
- Select Tools->Options from MS Excel menu and make Gridlines unchecked.
- Select Insert->Picture->From File from MS Excel menu and insert a picture into template. The picture “logo.bmp” is already stored in the images folder of Samples source directory as shown in the following.
- Insert the static text label into the template.
- Select “SalesOrder” from dataset list. The dataset “SalesOrder” saves the details of the single order. Select “Store” from fields list and insert it into the template.
- Repeat the step 8 and insert other fields into the template as shown in the following.
- Insert the parameter “SalesOrderNumber” into the template. First select a cell and then click Insert Formula
on the Aspose.Cells.Report.Designer toolbar.
- Select “Parameters” in the left panel, Double-Click “SalesOrderNumber” in the parameters list and click Insert button to insert it into the template.
- Design the header of the order detail table as shown in the following.
- Select “SalesOrderDetail” from dataset list and insert the fields into the template as shown in the following.
- Insert the function “&=RowNumber("SalesOrderDetail")” into the first field.
- Insert a dynamic formula “&=&=D{r}*S{r}” into the seventh field. In the sample, the D{r} means the current row number of the field “Qty”, the S{r} means the current row number of the field of “Unit Price” and the formula will be Subtotal = Qty * Unit Price.
- Click Set Footer on the Aspose.Cells.Report.Designer toolbar to insert two footer tags. Design the two footer rows: one row for total discount and another row for total as shown in the following.
- Now that we have completed all the design work and now the report is ready for publishing. Click Publish
button on the Aspose.Cells.Report.Designer toolbar. Type the URL of ReportServer and click Refresh button to list the folders. Select “Samples” folder, input the report name “Sales Order Detail” and Click Publish button to publish the report.
- Click View
button on the Aspose.Cells.Report.Designer toolbar. Type the URL of ReportServer and click Refresh button to list the folders. Select the report “Sales Order Detail” from Samples directory and click Preview button.
- Select “XLS-Excel Workbook via Aspose.Cells”, input “SO50750” for the parameter “Sales Order Number” and click OK button to export the report from ReportServer. The exported report will be opened with MS Excel.
