Skip to Content
Technical Articles

Show All Sub-Groups and a Summary for Every Group

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:

/wp-content/uploads/2012/07/blog1_117996.png

Here’s an example of the data that you may want to see:

/wp-content/uploads/2012/07/blog2_117997.png

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, please see my blog here.

6 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    I've tried your solution but got SQL query error where could not add table PS. I don't know where it went wrong with this SQL query. Could you help me, please? I'm sorry because I'm not familiar with SQL.

    Below is query that I’ve been tried:

    SELECT DISTINCT

    PS.DOC_COMP AS Status,
    USER.DEPT_CD AS Department,
    (SELECT
    SUM(PS.DOC_COMP)
    FROM
    ((PS
    INNER JOIN PSCR ON PSCR.CRFNO = PS.CRFNO)
    LEFT JOIN USER ON USER.USER_ID = PSCR.REQID)) AS sumStatus
    FROM
    (PS

    INNER JOIN PSCRON PSCR.CRFNO = PS.CRFNO)
    LEFT JOIN USER  ON USER.USER_ID = PSCR.REQID
    GROUP BY
    PS.DOC_COMP
    ORDER BY
    PS.DOC_DATE

    The report requirement is similar to your propose technique where the report should display the count of status by department but some of data doesn't exist in the database which means null.

    Thank you.

    • hi athi a, and thanks for looking at one of my blog posts.

      one thing i do see is that you've got a group by clause (that has only 1 of the dimensional values) in your syntax and this "group by" isn't really needed and may cause issues / errors...i.e. you've already got a "select distinct" in there and what you're trying to accomplish is just to bring back all of the unique combinations of Status and Department.  after those 2 dimensions your measure is the sumStatus which is taken care of with the subquery.

      try getting rid of the group by stuff and see if that resolves the issue. if not, try running your query in whatever database tool you use to test queries in. then let us know what the exact error is and what line and character it's pointing to.

      regards, -jamie

      • Hi Jamie,

        Thanks for your quick response! You're absolutely right. I've tried to remove the "Group By" and "Order By", the solution you give perfectly works. The error has been solved, but I'm still not getting the exact results for my report where all of the status isn't going to display in my report. The expected result should be like this, the status should be displayed, although it’s contained null value:

        Dept 1    Dept 2     Dept 3
        Status_1      1              2             1
        Status_2      0              2             1
        Status_3      1              0             3
        Status_4     0              0             0

        In the report, I've used date parameter field to filter the data by date. Is it because of this filter the status with null value not displayed at all? Is there any way to solve this problem?

        Please advice.

        Thanks.

        Regards,

        Athi

         

         

         

  • hi Athi, certainly any filters in the main query may negate the dimension values. think of it like this...the main query brings back 2 dimensions and those 2 dimensions form the rows and columns of a table. when you apply a filter then you risk eliminating some of those row and column header values.

    the subquery is the measure in the table...this is where you want to apply any filters as the filters will change the values in the summary cells. have a look at above where i reformatted the code so that it looks a lot better than before. the only WHERE statement is in the subquery. this means that the distinct values for year and product don't get filtered and only the subquery / order amounts are filtered.

    after you move your date filter to your subquery then this should be ok.

    regards, -jamie

    • Hi Jamie,

      Thank you. As per your solution, I've managed to show up all the status and department values in my report. But there's another problem when I've applied parameter and filter in the subquery, the values aren't displaying according to the prompt selection (Application ID). The report should display the count of status by department for certain application ID and date.

      Query use in subquery as per below:

      WHERE
      PS.APPID = '{?AppID}' AND 
      PS.DOC_DATE between TO_DATE('07/01/2019', 'mm/dd/yyyy') AND 
      TO_DATE('07/31/2019', 'mm/dd/yyyy')

      I'm sorry because I don't have strong knowledge in SQL and need to ask for your help. I wish I didn't bother you with that. Please advise.

      Thank you in advance.

      Regards,

      Athi

      • hey Athi, i don't know what your entire sql statement is at this point but don't forget that you need to provide an ALIAS to your tables and then reference that ALIAS in your subquery WHERE clause. otherwise your subquery will return the same value over and over.

         

        to see what i mean about the alias, have a look at my example above (the 2nd set of syntax that uses the subquery) and you'll see an alias assigned to the Orders table and later on for the product table as well. i.e. i've provided an alias of OrdersA to the Orders table so that it can be referenced in the subquery and then be treated as a unique table.

        `Orders` `OrdersA`

         

        in the subquery the aliased product and orders table are then referenced in the where clause and hence this provides a filter on the subquery for each Year & Product row of the main query. note that i'm setting Product.ProductName equal to ProductA.ProductName and the same sort of thing for the year.

        			`Product`.`Product Name` = `ProductA`.`Product Name`AND 
        			{fn YEAR(`Orders`.`Order Date`)} = {fn YEAR(`OrdersA`.`Order Date`)

         

        if you're used to crystal and using subreports as opposed to sql, think of the subquery being the subreport. when you don't link your subreport it stands on its own and there is no filter based on the main report...i.e. each instance of that subreport would have the same records. however, when you link your subreport, you can then create a record selection formula / filter based on a main report field linked to a subreport field. then each instance of the subreport has a record set which is filtered based on the values in the main report.

         

        in your case based on what you've provided above, you'll want to alias the Status and the Department fields in your main query and reference the status ID and department ID in the subquery's where clause.

         

        so in the end your subquery should contain any filters that we talked about earlier (i.e. filtering in the subquery as opposed ot the main query) as well as the filters based on the aliased_table.field combinations from the main query.

         

        the alias part i should have gone into at least a bit of detail in the blog as that might be a point of confusion and i apologize for that...i'll amend this in a bit.

         

        regards, -jamie