How to report on the sum of a multivalue attribute in a secondary D3 file

Introduction

D3 is a multivalue RDMS with the means to automatically sum multivalues in an attribute (column) of the primary file (table) being reported. It does not, however, work when summing a multivalued attibute in a secondary (or related) file.

This article describes a work-around which enables the ability to sum the multivalues of an attribute in a secondary file. A knowledge of the D3 RDMS, AQL, FlashBASIC and D3 Processing Codes is a requisite for this article.

Background

D3 is a multivalue RDMS which allows for the storage of multiple values in one attribute (column) of its files (tables). D3 provides "processing codes" which will sum the values of a multivalue field and report on the total. This works fine when reporting on a primary file, but not when reporting on an attribute in a "linked" or secondary file.  

The Problem

D3's processing codes allow you to sum the multivalues of an attribute using the "S" Algebraic processing code.

Here's an example:

The primary file is MEMBER with a Member ID, the secondary file is FAMLAW which is also keyed by the Member ID. File FAMLAW has a multivalue attribute 1 which may or may not have one or more values IF the member has FAMLAW item at all.

Creating and using the DICT item F.PTSRDN below sums the multivalues and displays the total:

:CT DICT FAMLAW SUMPTSRDN
    SUMPTSRDN  
001 A          
002 0          
003 SUM PTS RDN
004            
005            
006            
007 MD4           
008 AS(1)      
009 R          
010 10         

:LIST FAMLAW 200689      
Page   1     FAMLAW             
                                    
FAMLAW Pts..... POSS.... Undeduc
           Rdn      Months   Contrib
                    Reductn         
                                    
200689      47.0000  51.0000    8272
            48.0000  50.0000   11719
                                    
[405] 1 items listed out of 1 items.

:LIST FAMLAW 200689 SUMPTSRDN
Page   1     FAMLAW                 
                                        
FAMLAW SUM PTS RDN                  
                                        
200689          95.0000                  
                                        
[405] 1 items listed out of 1 items.    

Note the addition of the 2 values 47 & 48 yielding 95.

When we however, try to do the same thing using a translated DICT item from the MEMBER file, the sum only returns the 1st multivalue rather than the sum as the Translate processing code replaces the multivalue marks with spaces which foils the proper operation of the Sum processing code.

The Work-around

The work-around is to use a FlashBASIC CALL in the DICT item's correlative rather than an algebraic processing code and place all the logic and calculation into the called subroutine.

:CT DICT MEMBER SUMPTSRDN
​    SUMPTSRDN  
001 A          
002 0          
003 SUM PTS RDN
004            
005            
006            
007 MD4           
008 CALL SUM.FAMLAW.PTSRDN      
009 R          
010 10         

:CT SR SUM.FAMLAW.PTSRDN
001 SUBROUTINE SUM.FAMLAW.PTSRDN(VAL)
002 * dict sub to calculate the sum of MVs on a translated file
003 open "FAMLAW" to FAMLAW then
004   read rec from FAMLAW,val then
005     val = sum(rec<1>) ;* This pefroms the summation of the multivalues
006   end else
007     val = 0 ;* Return 0 if item not found
008   end
009 end else
010   val = 0 ;* Return 0 if error opening the file
011 end
012 return

Make sure the subroutine is compiled and cataloged so D3 can find and execute it. (I use the Update Processor to write code so I save and compile the code in one step by issuing the command Ctl-X, C which exits and compiles the item.)

Note: It may seem silly not setting val to 0 to start with instead of setting it in each else clause , but val is a 2-way variable, i.e. it holds the MEMBER ID on the way in and the value of the PTSRDN on the way out.

Using the code

The usage of the code is simplicity itself. Simply use the MEMBER DICT item SUMPTSRDN in an AQL sentence and the subroutine will be called to return the appropriate value.

:LIST MEMBER 200689 SUMPTSRDN
Page   1     MEMBER                   
                                      
MEMBER.... Sum Points                 
           Reduction                  
                                      
200689         95.0000                 
                                      
[405] 1 items listed out of 1 items.  

Points of Interest

This appears to be a bug since way back when with lots of developers requesting the D3 custodians to fix it with little luck, in fact no luck at all.

Another problem is with the D3 ODBC driver. It will not recognise and use the Output specification in A7 of the DICT item thus returning the internal representation (950000) instead of the converted value of 95. The work-around for this is to perform the output conversion in the subroutine itself rather than the DICT item.

Thanks to Mike Raffaelle of TData for putting me on this tip.

History

21/05/2015 V1.0 of the article.