Suggestions in creating a report

Questions
,
1

i need your suggestions. i am creating a report, count on each category (image below).

i am thinking of the following:

  1. loop with in the date and count (+1 on each every occurrence)
  2. executeSQL with count then display in a list
  3. create a separate table, everytime a record was encoded, add a script to add the count, then retrieve the table for future report.

which is more reliable and fast and logical?

2

Can you please refine the description of your needs, @marke1415 ? Perhaps describe the user workflow a bit, such as "Filter records by the fixed drop-downs; filter by date; and I want to see xxxx on a printed report."

Is this to be on a report that may be printed or preserved as PDF file, or is to be summaries or calculations on a form layout.

Perhaps a bit of clarification or additional detail will help people to help you.

1 Like
3

step 1. user will encode (website/source/status) (please refer to the image, the DATA part)
step 2 . at any point in time, supervisor will check on how many does this user encoded

image
image815×575 10.3 KB

**numbers in report are just a sample

4

One way that I have addressed this type of thing is by having a series of unstored calculation fields using ExecuteSQL to populate each. Unstored calculations can sometimes have a negative effect on performance, depending on several factors such as data set size, network quality (WAN is more problematic than LAN usually), other elements which must be loaded on the layout (such as summary fields), etc. In my use they are fine as I have mitigated the confounding factors and host them only on LAN.

In one such example, the calculation fields are on a dedicated table for the sole purpose of counting items, and the table is not related to any other. Execute SQL works without the need for a relationship as it's implied in the SQL query. (Once the user sets the criteria the data are loaded into the layout for viewing, then by button can export to Excel, move to a print layout, etc.)

In another application the fields are in the same table as the primary table for entering such data.

I use a combination of Let, ExecuteSQL, and While functions (since FMP v18), but there may be cases where looping to create lists or populate variables would make sense. Looping to "+1 on each occurrence" wouldn't likely be necessary if one uses SUM in the eSQL query. Adding to count in a While function is sort of the same as looping, and could be a way as well. I'd likely go eSQL first.

Edit to clarify: SUM may be used, but in lieu of COUNT in eSQL queries I SELECT the appropriate records, the use ValueCount on that result. In my use cases it’s many times faster than COUNT.

Hopefully, others will chime in with some suggestions. I must step away now to prepare for a presentation tomorrow. If I have time to put together an example (unless another Super Souper provides it) I'll post back, but it will be at least a couple days.

1 Like
5

in connection with this, i tried a query and the results are correct
select
website, source, status,
count(status) from myTable
where myTable.date_from >= ? and myTable.date_from <= ?
group by website, source, status

how can i get the result ?

6

This is a great question. Knowing more about the desired format of the report is helpful. For instance, the report could just be something viewed on screen, it could be exported as Excel, or it could be saved as a PDF file. Also, it seems kind of implied that the report may be for any possible date range that a user specifies, but knowing that detail is important, too.

@marke1415 In addition to responses that you receive here, I would also suggest making sure you understand how report layouts with sub-summary sections work in FileMaker, as well as how summary fields behave in such reports.

7

Not a direct answer to your question, but with FileMaker, when putting together your schema, you should think about the reports you will need to run. You have to do similar things with other development tools, except that for example calculated fields are outside of the tables.

You need to compute the amount of taxes, then you add a calculated field right in the proper table.

In short, when you create you tables, you should:

  • Think about the data you need to type and to display in a Layout
  • And think about the data you need to report

Read about the different kind of fields, and pay a special attention to calculated fields since they go beyond adding numbers and concatenating strings.

1 Like
8

format needed for the report,

  • can be viewed on screen (in presentation)
  • can be exported to excel (for documentation)

i just anticipated what the user might need in the future.

i was able to make the report by pulling the records using sql but i am not comfortable with the code, because i run the script on each item.

9

Another option, if you are comfortable with HTML, is to implement PivotTable.js into a Web Viewer

1 Like