Suggestions for building Filtered report

[infoweb]

Hi there,

 

I'm trying to build a particular report and was hoping to get some ideas / suggestions on the most efficient way to do this.

 

Some background to start with:

 

Using FM server 13 over WAN

 

Local client in numerous locations across australia

 

3 related tables:

 

Products

SalesLineItems

Sales

 

In Products we have a field SalesCount - this will be populated using script based on report parameters.

SalesLineItems has qty sold for each line item

Sales has the date of the sale

 

The report I want to run should do the following:

 

User is in a product list view

 

They enter a start and end date (in global fields) as the report parameters

 

A Script will set a field  (Products::SalesCount) with the number of sales of each individual product within the set date range by summing the SalesLineItems qty of the filtered SalesLineItems set. 

 

There are over 2000 active products and up to 10,000 SalesLineItems (depending on the start and end dates).

 

Some of the options I've considered (but haven't tried yet)

 

Option 1

 

Creating a Product layout with a filtered portal of SalesLineItems (filtered using the start and end dates) and a summary field for qty

Create a looping script that goes through each product and sets the Product::SalesCount with the Portal Qty summary field 

 

Option 2

 

Create a Global summary field in SalesLineItems for qty

 

Set a variable with the date range that was set in report parameters

Go to first product record

Set a variable with the Product UUID

Switch to the SalesLineItems Layout

Perform find for the date range AND Product UUID

Copy the value from the SalesLineItems qty summary field

Switch back to product layout

Paste the result in the Products:SalesCount field

Go to the next record

continue loop until last records.

 

At this point I'm just trying to get a workflow, so not that concerned with each script step, just trying to work out what the best / most efficient way of doing this will be. Open to any other suggestions or comments.

[comment]

A Script will set a field  (Products::SalesCount) with the number of sales of each individual product within the set date range by summing the SalesLineItems qty of the filtered SalesLineItems set.

 

This is hardly a good idea, since only one user will be able to produce a (correct) report at the same time.

 

Is there are a reason not to produce the report in the traditional way: i.e. find the records in the given range, sort them by product and show them in a summary report layout?

[infoweb]

Thanks for the input comment.

 

The Products::SalesCount is one of a number of figures that is used to set other values for each product eg. re-order point, re-order qty etc. which are then used to create purchase orders and also exported to an external point of sale system. ie. I need those figures to be exported along with the product data export.

 

Even though I call this a "Report" it's basically data that needs to be reviewed / manipulated in a list view - Hence the reason I was using the product list view.

 

I do understand your point regarding only one user being able to create a report at any one time and hadn't thought of that before.

 

One issue using a summary view of SalesLineItems is that I'll only see product that were actually sold, if a product wasn't sold in that period we won't be able to see it in our analysis. (I believe)

 

I'm not sure I can achieve what I need without using the Products table in a list view, as I need to see all product whether they have been sold or not.

[comment]

The Products::SalesCount is one of a number of figures that is used to set other values for each product eg. re-order point, re-order qty etc.

 

Are users able to enter any two dates as the range? If so, you would have different re-order quantities for different date ranges - kind of a difficult concept to grasp.

 

In any case, you must make a decision here: either your results are calculated based on the global fields (and will recalculate as soon as the global fields are modified), or they are populated by a script and will not change once produced (and in such case they would probably be better stored in another table).

[infoweb]

 

 

Are users able to enter any two dates as the range? If so, you would have different re-order quantities for different date ranges - kind of a difficult concept to grasp.

 

Correct, A user can enter any start and end date, the Sales count will then need to be calculated and after that  reorder point and reorder qty.

 

this information is then used for one of 2 purposes:

 

Create a purchase order based on the the calculated data

Export the re-order point and re-order qty to the POS system

 

Because we are using FM on a WAN I prefer to populate fields using a script - the system really grinds to a halt when we have multiple calculated fields (including fields calculated on calculated fields) and scrolling through the records to select which products will be added to the purchase order.

 

So your suggestion is to use a intermediary table to store the calculated results for the purpose of the report. I assume that next time the report is run the process would simply start by deleting all records in that table?

 

is there a benefit doing it that way over simply adding the fields in the current product table? once again, considering that we are using FM server over WAN performance is a big consideration for us.

[comment]

So your suggestion is to use a intermediary table to store the calculated results for the purpose of the report. I assume that next time the report is run the process would simply start by deleting all records in that table?

 

Actually, the idea was that if a user has already produced a report for the year 2014, then that would remain stored for all times, and could be simply retrieved when another user asks to see the summary for the same period.  But that was based on the assumption that users could ask for a limited set of report ranges. If that's not so, then I guess deleting the summary data before producing a new set (or perhaps deleting the summary data for the current user before producing a new set for this user) would indeed be in order.

 

 

is there a benefit doing it that way over simply adding the fields in the current product table?

 

The thing that bothers me the most is that in your scenario, User A could ask for a report on the first quarter of 2014. Then, unbeknown to User A, User B produces a report for the entire year 2014. Now User A's global fields still show the range for Q1 '14, but the actual summary data is completely wrong. That's just unacceptable.

 

A secondary issue is that if User C is editing a record in the Product table, neither User A nor User B will be able to produce their respective reports.

[infoweb]

Thanks heaps for the input.

I've got my head around the concept of a seperate table for a report now,

So I'm thinking of using a parent / child table setup for the report ( like and invoice with line items) I set the start and end date and some other info in the parent table.

Next I'll collect a list of product id's from a filtered product lis and create one record for each in the child table

Next I need to collect the data from the related Saleslineitems.

This is still where I'm a bit stuck. What would be the fastest way to collect the total sales of each product for the given period?

Ie. Can I do it in a calculation or would it have to be scripted ?

[comment]

It would have to be scripted, because any calculation across tables would be forced to unstored - thus defeating the purpose of the entire exercise.

 

The script itself could have been rather simple, except for the fact that the dates are in the Sales table, not in the LineItems table. This leaves you basically with three options for populating the Quantity field in the reporting table:

 

1. Use the Fast Summaries method to summarize the LineItems table into variables; then replace the Quantity field's contents with the quantity in the corresponding variable;

 

2. Use the so-called Ugo method to define a relationship between the reporting table and the LineItems table, using the Sales table as a filtering table; then replace the Quantity field's contents with a calculated value aggregating the related quantities from LineItems;

 

3. Replace the contents of  the Quantity field with a calculated value, using the ExecuteSQL() function.

 

 

I would suggest you pick option #3; I don't think I have ever seen a situation where ExecuteSQL() has so many advantages over the "native" methods.

[infoweb]

Thank you very much for taking the time for such a detailed response. It's given me some great insight into options and best way to proceed.

I'll spend some time developing some solutions around your suggestions.

[infoweb]

I've created the first part of the report building, ie. filtering which products to add to the report using global fields and portal filtering.

 

then taking the list of summary id's and using that to build the report - that part was easy enough.

 

I'm going to follow your suggestion reqsrding using the ExecuteSQL() - Will probably use it in a Auto enter calculation.

 

No comes the fund part - having never worked with SQL statements before it's a bit of a learning curve to get the statement right.

 

So basically I need to include the following in the formula

 

Sum the qty

where date is between a certain range AND where ProductUUID matches

 

Will post back once I get it going.