Totaling with dimensions - Financial Reporting

djswim

I'm re-doing a dimension setup right now, but I think I'm going to have to get a bit creative for some of my reporting (or at least to make it as easy as possible for the end user).

Essentially, this dimension represents two things: A business unit and a cost center. Yes, we could theoretically have two separate dimensions for these, but in their case, and given the other dimensions they want, I think this is the best way. Basically the first number of the dimension value represents the business unit, and the next three represent the cost center.

So a scaled down version would be something like this:

1100 - Denver Sales
1200 - Denver Warehouse
1300 - Denver Admin
2100 - Cleveland Sales
2200 - Cleveland Warehouse
2300 - Cleveland Admin
3100 - Miami Sales
3200 - Miami Warehouse
3300 - Miami Admin

You get the idea.

My problem isn't seeing everything for each city, I've got my totaling set up to do that (left it out here for simplicity). What I'm wondering how to do is see "All Sales" or "All Admin".

In a situation as simple as this, obviously the filter would be 1100|2100|3100. The problem I have is two fold:

1. The dimension values are (necessarily 6 digits long)
2. There are a lot of locations

Therefore our filters start looking like 108000|208000|308000 all the way up to the 700000 or 800000 range. Which is too large a filter string (unless there's a trick I don't know about).

Is there a way to do something like *8000 to grab everything that ends with 8000, or some other syntax? I've figured how I can do it with a few subtotals, but if I can keep it cleaner than that I would love to.

Thanks in advance for your time everyone! (These are filters that will be stored in views and account schedules, etc... so they'll only need to be typed a few times)

ShaiHulud

You can use wildcards * (any number of any characters) and ? (any single character) in your filter expressions.

When you know the exact number of the length, you can play around with ? wildcard, so your filter for "grab everything that ends with 8000" would be "??8000"

See the following example, of looking for G/L Accounts of 4 symbols, with 2nd symbol being "1", and 4th one being "0"

ShaiHulud

You can use wildcards * (any number of any characters) and ? (any single character) in your filter expressions.

When you know the exact number of the length, you can play around with ? wildcard, so your filter for "grab everything that ends with 8000" would be "??8000"

See the following example, of looking for G/L Accounts of 4 symbols, with 2nd symbol being "1", and 4th one being "0"

djswim

Ah haha! Perfect! I had been messing around with something like that but hadn't quite gotten it.

Thank you so much!