Aspose.Cells.Report.Designer integrates with MS Query and uses MS Query as a tool for creating data
sources and queries. To create a new data source and query in Aspose.Cells.Report.Designer, follow the steps below:
- Open MS Excel
- Click Build DataSet
on the Aspose.Cells.Report.Designer toolbar.
- All the data sources and queries are listed in the dialog box. The node with an icon
is data source node and the node with an icon 
is data set node. Select the Root node of the tree and click Add button.
- In the dialog box, input DataSource name “SqlServer” and dataSet name “EmpsSalesDetail” and click Next button to continue.
- Aspose.Cells.Report.Designer will start MS Query. In MS Query Choose Data source dialog box, select New Data Source and click OK button. You may also select a existing data source.
- Input a data source name and select SQL Server from the drop-down list of database drivers. Click Connect.
- In the SQL Server Login dialog box, select the appropriate value for each item and Click OK button. For example, select server (local), Database AdventureWorks and Use Trusted Connection.
- In the dialog box, click OK button to continue.
- You will see the new data source appears in the Choose Data Source dialog. Select it and click Ok to open MS Query.
- Now to create a query in MS Query you may refer to MS Query Helper. In the following sample, we will create a query with parameters:
The SQL is as follows:
SELECT C.FirstName + ' ' + C.LastName AS Employee,
DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
PS.Name AS SubCat,
SUM(SOD.LineTotal) AS Sales,
SOH.SalesOrderNumber,
P.Name AS Product,
SUM(SOD.OrderQty) AS OrderQty,
SOD.UnitPrice,
PC.Name AS ProdCat
FROM Sales.SalesOrderHeader SOH ,
Sales.SalesOrderDetail SOD ,
Sales.SalesPerson SP,
HumanResources.Employee E,
Person.Contact C,
Production.Product P,
Production.ProductSubcategory PS ,
Production.ProductCategory PC
where SOH.SalesOrderID = SOD.SalesOrderID
and SOH.SalesPersonID = SP.SalesPersonID
and SP.SalesPersonID = E.EmployeeID
and E.ContactID = C.ContactID
and SOD.ProductID = P.ProductID
and P.ProductSubcategoryID = PS.ProductSubcategoryID
and PS.ProductCategoryID = PC.ProductCategoryID
and (DATEPART(Year, SOH.OrderDate) = ?)
AND (DATEPART(Month, SOH.OrderDate) = ?)
AND (SOH.SalesPersonID =?)
GROUP BY C.FirstName + ' ' + C.LastName,
DATEPART(Month, SOH.OrderDate), SOH.SalesOrderNumber,
P.Name, PS.Name, SOD.UnitPrice, PC.Name
The Query has three parameters: ReportYear, ReportMonth and EmpID.
- From Microsoft Query's File menu, select Return To Aspose.Cells.Report.Designer.
- Now, the data source and query created above are listed in the dialog box. Click the data source SqlServer to view its detailed information.
- Click the query EmpSalesDetails to view its detailed information.
Click SQL Tab to view the sql for the query.
Click Columns Tab to view the columns of the query.
Click Parameters Tab to view the parameters of the query.
