Search found 135 matches

by rmw
Tue May 31, 2016 3:18 am
Forum: How do I?
Topic: A matrix of sheets
Replies: 7
Views: 852

Re: A matrix of sheets

Thx Heather for the suggestion.
I already tried that approach. See the attached report.
But it seems, as Teresa states in her post, that NL("Sheets") is evaluated before anything else.

I guess it is impossible for NAV connections

rmw
by rmw
Thu May 19, 2016 7:53 am
Forum: How do I?
Topic: Jet Scheduler Error
Replies: 4
Views: 2372

Re: Jet Scheduler Error

Thanks!

rmw
by rmw
Thu May 19, 2016 1:34 am
Forum: How do I?
Topic: Jet Scheduler Error
Replies: 4
Views: 2372

Re: Jet Scheduler Error

It is probably me, but I can't find the mentioned articles on the new KB site....
Can someone point me in the right direction?

Greatly appreciated.

rmw
by rmw
Mon May 16, 2016 11:00 am
Forum: How do I?
Topic: A matrix of sheets
Replies: 7
Views: 852

Re: A matrix of sheets

Thx for the reply. It is a NAV datasource, so it is probably a no go.... I tried to use a worksheet range as source of NL("Sheets") but that only seem to work for a fixed range, not a generated one. NL("Sheets",'Data!$C$5:$C$6) works ok, but if you use other Jet functions to make it grow,by insertin...
by rmw
Fri May 13, 2016 1:29 am
Forum: How do I?
Topic: A matrix of sheets
Replies: 7
Views: 852

A matrix of sheets

I want to create a sales report per country per sales type. So far so good, but for every combination I want a sheet. Report options give me the ability to select one or more countries and one or more sales types. Both are from a different table in NAV. On running the report after I selected the opt...
by rmw
Fri Apr 22, 2016 3:13 am
Forum: How do I?
Topic: Linking other data sources to Table Builder?
Replies: 2
Views: 469

Re: Linking other data sources to Table Builder?

To avoid the breaking of direct references, you can use the excel function INDIRECT. If you use =INDIRECT("Report!$E$7") instead of =Report!$E$7 as a formula, it will work after refresh too. But you have to be aware that excel will NOT adjust those formula's if you insert or delete rows or columns!!...
by rmw
Fri Apr 22, 2016 12:18 am
Forum: How do I?
Topic: The curious case of an an empty date
Replies: 0
Views: 421

The curious case of an an empty date

I have something strange going on. I made a report to show a list of items with no ending date entered. I used the double single quote ('') as a filter value and got what I wanted. Nothing spectacular there :) Then I shared the report with someone else and she isn't able to get the same results. The...
by rmw
Tue Mar 15, 2016 12:48 pm
Forum: How do I?
Topic: Formula Returns #Value - Debug returns the correct information?
Replies: 2
Views: 676

Re: Formula Returns #Value - Debug returns the correct information?

I've seen it happen to.
For no apparent reason and without a solution.
Mostly when there is an NF involved.
I am under the impression it has to do with memory...
But that is just a guess.

rmw
by rmw
Fri Jan 08, 2016 1:05 pm
Forum: How do I?
Topic: Filter field with Data from a different field for each entry
Replies: 6
Views: 873

Re: Filter field with Data from a different field for each entry

The comparison needs to be done on every record. The only way to do that is using an Excel formula to pass the comparison as a whole to the database and filter on the outcome, which is true or false. =NL("Count";"Table";;"=NF(;""From Date"")<=NF(;""To Date"")";TRUE) Be aware that you will skip the u...
by rmw
Wed Oct 28, 2015 7:04 am
Forum: How do I?
Topic: Max Value as Criteria??
Replies: 4
Views: 585

Re: Max Value as Criteria??

Yes, that is what I assumed in my previous answer. Again: the shipment has 3 posts: May 1st, May 20th and June 3rd. Running your report with DATEA = May 1st and DATEB = May 31st, you do NOT want to find it, because the MAX date (which is June 3rd) is not between the two dates. When using DATEA = Jun...
by rmw
Wed Oct 28, 2015 6:50 am
Forum: How do I?
Topic: Summarize with Pivot Table
Replies: 6
Views: 2467

Re: Summarize with Pivot Table

to deal with the blank row - I have two options (because it will be in my pivot as the report expands). Option 1 - for reports that are set to be run on a scheduler and hidden rows and columns deleted - I just add a HIDE to row 8, and no changes are needed. Option 2 - for reports that will not be s...
by rmw
Wed Oct 28, 2015 6:03 am
Forum: How do I?
Topic: Customize Existing Navision Report
Replies: 1
Views: 379

Re: Customize Existing Navision Report

Jet Reports pulls data out of NAV into Excel.
So you can not modify existing NAV reports, you will have to create new ones within Excel.

rmw
by rmw
Wed Oct 28, 2015 6:00 am
Forum: How do I?
Topic: execute NL immediately
Replies: 1
Views: 279

Re: execute NL immediately

There used to be a Jet Reports keyword for that in cell A1, but that is deprecated.
You have to use VB to start the Jet Reports Refresh macro in the AutoOpen method.
But that brings along the annoying security messages of Excel workbooks with macro's.

In short, no, you can not.

rmw
by rmw
Wed Oct 28, 2015 5:54 am
Forum: How do I?
Topic: Max Value as Criteria??
Replies: 4
Views: 585

