Active form incorrectly shows zero values in web
Posted: Wed Sep 04, 2013 4:18 pm
Hi All,
I have created an active form (which isn't supressing zeroes), I have two columns: Jnl and GL.
This is an input sheet so I can't simply show the correct GL account for each Journal where values already exist - I have to look up the GL account using vlookup from a lookup table (long story, this is an interim solution for the client). The Lookup table is another active form of a config cube.
The layout that I need to have is two rows per Journal entry (one for debit and one for credit) so it'll look like this:
Column1 Column 3
Jnl1 GL123
Jnl1 GL234
Jnl2 GL345
Jnl2 GL456
etc...
The problem is I have to look up the GL account so I'm using 'dummy' GL elements (in this case #GL) so in the proper/original active form that was created from the cube view it's actually
Column 1 Column 2 Column 3
Jnl1 #GL GL123
Jnl1 #GL GL234
Jnl2 #GL GL345
Jnl2 #GL GL456
Column 3 is a vlookup of the GL account based on the Journal and using MOD of the row number - the result is every even row is "Journal no - CR" and every odd row is "Journal no - DR". With this I can lookup the correct GL account for each journal. The #GL just allows the form to populate two rows for every journal entry. Sorry, hope this isn't too confusing but it's why I'm having to try to create this active from in the way I am.
End result is my DBRW formulas are referencing Column 1 for Journal and column 3 for GL account. The form shows zeroes when there are actually values in the cube.
Work arounds tried:
CTRL+SHIFT+ALT+F9 works in excel
Turning on auto calc works in excel
changing the DBRW's to DBR (and leaving one DBRW column somewhere - as a dummy and hiding it) works in excel.
Problem is that I'm using the web - so none of these work arounds work for me in the web.
I think the issue here is that the DBRW formula would prefer me to lookup adjacent columns from the original part of the active form but I am having to build the third columns elements using excel formulas and the DBRW formula executes before the excel formula so it isn't reading the correct intersection (needs to have the excel formula return the correct GL account and then lookup the value at that intersection).
I see others have struggled with this issue as well. I've tried inserting two active forms into one sheet and pointing the DBRW formula to the one form for Journal no and the sheet next door for GL (they line up row for row as I need them to) but this doesn't work - is the DBRW formula insistent on reading from adjacent columns?
Does anyone have any ideas on how to make this work in the web?
Thanks.
I have created an active form (which isn't supressing zeroes), I have two columns: Jnl and GL.
This is an input sheet so I can't simply show the correct GL account for each Journal where values already exist - I have to look up the GL account using vlookup from a lookup table (long story, this is an interim solution for the client). The Lookup table is another active form of a config cube.
The layout that I need to have is two rows per Journal entry (one for debit and one for credit) so it'll look like this:
Column1 Column 3
Jnl1 GL123
Jnl1 GL234
Jnl2 GL345
Jnl2 GL456
etc...
The problem is I have to look up the GL account so I'm using 'dummy' GL elements (in this case #GL) so in the proper/original active form that was created from the cube view it's actually
Column 1 Column 2 Column 3
Jnl1 #GL GL123
Jnl1 #GL GL234
Jnl2 #GL GL345
Jnl2 #GL GL456
Column 3 is a vlookup of the GL account based on the Journal and using MOD of the row number - the result is every even row is "Journal no - CR" and every odd row is "Journal no - DR". With this I can lookup the correct GL account for each journal. The #GL just allows the form to populate two rows for every journal entry. Sorry, hope this isn't too confusing but it's why I'm having to try to create this active from in the way I am.
End result is my DBRW formulas are referencing Column 1 for Journal and column 3 for GL account. The form shows zeroes when there are actually values in the cube.
Work arounds tried:
CTRL+SHIFT+ALT+F9 works in excel
Turning on auto calc works in excel
changing the DBRW's to DBR (and leaving one DBRW column somewhere - as a dummy and hiding it) works in excel.
Problem is that I'm using the web - so none of these work arounds work for me in the web.
I think the issue here is that the DBRW formula would prefer me to lookup adjacent columns from the original part of the active form but I am having to build the third columns elements using excel formulas and the DBRW formula executes before the excel formula so it isn't reading the correct intersection (needs to have the excel formula return the correct GL account and then lookup the value at that intersection).
I see others have struggled with this issue as well. I've tried inserting two active forms into one sheet and pointing the DBRW formula to the one form for Journal no and the sheet next door for GL (they line up row for row as I need them to) but this doesn't work - is the DBRW formula insistent on reading from adjacent columns?
Does anyone have any ideas on how to make this work in the web?
Thanks.