0

NL("Table")-function returns no records, breaks PivotTable

Hannes Holst

January 04, 2018 12:13

Answered

Follow

Hi there,

Thanks for the forum and the community contribution. I was able to find a lot of solutions to my problems by just searching the forum.

Unfortunately, for my current problem I didn't find anything.
I have a NL("Table")-function which retrieves records from the G/L Entry-table using a date-filter on the current month. Based on the result, I've created a Pivot-Table which provides the data in a format which is supported by the built-in Charts of Excel.

Now, in the beginning of the month, the result of the NL("Table")-function is empty, because no postings for the current month exists yet. This leads to the problem, that the PivotTable deletes the column (because no data exists) automatically. It is necessary to add the missing column manually when data is available.

Now, this manual process is not acceptable for a productional environment.
Do you have an idea how I could either configure the NL("Table")-function to create always an entry (even when no records in the DB exists) or how I could configure the PivotTable that the column will not be deleted?

I played already with the settings of the PivotTable, but it didn't work.

Kind regards,
Hannes

• Facebook
• Twitter
• LinkedIn

4 comments

• 1
  
  Malcolm Johnson January 04, 2018 13:44

  Hi

   

  I would test the record count for the table and then adjust the filters to make sure I return data as shown

  Use the last date filter in your report C9

  

  
   

• 0
  
  Hannes Holst January 05, 2018 10:20

  Hi Mjohnson,

  Thanks for your contribution.
  I've implemented your suggestion. Because I link multiple tables into one PivotTable using an independent Date-Table, it's ok to have records from the past month.

  But, unfortunately, my problem still exists and it seems that it is not related to my first suspicion.
  The business did some work and now I have records in the G/L Entry-Table, but the PivotTable interprets the number-values for whatever reason as Text.

  Thanks for your help,
  Hannes

• 1
  
  Heather Rowe January 08, 2018 21:14

  You need to trick Excel into treating that date as a number - if you know that there will always be a date in that field, then you can simply need to multiply the date value by 1.

  If the problem is with your data table (in your database the field is a text field instead of a number field) you can fix this in your table report by adding a formula column (see the "Add Formula" at the bottom of the Table Builder)

  1. In the Table Builder, I am creating the formula to multiply a field called "Ending Date" by 1  =[@[Ending Date]]*1
  2. in my output file (before running it) the cell has the following: = NP("Formula","=[@[Ending Date]]*1")
  3. this should multiply the end date by 1, resulting in the same value and forcing Excel to create a numeric field.
  4. then you can point your pivot table at that field instead of the original date field

  if the problem is in JUST the Pivot Table (the date comes out as a number in your report table, but the pivot table won't treat it that way), then you have a blank field in your date column which is messing up the pivot table.  The solution there is to force SOME type of date into every field.  Pivot Tables require that series fields like date fields not have blanks in order to properly group and sometimes even to sort.

• 0
  
  Hannes Holst January 10, 2018 14:54

  Hi Heather,

  Thanks for the hint!
  It may be a sign that a limit is reached when it is necessary to "trick around" :-)

  I've read in other Excel-related forums that this issue can be solved also, by creating a new PivotTable using the "Recommended PivotTables"-function instead of inserting a blank PivotTable.

  Kind Regards,
  Hannes