Cross-tab Report Question

[Gabriel G]

Greetings!

I'm experimenting with reports, and I stumbled across this cross-tab report. I am getting close results with this approach, but I am still missing something... Could someone have a look at the screenshots and tell me if this is possible. If so, maybe point me in the right direction.

It shows the first quantity, but excludes any others. This may fall within some of the limitations that I've read about. I haven't done much with reports yet.

Thanks, GG

[Steve Martino]

Can you show your cross tab layout in Layout Mode. 

[Gabriel G]

Hi Steve, sure thing.

The layout is showing records from the LineItem table, and the sub-summary part it is sorted on the OrderNumber field.

Thanks.

[Gabriel G]

If I change the Trailing Grand Total part to a Body part and move all of the product fields down into it, the report shows an expanded version of what I would like to have.

It may not be possible to have a condensed version of this. Similar to a version showing the red numbers in my previous screenshot.

[comment]

Your question is not entirely clear. It's easy to see that each column of your cross-tab report represent a product, but what are the rows supposed to represent?

[Gabriel G]

Hi comment, I guess I didn't ask a question- but I was wondering if it were possible to generate such a report? And to get some direction if it is possible.

I'll try to explain the scenario more. There is an excel sheet in place used to keep track of products per order from each customer. The row shows the Order Number, Customer's Name (missing in my examples), Order Date, Ship Date, and a tally of each product per order. The Trailing Grand Total part would show the totals for each page.

I'm having trouble condensing the "tally of products" per order on a single row. (My guess is that I have the calculation fields in the wrong place.)

When the report is printed, the ShipDate field is empty and used like a checklist when shipped. Those would be hand-written first, and keyed at a later time.

Thanks.

[comment]

4 minutes ago, Gabriel G said:

products per order from each customer

But that's exactly what the Orders table shows, isn't it? It shows it vertically, not horizontally - but it's still the same data, sliced the same way. There is no actual cross-tabulation required here - unless an order has two or more line-items for the same product.

[Gabriel G]

Well yes- it is the same data, but the condensed format is the goal here.

Again, this report will be printed and the pages are used outside of Filemaker.

30 minutes ago, comment said:

unless an order has two or more line-items for the same product.

In this case, the quantity field is what makes it unnecessary for an order to have two or more line-items for the same product. This was one thing that led me to think it was necessary to use a cross-tab report. (I am new and still learning the basics with reports, my apologizes)

[comment]

Okay, then what exactly is the problem you are running into? If you define a summary field for each prodNameXXX field you have (there is a better way, but let's go with what you have for now), and place these in a sub-summary part (when sorted by OrderID), you should get the expected result.

I do see a problem in the way you have related Orders and LineItems, though. This relationship should match:

Orders::OrderID = LineItems::OrderID

and there should be no LineItemID field in the Orders table.

[Gabriel G]

Oh wow. This isn't the first time I've made that mistake And I got it working as expected once that was fixed it.

So, this leads me to wonder...

1 hour ago, comment said:

there is a better way

This turned out to be pretty simple. But if you wouldn't mind sharing, I would interested to learn. It might help another newbie one day.

Thanks again

[comment]

Well, instead of using 7 calculation fields and 7 summary fields, you could use one repeating calculation field and one summary field. And make it dynamic, so that the product names can change (although Filemaker will never be dynamic when it comes to the number of columns to display).

See the attached demo:

CrossTabR.fp7

And see also:
http://fmforums.com/topic/71836-getting-more-out-of-filtered-portals-in-version-11/#comment-339728

 

 

CrossTabR.fp7

Edited November 12, 2015 by comment

[Gabriel G]

Yes- two extra fields for each product is quite a bit of overhead for just one report.

Thank you for the response, the demo, and the link. I'll have a look at it tomorrow.

[Gabriel G]

Hi comment, I looked over the workings of your demo. I appreciate this tip, and also for the example on how repeating fields work.

I attempted to go a little further and modified your demo. It adds a bit more depth on the ordering system. I got the report working to reflect on my changes. However, it now shows zeros where the quantity was blank before. This is a minor issue, and I used conditional formatting to suppress it.

I wonder if this could be done without showing the zeros at all (mainly for formatting purposes)? If anyone is interested, have a look and see if it's a trivial task to accomplish. It could be helpful in the future.

Either way, I wanted to share the modified version for others here.

Thanks again, GG

CrossTabR_v2.fmp12

[LaRetta]

Hi,

Select your sTotalQtyByProduct in layout mode.  Select the Data tab and below, on Data Formatting, select Format and 'decimal'.  do not check anything except the 'do not display number if zero.  Repeat for the second copy of the field.

[Gabriel G]

Yes. I recall that check box...

Thanks LaRetta