I'm creating a P&L using Report Designer and need to have it categorised as needed by my client.
I'm noticing that some row sets are not pulling the data. For example I have added a row set "Insurance" with type "Account" and the rule is "22.214.171.124.1 to 126.96.36.199.4 + 188.8.131.52.99".
On generating the report I see that account "184.108.40.206.99" is being omitted and I have checked to ensure that there is data for the specified period.
I also have another one where the rule for Account is "220.127.116.11.1 + 18.104.22.168.1 + 22.214.171.124.41 + 126.96.36.199.42 + 188.8.131.52.2 + 184.108.40.206.3 + 220.127.116.11.8 + 18.104.22.168.11 + 22.214.171.124.12 + 126.96.36.199.13 + 188.8.131.52.0 + 184.108.40.206.22 + 220.127.116.11.2 + 18.104.22.168.3 + 22.214.171.124.4 + 126.96.36.199.8 + 188.8.131.52.4 + 184.108.40.206.41 + 220.127.116.11.42 + 18.104.22.168.43 + 22.214.171.124.0 + 126.96.36.199.31", Is this too long? As the results is zero but there is movements in all these accounts.
One question for you: Did you try 188.8.131.52.99 on it's own to confirm the data comes through?
The formula looks good to me but I have found cases where there are really small things that are not readily visible that cause problems with rules.
Yes I did, it returns no data when I do so. I have also just tried on a blank worksheet and the result is the same. Weird though, Actuals for period 3 is showing -4132.46 but the value in that period is actually 13 678.56. When I drill down only the value for period 6 is showing in the drill down, but its not pulling as a balance for Actual Period 6.
This is good. We have narrowed the source of the issue.
Is your formula retrieving the account balance or the period net change?
I am not clear on your meaning about actuals versus value for the period so the preceding is a best guess based on what I would look for.
If there is no net change for the period and the report is retrieving the net change then that would explain why the account is not included.
The best I can do from here is suggest you compare the account(s) that do produce a value to the account that does not.
I think there is something different about the account the is not retrieved.
Without the ability to see everything it's going to be difficult to assist any further.
I would edit the formula in the formula editor and check each component until you see it return a number. I believe there's something about the formula that is causing your problem.
For example, in the image you included the 99 account is right aligned. Perhaps excel is seeing that value as something that causes the formula to not read it correctly and hence doesn't return a result for you.
If you test the different pieces of the formula manually I think you will find the source of your problem.
I finally figured it out, the way to get it correct is to always have a range. So in my case entering just the one account the formula doesn't work but if I do a range even if the account is the same then the calculation comes out fine.
In my example above I have 3 accounts in the 1st line and the range is (184.108.40.206.1 TO 220.127.116.11.2) and (18.104.22.168.99 TO 22.214.171.124.99).
So I'm sorted, thanks for your help.