Hello I am brand new to Jet Reports and Microsoft Nav (Serenic). Our training for Jet was less than stellar. I am getting basic numbers but it's not reading date filters, etc.
I tried using some of the predesigns on the jet reports player but those aren't working either…..
any guidance, tips, tricks, help???
thanks
Date
Votes
9 comments
-
Jet Reports Historic Posts Hi,
Can you give us some examples of what's not working for you? What date filters, etc. are you trying that aren't working? Hard to know what to explain without seeing what you are doing.
Regards,
Hughes -
Jet Reports Historic Posts Sure. For example I have tried balance and that adds the the actual and the budget number together. In one I tried Amount & Budget and in that example I get the correct amounts right now but how to get the it to pull just YTD information through a particular date isn't working.
Thanks -
Jet Reports Historic Posts You still haven't actually shows me what formulas you're using that you're having problems with or attached any reports. From your description I don't know what tables and fields you are dealing with. It sounds like you just need to add filters to your NL and/or NF functions, or possible you need to use a different field in your NF functions. I'm not really sure without knowing more exactly what the functions in your report look like.
Regards,
Hughes -
Jet Reports Historic Posts Here is a template of what I am trying to start with.
I am a little lost on where to start building the formulas. I can pull basic data dumps then I get all lost. :oops: -
Jet Reports Historic Posts Once again, I can't help you without knowing what your Jet formulas are. You stripped all the Jet formulas out of this workbook, so I don't know what those look like. Are you using GL functions? NL(Sum) functions?
Regards,
Hughes -
Jet Reports Historic Posts That's because I really didn't have any…:-( Here is what I tried…using the GL function, however it is pulling the actual + the budget journal as the balance and the when I copy it over into the budget column and filter budget instead of balance I get 0.
Sorry I am literally brand new and the Jet trainer we got showed us customers and data dumps. Not financials and we got no manuals. Just trying to figure it out.
Thanks for the help! -
Jet Reports Historic Posts Okay so I've done a little asking and I think I understand your problem. The GL function doesn't work correctly for Serenic because, unlike standard NAV, Serenic keeps budgets in the G/L Entry table along with actuals instead of in the G/L Budget Entry table. So in order to get financials in Serenic, you have to use NL(Sum) functions directly on the G/L Entry table and filter by the Transaction Type field to get just the actuals, just the budgets, etc.
So the function you need might look something like this:=NL("Sum","G/L Entry","Amount","Posting Date","<>C&"&NP("DateFilter",$H$6,$I$6),"Transaction Type","Actual")
So I'm not actually positive that "Actual" is the right value for Transaction Type because I don't have a Serenic database to test this on. You can use the Jet function wizard (Jfx) and do a lookup to see what the values for that field are. The other thing you might wonder about is the <>C in front of the date filter. This is because closing dates in NAV are prefixed with a C so the <>C& part of the filter tells Jet to exclude closing dates. If you remove that bit, closing dates will be included. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
You are a genius. I made one modification and now I just have to replicate and be able to add a filter for department:-) I am attaching for your thoughts.
You are a lifesaver!!!!! -
Jet Reports Historic Posts Well assuming department is one of your advanced dimensions, you should be able to just filter for it in the G/L Entry table in your NL(Sum) functions (it shows up in standard NAV as Department Code). So that filter should be easy enough to add. You can replicate the G/L Account numbers and names from the G/L Account table pretty easily using something like this (assuming you have a filter for G/L Account No. in cell H8):
D14: =NL("Rows","G/L Account",,"G/L Account No.",$H$8) E14: =NF(D14,"No.") G14: =NF(D14,"Name")
Does this help?
Regards,
Hughes
Please sign in to leave a comment.