How to create a Running Totals Report - A step by step guide

Products

• Cleverpath Reporter
• EUREKA:Reporter
• EUREKA:Strategy

Issue/Introduction

---

How can I include a column to show a running total for each detail row? This example shows how to create a running total for line item total by month, using the Global Sports sample database included with CleverPath Eureka Reporter.

 

Environment

Release:
Component: PORTAL

Resolution

It is assumed that the user has some basic knowledge about creating reports with CleverPath Eureka Reporter Designer.

1. Create a custom object called MON by doing the following:From Cleverpath Eureka Reporter Designer freeform design mode go to Insert/custom object,
   1. Name the custom MON.
   2. Set Type to numeric.
   3. Set Format to 99.
   4. Fill this for its value: month(order_date) This creates an object for the month of the Order_date column which is a date field.
   
   Note: Be sure to select order_date from the object directory rather than typing it. Order_ date is in the orders folder

 

• From the object directory/orders folder, drag in Line_Item_Amt column and place this next to the MON custom created in step 1.
  1. Set the aggregate function for Line_Item_Amt to sum.
  2. Set the sort order for your document to sort by the MON object ascending from query/sort order.

 

 

• Save the document as Running Total parent.iqr.
  

  A print preview of this report at this point will look like this:

   

   

• Create a second document. This document will be used as a child document and called Running total child.iqr.

 

 

• Create a user prompt object.

   

   

  1. Set the object label to "MON_PROMPT".
  2. Have it prompt for "Month?"
  3. Set the object Type to Numeric.
  4. Set format to 99.
  5. Place it in the page header area of your report document.

      

 

 

• Create the same MON custom object as you did on the parent document.

   

   

  1. Be sure to set the object type to Numeric.
  2. Set format to 99
  3. Place this in the detail area of your child report document.

 

• Add a Query filter where MON<= MON_PROMPT.

 

 

• Drag in Line_Item_Amt to the detail area of child report.
  1. Set the aggregate function for Line_Item_Amt to sum.

 

• Add a document footer area to your report by going to
         Insert/Area/Document Footer.

 

• Copy the Line_Item_Amt object from the detail area and paste it into the document footer area to create Sum(Sum):Line_Item_Amt2.

 

• Save this document as Running Total child .iqr and close it.
  

   

  At this point your child document design should look like this:

 

• Go back to your Running Total Parent.iqr and add a child document object to the detail area.
  
  1. Set the child document object to use the Running total child.iqr document you created.
  2. Pass the MON custom object to the MONTH_PROMPT user prompt on the child

      

      

      

   

• Hide the child document object.

 

• Create a child data object and specify the object to be the Sm:Line_Amt_Item2 on the document footer of the Running total child report and place the text object on the detail line of the parent.
  
  1. Size the object so that it can hold a large number.
     
     Your output should now look like this:

      

      

     In this case, the child document accepts MON, calculates the total payment for all months up to and including the month passed to it, and returns that value to the parent.NOTE: In this example the child is located in the detail area and as such will be processed one time per detail row (in this case 12 times)