I'm am trying to create a report for my client that will give a breakdown of revenue and expenses vertically. I need a breakdown of a specified range of departments with details to the account level so DEPT>Account detail for each department . The report should be able to automatically pick up any added departments rather than hard code. I'm looking the sample report GL!DepartmentalSummary.Sam and it will do much of what I am looking to do expect without the detail to the account level. I have tried modifying this report as well as building my own to no success. Am I missing something?

GL!DepartmentalSummary.sam doesn't have account level detail as far as I can see. It just has a column for Revenue and another for Expenses. To use this sample, just replace the account ranges in cells F9 and H9 with your Revenue and Expense accounts. Place your entire P&L account range in cell B12 to pik up all your departments.

Thanks Andrew. I used that report just as an example for explanation purposes as report that will automatically include new departments as the are added. It does give the departmental summary totals but I need to build a report that will give me account level detail sorted by department without need to hard code each individual department. Essentially a report that goes department to account level detail per department with sum total. We use this report to breakdown costing on individual Capital projects. Each project gets its own department. So a side by side comparison isn't that useful, but a "snapshot" that includes account level details for all active departments separately would be. Can it be done or should I build the sheet by manually including departments as they become active? I have tried different combination of .range .rangedept and the accountgroup and accountgroupdept formulas but can't seem to get to the sweetspot that I am looking for.

Are the account details in the rows or columns? Are the departments in the rows or columns?

If you have a picture of what you are trying to create, or a spreadsheet that you are doing manually, then posting that would be helpful.

here is an example of what i am trying to recreate. They key is the need to be able to pick up new departments as they are added. Thanks again for your help Andrew.

I can imagine doing this with departments listed vertically and revenue & expense account ranges in columns. You could get quite detailed with the ranges. In that scenario you would use a .RangeDept which would auto-expand to include any new departments. The problem I've encountered with having departments in columns is that when accounts are not consistent across departments the account name becomes meaningless.

I have created them vertically but with anymore than 10 projects going the details get lost, plus a side by side comparison doesn't provide any value as the nature of the projects can be very different. Ideally a report that gives a summary of all departments in range with an ability to expand to account level would be the ideal solution. Short of copy pasting and setting account and department parameters, I can't seem to find a solution.

In my mind that is where a statement group is very nice. Have a group that has a departmental summary with details for each department attached. This can be printed to pdf so if the user doesn't want to see the detail, no paper has been wasted. My clients' accounts are not consistent from one department to another so having a single page report that shows detail by account is simply not possible.

Paul:

Are you looking for;

Dept 1
Revenue
Expenses

Dept 5
Revenue
Expenses

Dept 7
Revenue
Expenses

Keep going for all departments....

Then at a future date if Dept 3 is added, you want the financial statement to automatically insert the new department to created;

Dept 1
Revenue
Expenses

Dept 3
Revenue
Expenses

Dept 5
Revenue
Expenses

Dept 7
Revenue
Expenses

It's possible but you would need to be an ultra high expert in either or both of Excel and Adagio FR.

Explaining how to make this work would take a significant effort. Accsys is always willing to take on these types of projects on behalf of other Adagio dealers. Feel free to contact me offline.

That's why i was thinking if a vertical rather than horizontal report could be created, creating a new summary per department with all active accounts detailed(with zeros eliminated) would be useful. That way, the end user could just print the report and any new departments would automatically be picked up, rather than hard coded. You would never have to do any maintenance to the statements that way. We have been using Dakota's Software account changer to standardize the chart of accounts and changing from account to dept account level. The software has done a beautiful job but to fully utilize FR, now we need to build templates for the end user. With 30-50 capital projects and over 100 special projects running at any given point, and multiple internal and external stakeholders, I need to build reports that will meet present and future needs without constant maintenance by providing enough details to each. Summary isn't going to provide enough details and horizontally would be too cumbersome. That is what lead me to this thought process. If i need to create it manually and maintain for my client then so be it, but FR is such a powerful tool, I was sure that it be utilized in this manner.

That is pretty much it Douglas. Only for a specified range. Thus any obsolete would be eliminated and new ones added automatically. Possible?

Paul:

My preference is to always deliver financial reports that just work (new accts/depts. do not require user or consultant intervention). This requires a well designed chart of accounts and dept code structure.

Though the initial cost is higher, in the long run this solution is less expensive.

Where we have delivered this solution before, it's always using dept ranges and excluding inactive depts.

Contacting you now

To me this just looks like a basic P&L where you ask a Statement Group to print the report for every department you have. This would put each summary on a separate page (if printed) or on a separate Sheet (if exported to Excel).

Wouldn't that meet your needs?

Paul:

I might have jumped the gun. Are you looking for multiple depts. per page or one dept per page. If the latter then there is likely a simpler way to do this.

That's what I was trying to say as well.

One page per dept would fill my needs but would make the overall statements rather large and full of white space as many as 150 projects in this case, each being a department. Preference would be all departments print together. I do also need to be able to specify range as well.

You can print all departments into a single PDF or Excel workbook (one department / worksheet) by using a Statement Group. Statement Groups are described in this video:

Andrew:

Just to make sure that I haven't missed some new trick I've re-reviewed the financial reporter features and still wasn't able to come up with an easy solution to create a financial report that meets the following criteria;

1. Separate mini statements for each dept.
2. One after the other.
3. Multiple to a page.
4. All on one worksheet in Excel
5. New depts. automatically inserted into the correct order.

Basically the solution as I set out in an earlier reply.

I'd be very happy if you could prove me wrong, but as it stands I don't see a solution in Statement Groups.

Hi Doug,

Sorry for the delay, I've been pondering this request.

I've never seen a request of this nature, and thinking about it, I'm not sure that a worksheet with 15,000 rows is any improvement on a workbook with 150 worksheets or a PDF with 150 pages. The workbook and PDF would be easier to print. and all formats can use ctrl-f Find to quickly locate the information for a given department.

You can build a statement formatted to meet conditions 1-4 in your list, but the user would have to maintain the statement when new departments were added or departments decommissioned. This could be accomplished through a simple cut and paste operation, but it cannot be done automatically. I expect that pagination would immediately become an issue in a statement laid out this way.

We're getting close to Adagio Wish season. Perhaps others will jump in and express a desire for this type of automation.

Andrew:

The solution I propose is already possible in the Adagio FR, it's just not easy to accomplish. So if there is some demand for this type of reporting, an easier way to accomplish it might be useful.

Where we have done this we have turned a 180 page f/s into 30 pages. Though our client doesn't send this f/s to Excel, I don't imagine that 180 worksheets would be very manageable but 30 would be.

I cannot imagine any method of satisfying 5 without resorting to user edits or a very complex VB macro in Excel.

Andrew:

Just an equally (to VB in Excel) complex formula in Adagio FR.