We have been playing around with SQL Server Connector and accessing SQL Server database directly without any problem. But, today I got a problem with NF function. With Dynamic Nav, we could use this formula:
NL("SUM","One table","=NF(,""field1"")-NF(,""field2"")","filterfield1","field1",,,,,,,)
This formula does not work with SQL. Is this feature not available with SQL Server connector?
Date
Votes
17 comments
-
Jet Reports Historic Posts Official comment Hi!
If the filter doesn't actually depend on a value being set by the user, then there is no reason to make it a %FILTER% in your SQL query. You can just put that filter in the query. So something like this:=NL(,"SQL=SELECT COUNT([Item No_]) as skuCount FROM [Phys_ Inventory Ledger Entry] WHERE %FILTER1% AND %FILTER2% AND %FILTER3% AND ABS([Quantity]) > 0.05*[Qty_ (Calculated)]", "skuCount","1D=[Posting Date]",IR$3,"2S=[Location Code]",$B14,"3S=[item no_]",$IX$1)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts That is correct. Calculated fields, filter fields, and filters are only available in Nav connectors. This is because in Nav connectors we actually have to spin through the records as we get them from the database using C/FRONT. In a universal connector, we just construct the appropriate SQL query and it returns the records to us; we never spin through records the way we do in Nav and therefore we don't execute calculated fields/filters. You can achieve what you are trying to achieve through the SQL= feature of the universal connector if you know how to create SQL queries. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes,
I will try that SQL= feature. Fyi, I work quite a lot with SQL/SQL Reporting Services. -
Jet Reports Historic Posts Good deal! You should be right at home with the SQL= feature then. There's just a little to learn with putting the fields you want to return or filter by into the SQL query and how you reference those fields in the rest of the Jet function, but otherwise it's pretty straight forward. Let me know if you can't figure it out from the documentation.
Regards,
Hughes -
Jet Reports Historic Posts I have been using the "SQL=" feature quite alot lately, but I still have problem with the following:
On Nav, we could use this feature to filter a field with another field:
NL("count","Phys. Inventory Ledger Entry","item no.","quantity","=concatenate("">"",0.05*NF(,""Qty. (Calculated)""))","Posting Date",IS$3 . . . .
With SQL Query, we could use the following:
Select COUNT([Item No_] FROM [Phys. Inventory Ledger Entry]
WHERE ABS([Quantity]) > 0.05*[Qty_ (Calculated)] –(I use ABS since I convert all neg. adj to negative values)
How to put the following Jet SQL to work (The last where clause - FILTER 4):
NL(,"SQL=SELECT COUNT([Item No_]) as skuCount FROM [Phys_ Inventory Ledger Entry] WHERE %FILTER1% AND %FILTER2% AND %FILTER3% AND %FILTER4%", "skuCount","1D=[Posting Date]",IR$3,"2S=[Location Code]",$B14,"3S=[item no_]",$IX$1,"4N=ABS([Quantity])","0.05*[Qty_ (Calculated)]")
Thank you. -
Jet Reports Historic Posts I was so stupid and did not think about that. It's just plain SQL. It works like a charm.
Thank you very much Hughes, you are a life saver. -
Jet Reports Historic Posts With Navision we could use NL on a cell as the key and draw another fields based on that key cell with NF functions.
Can we do the same with NL("rows","SQL= . . . . . . ? -
Jet Reports Historic Posts Yes definitely! In the function above, we are returning a specific field (skyCount) in the Field parameter of the NL function. If the SQL statement returns more than 1 field, you can specify multiple fields in the field parameter using an Excel array and then use NF function to retrieve those fields. Here is an example from Northwind:
=NL("Rows","SQL=SELECT * FROM CUSTOMERS",{"ContactName","Address","City"}) =NF(C3,"ContactName") =NF(C3,"Address") =NF(C3,"City")
Does that make sense to you?
Regards,
Hughes -
Jet Reports Historic Posts You're right, it works. Thank you.
-
Jet Reports Historic Posts Here is the key query:
CELL B7 =nl("rows","SQL= select * from [master summary report] where [document type] = 0 and [type] = 2 and [unit price] < [unit cost] and [quantity] <> 0 and %filter1% and %filter2% order by [sell-to customer no_] ",{"salesperson name","salesperson code","document no_","sell-to customer no_","item category code","product group code","no_","description","Unit of Measure Code","item status code","quantity","unit price","unit cost"},"1D=[posting date]",np("datefilter",$C$2,$D$2),"2S=[item category code]",$C$3)
it gives me all fields I need except this one:
CELL H7 = NF(B7,"unit of measure code").
Before I run the report, I got "EACH" as the value, which is the actual value (either each or PR).
But we I run it, only the first row has "EACH" as the value and the rest gives me "#VALUE!".
Why is that? Thank you. -
Jet Reports Historic Posts When you debug one of the NF functions which has #VALUE!, what message do you get? Also, if you look at the results of the NL(Rows) function on the lines which are returning #VALUE!, what do they look like? Do they have the "Unit of Measure" field values in them?
Regards,
Hughes -
Jet Reports Historic Posts Here is the message I got when I clicked debug on the cell with error:
"An unexpected error has occured: Exception from HRESULT:0x800A03EC"
the complete message is attached. -
Jet Reports Historic Posts Hmmm, generally speaking when you get an unexpected exception error like this, you should create a support ticket for it. It's really not something the community can diagnose so it should go through Jet support where someone can decode it and figure out if there is an issue to fix with the software. That being said, looking at this, it could be an issue that has been fixed in the latest version of Jet. I seem to recall a problem like this with SQL= and a performance optimization done for NF replication. So I would first try upgrading to the latest version of Jet Reports and if that doesn't fix the issue, create a support ticket for it with Jet support.
Regards,
Hughes -
Jet Reports Historic Posts Have installed the latest version and the error is still there.
I submitted a ticket for this problem and will post a message when I get the answer. -
Jet Reports Historic Posts I have another problem with SQL connector:
How we deal with the Nav date-formula (4D, 3w ETC.) on some fields such as "Lead time calculation", "reorder cycle"?.
What I see on the SQL table is the integer part + box icon.
Thank you. -
Jet Reports Historic Posts Hmmm, unfortunately problems like this and flow fields of course are why Jet doesn't generally recommend using a universal connector to connect to the back end SQL database for NAV. The box icon indicates some sort of an unprintable character. It must just be how NAV is storing the data for those field types. I'm not sure what to tell you other than to use a standard NAV connector to retrieve those fields.
Regards,
Hughes -
Jet Reports Historic Posts For now, I use SQL datasource for all other fields, and use Nav datasource to get the special fields.
Hopefully it will be in the future version. Thank you.
Please sign in to leave a comment.