Jump to content

Cross-tab Report Question


Gabriel G

This topic is 3030 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

snip1.png

snip2.png

Link to comment
Share on other sites

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.

snip4.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 3030 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.