Prev: what do i do if product key i hav got is being shown invalid
Next: Data not downloading to .xls file but it is in the database
From: JiiPee on 18 May 2010 04:26
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
 | 
Pages: 1
Prev: what do i do if product key i hav got is being shown invalid
Next: Data not downloading to .xls file but it is in the database