Hi all! So I'm working for a NAV partner and I've been tasked to create some reports that we can give to our Express clients to use. I was told to try and figure this out using the Express version, but after 2 weeks of searching forums I'm thinking the Essentials version is going to be needed.
Task:
I'm using the Table Builder in Express to build table(s) that I can then display using Pivot Tables. My main issue is that I have a Run Time field that is a decimal type. But that value can either represent "Seconds/Minutes/Hours" (Unit of Measure field). But I need to display my Run Time in hours. So line 1 may say Run Time is 1200 and Unit of Measure for that is "Minutes". I need this to show up as 5 "Hours" instead. (Also have an issue not being able to link two tables that have a many-to-many relationship but that issue is not as important as I can use two worksheets with separate tables in the end)
I tried:
Inserting a Calculated Column on the Pivot Table. NO GOOD. My goal here was to put an "IF" statement in the formula field saying if the Unit of Measure = "Minutes", then divide the Run Time by 60, but Excel treats Text values within the Calculated Column formula as a number and since it can't identify "Minutes" as being a number it treats it as a numeric zero. This does not happen if you put the same formula in a cell on a worksheet though. {http://www.clearify.com/wiki/view/326/calculated-fields (scroll to bottom for proof lol)} That's an Excel problem.
So I then thought I could insert a Column on the Jet Report, and it works after you input the column with the formula. BUT of course as soon as you refresh the data, the inserted column is deleted. So this would entail having the client insert a column with formula every time they refresh their data, obviously NO GOOD.
Now I assume but haven't tried yet (Haven't enabled Essentials yet although I do have the license) using the "Add Formula" that is available with Table Builder in Essentials, and maybe doing a formula there with "IF" statement logic saying if it's "Minutes" do a calculation before spitting out the table so I would essentially have all the values already converted to "Hours". But I'm not even sure that would work and I'm still trying to see if I can figure something out with Express.
I want to try:
Type Casting the Unit of Measure field to an int or decimal, so that "Seconds=1, Minutes=2, Hours=3". Then I could use the Pivot Table Calculated Column method I mention above. But my issue here is:
1. I don't know how to insert Excel formulas into a Jet Report formula. There is literally no documentation or training provided with Jet Reports on the format to do this. For example NL("Table", "Table Name", INT("Field Name)) could convert "Field Name" to an INT maybe? But I get an obvious #VALUE error. And how would I put that into the main NL function? At this point I'd almost say that after 2 weeks of trying to figure this out by Googling everything I can think of that I'm almost an Excel Pivot Table expert, but I still haven't learned anything new about Jet Reports. And while there is lots of tidbits on these Jet forums, there is still no guidance on how to use and structure Jet Reports functions, or how to combine them with Excel functions.
The online Jet Reports videos are very very basic and so are obviously no help either.
I've included a copy of the excel sheet with the table that uses Table Builder. Cell D11 says "Table" so when i examine that, there is an NL function, cool. But let's say I want to type cast the "Time Entry Unit of Measure" column to an INT (or some other numeric), How can I possibly insert an excel function into the NL function in cell D11. This is what I mean by no training tools on any of this. Or how does one understand cell E8 (the Links formula)? There is a little "infinity" symbol and then a bunch of ,"","",""..etc at the end. Makes no sense.
Anyways, If anyone has any resources where a guy could learn this stuff, let me know. I've searched Google, Youtube, spent two weeks trying to learn this (while learning all about Excel at the same time), and I feel stonewalled. Even if and when I upgrade to Essentials, I'll still be left in the dark trying to figure out use Jet Report functions. I guess I'm looking for all the actual theory behind this stuff.
Thanks in advance!
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi.
I believe you are correct and you will need the full version of essentials to get this to work. The Express version doesn't give you access to any of the Jet functions nor does it allow you to store formulas in the table builder.
Sorry, but the answer I think is to upgrade.
I believe as a partner you get access to time with the Jet Reports team. If you are new and working for a partner you should follow up with your Jet representative to see if you have time on the books already to work with Jet team.
Hope this helps.
Jason -
Jet Reports Historic Posts Hi,
I'm not sure I understand the part of your question where" Excel treats Text values within the Calculated Column formula as a number".
See my attached file where I created a table from the Cust. Ledger Entry table and looked at the Document Type in an IF statement.
(if doc type is invoice, multiply amount by 5, else if doc type is credit memo, multiply by -1, else amount)
The NP Formula looks like this: =NP("Formula","if([@[Document Type]]=""Invoice"",[@[Amount ($)]]*5,if([@[Document Type]]=""Credit memo"",[@[Amount ($)]]*-1,[@[Amount ($)]]))")
My Document type isn't an integer and the formula isn't treating it as an integer.
I have attached the report and the XML so you can load it in the Table Builder to explore. (IMPORTANT: It wouldn't let me upload and XML file, so you will need to copy the code below into a text file and save as report.xml)<?xml version="1.0" encoding="utf-8" standalone="no"?> <tableReport xmlns="http://www.jetreports.com/jet.tableUI" table="Cust. Ledger Entry"> <fields> <tableReportField table="Cust. Ledger Entry" header="Customer No." type="Normal"> <stringField>Customer No.</stringField> </tableReportField> <tableReportField table="Cust. Ledger Entry" header="Document Type" type="Normal"> <stringField>Document Type</stringField> </tableReportField> <tableReportField table="Cust. Ledger Entry" header="Amount ($)" type="FlowField"> <flowField id="3ff2711c-b3c7-4da4-9265-e7f5b859811a" name="Amount ($)" type="FlowField" reverseSign="false"> <flowFilters xmlns="http://www.jetreports.com/jet.common" /> </flowField> </tableReportField> <tableReportField table="" header="New Formula" type="FormulaField"> <stringField>if([@[Document Type]]="Invoice",[@[Amount ($)]]*5,if([@[Document Type]]="Credit memo",[@[Amount ($)]]*-1,[@[Amount ($)]]))</stringField> </tableReportField> </fields> <stringFilters /> <linkFilters /> </tableReport> -
Jet Reports Historic Posts Try the attached file. I tried to build the formula based on your description but I can't test because I don't have those tables in my DB.
But Jason is correct that this will only work for Essentials, not Express since it requires the use of an NP function which is not accessible via Express.
Please sign in to leave a comment.