0

GL function won't accept reference [RESOLVED]

Jet Reports Historic Posts

October 05, 2012 14:41

None

Follow

Hi,

I have a GL function doesn’t function. It stops functioning when I try to reference a dimension :

=-GL("Balance",$A9,FYStart,PerEnd,,EntityFilter,,,,,,,,"True")
where EntityFilter is a named range, a cell with the value UK999.

It also fails to work if I change it to :
=-GL("Balance",$A9,FYStart,PerEnd,,g$5,,,,,,,,"True")
where g$5 is a cell with the value UK999.

However, it does work if I enter UK999 in directly. It works just fine!
=-GL("Balance",$A9,FYStart,PerEnd,,UK999,,,,,,,,"True")

I have noted that in this last version I have UK999, not “UK999” or ‘UK999’. This hasn’t helped me crack it.

I haven’t found anything in Online Jet Help that gives any further clue.

I can’t help feeling I am missing something very obvious! Any ideas please?

[Using a cloud based Excel Office Professional Plus 2010]

Thanks

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts October 10, 2012 12:10

  Still stumped by this. Don't seem to have attracted any responses either. Should I have chosen a snappier title?
  
  It seems very odd, the way it accepts a reference in one part of the function, but will only take a hard text (and without "quotes"!) in another part of the function. I can hard code everywhere, but it isn't user friendly and it also prevents a query for blanks, uncoded items.
  
  Anyone feeling inspired?

• 0
  
  Jet Reports Historic Posts October 10, 2012 16:44

  Hi -
  
  What is returned by this function? =-GL("Balance",$A9,FYStart,PerEnd,,EntityFilter,,,,,,,,"True")
  
  (note: column A and row 1 are reserved for Jet Express keywords, please adjust your reports so that data, functions, and formulas do not appear in either column A or row 1).
  
  Is UK999 a valid value for Global Dimension 1?
  
  -HP

• 0
  
  Jet Reports Historic Posts October 11, 2012 08:28

  Hi HP,
  
  Thanks for the response.
  
  The answers to your questions :
  1) It returns the balance on the account referenced in $A9 for the period from dates FYStart to PerEnd, filtered by the dimension given in the Global Dimension1 place (which is the Region dimension. In this case A9 contains the Total Revenue code from the chart of accounts.
  
  2) Yes, UK999 is a valid value, and it returns the number I expect if I hard code it into the formula (as I say in the original post).
  
  Point taken about Column A and Row 1.
  
  I can create a report that hard codes the countries in every formula if I want to. I can't create a much more user friendly report which uses a relative reference or a single cell named range like the examples in the original post - the formula just returns zero.
  
  One extra piece of information. If G5 contains the text UK999 the formula returns zero. If it is empty it returns the unfiltered total value posted to that account.
  
  It's weird!

• 0
  
  Jet Reports Historic Posts October 12, 2012 10:15

  Hi,
  
  I've solved it. It was blindingly obvious. The function will not report dimensions unless an Analysis View is specified. Doh
  
  It returned the "right" answer because I was testing with a limited dataset, which threw me. Expanding the dataset put me back on the right track. I was nuts to think that the function could be interpreting UK999 as "UK999"!!! I fooled myself into thinking it was plausible.
  
  Thanks HP. Just knowing someone else had given it some thought inspired me to think about it differently.