Re: Max Value as Criteria??

To be sure I understand you correctly... You have 3 posts on, say, No. 4300. Posting date on those is May 1st, May 20th and June 3rd. Running your report with DATEA = May 1st and DATEB = May 31st, you do NOT want to find No. 4300, because the MAX date of the posts is not between the two dates? Assum...
by rmw
Wed Oct 28, 2015 5:31 am
Forum: How do I?
Topic: Design vs. Report Mode
Replies: 1
Views: 381

Re: Design vs. Report Mode

In short: yes.
Look here for some explanation about user data in JetReports

HTH

ruw
by rmw
Thu Sep 24, 2015 4:47 am
Forum: How do I?
Topic: NL Sheets showing just date from datetime field [SOLVED]
Replies: 2
Views: 416

Re: NL Sheets showing just date from datetime field

You might try to isolate the date part with an excel function.

Code: Select all

NL("Sheets","<Table>","=TEXT(NF(,""<DateTimeField>""),""dd-mm-yyyy"")","<DateTimeField>","01-01-15..06-01-15")
Where <names> need to be replaced by real names.
No syntax check done...

rmw
by rmw
Fri Sep 18, 2015 9:52 am
Forum: How do I?
Topic: Extra Excel Workbook showing
Replies: 3
Views: 566

Re: Extra Excel Workbook showing

It happens at different moments. When I refresh a report that is saved in report mode. When I start the Jfx on a new worksheet. When I change a formula with an embedded NF(,"field") function inside an NL() in the formula bar of excel. And at other moments, quite random, actually. I have two identica...
by rmw
Fri Sep 18, 2015 6:54 am
Forum: How do I?
Topic: Extra Excel Workbook showing
Replies: 3
Views: 566

Extra Excel Workbook showing

Using the latest Jet Essentials (15.1) on the latest Excel (2013) in a shared environment (Citrix or Windows server 2012 R2 with RDP) it happens that when I enter Jet formula's a new Excel workbook pops open with 1 formula in cell A1. Closing that workbook makes Jet unavailable, minimizing it let me...
by rmw
Wed Sep 09, 2015 2:21 am
Forum: How do I?
Topic: Jet Reports on MAC
Replies: 5
Views: 2097

Re: Jet Reports on MAC

Running Windows in Parallels on OSX means you have two machines in one.
Office for OSX won't interfere with Office for Windows.
You just need to install Office in the Windows environment you are running in Parallels on OSX.

Sorry for the confusion :)

rmw
by rmw
Tue Sep 08, 2015 11:49 am
Forum: How do I?
Topic: Nested NP function within an NL function
Replies: 2
Views: 539

Re: Nested NP function within an NL function

=NL(NP("Companies","AC Ltd"),"Lease",$D$4:$E$4,"Headers=",$D$3:$E$3,"TableName=","Lease","IncludeDuplicates=","True") Three assumptions: - You are trying to build an excel table object from the table 'Lease' - The fields in the table are in $D$4:$E$4 - NP("Companies","AC Ltd") returns just ONE comp...
by rmw
Tue Sep 08, 2015 11:41 am
Forum: How do I?
Topic: Jet Reports on MAC
Replies: 5
Views: 2097

Re: Jet Reports on MAC

Does Jet reports work on a Mac running windows with Parallels in OS X?
Yes, it does!
Only on the windows you are running with parallels, of course :)

Jet and OSX are a definite no-go.

rmw
by rmw
Wed Aug 12, 2015 12:27 pm
Forum: How do I?
Topic: Hide or Show Lines
Replies: 3
Views: 899

Re: Hide or Show Lines

You need some excel tricks to make that happen. Say, you have 1 line from table A (row 6), 1 line from table B (row 7) and 3 lines from table C (rows 8,9 and 10) On the rows from table C you make a calculation to know if the row should be shown or not in column E E8 = IF(<condition to show>,1,0) E9 ...
by rmw
Tue Aug 04, 2015 2:42 pm
Forum: How Do I?
Topic: NL Rows - various companies one table
Replies: 1
Views: 4046

Re: NL Rows - various companies one table

Try to use NL(Filter)

E5 =NL("Filter","X Table","Field","Company=","DEF Limited","Active Groups","Filter")
E6 =NL("Filter","X Table","Field","Company=","ABC Inc.","Active Groups","Filter")
E7 =NL("Rows",NP("Union";E5,E6))

HTH

rmw
by rmw
Wed Jul 08, 2015 12:21 am
Forum: How do I?
Topic: SQL-formula on our Payroll-db VALUE error [SOLVED]
Replies: 4
Views: 842

Re: SQL-formula on our Payroll-db VALUE error [SOLVED]

Could the following syntax be the problem?: FROM cmb cmb_k_srt_contr ,csa,con,wnr,wg1 WHERE Normally databases that you pull data from are comma separated. Is that space used for renaming the database for further use? I would suggest you use the AS clause to do that FROM cmb AS cmb_k_srt_contr,csa,...
by rmw
Wed Jul 08, 2015 12:09 am
Forum: How do I?
Topic: Consolidate
Replies: 1
Views: 420

Re: Consolidate

In cell D4: =NL("Filter","Employee","EmpID")
In cell D5: =NL("Filter","Dept","EmpID")
In cell D6: =NL("Rows",NP("Union",D4,D5))

HTH

rmw

Go to advanced search