The sample demonstrates how to create a table with group fields, set footers and create / apply Excel Charts with Aspose.Cells for Reporting Services.

 

To design and run the report, follow the steps below:

 

1. Make a directory named “design” and copy the file “Employee Sales Summary.rdl” from “DataSources” directory to design directory.
2. Open Excel
3. Click Open Report on the Aspose.Cells.Report.Designer toolbar to open “Employee Sales Summary.rdl” from “design” directory.
4. Click Report Parameter on the Aspose.Cells.Report.Designer toolbar, we can see that the report has three parameters.

 

5. Click Build DataSet on the Aspose.Cells.Report.Designer toolbar, select Root-> AdventureWorks-> EmpSalesDetail or Root-> AdventureWorks-> EmpSalesMonth, we can see that the dataset has three query parameters and all the query parameters have been mapped to report parameters respectively.

 

 

6. Create a sheet named “SalesChartData” and design the table header as shown in the following.

 

 

 

7. Select EmpSalesMonth from dataset list, select ProdCat from Fields list and insert it into template.

 

     

 

 

8. Repeat step 7 to insert another field Sales into the template.

 

9. Click Set Attribute on the Aspose.Cells.Report.Designer toolbar to set the attribute of the table report. Input “SalesMonth” as report name in the dialog box and click Next button

 

 

10. Select cells from C4 to D4 and consequently, C4:D4 will appear in Range text box in the dialog box. Click   Next button.

 

 

11. The table is a detail list report and has no group by fields. Click Next button.

 

 

12. Click Next button.

 

 

13. Click Finish button to complete this procedure.

 

 

14. Create a Sheet named “Sales” and make it active.
15. Select Insert->Chart from MS Excel menu to insert a chart into the template. Click Next button.

 

 

16. Click Series Tab.

 

 

17. Click Add button to add a series named Series1. Set the Values of Series1 to =SalesChartData!$C$4!$C$4 and set Category(X) axis labels to  =SalesChartData!$C$4!$C$4. Here, the cell C$4 refers to the ProcCat field in the table “SalesMonth” defined in the previous steps.

 

 

18. Click Add button to add a series named Series2. Set the Values of Series2 to =SalesChartData!$D$4!$D$4. Here, the cell D$4 refers to the Sales field in table “SalesMonth” defined in previous steps. Click Finish button to complete this procedure.

 

 

19. Double-Click the Y axis to set its format. Click Number and select Currency. Click OK button to complete.

 

 

 

20. Right-Click on the chart and select Chart options to set the chart title.

 

 

21. The chart will look like:

 

 

22. Click Insert Formula on the Aspose.Cells.Report.Designer toolbar to insert report parameters.

ReportYear:  &=Parameters!ReportYear.Value

ReportMonth: &=Parameters!ReportMonth.Value

EmpID      &=Parameters!EmpID.Label

To make the parameter text arranged well it is necessary to insert some columns and merge some cells.

 

 

23. Create a table as shown in the following.

 

 

24. Click Set Group on the Aspose.Cells.Report.Designer toolbar and select “SalesOrderNumber” from fields list to insert group tag into the template.

 

 

 

25. Design the group row as shown in the following.

 

 

26. Click Set Footer on the Aspose.Cells.Report.Designer toolbar to insert Footer tag into the template.

 

 

27. Design the footer row as shown in the following.

 

 

28. Click Insert Formula on the Aspose.Cells.Report.Designer toolbar to insert report parameters into the header area of the table.

ReportYear:  &=Parameters!ReportYear.Value

ReportMonth: &=Parameters!ReportMonth.Value

 

 

29. Click Set Attribute on the Aspose.Cells.Report.Designer toolbar to set the attribute of the table report. Input “SalesSummary” as report name in the dialog box and click Next button.

 

 

30. Set the range of the table. The range of the table includes one detail row, one group row and one footer row as shown in the following. Select the cells using mouse. The index of starting cell and the index of the ending cell will appear in the text box automatically. Click Next button to continue.

 

 

31. Select SalesOrderNumber and ProdCat as group by fields. Click Next button to continue.

 

 

32. Select SalesOrderNumber and ProdCat as order by fields. Click Next button to continue.

 

 

33. Click Finish button to complete the setup.