Computing Sum of multiple 1:n-Tables 1

[mpegjunkie]

Hi,

I have one master table and three detail tables linked 1:n to the same master field, respectively.

Master Table:
Customer
----------
Cust_ID
...

Detail Table 1:
OutstandingItems
-----------------
Cust_ID
AmountDue
ActFlag
...

Detail Table 2:
ValutaItems
---------------
Cust_ID
AmountNet
ActFlag
...

Detail Table 3:
IM_DailyVolume
--------------
Cust_ID
DailyRevenue
ActFlag
...

The aim is to get the GrandTotal of (Sum(D1.AmountDue) + Sum(D2.AmountNet) + Sum(D2.DailyRevenue)) grouped by Cust_ID and having the ActFlag set to NULL.

I'm using CR 9.0 Pro and get the Data via ODBC from a Centura SQLBase Database.

I made it already with three subreports and shared variables, but these variables only compute when displaying the subreports. I want the result as a single value, not displaying all the details. But when suppressing details, the "WhenPrintingRecords" is not executed.

Please give me a hint how to do this.

Thanks in advance,
mpegjunkie
----------------

 

[FVTrainer]

Well, the prefered method, I think, would be to compile the data in the format you want in a stored procedure. I have no idea, however, if Centura SQLBase supports stored procedures, if you have experience writing them, or what the exact syntax would be for Centura SQLBase.

So, if you're game for trying it with a Crystal Command object, we can take a small step in that direction. As a test, do the following:

Start a new report and in the Database Expert, select the ODBC datasource you are using for your report. After you connect to it, you should see an "Add Command" option. Double-click on it and see if the following syntax will be accepted:

SELECT CustID, Sum(D1.Amount) FROM D1 WHERE ActFlag IS NULL GROUP BY CustID

If that syntax works and you are able to base a report on it, we can look at expanding it to encompass the rest of your data collection.

 

[synapsevampire]

I think that FVT meant:

SELECT CustID, Sum(D1.Amount) FROM Master, D1 WHERE Master.cust_id = D1.cust_id and d1.ActFlag IS NULL
GROUP BY CustID

You can do this within Crystal as well, though I would use a View or SP (this seems to state that at least Views can be created in SQLBase:

http://www.cemml.colostate.edu/files/trmch8.pdf

).

In Crystal you might add the tables, join by the cust_id (left outer the Master to the Children in each case), and set the record selection formula to each tables actflag being null, and create a sum of each tables values, ultimately adding them up.

Another approach might be using subreports.

-k

 

[FVTrainer]

Actually my thoughts on this are to use a Command object (although a stored proc or view would be preferred) that would use my original query as one of 3 derived tables to be joined against the master table. But I want to see if the syntax works against the Centura database first before going through the exercise of creating the rather complex command object

 

[FVTrainer]

Here is the full text of the command object I was thinking of. This syntax (assuming I haven't made any typos) would work in SQL Server. Obviously, that isn't your database, so there could be variations in syntax and/or what is supported. It won't be great in terms of performance if you have a large dataset, but it makes the report a very simple exercise, as the result is simply one record per CustID, with AmountDue, AmountNet, and DailyRevenue fields.

SELECT M.CustID, D1.AmountDue, D2.AmountNet, D3.DailyRevenue
FROM Master M LEFT JOIN
(SELECT CustID, Sum(AmountDue) AS AmountDue FROM [Detail Table 1] WHERE ActFlag IS NULL GROUP BY CustID) D1
ON M.CustID = D1.CustID
LEFT JOIN
(SELECT CustID, Sum(AmountNet) AS AmountNet FROM [Detail Table 2] WHERE ActFlag IS NULL GROUP BY CustID) D2
ON M.CustID = D2.CustID
LEFT JOIN
(SELECT CustID, Sum(DailyRevenue) AS DailyRevenue FROM [Detail Table 3] WHERE ActFlag IS NULL GROUP BY CustID) D3
ON M.CustID = D3.CustID

 

[mpegjunkie]

Hi FVT,

thanks for your great tip. It works with "Add Command.." and the huge challenge shrinks to a simple report...

In the meantime, I tried the same approach in combination with Access, i.e. I made three queries, each summing and grouping one detail table, and linked these queries to my report. So I have the same easy report, one field for each Sum().

In addition, the Access-based queries has better performance than the Command Object-based solution. (Access: 15 sec, Command: 2 min)

I am not allowed to add views or stored procedures to the Centura database, because it's the base for a third-party product, so I will use the Command Object solution, because it's not very elegant to use Access as a separate tool for basic filtering that CR could do also.
And I don't mind about the performance, the Reports run via Crystal Enterprise Pro, once every night.

Thanks,
mpegjunkie