Jump to content

Group by month


Recommended Posts

My first post on FM Forums so be gentle and don't flame...

I'm desperate to ditch MS Access and a virtualised window on a Mac. I've created a new DB from data exported from Access - that's all good. I've got lookup working and summary fields working. I can also generate month and year fields via a calculation from a date field.

But for the life of me, I cannot get the report to group *all* records from a given month in one block, no matter what I try. Instead, the report insists on grouping by day.

The attachment 'Table' shows the table I've created. 'Report' (excuse the redactions) shows the problem - column 1 is the invoice date, 2 is the client (redacted), 3 is the item sold, 4 is the invoice number, 5 is the gross (redacted), 6 the VAT (redacted), 7 the net (redacted), and 8 the date the invoice was settled.

I saw one post where a value list was set up for each month (see Mr Vodka in the link below) but when I look at the example I cannot see how to deploy the value list in the example he gives.

So... quite simply, how do I tell the report to lump, say, all invoices for April in one chunk to subtotal, and so on for other months?

 

 

Report.thumb.png.a39f360f1d5a3d83b8fd5efcf9f60bf7.png

 

Table.jpg

Report_Date.fmp12

Link to post
Share on other sites
23 minutes ago, adambern said:

I cannot get the report to group *all* records from a given month in one block, no matter what I try. Instead, the report insists on grouping by day.

I clicked the [Month Report] button in your file and got a report grouped by month. The report is flawed in that it groups together all records from the same month in any year, and it does not display a sub-summary value for each month - but before getting to that, I would like to understand what is the main issue here.

 

Link to post
Share on other sites

Thank you for the quick response but the file Report_Date is not mine, it's the file I downloaded to see how the value fields work. Unless I'm being particularly dim - it happens - I cannot see how the value field has been deployed.

As you say, the button on the sample table works... I just can't see how!

The main issue... on the report *page* that I uploaded (with the redactions), is that I can extract month from the date field. But when I try to group (via inserting a part) to get a subtotal per month, I get subtotals per day.

Thank you for your attention.

Link to post
Share on other sites

It works by sorting the records by a field that returns the same value for all records in the same month, then showing them in layout that has a sub-summary part associated with the same field. As I said, there are flaws - but let's start with that.

 

Link to post
Share on other sites

But isn't that what I've done? Month is the only field in the part and it returns (say) a load of 'April's. The problem is that FM is still picking out the individual days.

See attached.

Screenshot 2020-09-15 at 17.03.27.jpg

Link to post
Share on other sites

What do you mean by " picking out the individual days"? It displays individual days because your layout has a body part. Does it not group correctly the individual days by the month?

Link to post
Share on other sites

It's grouping by 1/4/2020, 2/4/2020, 3/4/2020 and so on and subtotalling each. I'm trying to get the report to put all of the results of (say) April in one block, ditto for May...

So, dare I ask, how would you configure the report in my last post?

Link to post
Share on other sites

I don't know how to answer that, because you already have a working (sort of) demo, yet you are unable to reproduce it. Why don't you post your file (preferably a copy stripped down to the bare minimum necessary to show the problem)?

 

Link to post
Share on other sites

I am glad you got it sorted (pun intended, sorry). Just note that if you sort by month name (or month number), you must also sort by the year. Otherwise you'll get the same problem I pointed out earlier.

My preference is to calculate the date of the first day of the month and sort/group by that. Then it doesn't matter if you also sort by year. And you don't need a value list to get the correct order.

 

Link to post
Share on other sites
11 minutes ago, adambern said:

how you'd construct a calculation for the date of the first day of the month?

Quite simply:

InvoiceDate - Day ( InvoiceDate ) + 1

Set the result type to Date, so that you can easily format it to display as "September 2020" or any other format you prefer.

 

Link to post
Share on other sites

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
  • Who Viewed the Topic

    2 members have viewed this topic:
    alanf  imcfresh 

×
×
  • Create New...

Important Information

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