Good morning,
I'm hoping someone can solve my problem...or at least point me in the right direction...
When I say problem...it may be a couple.
Firstly, I have a report which displays a customer's contibution to Costs of an Account, but only the Areas that particular customer contributes towards. A form will filter the Account and a button then pulls up the report for that account and for a specific customer (based on customer number which is found in a drop down).
When the report is run the front page has a sub report which shows the total contribution per area; whilst later pages then break out each area into separate costs.
For example - Office Costs (Area) could include electricity, water, cleaning etc; Whilst External Costs (Area) could include electricity, water, gardening etc; Gym Costs (Area) would include equipment leasing, repairs etc.
If the customer only pays towards the external and the gym...that's all they see. The current report then shows the finances - prior costs, expected costs, current budget and new budget.
I have created a copy of the report that will only show the new budget, but then shows, for each cost in each area, how much the customer contributes; it will also show how much that contribution works out per week. This is all fine.
One problem I'm having is when I try to throw in a formula that details how much of the Area Total each cost equates to. The total for the area is calculated as =Sum[Column4] and this works fine for each Area...and then for the whole Account (the sum of the area totals). However, when I try a simple =[column4]/sum([column4]) as a percentage, it uses the Account total as the sum rather than the area total. It seems to work for everything else...so I can't figure out why it won't work for this.
The other thing I need to try to create is probably another sub report for the first page. I want to be able to pull out some of the key costs for each area, and display thew information detailed in the paragraph above. What I've not decided is whether to show, say, the 5 most expensive costs; or to allow the user to define which costs they want to show. Writing a query to get the core information is easy enough, but how to I get a filter / parameter to display either the top 5 or user defined?
Sorry that it's a little long-winded. I'm not able to upload the db so I'm trying to explain as much as possible. If I need to be more specific, or copy/paste areas of coding, I will do what I can.
Many thanks
Stuart
I'm hoping someone can solve my problem...or at least point me in the right direction...
When I say problem...it may be a couple.
Firstly, I have a report which displays a customer's contibution to Costs of an Account, but only the Areas that particular customer contributes towards. A form will filter the Account and a button then pulls up the report for that account and for a specific customer (based on customer number which is found in a drop down).
When the report is run the front page has a sub report which shows the total contribution per area; whilst later pages then break out each area into separate costs.
For example - Office Costs (Area) could include electricity, water, cleaning etc; Whilst External Costs (Area) could include electricity, water, gardening etc; Gym Costs (Area) would include equipment leasing, repairs etc.
If the customer only pays towards the external and the gym...that's all they see. The current report then shows the finances - prior costs, expected costs, current budget and new budget.
I have created a copy of the report that will only show the new budget, but then shows, for each cost in each area, how much the customer contributes; it will also show how much that contribution works out per week. This is all fine.
One problem I'm having is when I try to throw in a formula that details how much of the Area Total each cost equates to. The total for the area is calculated as =Sum[Column4] and this works fine for each Area...and then for the whole Account (the sum of the area totals). However, when I try a simple =[column4]/sum([column4]) as a percentage, it uses the Account total as the sum rather than the area total. It seems to work for everything else...so I can't figure out why it won't work for this.
The other thing I need to try to create is probably another sub report for the first page. I want to be able to pull out some of the key costs for each area, and display thew information detailed in the paragraph above. What I've not decided is whether to show, say, the 5 most expensive costs; or to allow the user to define which costs they want to show. Writing a query to get the core information is easy enough, but how to I get a filter / parameter to display either the top 5 or user defined?
Sorry that it's a little long-winded. I'm not able to upload the db so I'm trying to explain as much as possible. If I need to be more specific, or copy/paste areas of coding, I will do what I can.
Many thanks
Stuart
