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.