The sample demonstrates how to use MS Excel PivotTable 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 “Company Sales.rdl” from DataSources folder to design directory.
- Open MS Excel.
- Click Open Report
on the Aspose.Cells.Report.Designer toolbar and open “Company Sales.rdl” from “design” directory.
- Create a sheet named “Company Sales Details”.
- Select Tools->Options from MS Excel menu and make Gridlines unchecked.
- Design the table header as shown in the following.
- Select “Sales” dataset from dataset list on the toolbar and select OrderYear field from Fields list as shown in the following.
- Repeat the step 6 to insert other fields as shown in the following.
- Click Set Attribute
on the Aspose.Cells.Report.Designer toolbar to set the attribute of the table report. Input “CompanySalesDetail” as report name in the dialog box and click Next button.
- Select cells from C7 to G7 and consequently C7:G7 will appear in Range text box in the dialog box. Click Next button.
- This table is detail list report and has no group by fields. Click Next button.
- Select order by fields i.e., OrderYear, OrderQtr, ProdCat and SubCat and then click Next button.
- Click Finish button to complete the report attribute setup.
- Select Insert->Name->Define from MS Excel menu to define a name named “sales”. The range of the name refers to the table CompanySalesDetail defined in the sheet “Company Sales Details”. Please note that the range includes the title headers of the table.
- Click OK button to complete the defining name process.
- Insert a sheet named “Company Sales PivotTable”.
- Select Format->Sheet->Background from Ms Excel menu and set the background of the sheet “Company Sales PivotTable”. The background “background.bmp” is stored in images directory of your Samples source folder.
- Design the header of PivotTable as shown in the following.
- Select the Cell of B5 and Select Data->PivotTable and PivotChart Report… from MS Excel menu to insert a PivotTable into the sheet “Company Sales PivotTable”. Check Microsoft Office Excel list or database as data source and PivotTable as report type and then click Next button.
- In the range text box, input “sales” defined in the previous step as data source of the PivotTable. Click Next button.
- Click Finish button to complete the procedure.
- In the dialog box, drag the field Year and Quarter from the PivotTable fields list to the Column Fields area of the PivotTable, drag the field Category and Sub-Category to the row fields area of the PivotTable.
- Drag the field Sales to the Data Items area of the PivotTable.
- Right-Click on “Count of Sales” field on the PivotTable and select Field Settings, modify the field “Summarize by” from “Count” to “Sum”.
- Click Number button and select “Currency”. Click OK button to complete the procedure.
- Right-Click the PivotTable and select Table Options. In the dialog box, make Refresh on open option checked and set “For empty cells,show: ” to 0 as shown in the following encircled in red color
- Setup the other presentation attributes of the PivotTable such as font, border color etc. according to your need.
- Now that we have completed all the design work and the report is now 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 “Company Sales” 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 “Company Sales” from Samples directory and click Preview button.
- Select “XLS-Excel Workbook via Aspose.Cells” and click OK button to export the report from ReportServer. The exported report will be opened with Excel.
