Crosstab

[mtpaper]

I have read about ways to achieve a crosstab presentation for calculated fields.

But, what about if the body is text?

eg, I have a table of the following fields:

week ending date

city

actorname

role

I want the end result to have the week ending dates going down the left, the roles going across the top, with the actor names in the body.

I can achieve this via Excel by using vlookup function on my source data, but I would like to generate it from Filemaker.

The only 'solution' I can think of is to restructure my database, so that the fields are:

week ending date

city

actorname

role1

role2

role3

role4

role5

Suggestions?

Thank you -

Marion in NY

[Ocean West]

will this http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

or http://fmforums.com/forum/showtopic.php?tid/198958/

[mtpaper]

Hi

I knew about the first suggestion; it's for numbers, not text

The second suggestion looks very promising - but I think it's a different issue than what I'm talking about.

thank you

Edited April 1, 2009 by Guest

[comment]

IIUC, what you describe is not a crosstab at all, since no values need to be summarized. You only need to write out a list in a tabular format:

http://www.briandunning.com/cf/880

[mtpaper]

thank you, but I don't see ANY resemblance between what I'm trying to do, and the example you gave me! Perhaps xtab is the wrong word - I am trying to create a matrix of data.

My records are:

week, actor, role

5/13/09, Tom Smith, Clark Kent

5/20/09, Tom Smith Clark Kent

5/13/09, Jane Doe, Lois Lane

5/20/09, Jane Doe, Lois Lane

5/27/09, Dick Brown, Clark Kent

5/27/09, Sally Cooper, Lois Lane

5/6/09, Dick Brown, Clark Kent

5/6/09, Mary James, Lois Lane

My desired result is shown in the attached pdf/jpg.

Thank you.

Argh. The file uploads, but then isn't reachable. Here is a recap, looking ugly

WEEK CLARK KENT LOIS LANE

5/6/09 Dick Brown Mary James

5/13/09 Tom Smith Jane Doe

5/20/09 Tom Smith Jane Doe

5/27/09 Dick Brown Sally Cooper

Edited April 1, 2009 by Guest

[*susan*]

The attachment is missing....

*susan*

[mtpaper]

@ Susan

the attachment uploads, but then cannot be accessed.

I created an ugly version of what I need, in my post above.

[comment]

It's true the custom function will probably not do it all by itself. But the principle is correct, I think. If you sort your records by week, then by role, you'll get a list like:

5/6/09, Dick Brown, Clark Kent

5/6/09, Mary James, Lois Lane

5/13/09, Tom Smith, Clark Kent

5/13/09, Jane Doe, Lois Lane

5/20/09, Tom Smith Clark Kent

5/20/09, Jane Doe, Lois Lane

5/27/09, Dick Brown, Clark Kent

5/27/09, Sally Cooper, Lois Lane

Now you need to loop through this list and write it out like this:

header

for every first row in a week:

& ¶ & Week & tab & Actor

for every other row in the same week:

& tab & Actor

This is assuming the same number of roles repeat every week.

[mtpaper]

ok - I understand that

But I've never done a custom function, so I'll have to go read about how to get started with that.

Thank you -

I'm eager to try it.

[comment]

I believe it would be simpler just to script it.

[mtpaper]

LOL!

I've created scripts, but not in FileMaker....

I looked at custom functions and was overwhelmed.

I'll try looking at scripts.

[comment]

Well, this is not exactly a beginner's task.

In general, Filemaker (like any database) has a strict structure of rows (records) and columns (fields). Therefore, it is not flexible in the horizontal direction, and cross-tabs are not easy to produce.

A more "natural" format for your report would be:

5/6/09

Clark Kent: Dick Brown

Lois Lane: Mary James

5/13/09

Clark Kent: Tom Smith

Lois Lane: Jane Doe

5/20/09

Clark Kent: Tom Smith

Lois Lane: Jane Doe

5/27/09

Clark Kent: Dick Brown

Lois Lane: Sally Cooper

This would be VERY easy to produce, just by sorting and sub-summarizing by date. Same thing for a report sub-summarized by role, i.e.

