Creating reports and dashboards | Stimulsoft community forum

Hi,

I am working on a report for invoicing. I have the following tables:
purchase_orders_receive (the combined invoice)
purchase_sub_orders_receive (each product on the invoice)

My shipping cost only exists in purchase_orders_receive but the product is a sum of the AMOUNT in the purchase_sub_orders_receive. I am using purchase_sub_orders_receive as my datasource.

I am grouping by the purchase_orders_receive in order to get the calculated details for each invoice.

The amount calculates correctly, but the shipping cost is being duplicated.

I am new to Stimulsoft but have done a lot in crystal reports over the years. In Crystal I would have a running total for each distinct record in purchase_orders_receive so it only counted the value once. I don't see this option in Stimulsoft.

Any help or examples are appreciated!



Thanks,
Darla

Hello Darla,

Please send us a sample report with test data which reproduces the issue for analysis.

Thank you.

I have been able to solve this problem and wanted to post my steps for anyone else who might benefit.

1. I created an SQL query for the table purchase_orders_receive. In the select I added SUM(AMOUNT) as PRODUCT. Amount is in the table purchase_sub_orders_receive and I joined them based on their unique keys.
2. I retrieved columns and it created my calculated field for PRODUCT in the purchase_orders_receive.
3. I was able to use the single table (purchase_sub_orders_receive) to create my report instead of using both tables in the report designer. The data I needed from the more detailed table became a field in the larger table.

I believe there should be an alternate solution to this problem. There should be some way to sum a field based on the unique value of that table. In that way I could created a SUM for the shipping cost (there was only 1 per many item records). Writing this formula MANY ways I was not able to find a method that produced the results I needed. I would still be interested in hearing of this option if anyone knows the answer.

Thanks!
Darla

Hello,

Thank you for the provided solution.

Thank you.