Report? or Calculation? 
Author Message
 Report? or Calculation?

Hi All,

I have an FMP database and want to extract information from it. It's a user log
database (all parsed in a nice FMP column layout). I have a field for IP
address and a field for the DATE where the particular address used the FMP
database.  

I want to create a report where it displays the DATE and the number of
different users on various dates

Example:
DATE                     USERS
12/28/2000               6
12/29/2000               8
12/30/2000               4
12/31/2000               1
1/1/2001                  0

I guess it's something related to count the number of occurrences of a
particular IP address on a particular day.

Thanks,
-Earl-



Wed, 30 Jul 2003 22:32:23 GMT
 Report? or Calculation?

You would probably be best served with a Summary report, in which you build
a layout that displays, in Preview mode or print, the subsummary parts you
describe, sorted by Date. Read the various sections, in both the manual and
the online help, that  describe working with Summary parts, and get back to
us with specific questions.
--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634


Quote:
> Hi All,

> I have an FMP database and want to extract information from it. It's a
user log
> database (all parsed in a nice FMP column layout). I have a field for IP
> address and a field for the DATE where the particular address used the FMP
> database.

> I want to create a report where it displays the DATE and the number of
> different users on various dates

> Example:
> DATE                     USERS
> 12/28/2000               6
> 12/29/2000               8
> 12/30/2000               4
> 12/31/2000               1
> 1/1/2001                  0

> I guess it's something related to count the number of occurrences of a
> particular IP address on a particular day.

> Thanks,
> -Earl-



Thu, 31 Jul 2003 00:41:35 GMT
 Report? or Calculation?
Pouring over the manual, on-line help and a few FMP books, but don't know if
this is a specific question. In my original message in the USERs column, I put
in those numbers to illustrate what I wanted as a result. The total number of
occurrences of the individual IP addresses on a particular date is what I'm
looking for.

I can't seem to figure out how to get FMP to count the number of users on the
days that the DB was used.  I can do it manually by doing a find on 12/28/2000
and then doing a sub-summary and seeing on screen that there are 4 different IP
addresses listed.  But the DB has 31 different dates.  

If I use the "count" function it counts each IP address and lists it as "1" so
I can't figure out what next.

-Earl-

Quote:

>Date: 2/10/01 10:41 AM Central Standard Time
> Read the various sections, in both the manual and
>the online help... and get back to us with specific questions.

Earl said:
Quote:
>> I want to create a report where it displays the DATE and the number of

different users on various dates
Quote:
>> Example:
>> DATE                     USERS
>> 12/28/2000               6
>> 12/29/2000               8
>> 12/30/2000               4
>> 12/31/2000               1
>> 1/1/2001                  0



Thu, 31 Jul 2003 10:29:33 GMT
 Report? or Calculation?
Dear Earl,

Some of the ways of returning summary results depend on found sets (the
results of either finds, or a GTRR [Go To Related record]), or
relationships. The Summary fields, and  Sub-summary layout parts, use found
sets to return results, and thus require some action, most typically a Find.
The Aggregate functions (such as Count) work instead across relationships. A
typical use of the Count function is :

Count(Relationship::My_Field),

in which "My_Field" is usually the foreign key (the right hand match field).

Building Summary reporting layouts is a more common way to gather totals,
and it is not clear from your post (this one) whether or not you understand
how to build a Sub-Summary layout, and view it in Preview mode. It requires
the creation of a found set, ususally by a find, and then a Sort by the
break fields in the layout. I don't want to take the time here to describe
the process, as it is reasonably well documented in the manual and online
help, and it is also a bit tricky to get right. My post on the list
basically said that is the most common method, and would probably do what
you want.

However...

As you also note, these reports are dynamic. You can print them, but they
won't stay on the screen (or in the computer) once you move on to do
something else. to do that, you need Aggregate functions, and to use them,
you need to build relationships that will return the data you want.

The relationship you want is based on dates, and Filemaker does not easily
build relationships on date ranges. However, it is easy to build a
relationship on a single date, as follows:

Create a value list, "Dates", using the Date field to populate the value
list.

