Need charges summary by month report

[mcbsys]

I'm using CommitCRM 5.2.0.3.

I've been trying to create a report that summarizes charges by account, contract name, and month.

My main client has two contracts each month. The contract numbers increment, but the names are always the same:

MSP Monthly Maintenance Contract
Pro-Bono Work

I need to see a list of all MSP charges, one line per month, followed by all pro-bono "charges" by month.

I copied the the Itemized Account Charges Summary report and changed the groups to show as follows, with corresponding changes to the description and total lines:

Charges - Account
Charges - Contract: Name
Charges - Contract

The problem is, the report is not sorting and summarizing on the group values. It goes back and forth between the two contract names, seemingly at random, giving me a 5-page report when it should be 2 pages. Also, the Contract Codes are not sorted. I wonder if it is somehow "stuck" on the sort and break sequence of the original report.

How do I get the report to sort and break by the groups that I specify?

Thank you,

Mark Berry
MCB Systems

[Support Team]

The way you should do this is base your report on a Charges List report. This report only has basic sorting constraints. Then, you should build your groups within the report designer. This, however, breaks on data change per group, so it all depends on how data is flooding into this report - this means that you need to sort your report by the fields you actually grouped the report by, and using the same order. When generating such a report click the 'Sort' button and select the fields to sort by (the order is important).

Hope this helps.

[mcbsys]

Thanks for your reply. Grouping doesn't make sense without sorting, so when I did not find Sort as a design option, I assumed that the Group would automatically determine the Sort.

How do I set the Default Sort for the report? Surely I don't have to re-set the Sort every time I run it?

Also, why is the Sort button grayed out for the Itemized Account Charges report?

[Support Team]

Some of the system reports have predefined sorting options that cannot be overwritten or else it would have broken the report. In regard to selecting the sorting fields, unfortunately it cannot be saved and you'll need to select it during every session where you need to run this special report that you customized.

[mcbsys]

Contract Name is not available as a sort field so this will not work.

Hard to believe I can't generate a historical summary report by account and contract.

Can I use Crystal Reports against this data? How?

[Support Team]

Please have a look at the 'Detailed Account Charges by Contracts'. You may need to customize it to remove the detailed information about Charges, but it may work for you.

Besides, sure, you can use Crystal Reports and access the database direct (read-only) with our ODBC API.

[mcbsys]

Thanks. Unfortunately it looks like that report only sums by month, not by the "master" contract name. It will be helpful, though, if I decide to manually create a spreadsheet, which it seems might be the simplest solution.