Hello Everyone!
I am trying to build standard grouping (Rows=3) report with expanding columns for each month. The problem is that I don't know how to combine NF with SUM. I have a Pimary Key formula without a Field and I link columns with NF to it. But the expanding column should be NL("Sum", "Sales Invoice Line", "Amount" etc). not sure how to make this work. I am attaching the half done report. Could you please help?
Date
Votes
6 comments
-
Jet Reports Historic Posts Just in a cursory glace, I can't see what you are trying to create in this report.
What are you trying to show in the Amount column? Is it showing a sum of all the items on an invoice or is it showing the amount of the item purchased on that sales line? -
Jet Reports Historic Posts Thanks for the speedy response. I agree that my report might be confusing as I was trying different things none of which worked.
First column will be my 1st Global Dimension - Territory Code. I want all unique territory codes to be replicated. The second column would be all Manufacturer Codes (field in the "Item (had wrong table here initially)" table) that appear under the Territory Code. For the amounts, I need to have sales amount ("probably from "Sales Invoice Line" or "Sales Invoice Header" tables) that match the Territory Code and Manufacturer Code.
Instead of the Manufacturer Code I might need Item Category Code or Product Group Code. I was able to replicate all those via NF linked to the Primary Key formula. But I don't know how to get amounts to be replicated for the territory code.
I will be glad to provide more details on this, if needed. Thank you! -
Jet Reports Historic Posts I am sorry, one clarification. Manufacturer code is on the "Item" table. That is why I included Invoice No./Item No. to create link between those tables.
Here is the another version of the draft report. -
Jet Reports Historic Posts Do you really need columns E-L? I don't see their purpose in the report when looking at your "Example" for your final results.
Perhaps this is something you need to work with a designer or Report Analyst to work out but to me, this seems to be the structure that I would expect to see.
Main Grouping: Territory code (rows=4) with posting date within full range of date filter
Secondary Grouping: Company ID where territory code = main Grouping value (rows) within full range of date filter
Secondary Detail: Company name where no. = company no. in previous cell.
Secondary Detail: Amount sum of Sales/Amount/etc. where company = company no. in Secondary Groupingand posting date = date column value
You will need to determine the tables at that point but it seems like you have lots of items in your report that you simply don't need.
I think it would be a pretty easy report if you simplify it and take out any unnecessary items. -
Jet Reports Historic Posts Thanks a lot for the guidance and the sample report. I will look into it and report back!
Just one question, will your report work if I need to filter based on an item coming from another table? Say, I need to get amounts from "Sales Invoice Line" but based on the "Manufacturer Code" or "Product Group" coming from the "Item" table. So, in order to have filtering based on these, shouldn't I get Invoice Lines with Item numbers so then I can establish link beteen "Sales Invoice Line" and "Item" tables?
I am enclosing the final version of the report I have. Any suggestions how to make it work? Thank you! -
Jet Reports Historic Posts As long as you can link tables together, then you should be able to pull data from them.
I'm not following the logic of calling the replicator for the Manufacturer code on a detail within it (item no.)
Based on your explanation, you want all the territory codes and then all the manufacturer codes in that territory so the Rows for the Manufacturer should be referencing the Territory code not an item number in the detail of its own replicated region.
This is where working with a designer may have it's advantages since my report was just a very quick, very simplified example.
Please sign in to leave a comment.