0

Primary Key with Two Field

Jet Reports Historic Posts

April 10, 2012 15:22

None

Follow

Hi,

i need to calculate the totale of Sale Invoice Line Quantity and Amount, with filter on Section Code and Axe Code, that we find in Posted Document Dimension table.
how can i do that using Jetreports Function without displaying Rows?

Thank you,

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts April 11, 2012 14:47 Official comment

  Hi,
  
  You can mostly do it manually although I think there would be problems if you were trying to filter for blank dimension values (since those lines simply do not exist in the Posted Document Dimension table). Jet does something rather special in that case. At any rate, if you were going to create a link manually, the formula would look something like this:
  
  =NL("Sum","Sales Invoice Line","Amount","Link=",NL("Link","Posted Document Dimension",,"Table ID","113","Document No.","=Document No.","Line No.","=Line No.","Dimension Code","AREA","Dimension Value Code","Your filter here"),"Link=Sales Invoice Line",NL("Link","Posted Document Dimension",,"Table ID","113","Document No.","=Document No.","Line No.","=Line No.","Dimension Code","SALESPERSON","Dimension Value Code","Your filter here"))
  Since you want to filter on 2 separate dimensions, we actually have to make 2 separate links. The second Link= must must have the base table to show that it is linking from the Sales Invoice Line rather than a nested link inside the first link. In my example, I am filtering on the AREA and SALESPERSON dimensions since I don't know what the dimension codes are for your Section Code and Axe Code dimensions. We have to filter on Table ID of 113 since that is the table number of the Sales Invoice Line table (dimension tables hold dimension values from multiple tables). You replace the "Your filter here" with your filter value or a cell reference to a value.
  
  Of course once you know the dimension codes, you can just filter on those directly as I said earlier rather than using these link formulas or even the "Shortcut Dimension x Code" fields that you mentioned. This is much easier than doing the links manually as I have done here. :)
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 10, 2012 20:10

  Hi,
  
  Are Section Code and Axe Code Advanced Dimensions in NAV? If so, you should be able to filter by them without actually manually linking to the Posted Document Dimension table. You should be able to just filter by their dimension codes directly on the Sales Invoice Line table (these do not appear in the list of fields in the Jfx in the currently released version of Jet, but they should still work as filter fields). Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 11, 2012 07:30

  Thanks for your reply fhilton,
  
  Actually yes,i'm working on Nav and your solution works very well, i tryed the filter on Shortcut Dimension 2 Code field.
  
  Please, if i would like to do it manually, how can i do that? Get the Invoice Header ID and the Line N° from the Posted Dimension Document?
  
  Thanks,

• 0
  
  Jet Reports Historic Posts April 12, 2012 07:35

  Hi,
  I totaly agree with you :).
  Thanks again, you have been very helpful.