On the item table, we have item category code, product group code, and brand code.
To GET unique combinations of items grouped by those 3 fields, we do the following:
B3: NL("ROWS","ITEM","ITEM CATEGORY CODE")
C3: NL("ROWS","ITEM","PRODUCT GROUP CODE","ITEM CATEGORY CODE",B3)
D3: NL("ROWS","ITEM","BRAND CODE","ITEM CATEGORY CODE",B3,"PRODUCT GROUP CODE",C3)
Is there a better way to that, since it looks like it has to iterate three times on the table to get the result.
I tried using simple SQL statement with group by using sql management studio and it took me seconds to get 26,000 lines, but it took me hours using the above jet formulas.
Thanks
Date
Votes
4 comments
-
Jet Reports Historic Posts Hello,
I wouldn't think it would take you hours if those 3 formulas are the only things on your report; I'm guessing it's those 3 formulas combined with whatever else is on your report that's taking hours, correct? Or do you literally have just these 3 formulas in a workbook that is taking hours to run? Still, your point is taken that doing the combination of these 3 formulas would take longer than just a single formula, especially since it's not just 3 formulas being evaluated but rather the number of replicator formulas evaluated would be 1 + (number of unique item category codes) + (number of unique item categories x number of unique product group codes).
Anyway, to the actual solution. I think maybe you could just do this with a field cache. Try this:
B3: NL("Rows","Item,{"Item Category Code","Product Group Code","Brand Code"})
C3: NF(B3,"Item Category Code")
D3: NF(B3,"Product Group Code")
E3: NF(B3,"Brand Code")
A field cache should give you the unique combinations of the specified fields that exist in the table, which I believe is what you were achieving with your 3 replicator formulas previously. Is this method faster for you?
Regards,
Hughes -
Jet Reports Historic Posts Actually, I am not working with item card, it was just an example.
We have an aggregate sales table based on sales invoice header and sales invoice line which we call "Sales Summary".
We combine items with the same description as one item, and we combine the sales amount and sales quantity in a month using a single date.
The table has 41 fields and almost 6 million records.
I want to get a list of unique combination of customer no.(9000 accounts)/item commission group code(9)/salesperson code (138).
With the following SQL Statement: SELECT [customer no_], [item commission group code], [salesperson code]
FROM [Sales Summary] GROUP BY [customer no_], [item commission group code], [salesperson code]
in less than 1 minute, I got 26,000 unique records.
I have found that the idea of field cache is a good one when dealing with group by scenario, I will use it a lot.
As with my problem, I tried using your suggestion with field cache, and it is still running after more than 40 minutes.
Is it just because of the size of the table?
Thank you. -
Jet Reports Historic Posts I'm guessing it is more the total number of Jet functions being replicated and evaluated by 26,000. Obviously you have the 3 NF functions for the 3 fields. With a field cache, the NF functions won't actually hit the database; instead they will just parse their values from the field cache itself. Still, it's doing that parsing 26,000 x 3 = 78,000 times. Then whatever other functions you have that are being replicated and evaluated 26,000 times would also have to be evaluated. So if you're bringing back all 41 fields in Excel, then that is 41 * 26,000 = 1,066,000 function evaluations (and actually more evaluations than that since functions get evaluated multiple times by Excel during a report run).
So my guess is that it's not the size of the table since, as you pointed out, it's relatively quick to evaluate a SQL query and get back the 26,000 results. Rather, it's the replication of those results in Excel and the calculation of all the Jet and Excel functions that are being replicated.
Regards,
Hughes -
Jet Reports Historic Posts In my report, I only have those 3 fields. I tried to run the report early this morning and i had to cancel it since it had been running for 1 hour and 40 minutes. To solve my problem, I have created a simple report on SQL Reporting Services, and it's working well.
Anyway, thank you very much for your help.
Please sign in to leave a comment.