Displaying a Sum from Data in Another Table

In this article, we will explore how to display a sum of data from another table. This is a common problem in data analysis and reporting, particularly when working with large datasets.

Understanding the Problem

The question states that we have two tables: an INVOICE SUMMARY table and an OUTSTANDING ORDERS table. Both tables contain the BUYER ID (customer unique reference). The user has successfully created a report to show performance metrics for each individual customer, including total sales, total costs, and total profit and profit percentage.

However, when trying to display information on outstanding orders for each customer, the data from the INVOICE SUMMARY table becomes incorrect. Specifically, any field brought in from the OUTSTANDING ORDERS table causes duplicated records within the INVOICE SUMMARY page, resulting in incorrect totals.

SQL Table Schema

To better understand the issue, let’s examine the SQL table schema used in Crystal Reports:

SELECT "vw_Invoice_Summary_01"."Invoice_Date", 
       "vw_Invoice_Summary_01"."Goods_Value", 
       "vw_Invoice_Summary_01"."Charges", 
       "vw_Invoice_Summary_01"."Total_Costs", 
       "vw_Invoice_Summary_01"."CostedCharges", 
       "vw_Invoice_Summary_01"."Tax_Base_Total", 
       "vw_Invoice_Summary_01"."Charges_Costs", 
       "vw_Invoice_Summary_01"."Buyer_ID", 
       "vw_Invoice_Summary_01"."Company_Name", 
       "vw_Invoice_Summary_01"."Invoice_Reference", 
       "ksl_vw_rep_CustomerOrdersOutstanding"."Quantity", 
       "ksl_vw_rep_CustomerOrdersOutstanding"."Quantity_Shipped"
FROM   "CS_Interex_Test_KSL"."dbo"."vw_Invoice_Summary_01" vw_Invoice_Summary_01
LEFT OUTER JOIN "CS_Interex_Test_KSL"."dbo"."ksl_vw_rep_CustomerOrdersOutstanding" ksl_vw_rep_CustomerOrdersOutstanding ON vw_Invoice_Summary_01."Buyer_ID" = ksl_vw_rep_CustomerOrdersOutstanding."Buyer_ID"
WHERE  ("vw_Invoice_Summary_01"."Invoice_Date" > '{ts '2024-01-01 00:00:00'} AND "vw_Invoice_Summary_01"."Invoice_Date" < '{ts '2024-02-01 00:00:00}')
ORDER BY vw_Invoice_Summary_01."Buyer_ID"

The Problem with Joining Tables

The issue arises when joining the two tables. The LEFT OUTER JOIN is used, which returns all records from the INVOICE SUMMARY table and matching records from the OUTSTANDING ORDERS table.

However, since the OUTSTANDING ORDERS table has a field called QUANTITY OUTSTANDING, this causes duplicated records within the INVOICE SUMMARY page. This is because Crystal Reports is not summarizing the results based on the BUYER ID in the second table, but instead is treating each record as a separate entity.

Solution Options

To resolve this issue, we need to consider alternative approaches:

Creating and Joining to a Database View

One solution is to create a database view that summarizes the data from Outstanding Orders by customer. This would involve creating a new table that combines the relevant fields from both tables and performing calculations to provide the desired summary.

For example:

CREATE VIEW vw_OutstandingOrdersByCustomer AS
SELECT Buyer_ID, SUM(Quantity) AS TotalQuantity
FROM ksl_vw_rep_CustomerOrdersOutstanding
GROUP BY Buyer_ID;

Then, in Crystal Reports, we can join this view with the INVOICE SUMMARY table and use the summarization feature to display the desired data.

Using a Correlated SQL Expression

Another option is to use a correlated SQL expression to bring in the summary data for each customer. This involves using a subquery within the main query to calculate the desired field.

For example:

SELECT "vw_Invoice_Summary_01"."Invoice_Date", 
       "vw_Invoice_Summary_01"."Goods_Value", 
       "vw_Invoice_Summary_01"."Charges", 
       "vw_Invoice_Summary_01"."Total_Costs", 
       "vw_Invoice_Summary_01"."CostedCharges", 
       "vw_Invoice_Summary_01"."Tax_Base_Total", 
       "vw_Invoice_Summary_01"."Charges_Costs", 
       "vw_Invoice_Summary_01"."Buyer_ID", 
       "vw_Invoice_Summary_01"."Company_Name", 
       "vw_Invoice_Summary_01"."Invoice_Reference", 
       (SELECT SUM(Quantity) FROM ksl_vw_rep_CustomerOrdersOutstanding WHERE Buyer_ID = vw_Invoice_Summary_01."Buyer_ID") AS TotalQuantity
FROM   "CS_Interex_Test_KSL"."dbo"."vw_Invoice_Summary_01" vw_Invoice_Summary_01
WHERE  ("vw_Invoice_Summary_01"."Invoice_Date" > '{ts '2024-01-01 00:00:00'} AND "vw_Invoice_Summary_01"."Invoice_Date" < '{ts '2024-02-01 00:00:00}')
ORDER BY vw_Invoice_Summary_01."Buyer_ID";

This approach requires careful consideration to avoid duplication and ensure accurate calculations.

Using a Subreport

A third option is to use a subreport. This involves creating a separate report that contains the desired data, which can then be embedded within the main report.

For example:

CREATE VIEW vw_OutstandingOrdersByCustomer AS
SELECT Buyer_ID, SUM(Quantity) AS TotalQuantity
FROM ksl_vw_rep_CustomerOrdersOutstanding
GROUP BY Buyer_ID;

Then, in Crystal Reports, we can create a subreport that references this view and uses the summarization feature to display the desired data.

Conclusion

In conclusion, displaying a sum from data in another table requires careful consideration of how to join the tables, perform calculations, and handle duplication. By using alternative approaches such as creating a database view, using a correlated SQL expression, or using a subreport, we can resolve this issue and provide accurate and reliable reports.

Additional Considerations

When working with large datasets, it’s essential to consider the performance implications of these approaches. For example, creating a database view may require significant resources and storage space, while using a correlated SQL expression may impact report performance.

Additionally, when working with multiple tables, it’s crucial to ensure that the joins are properly configured to avoid duplication and incorrect data.

By understanding these considerations and using the right approach for your specific use case, you can create accurate and reliable reports that provide valuable insights into your data.

Last modified on 2024-05-20