Using the dBase filter in the properties of a Leadsheet, and other trial balance-based Automatic Documents, allows you to display specific accounts or balances.

Refer to CaseWare Working Papers help for more information on dBase filters.

For example, we can develop a dBase filter that will allow you to display Material or Immaterial accounts.

Determining the Trial Balance part of the dBase filter.

We need to acquire the following date:

1. The Cell function we require. In this case we want the Absolute Number function (ABS), to ensure both Debits and Credits are treated the same.
   
   

2. The Database we want to acquire the information from. CaseWare Working Papers has up to 22 databases. In this case we want the Trial Balance (AM) database to gather the appropriate information.
   
   

3. The ACT Function (ACT) returns the current account balance for the specified account.within the trial balance database that we require.
   
   

4. The Value of the balance we require. In CaseWare Working Papers we can determine if it is the current year balance including adjusting entries, that we are after, or whether it is the unadjusted prior year balance. In this case we want the Balance report (normal + reclassifying) (BR).
   
   Note: 

• For Consolidations, we should consider the Balance consolidated (normal+ reclassifying + eliminating) (BC).
  
  

• For Client’s closing balance, we should consider the Balance unadjusted (no adjustments) (BU).
  
  

5. The Calculation operator to determine how the filter will work. Pending on whether we want to see Material (>) or whether we want to see Non-Material (<) will determine the operator used in this case.
   

Formula: ABS(ACT("BR",AM->AC_NO))

Determining the Materiality part of the dBase filter.

The table below represents the different dBase filter pending on which materiality we’d like to utilise. 

Materiality dBase Filters

So, what is the final dBase filter to ensure my Leadsheets list either material / non-Material accounts?

Firstly we need to ask a couple of questions in order to set up the correct dBase filter.

cvdata('WPG','PMATERIAL','VALUE')

1. Which balance value are we considering comparing to materiality?
   
   

• For CaseWare’s Final balance, we should consider Balance report (normal + reclassifying) (BR).
  
  ABS(ACT("BR",AM->AC_NO))
  
  

• For Client’s closing balance, we should consider the Balance unadjusted (no adjustments) (BU).
  
  ABS(ACT("BU",AM->AC_NO))
  
  

• For Consolidations, we should consider the Balance consolidated (normal+ reclassifying + eliminating) (BC).
  
  ABS(ACT("BC",AM->AC_NO))
  
  In this example, we will use Balance report (normal + reclassifying) (BR).
  
  

2. Are we wanting our Leadsheets to display Material or Non-Material balances?
   
   

• Material: ABS(ACT("BR",AM->AC_NO))>
  
  

• Non-Material: ABS(ACT("BR",AM->AC_NO))<
  
  In this example, we will use Material: ABS(ACT("BR",AM->AC_NO))>
  

3. Using the Materiality dBase Filters table above, which Materiality Type do we want to filter the account balances by?
   
   In this example, we will use either of the following, depending on whether we are using Final or Preliminary data:
   
   

• Performance Preliminary: ABS(ACT("BR",AM->AC_NO))>cvdata('WPG','PMATERIAL','VALUE')
  
  

• Performance Final:
  ABS(ACT("BR",AM->AC_NO))>cvdata('WPG','PMATERIAL','FINAL')
  
  

4. Is there a way I can automate which materiality filter is used based on the stage of thw data - whether Preliminary / Final?
   
   The Final / Period-end trial balance has now been imported checkbox will determine which materiality is being used for the engagement.
   
   
   
   To ensure we automate which materiality will be used as the base in the Leadsheet, we need to include a CASE statement. This statement will refer to the whether or not the key checkbox above has been pressed. If it has not, the result is 0 and therefore, the dBase Filter will drive the formula segment: cvdata('WPG','PMATERIAL','VALUE')
   
   However if it is selected, the result will be 1 and therefore, the dBase Filter will drive the formula segment: cvdata('WPG','PMATERIAL','FINAL')
   
   The CASE Statement and the final formula for the dBase Filter will be:

• ABS(ACT("BR",AM->AC_NO))>CASE(cvdata('FINTBCWANZ')=0,cvdata('WPG','PMATERIAL','VALUE'),cvdata('WPG','PMATERIAL','FINAL'))