Is there any way to use dynamic account range feature to get a total by segment 1 of an account number?
Comparing to FR statement designer, we would do this by putting d(acsegval01) in column D.
For example, our account numbers have two segments -- account-department. I want to run the report on a range of accounts in segment one from 42100 to 42300. I want to see one total for every unique account # in segment 1.
If there are three 42100 accounts in 3 different departments I want to see one total.
I'm busy taking a look at this and will get back to you shortly.
I should also mention that I am running a consolidated report pulling in data from about 25 companies. I've been able to achieve some measure of success using the dynamic range tool and then using that data as the basis of a pivot table. It has problems when I bring in all 25 companies though -- works fine when using data from not as many companies. If you can find a better method I would be very happy to hear it.
I'm busy waiting for a license for a 300 environment. I might only be able to get back to you next week.
No problem. Thanks!
Andy, what I thought of trying was to use a static structure code of ACC in your template row and referenced in your formulas to only return Main Accounts, i.e. the account segment in your case. I however don't know if duplicates will be returned or not. You might want to give this a try in the meantime.
I'm not quite sure how this would work. I made an attempt but pretty sure I am doing it wrong. I'm attaching an excel file that shows the formulas I am using in my template row so you can see what I am attempting.
I don't see any attachment. If you like you can email it to email@example.com
I just emailed it.
Just to close off this thread. As discussed, based on your 'flat' account structure, you wouldn't be able to return just the first segment of your account numbers, and even if you could, Dynamic Ranges wouldn't be able to consolidate the segments with the same value.
The suggestion here would be to use a Pivot Table to create the aggregation.
I was having the exact same struggle. Peter, if I set up my Account Structure as ACC-Compny, will Dynamic Range work if I input a static structure code of ACC in my template row? There will not be any actual accounts that have the structure ACC in any of the databases, as all of them will be ACC-Compny. I was just at the Sage Conference in Atlanta, GA and nobody at the SI booth was able to help me with this. I would really appreciate being able to email you on this or the Pivot Table option (I need that one explained a bit). Thank you!
Is your intention also to consolidate accounts with the same account number / segment from the different companies?
Yes, I am trying to consolidate accounts with the same Seg1 from different companies. Unfortunately, because all of the accounts are Seg1-Seg2, the Dynamic Range does not understand how to only include Seg1 and exclude Seg2 for my consolidation purposes. Seg2 defines the company, so I have to exclude it if I am to get consolidated values across companies.