Clark Kent

5/6/09: Dick Brown

5/13/09: Tom Smith

...

or by actor.

That said, take a look at the attached sketch.

Note that one could continue this and create a record in a report table for each row in the $report.

[mtpaper]

LOL! I know it isn't simple... that's why I'm here!

I know how to generate the layout you've demonstrated; that's not useful to is. I need a matrix.

An alternative would be for me to change the structure of the table, from:

FIELDS: Date, Role, Actor

to:

FIELDS: Date, Role1, Role2, Role3, Role4, Role5, Role 6

Then the report would be a cinch.

Just that it isn't the most efficient database design; I was always under the impression that I should make the table as 'compact' as possible.

Marion

[mtpaper]

PS - the attachment with the script example doesn't 'exist'

[comment]

Seems like the attachment feature is not working at the moment, and we'll have to wait until Stephen fixes it.

Changing your structure to multiple role fields is something I would certainly NOT recommend. On the contrary, you should normalize your data further, so that your current table looks something like:

Week

ActorID

RoleID

and is related to separate tables for Actors and Roles.

[mtpaper]

yes, I've thought about making that change....

[*susan*]

When the attachment feature returns, I have a demo file which uses some custom functions found on brian dunning's site that create the format you are looking for.

Haven't actually added the report, since I don't know if you are printing this or viewing on screen.

*susan*

[mtpaper]

Hi - at this point in time, I'd be printing the report (to pdf)

Thank you!

[*susan*]

Attachments still don't seem to be working. Will upload this demo when they are back. I added a really simple report layout and a sample script.

*susan*

[*susan*]

This demo uses two tables:

ActorRole

weekEndingDate

The report will be created in WeekEndingDate. In this table there is a calculation field that uses two custom functions to get return all related ActorRole records as a paragraph separated list.

The script CREATE: Report will assemble the separated list into one reporting field. I commented the script so you can learn some basic scripting methods.

Hope that this is helpful,

*susan*

ActorWeekReport.fp7.zip

[comment]

File re-attached.

ScriptedTabularReport.fp7.zip

[mtpaper]

This is PERFECT

Usually, once I have a script as a starting place, I can learn and mimic and adjust -

Thank you -

I'll be back if I have questions -

very exciting -

Marion

PS: As a note to anyone who may read this in the future.... the data table MUST include a record for each 'data point' - ie, intersection, even if the intersecting field is empty. In this example, we must have a record for each week, for each role, even if the actor field is empty - or the data will get all messed up. Because this is the data in tabbed format, not a x-tab.

Edited April 2, 2009 by Guest

[Søren Dyhr]

I toyed with something I have seen Agnès Barouh do when dealing with massive measure of variables getting assigned - by rewriting your second loop:

Go to Record/Request/Page 

[ First ] 

Set Variable [ $date; Value:"" ] 

Loop 

     If [ $date ≠ Table::Date ] 

          Set Variable [ $report; Value:$report & ¶ & Table::Date ] 

          Set Variable [ $date; Value:Table::Date ] 

      End If 

      Set Variable [ $report; Value:$report & $tab & Table::Actor ] 

      Go to Record/Request/Page [ Next; Exit after last ] 

End Loop 

...into:

Set Variable [ $date; Value:"" ] 

Replace Field Contents [ Table::gReport; Replace with calculation: Case( 

    Table::Date ≠ $date; 

    Let( 

        [ $report = $report & ¶ & Table::Date; $date = Table::Date ]; 

        "" 

    ) 

) & 

Let( $report = $report & $tab & Table::Actor; "" ) ] 

[ No dialog ] 

 

The tansfer of the $variable to the global is the same for both scripts, the Replace just needed a "daft" focus!

...and wondered if it made sense at all? With this limited number of records is it obiously immeasurable???

--sd

[comment]

I did consider writing to the global directly by way of Replace Field Contents[], but it cannot be done all in one run (at least not simply) because of the header part. So I thought it would be best to stick to a single method. After all, this is a demo for a self-proclaimed beginner, and keeping it simple outweighs saving a few milliseconds every week or two.

