Two Versions of the Same Report – Detailed & Summary

Monday, June 5, 2017 - By Jon Gardner

....and how to make a custom menu to select which version

There are times when it is helpful to have two versions of the same report – a summary version and a detailed version. Aged Debtors or Aged Creditors reports are an example – where you might wish to show the total ageing by customer or vendor, but equally might wish to see the detail…..

In the version (left) the report is running in summary mode – with all the detailed report lines and columns hidden from view

In the version (right) the same report is running in detail mode. Note that not only are the individual Invoices and Credit Memo’s now visible on the lines, but so too are the column headings that relate to the report at detail level  ⇒

There are two useful pieces of functionality that can make this happen – the first is “Conditional Hide”, to show or hide the rows or columns that meet certain criteria – the second is “Non-Database Lookups”, the ability to manually specify lookup values that are not in the database.  We take a look at each of these in turn –

Non Database Lookups

This functionality allows you to create menu options in the Lookup Window based on values that are not in the database. Whilst still using the standard =NL(Lookup) functionality the syntax is different because there is no reference to a table in the database.  In place of the data table ID or Name you instead create an array containing the values to be displayed in the menu. The Field parameter then holds the column header for the lookup window.

On the left we see the Jet Function Wizard populated with the two non-database options for this report – Detailed and Summary – and we see the column header for the menu option – Report Type.

In this instance we show only two options, but it is possible to extend the option list significantly by following the same syntax as shown on the Table parameter.

It is also possible to have multiple columns in the Lookup Window and we will address that functionality in a later blog posting.

If not using the wizard to build the lookup then the detailed expression on the address bar should be keyed-in as below –

                   =NL(“Lookup”,{“Detailed”,”Summary”},”Report Type”)

Conditional Hide

Conditional Hide functionality is well documented elsewhere but in this example we are using it twice – once to hide the detail rows – and once to hide the detail columns.  Having created Detailed and Hide as menu options in the Lookup Window, the option selected updates the value cell on the Options sheet and in turn updates the filter value on the report itself.

In our example (left) we have placed the “Hide+?” expression in the two cells – Cell B1 to control the operation of the detail rows – and Cell A2 to control the operation of the detail columns.

In both Column B and Row A we have placed a number of =IF expressions to control the required rows and/or columns – whether to “Show” or “Hide” depending on the filter value in Cell E7.

Thus the =IF statements read as follows –  =IF($E$7=”Detailed”,”Show”,”Hide”)  If Cell E7 has the value “Detailed”, then “Show” the selected rows or columns………else “Hide” them

In closing….

The report shown is one of our basic “stock” reports which we are happy to make available.  If you would like a copy of the report file, or have any questions regarding the Non Database Menu Options or Conditional Hide functionality please feel free to contact us here – Contact us