In this example, we will modify the customers' report generated in the previous example. We will add a SalesAmount column for each customer. We will apply sorting on the report and we will add a group for NumberChildrenAtHome. We will also add a subtotal for each group item. Perform the following steps:
In the Customers report, go to the Report Data pane and change the query of DataSet1 to the following script:
SELECT FirstName, LastName, BirthDate, MaritalStatus, Gender, TotalChildren, NumberChildrenAtHome, EnglishEducation, NumberCarsOwned ,sum(FactInternetSales.SalesAmount) as SalesAmount FROM DimCustomer left outer join FactInternetSales on DimCustomer.CustomerKey=FactInternetSales.CustomerKey group by FirstName, LastName, BirthDate, MaritalStatus, Gender, TotalChildren, NumberChildrenAtHome, EnglishEducation, NumberCarsOwned
Now you will see the SalesAmount column under DataSet1. Drag-and-drop this column...