BTW, one could define a value list of Roles, and consult it at each record to determine the number of tabs required. This would allow missing assignments - which, as Marion correctly points out, aren't allowed with the current method.

[mtpaper]

On the same subject, but with a different end result.... I needed to create a pivot or crosstab again. The above solution wasn't quite right, since I'm working with numbers that, in some cases, needed to be summed.

I looked at the two great solutions by Edoshin at Bits and Pieces, and by Dwayne Wright. But I'm not experienced, and I couldn't figure out how to apply their examples to what I needed.

So, I solved it another way; example file is attached.

Do any of you see any problems with this solution?

Thank you -

Marion

EDIT: I realized file was not attached

crosstab.zip

Edited May 21, 2009 by Guest

[comment]

Do any of you see any problems with this solution?

Other than having to create a pair of fields for each column? And hard-coding the category names?

[mtpaper]

Well, sure. Yes. Aside from that.

Because I don't see how to implement either of the other methods.

I'm not tallying by Month or Year.

Which method would you implement? I'l try to apply it.

Thanks for the response -

Marion

[comment]

Well, you have to work with what you have, so as long as it produces the results, you needn't fret.

I try to avoid cross-tabs if possible, and stick to the "native" Filemaker method of vertical summaries. But if I had to do it, I'd probably use this method (which is very close to yours)

http://fmforums.com/forum/showpost.php?post/165564/

[Brudderman]

You may not have seen this, but there is an example file that shipped with FM 9 called Web Viewer Example.fp7. One of the examples in that file is for a matrix report (click on Reports) that generates HTML displayed in the web viewer. It may give you some ideas as well as it produces the kind of output you're seeking.

James

www.james-mc.com

[mtpaper]

@Brudderman

I had seen the 'matrix' report in the Web Viewer Example.... but I don't understand how to navigate and find the report structure - I've positioned myself at the Matrix Report tab, and switch to Layout Mode... But I don't see anything that shows me how to create the report - the body of the report appear to be a Web Address....

How do I learn how the report is structured??

And, the table structure is more sophisticated than I know how to read... I don't know what is meant by Constant One. I will make a copy of the file and try to weed out the parts that don't have anything to do with the matrix, so I can try to learn it.

Thanks for the reminder about this -

Marion

[mtpaper]

@Comment

Thanks for the link to the Counting Sheep Post..... I think that example is something I can understand and implement.

Edited May 23, 2009 by Guest

[Brudderman]

The Generate Report button runs the Generate Matrix Report script where all the report magic is done. The script basically creates an HTML table from the data and then exports it as file (e.g., Web Viewer Example1.html). The URL of the path to this file is then loaded into the web viewer, where it is displayed.

I would guess that a clever person could use the same technique without the HTML and separate the columns by tabs and put the results into a global text field as Comment did in his example. Unfortunately, I don't think I'm that clever.

James

www.james-mc.com

[comment]

A clever person would use summary fields instead of looping through the records one by one - either by implementing Edoshin's FastSummaries for an in-field report, or by exporting summary values and using a XSLT stylesheet to pivot them.

I don't know what is meant by Constant One.

An obsolete technique that you needn't spend your time on.

[mtpaper]

With the Counting Sheep total by sub-category link....

is there a way to select which Categories appear?

The method works fine if you have 7 categories and want them all to appear.

What if you have 7 categories, and want on 3 columns to appear?

I can easily filter the source table.

And the answer pivot reflects the correct info.

But is there a way to alter the formulas so the pivot table only reflects the specified columns?

My source table has about 50,000 records, and about 30 categories; in each report I need to specify which Categories get utilized.

Thank you -

Marion

Edited May 23, 2009 by Guest

[comment]

Yes, it's possible - but the beauty of the method is that it automatically reflects the value list...

Anyway, you can change the cCategoriesR field to gCategoriesR and make it a Text field with global storage. On the layout, apply the same value list to it and select the categories you want.