Help needed - Report with summaries

[Peter Gar]

I have a database of transactions that contains a 38,000 unique transaction numbers and customer number field. There are dramatically more transactions than customers.

I want to generate a report that looks something like this:

1 transactions - 30,000

2 transactions - 5,000

3 transactions - 2,500

4 transactions - 400

5 transactions - 90

7 transactions - 4

11 transactions - 1

Total Transactions - 38,000

The report simply tells me how many repeat customers i have.

How do i create a report that looks like this?

I've included the data.

Edited May 23, 2008 by Guest

[Søren Dyhr]

The problem is that you can't organize the reporting by a summary field, you have to make a selfjoin category to category, then provide your solution with a calc' with the aggregate function Count( over this relation ... then is just to group your summary report with this.

If you do this via the wizard will you initially get a body part where you are likely to expect the cathegory field, which then gets moved up in the subsummary part of the layout, before the body part gets deleted.

--sd

[Peter Gar]

What you suggest is way over my head.

Any chance you can build me an example in the data i provided?

Thanks in advance (either way)!

Peter :P

Edited May 23, 2008 by Guest
typo

[Søren Dyhr]

Any chance you can build me an example in the data i provided?

More or less, where would you expect me to find the +30K records to report upon - anyways I've made you a template to tear appart.

--sd

Transactions.zip

[Peter Gar]

YOU ROCK! Thanks for the example.

I attached a sample data file with 1000+ rows of data.

Its much simpler than yours. Simply a column of transaction numbers and a column of corresponding customer numbers.

My goal is to know how many customers had 1 transaction, 2 transactions, 3 transactions etc.

THANKS AGAIN!

data.txt

[Søren Dyhr]

All right with the given data would it look like this attachment:.

--sd

Transactions2.zip

[Peter Gar]

Soren,

I think you are close. The goal is to know how many individuals bought once, twice, etc. See my original post.

THANKS AGAIN!

eter

[Søren Dyhr]

I think you are close. The goal is to know how many individuals bought once, twice, etc. See my original post.

I thought it was what the lateste template did, whats wrong? ... the longest number is the individual's ID isn't it, while the first of them is the transaction ID.

--sd

[Peter Gar]

SD,

The goal is to know the total number of people who have 1 transaction, 2 transactions etc.

the report would look like this:

Number of Transactions - Number of individual users

1 - 30,000

2 - 5,000

3 - 2,500

4 - 400

5 - 90

7 - 4

11 - 1

Does that make sense?

[Søren Dyhr]

Indeed, but how would make 1208 transactions become that?? Where is the flaw in my approach??

Ah I've just seen it!

--sd

Transactions3.zip

Transactions4.zip

Edited May 24, 2008 by Guest

[Peter Gar]

BINGO!

The latest fix is exactly what i'm looking for!

THANK YOU!

Peter

[Søren Dyhr]

I've added graphing as well on ver. 4!

--sd

[Peter Gar]

i didnt even know FM could do that... way cool!

[Peter Gar]

Søren

Enclosed is a file with 38000 records.

Run the report and you'll see an error i cant figure out.

Can you tell me why the summaries for the higher number of transactions have so many customers? It doesnt make sense to me. The data doesnt back it up.

Thanks again for all your help. I OWE YOU!

Transactions5.zip

[Søren Dyhr]

It's a bit worse than I thought in the first place, we then better do it the non relational way - although it's going to be quite a bit slower in performance ... I would hope that others might chip in and make the algorithm faster!

--sd

Transactions6.zip

Edited May 29, 2008 by Guest

[comment]

I would do it this way. I believe it's faster and more straightforward.

Note that it ignores the found set in Transactions, so if you want to restrict the report, you will need to filter the relationship.

ReportByTXCount.fp7.zip

[Peter Gar]

Your solution works great!

Unfortunately when I try to reproduce your solution in my existing database i get the following. (Your correct report is on the left, my incorrect report is on the right)

Any clue what I'm doing wrong?

Peter

[comment]

From the picture? Not really.