Tips and tricks

Slice and Dice

XLstatements is, of course, a very different tool from FRx or MMR (the doomed “replacement” for the dead FRx). XLstatements doesn’t have “building blocks” from which reports are made, so no Row Formats, Column Layouts, or Reporting Trees. If you really miss these concepts, then you can always copy the “row” parts (the columns of cells containing the account codes and descriptions) from one worksheet into another. For the column layouts, it’s easier to just enter the XLstatements functions (e.g., PTDBAL) than it would be to try and replicate Column Layouts. The Reporting Tree, however, is replaced by concepts that are very different and, we think, better. If you are using Reporting Trees for multi-company consolidations, then it’s much easier and more straightforward to use XLstatements functions and I won’t go into that. For reporting at the subaccount level, however, XLstatements has Structures tables.

Although we point out that the Insert from SL form isn’t necessary to create an XLstatements report, it certainly does make things easier. One feature is the ability to automatically create dropdown lists for entities like companies, ledgers, and subaccounts. If you check the Dropdown List option before inserting any of these entities, the cell used will have a dropdown list, with the first choice being All. If you also use the Create Description option, which I certainly recommend doing, the dropdown will be on the description cell. The first time you insert an entity as a dropdown list, a worksheet named Structures will be added to the workbook. The Structures worksheet has the Excel tables used for the dropdown lists. For companies and ledgers, this is very straightforward (although some of the following tips are still applicable). For subaccounts, things get more interesting.

If you used Reporting Trees to create reports filtered by subaccounts or subaccount segments, then you will want to use Insert from SL to create the Structures worksheet with tables for this. If you choose Subaccounts, a table like this will be created:


There are some nuances about this table that I touched on in another blog post. Note that is it pre-filtered and it has a Description column to use with the dropdown. By choosing Sub Segments, you can have dropdown lists made for one or more segments. (To select multiple segments, as shown here, use Crtl-left click.)


Here is the Structures table created by choosing the above segments:


Please note several things about this table. Because multiple segments were chosen, there is no one Description column, but instead an IDs column that will be used as the description dropdown. This is because each ID description (entered into the Flexkey Table Maintenance screen in the Dynamics SL Shared Information module) can be up to 30 characters, so, with multiple segments, the concatenated description could get very long (we have also seen many sites where that screen hasn’t been used so the descriptions would be blank). Separate columns for the descriptions are included in this table, though, so one can refer to this. Also note that, unlike the table for subaccounts, the filter on IDs is not being used (Select All is checked). This is because only combinations of the segments that have been used are included in the table. Unlike subaccounts, where we include all that have been defined but filter out any that have never been used, we instead list only combinations that have been used. Including all possible combinations would have gotten unwieldy; think about choosing three segments with 30 IDs each—that would be almost 30,000 (31 cubed) rows! The list shows any combinations that exist in active subaccounts or that have been used in posted transactions. This does mean that the table could be missing values if subaccounts have been added or used since the table was created. It’s easy to refresh the Structures tables by just choosing to insert the dropdowns again. Should XLstatements automatically refresh such tables in the next version? Please leave a comment with your opinion.

I’m going to go back to the subaccounts list just to simplify things, but you can use the following tips with any Structures tables.


Let’s say you always consolidate the two subaccounts that have the same name in the demo database: Sales Administration-A. Note that the actual subaccount codes are 02-230-AA-00-00-1 and 03-000-AA-00-00-1. You can edit the table to change the first column value to a list: 02-230-AA-00-00-1;03-000-AA-00-00-1 and delete the row I didn’t edit.


I could also change the description and see the effect on the dropdown list:


Note that the description here can be longer than the 30 character limit in Dynamics SL. So you can edit these tables to make them more usable; just do not change the Print or List columns. Those are explained in the other blog post. Any changes you make will be overwritten if you do refresh the table, though.

The subaccount structure lists are used for more than just the dropdown list, of course. The structured reporting (“Report” on the XLstatements menu) and budgeting also require them to create separate workbooks or worksheets for each entity in the subaccount dropdown list. Although this is useful when budgeting by subaccount or subaccount segment(s), e.g., departmental budgeting, we recommend using compartmental security rather than structured reports for internal reporting: that way you will never again need to schedule, generate, or distribute such reports.




2 thoughts on “Slice and Dice

  1. Pingback: Missing Pieces
  2. Pingback: Got Formula?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s