- SAP Managed Tags:
If you've ever created a report that has several groups, for example Customer, Year, & Product, you may notice that there may be missing groups or sub-groups. For example you may not see groups for all Products in a certain Year for a particular Customer. However, you need to have summary values for all Products for each Year for every Customer...even if the sub-total is just a zero. This issue may affect any charts or cross-tabs you have on your report.
Here's an example of the data you may see:
Here's an example of the data that you may want to see:
The reason why you don't have all of the sub-groups and all of the summaries in your report is that the data doesn't exist in your database. If Customer A has never purchased Product 1 in years 2012 or 2010, then your report isn't going to show those groups or sub-totals. If Customer A has never purchase Product 2, then a Customer level cross-tab will not have a column for Product 2.
If it's really important to you that each group show all sub-groups and at least a zero for a sub-total, then there are workarounds. One solution is to "roll up" data in a main report and passing this rolled up data to a subreport that brings back all sub-groups. This technique is covered in a blog here. The technique that is covered in this blog though is to add a subreport to your report based on a Command object that will create all needed records. You will need to be able to write your own SQL syntax in the Command object to use this technique.
1) First, start by opening up the sample report located here. Note that the report consists of a main report and a sub-report. The main report is used to bring back any Customers that we need to show. The subreport will be used to bring back all of the Customer Orders by Product and by Year.
2) Now go to the Database menu > Show SQL Query. You may be prompted to set the database location to view the query, so just set the database to any ODBC connection that you have. Or just view the main report's query below...
SELECT
`Customer`.`Customer Name`,
`Product`.`Product Name`,
`Orders`.`Order Amount`,
`Customer`.`Customer ID`,
`Customer`.`Country`, `Orders`.`Order Date`
FROM ((
`Customer` `Customer`
INNER JOIN `Orders` `Orders`
ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
)
INNER JOIN `Orders Detail` `Orders_Detail`
ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`
)
INNER JOIN `Product` `Product`
ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`
WHERE (
`Customer`.`Country`=’Canada’ OR
`Customer`.`Country`=’France’
)
ORDER BY
`Customer`.`Customer Name`3) Note that if you had the Xtreme sample database and pasted this query into a query analyzer against that database, you would see that a lot of Customers were missing a lot of products and dates.
4) Now right click on the Subreport and choose Edit Subreport. Go to the Database menu > Show SQL Query. The query below is what is used to ensure that every Customer has a line for every Product and year combination and a sub-total for Orders.
SELECT DISTINCT
`ProductA`.`Product Name`,
{fn YEAR(`OrdersA`.`Order Date`)} AS OrderYear,
(
SELECT
SUM(`Orders`.`Order Amount`)
FROM ((
`Customer` `Customer`
INNER JOIN `Orders` `Orders`
ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
)
INNER JOIN `Orders Detail` `Orders_Detail`
ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`
)
INNER JOIN `Product` `Product`
ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`
WHERE
`Customer`.`Customer ID` = {?CustomerID} AND
`Product`.`Product Name` = `ProductA`.`Product Name`AND
{fn YEAR(`Orders`.`Order Date`)} = {fn YEAR(`OrdersA`.`Order Date`)}
) AS sumOrderAmount
FROM (
`Orders` `OrdersA`
INNER JOIN `Orders Detail` `Orders_Detail`
ON `OrdersA`.`Order ID`=`Orders_Detail`.`Order ID`
)
INNER JOIN `Product` `ProductA`
ON `Orders_Detail`.`Product ID`=`ProductA`.`Product ID`So what is this SQL query actually doing?
1) The second line brings back all distinct Products.
2) The third line brings back all distinct Year values in combination with the Products above.
3) The fourth line to "AS sumOrderAmount) is a sub-query which brings back a value for Orders based on the Product and the Year combination. Using a main query plus a sub-query will ensure that a sub-total of 0 will be returned for situations where a Product did not have any Orders for a particular Year.
4) Note that the only filter on the data (for the Customer ID) is in the above-mentioned sub-query. We don't want to filter the main query as we want to ensure that a line of data is brought back for each Customer > Product > Year > Orders sub-total.
5) Another important thing to note is that there is an alias for both the Orders table (OrdersA) and the Product table (ProductA) in the main query. This is because those aliased tables are used in the subquery's WHERE clause to provide a filter based on the main query's rows. The Product Names and the Years from the main query thus become a filter for the subquery. If you're an avid Crystal Reports developer, think of this like having a linked subreport.
Please note that the above SQL syntax is based off of the Xtreme sample database and MS Access. The SQL syntax that you will need to write will vary depending on what your database client is. Please consult your database online help for syntax.
I hope that you find this technique helpful. If you are looking for any other solutions or workarounds for Crystal Reports, jamie.wiseman/content.
6 Comments
