I am having difficulty making my report utilize the sorting commands I have entered. I have not had a problem with this in the past, but this is my first time using the NP("intersect",,) function and it doesn't seem to recognize the commands. I don't know if they're in the wrong place, I really have no idea. I am new to Jet Reports (two weeks in as a user), so excuse me if this seems rudimentary.
The data I want to sort on happens to all be coming from the same table in one database, but the information being returned per record is coming from 2 tables (still in the same database). What I have currently reads as follows:
F9 =NL("Filter","Employee","No.","Status",$C$2,"Class Code",$C$3,"Employee Location Code",$C$4)
G9 =NL("Filter","Employee Attribute","Employee No.","Integer Value",$C$5)
H9 =NL("Rows",NP("intersect",F9,G9))
I9 =NL(,"Employee",,"No.",H9,"+Employee Location Code",$C$4,"+Last Name","*","+First Name","*")
Any assistance is greatly appreciated.
Thanks,
Alex
Date
Votes
17 comments
-
Jet Reports Historic Posts Official comment A new build of Jet was released to resolve this dilemma; when adding a third sort command (while Link= is present) the second sort command was being ignored.
Rather than posting the link to the new version, I would suggest contacting a JCP to determine the suitability of a new download for individual scenarios.
Thank you again, Hughes, for all your assistance! -
Jet Reports Historic Posts Hi Alex,
I think you actually don't want to use NP(Intersect) in this situation since your sort won't be possible this way. Sorts have to be specified in the NL(Rows) formula and aren't possible with NP(Intersect). I think a better way to do this would be to use Link= instead. It should be faster than the intersect as well. So the result might look like this:=NL("Rows","Employee",,"Status",$C$2,"Class Code",$C$3,"+Employee Location Code",$C$4,"+Last Name","*","+First Name","*","Link=","Employee Attribute","Employee No.","=No.","Integer Value",$C$5)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
I actually had that exact function in place when I first started designing this report. However, I ran into an issue with it not giving the correct results. To explain: one of the filters you saw is "class code"–this field is an attribute and holds a history of all entries with their effective dates. I set the filter to only show records with "2" in this field, but when running the report it gave me a result that said "3". After looking into this particular employee (I operate in a public school district environment) I found that the result shown was the first entry in an employee's record that is currently a Class 2. It seems there is a miscommunication between the criteria set in the replicating statement and the function in the field to display the specific data.
Thoughts? -
Jet Reports Historic Posts Hmmm, that leaves some unanswered questions. I still think that my function (your original function) is going to be equivalent to the intersect function you were trying to create, so I think you'll have to solve the problem in a different way.
When you say that the Class Code field on the Employee table "holds a history", do you mean that this is a flow field to another table? Normally the Employee table only holds one record per employee so I'm guessing Class Code might be a flow field with Flow Filter Fields that affect it; is this right?
What function are you using to get the class code that is returning a 3 instead of a 2? Is it an NF function? If so, did you set all flow filter fields that are being set in the NL(Rows) function? Perhaps there's a flow filter that needs to be set in both places.
Regards,
Hughes -
Jet Reports Historic Posts =NL("First","Employee Attribute","Integer Value","Employee No.",H9,"Caption","Pay Class","Integer Value",$C$5)
Class code is held on the Employee Attribute table, not the regular Employee table. -
Jet Reports Historic Posts The filters being applied to the Rows replicator don't affect this function at all. Notice that if you think about this function in isolation, the only thing it is filteirng on from the rows replicator is the employee number. None of the other filters you've applied to the other function will apply to this one except in so far as they are used to isolate a specific list of employee numbers.
You say that "Class Code" is held on the Employee Attribute table, but I'm confused because in your previous function, you are filtering by a field called Class Code on the Employee table; is that a flow field which goes to the Employee Attribute table?
Btw, just to be sure, did you actually run this report by going to Jet -> Report or Jet -> Refresh? Results in design mode are not guaranteed to be correct because they are optimised for speed. I think you said you ran the report, but I just wanted to cover all bases just in case.
Regards,
Hughes -
Jet Reports Historic Posts I apologize; it has been a very long day and I think I need to take a look at this more tomorrow. When referring to class code I actually meant "Pay class"–both times.
So I think what you're getting at about the function I posted last is that it is written like a replicating statement, but in fact isn't; and therefore should be written differently.=NL("Last","Employee Attribute","Integer Value","Employee No.",H9,"Caption","Pay Class")The function above works, so I hope I understood you correctly. You'll notice I changed the "what" to LAST instead of first and also removed some frivolous filtering information. Unfortunately I don't know how to explain why this works, but for the task at hand–this yields the correct data.
Using Link= as we've discussed I have come up with a second quandary; if the filter on Pay class was set to show more than one at a time (e.g. "2|3"), I would also want to sort on this data. A practical sequence of sorting would be: Employee Location Code, Pay Class (Integer Value), Last Name, First Name. However, when writing the sort commands, Integer Value is not valid until after Link= is used in the function.
Is this not possible or would some restructuring of the function need to occur?
-Alex -
Jet Reports Historic Posts Hi Alex,
Hmmm, this is interesting. So that formula will get the Integer Values for the Employee sorted by Integer Value. Changing it from a first to a last just means that it will get the last integer value in that list, which may be what you want or it could be right by accident.
The sorting you're talking about is possible although it will slow your report down a bit. You'll have to change your rows formula to something like this:G9: ="+=NL(""Last"",""Employee Attribute"",""Integer Value"",""Employee No."",NF(,""No.""),""Caption"",""Pay Class"")" H9: =NL("Rows","Employee",,"Status",$C$2,"Class Code",$C$3,"+Employee Location Code",$C$4,$G$9,"*","+Last Name","*","+First Name","*","Link=","Employee Attribute","Employee No.","=No.","Integer Value",$C$5)
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts I understand the idea of what you have written below; however, it does not seem to register as a valid formula when pasted in excel. Is there something I have to change to make it work for my report specifically? I couldn't seem to figure it out on my own–I keep getting errors like "Empty filter not allowed" :cry:
-
Jet Reports Historic Posts Empty filter not allowed probably means that you got the filters slightly wrong. Notice in the formula I wrote, the value in G9 is a filter field with "*" as the filter. When I look at my formula, I think the filters are right, but I'm guessing when you modify it to put it into your own workbook, you've changed the filters around just a little bit and eliminated something like a "*" that is required, which would cause an error like "empty filter not allowed." If you open the formula in the Jfx and check the filter fields and filters, it may make it more obvious.
Regards,
Hughes -
Jet Reports Historic Posts I did happen to miss a comma; so the empty filter error is gone, but when I run the report–the sorting does not work. I have copied and pasted right out of design mode below:
E9 ="+=NL(""Last"",""Employee Attribute"",""Integer Value"",""Employee No."",NF(,""No.""),""Caption"",""Pay Class"")" F9 =NL("Rows","Employee",,"Status",$C$2,"Class Code",$C$3,"+Employee Location Code",$C$4,$E$9,"*","+Last Name","*","+First Name","*","Link=","Employee Attribute","Employee No.","=No.","Integer Value",$C$5)When I run the report, the first unhidden column (E) still shows the formula as written above on each replicated line. This indicates to me that there is something wrong with the syntax, but I would not know what to adjust.
I cannot express how much being unable to figure this out frustrates me, but I must reiterate my being a novice to Jet functions and thank you for your patience in assisting me. -
Jet Reports Historic Posts Hi,
Actually, the value in E9 will always be the same. It's just a string. You don't even need it on the same row as the replicator in this case. You could put it in a hidden cell at the top of your report and reference it from your replicator.
I have no idea why the sort isn't working. It should be sorting first by Employee Location Code, then by the Integer Value from the Employee Attribute table, then the Last name, then the First Name. All of these sorts should be in ascending order. Can you attach the actual Excel file? Maybe there is something else going on here unrelated to this specific function that I'm not aware of.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Sorry for delay, some other things took precedence in the office. –I have attached the file as requested.
Thanks,
Alex -
Jet Reports Historic Posts Alex,
So when you run this, what does the data look like? Specifically, what are the first few values for Employee Location Code and Pay Class that aren't being sorted in the correct order?
Regards,
Hughes -
Jet Reports Historic Posts It's quite curious: the buildings are displayed together, but not alphabetically (e.g. A,A,A,A,C,C,C,C,C,B,B,B,B,B). Also, the last names seems to sorted A-Z within each building, but Pay class is neglected. With the particular filters I used to be able your question, I was not able to tell if First name was being sorted on or not.
:?: :?: :?: :!: :?: -
Jet Reports Historic Posts I was hoping you would tell me what the actual values are, not just how it's appearing to sort. Anyway, probably the best thing to do at this point is open a ticket with Jet Reports support. Someone can actually look at the report with you and hopefully figure out what is going on. It's not an easy thing to solve on a forum since I can't actually see and run your report and try things.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
I greatly appreciate all your efforts in trying to help me; I will submit a ticket to JET Support and post the resolution when it is found.
Thank you,
Alex
Please sign in to leave a comment.