Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

Recommended Posts

Groom

Can anyone help, I am a novice with Filemaker but have developed a database for my own business a Guesthouse B&B.

I am building some reports and one for the love of me I do not know why I can’t get it, it should be simple…I think.

I have a value list called Account_Category in a Table called Expenses. The Account_ Category is assigned to an invoice that comes in along with date and value supplier goods etc.

An example of some of the Account_Category are

Legal / Professional Fees

Loan Repayment - Capital

Loan Repayment - Interest

MISC

Pension Costs / NI Stamp

Phones / Broadband

Property Maintenance

Rates

Stationary / Postage

Supplies / Groceries

Vehicle Costs

 

The report I am trying to get is 

The Category ie Rates 2011 £xxx

                                    2012 £xxx

                                    2013 £xxx

                                    Total £xxx Should be simple but can I do it..no.


 

I can get the category and a total over the whole period for each category and a total spend

Any advice would be appreciated. Thanks David

Screen Shot 2018-02-13 at 11.54.56.png

Screen Shot 2018-02-13 at 11.56.02.png

Share this post


Link to post
Share on other sites
Steve Martino

Can I see your layout in layout mode, showing the parts on the left?

Also you don't need Sort Records by field, just sort records, and you can put multiple criteria in one script step

In sub-summary reports in general, make the sub summary part the field you want to see, and sort by this field. You can have multiple sub-summary parts, and sort by all of them to get multi layered reports, or just sort by one of them to get a more simplified or overview report.

In your case, just guessing, your first sub-summary report, should be by category, and the second sub-summary should be by date, sorting on both in one step. 

  • Like 1

Share this post


Link to post
Share on other sites
Groom
12 minutes ago, Steve Martino said:

Can I see your layout in layout mode, showing the parts on the left?

Also you don't need Sort Records by field, just sort records, and you can put multiple criteria in one script step

In sub-summary reports in general, make the sub summary part the field you want to see, and sort by this field. You can have multiple sub-summary parts, and sort by all of them to get multi layered reports, or just sort by one of them to get a more simplified or overview report.

In your case, just guessing, your first sub-summary report, should be by category, and the second sub-summary should be by date, sorting on both in one step. 

Hi Steve

Thanks for your help on this I have attached the layout as asked.

Screen Shot 2018-02-13 at 15.03.02.png

Share this post


Link to post
Share on other sites
Groom

Hi Steve

Your comments have help me greatly i can now get what I am after with the years running down then listing the catagories but what I am trying to do is have the categories down the left axis and the year along the top as shown but it keeps returning the one year...is this possible. Thanks Dave

 

Screen Shot 2018-02-13 at 15.24.45.png

Share this post


Link to post
Share on other sites
Steve Martino
1 hour ago, Groom said:

Hi Steve

Your comments have help me greatly i can now get what I am after with the years running down then listing the catagories but what I am trying to do is have the categories down the left axis and the year along the top as shown but it keeps returning the one year...is this possible. Thanks Dave

 

This statement is confusing to me, because it sounds like the same thing.

First, make sure you are on list view

Also, your screen shot shows the field at the top as a global end date field, so it will always be whatever is in that value.

Does your report show like this:

2018

   Cat1

   Cat2

2017

   Cat 1

   Cat 2

2016...etc.

If not, show it the way you would like to see it.  Maybe you can attach a file with some sample data.

Share this post


Link to post
Share on other sites
Groom

Hi Steve

Your help has been great I've now got the report and data I wanted, which is just as you have said. What I am trying to do is now in the layout as where instead of

year

Cat

Cat

Summary

Year

Cat

Cat

Summary

I am looking for

                   Year            Year           Year           Year

Cat

Cat

Summary

I hope this makes sense to you and really appriecate you time in schooling me here.

Screen Shot 2018-02-14 at 14.10.03.png

Screen Shot 2018-02-14 at 14.09.50.png

Share this post


Link to post
Share on other sites
Steve Martino

Ah, now were getting tricky.  What you are looking for is call a cross tab report.  You can google 'FileMaker Cross Tab Report', or a Virtual list report.

Here's a Tip file from the great and powerful John Mark Osborne and 2 resources:

http://www.philosophyoffilemaker.com/list.php

https://www.databasepros.com/index.php

CROSSTAB.fmp12

Edited by Steve Martino
additional info

Share this post


Link to post
Share on other sites
Groom
2 hours ago, Steve Martino said:

Ah, now were getting tricky.  What you are looking for is call a cross tab report.  You can google 'FileMaker Cross Tab Report', or a Virtual list report.

Here's a Tip file from the great and powerful John Mark Osborne and 2 resources:

http://www.philosophyoffilemaker.com/list.php

https://www.databasepros.com/index.php

CROSSTAB.fmp12

Hi Steve

Thought you were going to say that...have already started researching it and will attempt in the next few days, its good to have you confirm it, will let you see my finished result!!!

Share this post


Link to post
Share on other sites
Groom
On 14/02/2018 at 10:03 PM, Groom said:

Hi Steve

Thought you were going to say that...have already started researching it and will attempt in the next few days, its good to have you confirm it, will let you see my finished result!!!

Hi Steve Have made great progress with your help but there is now on thing driving me mad. I have the 12 months across the top Jan to Dec but I want the report to be my tax year which is Apr to Mar...any thoughts.??

Screen Shot 2018-02-17 at 17.46.35.png

Screen Shot 2018-02-17 at 17.47.02.png

Screen Shot 2018-02-17 at 17.47.22.png

Screen Shot 2018-02-17 at 17.46.00.png

Share this post


Link to post
Share on other sites
Steve Martino

Your killing me!  Hope your having fun with it :).  I don't know how you have the dates in your header.  Wouldn't simply moving the dates and the corresponding fields give you the look you want?  I see you went with repeating fields so I'm not sure if this will work.

You'll need a calculation for fiscal year to put into your search.  This one comes from the brilliant mind of Comment:

 Let ([   
       year=Year(Del_date) ;   
       dd = Delivery::Del_date ;
       offsetStart = Case ( dd < Date (8 ; 31 ; year ) ; 0 ; 1 );
       start= Date ( 8 ; 31 ; year+ offsetStart );
       offsetEnd = Case ( dd < Date ( 9 ; 1 ; year ) ; 1 ; 0 );
       end= Date ( 9 ; 1 ;year - offsetEnd) 
   ];
end &"..."& start
)

See if this puts you on the right path.

You obviously have to change your fields to match and the months.  I use this for a fiscal year that starts on 9/1 and ends on 8/31

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use