Difficult Question...Need Help

[proton]

Hi all,

I have a very tricky question and I do need some help with it. I have a database system that I'm doing for a company (using FMP 4.x). One of the databases in the system is a transaction database where they record their sales. It contains standard information: Invoice number, date, product, quantity, price, etc.

They generate a lot of reports from this particular database. However, management has asked for some management type reports that summarises the data. For example:

A report showing the quantities of each product sold for each month, sorted by year. In other words, you see the product as a sub-summary and under it you see the example 1999, and going across you see the quantities sold for january, february, march, etc. Under that you see 2000 and the quantities going across for that as well and so on.

Another report is to show the customer as a summary, under that the product as a subsummary, and under that the years 1999, 2000, 2001 and going across the quantities for each month for the respective years.

Now these reports I can get in Filemaker easily. The difficult thing is that I can't get it to LOOK like how managment wants it. If I do the report, it will list the dates/months going down and I can get the quantity listed down (one under the other). What management wants is the months listed across (January, February, March, etc. going left to right) and the quatities and prices listed under the respective months.

When they requested it initially, I set it up to export the data to excel (CSV format) and then use the excel program to generate a pivot table which allows me to list the months and so on as they want (table format). This was a tad time-consuming as they had to format the data after and so on, but it worked. However, they have now requested some changes to the pivot table structure in such a manner that it is very difficult to get it as they want.

How can I do these types of reports in Filemaker? I really need this info ASAP, and I know for a fact you brilliant people can help. I eagerly await your replies. Thanks a million in advance.

[LiveOak]

The only way to build reports across is to use relationships to isolate the January, February, etc. data and summarize it. The calculations fields with the summaries (using sum() function) can then be placed anywhere on the layout you wish. You would need one relationship for each month. The key would probably be based upon month/year.

-bd

[proton]

Geeze,

I know how to do it that way. I was hoping there was a less time-consuming way to do it. That was the reason I shied away from doing the reports originally in Filemaker...sigh. Thanks loads though LiveOak. Your reply was indeed quick.

The thing is that they are always coming up with changes for the reports and new reports, and they will have to keep calling me so it could turn out to be a lot a lot of work. What do you think I should do?

I guess I'd better get out the elbow grease and get to work...smiles.

Thanks again.

quote:

---

Originally posted by LiveOak:

The only way to build reports across is to use relationships to isolate the January, February, etc. data and summarize it. The calculations fields with the summaries (using sum() function) can then be placed anywhere on the layout you wish. You would need one relationship for each month. The key would probably be based upon month/year.

-bd

---

[ August 09, 2001: Message edited by: proton ]

[proton]

quote:

---

Originally posted by LiveOak:

The only way to build reports across is to use relationships to isolate the January, February, etc. data and summarize it. The calculations fields with the summaries (using sum() function) can then be placed anywhere on the layout you wish. You would need one relationship for each month. The key would probably be based upon month/year.

-bd

---

LiveOak,

Bubble bubble toil and trouble. I created the fields to summarise the data and it worked. It's a lot a lot of fields though. But now another problem has popped up. These reports as I stated are very complicated with a number of sub-summaries. When I used test data on the newly created reports it worked, but when I put in the companies data (they have over 200,000 records) it is giving problems. I did a report on just 700 records and it crashed. It's a lot of calculation fields and I think that the server can not handle it.

Filemaker crashed about 5 times and I still can't get it to show the report in preview. I find the data, sort it and go to the layout. All this is good. But I can't see the data as it is in sub summary part, so I switch to preview and Filemaker stays blank for awhile and crashes.

They are running the databases on a Dell PowerEdge 2300 server with 128 Mb of ram. I think they need much much more memory to handle this task. Can you all please advise?

[LiveOak]

Are you running FM stand alone or do you have FM Server and regular FM? The possibilities for cause ofthe crashes are many. I always look first at factors outside FM, the OS and machine environment. Next, I'd look at the solution itself and the possibility that one of the crashes has left latent damage in the files. When developing this sort of a solutions, don't work on the primary copies, if you do, you may not be able to recover a damaged file to 100% health. Beyond that, it is really hard to help much without seeing the patient. You might want to consider having a local consultant take a look, second opinions are very helpful when your head-down in a tough problem.

-bd

[JPaul]

And don't forget, in multi user environment, to do an extensive use (in your scripts) of the 'FLUSH CACHE TO DISK' statement: it's a lifesaver !!

Best regards.

[proton]

quote:

---

Originally posted by LiveOak:

Are you running FM stand alone or do you have FM Server and regular FM? The possibilities for cause ofthe crashes are many. I always look first at factors outside FM, the OS and machine environment. Next, I'd look at the solution itself and the possibility that one of the crashes has left latent damage in the files. When developing this sort of a solutions, don't work on the primary copies, if you do, you may not be able to recover a damaged file to 100% health. Beyond that, it is really hard to help much without seeing the patient. You might want to consider having a local consultant take a look, second opinions are very helpful when your head-down in a tough problem.

-bd

---

I'm running FM 4.0V3 on a Dell PowerEdge 2300 Server with NT4.0 and 128MB RAM. The reports don't use scripts. I was not using the primary for the importation but a copy. But if I put back a new copy of the primary and try it again it still doesn't work. I think the server needs more memory due to the sheer volume of data in the file (222,000 plus records as I stated earlier). The database works fine, it performs the find, sorts the records, changes to the report layout, but from the time I preview to view the data it stops responding.

No other operations on the database cause this problem.

Thanks very much for the advice and eargerly awaiting any more suggestions that you may have.

Thanks guys.