J
JiiPee
I have a complex query that is based on ERP data exports. I am supporting a
multi-dimensional matrix organization. A pivot table would be a perfect tool
for analysing sales, margins, and changes for different business units. I
have been using Excel pivots a lot and also in this case started with excel.
However, the amount of data and usability of reports limits the possibility
to distributing information in excel.
With Excel I was able to all the tricks especially using the calculated item
functionality, which is missing from the access pivot component. What I would
like to have as results, is the following:
Page filters (could be used also as column fields):
Business unit, Period, Product category
Row fields:
P/L accounts e.g.
- Net sales
- Cost of goods sold
- Administration costs
- Capital costs
Values:
- Value in local currency
What I would like to have is calculated Gross margin after Net sales and
Cost of goods sold. In addition total results after all accounts. (this is
heavily simplified example though).
I can create the above Gross margin using grouping, which is not the nicest
solution, but ok still. However, getting a Gross margin as % of Net sales
seems to be impossible. I've tried without success creating a caculated Total
and detail fields, which would calculate the percentage. I've been trying to
figure out a way to precalculate something in the underlying query, but did
not figure out a clever way.
The query results are roughly the following.
Period - P/L Account - Value in currency - Business unit - Product category
multi-dimensional matrix organization. A pivot table would be a perfect tool
for analysing sales, margins, and changes for different business units. I
have been using Excel pivots a lot and also in this case started with excel.
However, the amount of data and usability of reports limits the possibility
to distributing information in excel.
With Excel I was able to all the tricks especially using the calculated item
functionality, which is missing from the access pivot component. What I would
like to have as results, is the following:
Page filters (could be used also as column fields):
Business unit, Period, Product category
Row fields:
P/L accounts e.g.
- Net sales
- Cost of goods sold
- Administration costs
- Capital costs
Values:
- Value in local currency
What I would like to have is calculated Gross margin after Net sales and
Cost of goods sold. In addition total results after all accounts. (this is
heavily simplified example though).
I can create the above Gross margin using grouping, which is not the nicest
solution, but ok still. However, getting a Gross margin as % of Net sales
seems to be impossible. I've tried without success creating a caculated Total
and detail fields, which would calculate the percentage. I've been trying to
figure out a way to precalculate something in the underlying query, but did
not figure out a clever way.
The query results are roughly the following.
Period - P/L Account - Value in currency - Business unit - Product category