let's say I'm extracting data from MS Navision for Col A and B
Col A and B are filled. Then for column C I want formula =sum(A,b), can I do it while generating the jet report? Or must I do it outside of jet report?
Date
Votes
18 comments
-
Jet Reports Historic Posts Absolutely that can be done. My approach would be (presuming a single source table):
B1 - nl function returning the record key
C1 - formula for col1 referencing B1 without absolute references
D1 - formula for col2 referencing B1 without absolute references
E1 =C1+D1
When you run the report, the formules in E1 will propogate down performing the addition for each row. If you use absolutel references in D1 (i.e. $C$1 and $D$1) you will have problems if your formulas replicate down, for example using Rows function.
The exact method may depend on how you're getting your data from Navision. Can you supply any more information as to what you're doing? -
Jet Reports Historic Posts I just realized I got another problem. When I click on "test connection" it says I'm logged in as a viewer.
I must be in designer mode to do what I want above right?
So…who does that? The server? -
Jet Reports Historic Posts This is your Jet license, you must be a Designer in your flf file and it can be linked through your options.
If you are the person in the Jet License, check if the name matches your Office-user name. -
Jet Reports Historic Posts You can check what name Excel has you setup as by going into Tools, Options and selecting the 'General' tab. The name under 'user name' must match the designer name in the Jet license.
-
Jet Reports Historic Posts You can check what name Excel has you setup as by going into Tools, Options and selecting the 'General' tab. The name under 'user name' must match the designer name in the Jet license.
Excel 2007: "Office Button" -> "Excel Options" -> "Popular" -
Jet Reports Historic Posts Oh, sorry my instructions were for Excel 2003. Thanks Jan!
-
Jet Reports Historic Posts Oh, sorry my instructions were for Excel 2003. Thanks Jan!
np, don't know what's relevant here. Thought I'd post it, first time i had to look for them in 2007 took me about half an hour ;) -
Jet Reports Historic Posts Ok, got myself in designer mode
SOrry if my questions are really dumb…I'm completely new to this.
I went through some of the video clips(provided by the consultant) I noticed the need to click and drag. SO do I have to click and drag everytime when I create a new report? If I create a exact same new report, do I have to click and drag again?
Another thing I noticed, must it always start at Column B onwards? can I start at column A?
Must it always start at Row 7 onwards? can I start at row 1? -
Jet Reports Historic Posts I'm not quite sure what you mean by click and drag. Maybe somebody else has seen the videos and can comment. The thing to remember is Jet is basically some new Excel functions, and not much more. So if you are not too proficient with Excel, getting some training on that would help. The combination of Jet and Excel together means you can do some very cool things.
With regard to the cell placement, at a minimum you must start at B2, no higher or more left.
Its recommended you start mid way down and in, so you can use the top section for creation of static data, like a date range calculation for example. You can put the word 'hide' at the top of each column or start of each row to make it not visible once you run the report (i.e. hit the report button). This is great if you want to add two columns and show the total, without showing the individual totals (although you can do it in once cell, splitting it up is great for troubleshooting)
Best way to get started is to use the wizard to pull out something simple like a list of customers. Take a look at the cell formulas and see how they relate to what is displayed. The concepts of the NL functions Rows, columns and sheets are similar to loop concepts in traditional programming languages -
Jet Reports Historic Posts Ah. Thanks for the reply after I run the wizard, it shows me this(see attached file):
but the rest of the rows is empty! Am I doing it wrong…? -
Jet Reports Historic Posts Generally, you build the formulas in Design mode, and then press 'Report' to get data to populate. Once its run you can save it, or return to design mode and make changes.
What happens when you press the 'Report' button on the toolbar? -
Jet Reports Historic Posts it made no changes, it shows exactly the same
hmm…what does wizard do? does it generate the formulas for you in design mode? -
Jet Reports Historic Posts Yes, the wizard is a tool which can help you get started the first time you make a report. After a while, you'll probably find it easier to create them yoursellf.
Maybe you should look into the JetReports folder if there are some examples installed. So you get the basics running…
This is what you should do (in short):
Create a =NL function (start with Rows and work your way up from there)
Then create an =NF on that…
The "function wizard" (not report wizard) is quite handy, it gives you very good information on each step of the formula…
Keep us posted on how it works out!
PS: of course, when adding formulas, it should always been done in design mode
Regards,
Jan -
Jet Reports Historic Posts thanks. I more or less figured it out…
I thought wizards make my life easier…..looks like it's not so automated afterall… -
Jet Reports Historic Posts Like I said, it's ok if you use it for the first time, but you'll soon find it obsolete.
I still use the function wizard though… -
Jet Reports Historic Posts Peter - were you able to get Jet to pull in any information? When you press the 'Report' button, you should see a dialog box display whilst Jet pulls the data through. Does that happen for you? Also, after completion you will likely see Row 1 and Column A become hidden.
Once you get over the initial learning curve, you'll find Jet a great tool. I was initially apprehensive about Jet, and coming from a DBA background I do still find some aspects frustrating, however you can get some large volumns of data on a report without too many formulas.
The built in help is great for learning the NL and NF functions. -
Jet Reports Historic Posts yup, got it to work in design mode. Just don't understand why wizards doesn't work for me…
-
Jet Reports Historic Posts Can you list the series of steps you go through with the wizard. Should be something like this:
1. Select 'Wizard' from toolbar, Wizard is displayed.
2. Select 'default data source' and Customer table, press Next
3. Select No, Name and Address as fields, press next
4. Press next past all remaining screens, except the formatting options. Select the top 2 options and press Finish.
5. Report is built in design mode.
6. Press the 'Report' button. Dialog box is displayed.
7. List of customer is displayed.
At what point does this not work for you?
Please sign in to leave a comment.