SEI count of entries in a worksheet

Question

I have a list of suggested and firm workorders in an SEI worksheet coming from the ORDERS table. Very straightforward. What is stumping me is I'm trying to make a column that counts the number of work orders that have the same product as the current record. An example would be a column called "# of Workorders" So if there are 5 lines in the report for product "ABC" each line would have "5" in the "# of Workorders" column. Has anyone done something like that? Like an invoice report that counts the number of invoices for each customer?

Nick Brown CPiO · Accepted Answer

Hi John, 
 If you set the Subtotal value against the Work Order column to Count Distinct, when you group the Work Orders by Product with the Work Order field as a column, you should see a total against each product for the number of work orders. 
 Best regards, 
 Nick

Nick Brown CPiO · Answer

Hi John, 
 I've had a look and I can't see a way of doing this. Any filtering you apply is based on the line level detail. 
 One possible solution would be to create a SQL view that counts the number of firm work orders for each product and include that in the SEI Data Model. The number of work orders would then be added as a column with the subtotal field set to either None or Max (depending on how you want to use / display it in the worksheets). It couldn't be a sum or count field as that would distort the figures. 
 You would probably need to add a subtotal restriction as well to prevent worksheets from displaying a subtotal for other fields aside from the product when they are included as groups to prevent erroneous subtotals from being calculated. 
 You would then be able to use this field and filter for quantities > 1. 
 Someone else might have a more elegant solution, but that's all I can come up with at the minute. 
 Best regards, 
 Nick

Sage X3

SEI count of entries in a worksheet

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING