Hi there, I am a new member here and have a question:
I have created a report that shows me the stocks of my items in the warehouse.
Since having a lot of items (some 3.000) and only having stocks or movements on 200 items during the defined weekly period I use the function “hide” to hide the information that is not relevant for the report.
Information is hidden if following applies:
==> Field 1: 0 Stock level on the e.g. 01.02.2009
==> Field 2: 0 Stock level on the e.g. 08.02.2009
==> Field 3: No movements on this item during the period e.g. 01.02.09..08.02.09
(excel formula: if field 1+2+3 = zero; "hide"; "show")
Is there a possibility to program a report which would delete all rows with a 0.00 on all 3 fields (instead of hiding the rows which automatically leads to a huge excel file)?
Thanks
Date
Votes
11 comments
-
Jet Reports Historic Posts I'm not are of anything that will delete rows from a report.
My approach would be to include that logic into your Jet function, that way Jet doesn't have to even return the results. The complexity of doing this would depend on how you calculate your stock levels.
Some of the help in Jet would assit - check up on using nl() options like Filters & Link and also Arrays.
If you can provide some of your forumlas we can point you in the right direction. -
Jet Reports Historic Posts hi there,
thank you for your responce. I have attached a sample file showing the problem (Information & Comments included)sample file.xls -
Jet Reports Historic Posts Hi there.
Any chance of getting the formulas in English? My apologies for being too lazy to translate (is it German?).
cheers -
Jet Reports Historic Posts here you are, all translated :D
yes this was german -
Jet Reports Historic Posts Hi Hokiman,
Haven't looked at your fiter question yet, but I'm wondering about something in your report…
There are several NL(Sum) functions in the report. I wonder why. Did you know there are a couple of flowfields on the item table you could use?
in H16: NF($C16;"Net change";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";H$9)
in J16: NF($C16;"Transfer (Qty)";";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";$H$10)
in K16: NF($C16;"Net change";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";K$9)
And I'm thinking of using the net change field in the initial NL funtion to only retrieve items that have a value in the selected period. But I'm not quite sure about that as I'm doing this by heart. Something for later I'm afraid.
Please let me know if the NF functions give exactly the same result as your NL(Sum). -
Jet Reports Historic Posts Hansfoursert may be better placed to analyse than me (given his knowledge of both Jet and Nav), but from where I stand (give the current formulas), it seems you would need to embed those forumlas directly into C16. This would create a maintenance nightmare, which to me would be worse than hiding the cells. Doing this would probably cause you to exceed the maximum amount of space allowed for a Jet formula. If you want to try, you can include the exact formulas as field filters, just keep them in the " as you normally would, and use double " (i.e. "") for the internal filters. I presonally stay away from this unless I have no other option.
So my question is - Is there a better way to select the records you want?
Looking back at your original english of the filter:
==> Field 1: 0 Stock level on the e.g. 01.02.2009
==> Field 2: 0 Stock level on the e.g. 08.02.2009
==> Field 3: No movements on this item during the period e.g. 01.02.09..08.02.09
I don't see why you need all 3 filters. If there are no movements in the time period, and the stock at the end is 0, then surely it would have been zero at the start?
Look through the help on the NL("Filter"), "Link=" & array functions functions, to then filter on the Item No, and combining that with hans suggested flowfields, you should be able to re-work the filtering on C16 to at least limit the amount of cells you are hiding.
Good luck! -
Jet Reports Historic Posts hi markl, hi hansfoursert,
thank you for your input to this report, but maybe I should explain the reason for this report and my intension to keep the files small.
This report is intended for some 30 sales reps who receive an email with a similar file as an attachment every week. The file gives an overview of their stocks e.g.
==> stock count of the item XXXX on the 01.02.09
==> Sales quantity 010209..080209
==> Transfers quantity 010209..080209
==>stock count of the item XXXX at the end of the period, 08.02.09
(this explains the several NL(Sum) functions)
having a lot of items with no movements or stocks in the requested periods I have chosen, not knowing better, the way to hide information not required as presented in the file. But all in all for every report only some 30-50 rows are shown and some 3.000 rows are hidden. The files are scheduled weekly and sent out by Email. Every file ends in some 5 MB with a lot of “useless + hidden” information
And this is where the problem starts. Every email with the attached file reserves some 5 MB, multiplied by 30 sales reps all ends up in 150 MB of information in "sent items box". This collides with the email policy of max. 100 MB per user.
There is probably a simple way… :?: -
Jet Reports Historic Posts I see where you are coming from. I have issues with some of our reports here being 40 MB, however when extracting the raw data only through cut & paste or a macro, its down into the 200kb range.
Back to your report. In summary, given your current formulas, I don't know how to reduce the size of the file.
But I have some workaround suggestions:
- With the last requirement, "stock count of the item XXXX at the end of the period, 08.02.09", can you manually calculate that based on the results from the other 3 fields you've brought in? That would be one less formula.
- Can you change the scheduler option to HTML instead of workbook? Never tried if before, but it might work if its just for visual display purposes
- Reducing the records returned by formula in C16 - Is there some sort of flag on the Item card you can filter on to exclude? We have a 'discontinued' flag but I do not know if that standard or not.
- If you've got a valid support contract, Jet Reports support may be able to assist you in getting the report down in size.
A non-Jet idea… If you're using MS Outlook, set the reports to run over a weekend (Sat, Sun for example), then set auto-archiving of sent items to be daily, moving into a PST file. Ensure that runs between jet report runs, and you'll have ~75MB each day if you balance them out over a few days.
Other than that there is nothing else I can think of at this point. I think Jet Support would be your best bet. They have always been great when I've had to deal with them, especially in report tweaking. -
Jet Reports Historic Posts in relation to the size of the files of my report I have discovered a phenomena that I can’t really explain,
When creating one of the mentioned reports (a smaller one with less Items) the reports ends up in 3,2 MB. This report contains two sheets.
When I take each sheet of this report, paste or copy them to a new file then save the file with a new name suddenly the 3,2 MB become 0,172 MB.
Why? :o :o :o
(PS. Cell A1 in the report contains auto & hide & values.) -
Jet Reports Historic Posts Hi Hokiman,
Haven't looked at your fiter question yet, but I'm wondering about something in your report…
There are several NL(Sum) functions in the report. I wonder why. Did you know there are a couple of flowfields on the item table you could use?
in H16: NF($C16;"Net change";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";H$9)
in J16: NF($C16;"Transfer (Qty)";";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";$H$10)
in K16: NF($C16;"Net change";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";K$9)
And I'm thinking of using the net change field in the initial NL funtion to only retrieve items that have a value in the selected period. But I'm not quite sure about that as I'm doing this by heart. Something for later I'm afraid.
Please let me know if the NF functions give exactly the same result as your NL(Sum).
Hi Hansfousert,
thank you very much for your suggestion!!!
I did not really know the meaning of the flowfileds in the Item Table, but i have been investigating and understand them know. After that I have tried your formulas in my report and YES:
=NF($C16;"Net change";"Location filter";$H$8;"Variant filter";$E16;"Date filter";H$9) gives exactly the same result as H16
=NF($C16;"Transferred (Qty.)";"Location filter";$H$8;"Variant filter";$E16;"Date filter";$H$10) gives exactly the same result as J16
=NF($C16;"Net change";"Location filter";$H$8;"Variant filter";$E16;"Date filter";K$9) gives exactly the same result as k16
(had to adjust the formulas slightly)
in the meanwhile I have managed to filter out some items and variants by using following :
for the items: NL("rows";"Artikel";;"Gen. Prod. Posting Group";"pt";"Nr.";NL("filter";"Item Ledger Entry";"item no.";"posting date";"01.11.08";"Location code";$L$6))
for the item variants: NL("rows";"Item Variant";"Code";"item no.";D20;"code";NL("filter";"artikelposten";"variantencode";"item no.";D20;"posting date";"01.11.08";"location code";$L$6)) -
Jet Reports Historic Posts Hi Hokiman,
Yes, the Item table has lots of great FlowFields! Have you installed the Jet Reports Advanced Designer - that allows you to actually see the definition of the FlowField Formulas. Really cool stuff for designers :ugeek:
Anyway, back to your report… Mark already suggested to have a look at the NL(Filter) commands.
If you create 2 NL Functions:
- an array of all item nos that have a starting qty: NL("Filter";"Item";"No.";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";H$9;"Net change";"<>0")
- an array of all item nos that have a transfer qty: NL("Filter";"Item";"No.";"Location code filter";$H$8;"Variant code filter";$E16;"Date filter";$H$10;"Transfer (Qty)";"<>0")
Combine these 2 arrays to list these items in Rows:
- NL("Rows";NP("Union";CellRefToArrayWithStartingQty;CellRefToArrayWithTransferQty))
Does this give you the same list of items as in your report?
FInally you need to link to the Item table to retrieve the fields for your report, but that's the easy part, isn't it? :)
I'm doing this by heart again, so you may have to adjust the functions a bit again.
Please let us know about the results!
Please sign in to leave a comment.