Create a global date field, gDate, and format it with the dates value list,
using a popup list or menu. In other words, you can select a date from the
field, and every date in the value list will be a date that you actually
have a record for.

No create a relationship, "All_Dates", between gDate on the left, and Date
on the right. Place gDate next to a new calculation field, "Total", that
returns a number:

Total= Count(All_Dates::Date).

Choosing different values in gDate wil return the total for that date.

If you want to see a list of all the totals for all the dates, it gets more
complicated, and involves building a mutiline key for the left side, which
can be done with either a script, or one of the Design functions. But try to
get comfortable with either the SubSummary layout method, or what I have
described here, before getting too fancy.

--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634


Quote:
> Pouring over the manual, on-line help and a few FMP books, but don't know
if
> this is a specific question. In my original message in the USERs column, I
put
> in those numbers to illustrate what I wanted as a result. The total number
of
> occurrences of the individual IP addresses on a particular date is what
I'm
> looking for.

> I can't seem to figure out how to get FMP to count the number of users on
the
> days that the DB was used.  I can do it manually by doing a find on
12/28/2000
> and then doing a sub-summary and seeing on screen that there are 4
different IP
> addresses listed.  But the DB has 31 different dates.

> If I use the "count" function it counts each IP address and lists it as
"1" so
> I can't figure out what next.

> -Earl-


> >Date: 2/10/01 10:41 AM Central Standard Time

> > Read the various sections, in both the manual and
> >the online help... and get back to us with specific questions.

> Earl said:
> >> I want to create a report where it displays the DATE and the number of
> different users on various dates
> >> Example:
> >> DATE                     USERS
> >> 12/28/2000               6
> >> 12/29/2000               8
> >> 12/30/2000               4
> >> 12/31/2000               1
> >> 1/1/2001                  0



Thu, 31 Jul 2003 11:09:26 GMT
 Report? or Calculation?
Dear Earl,

If there is a risk of loosing a site to Access, I would be happy to help.
From your description, it sounds likely that you may be trying to count the
number of cows in a herd by counting the legs and dividing by four, i.e.,
trying too hard. I doubt you need to go into Access to parse out a bunch of
strings, although I've probably got enough VB to do it if necessary.

I'm not quite sure what you mean by "web.log". Do you mean the data come
from a web site, or is this some template that FMI puts out? In any event,
if it's text, it can be parsed by FMP. The rest of your problem is simply a
matter of understanding how Filemaker deals with relationships.

So, feel free to email me the files, and I will be happy to take a look at
them.

Warm regards,

John

--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634


Quote:
> Sorry I didn't go into details, but yes, I'm familiar with sub summaries
and
> previewing them. The pouring over manuals, and on-line help...in this
instance
> didn't help.  I'm a firm believer in "RTFM".

> The "find" is the problem. I'm not finding anything, I'm using the whole
list
> of records and each contains both an IP and a date.  I'm drawing from the
FMP
> "web.log" which as you probably know doesn't parse very easily to be able
to
> extract useful data.  I got a VB programmer to write an Access (ick)
program
> that does parse the data from the FMP web.log.

> The stats I'm collecting are being used to demonstrate that we should keep
the
> DB in FMP and not try to muck with it and re-translate it back into Access
> which didn't work at all well, and was extremely "programmer dependent"
for the
> simplest of tasks. Despite my description, the Master DB is fairly fancy,
with
> scripts, with dozens of layouts, with calculated fields...and so on.

> I'll try the value list approach for the dates and see if I can figure out
how
> to count the number of occurrences of the IP addresses. Cause counting
them
> manually is making me far sighted...

> Thanks again,
> -Earl-



Thu, 31 Jul 2003 15:23:28 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Calculation in detail section of report

2. Quick Report (v1) Calculation Problem in D2

3. Quick Report or TQuery calculation question

4. Reports -- calculations

5. ACE Report Calculation Using DATETIME Type

6. Report Groups and Summary Calculations

7. calculations with reports

8. Calculation problem in a Report PDOXWIN

9. Query calculations in reports

10. Newbie Summary Report and % Calculation

11. calculation help needed for report


 
Powered by phpBB® Forum Software