Access 2007 pivot - how to calculate eg P/L marings both in curr &

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

 - 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
5/18/2010 8:26:01 AM
access 16762 articles. 3 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 2


Similar Artilces:

Exporting CRM Opportunity List to Office Excel 2007
We have CRM 3.0 Server. Our users are using CRM Web Client to access their leads, opportunities, quotes and orders. Using the Export toolbar button in CRM, users who have Office 2003 installed on their PCs are able to export opportunity lists to Excell perfectly all right, however users who have Office 2007 installed are not able to do that! Is this a limitation in CRM 3.0 ? Is there a workaround (If any)? Any possible risks when using or applying this workaround (If any)? "luai7" <> wrote in message news:6CADFDC5-8439-4B5F-955A-D6DBF...

Publisher 2007 Merge Print Screen
This only happens in Publisher 2007. I do merges: in Excel, in Word, & in Publisher. When the "print" screen comes up in Publisher, the screen is so large, I have to push up as far as I can, and there's just a sliver of the print button on the bottom - can't even see the "print" word on the button. However, my cursor does react with it, so I do print successfully. I just would like to know if there's a way to make this one screen smaller. There's no arrows at any of the corners, so I can't squeeze it down. Thank you! lewzan wrote: > When the...

How do I use the Outlook Address Book in Word 2007
Our company used to use Groupwise along with Word 2007. I could do individual envelopes using the address book on Groupwise. Now we have Outlook 2007 and my address book was migrated to Word. But when I try to do an individual envelope and click on the Outlook Address Book, it says there are no entries in the address book. Do I have to re-do all my addresses in Outlook? Or is there a way to access this address book? Do you have a choice to use the Outlook Contacts Folder? Try that instead if you do. Also, ensure your Outlook address Book Service is installed. -- Milly Stap...

Pivot Table- Column 2 sort
How can I sort a Pivot Table by the 2nd (or any other) column? I've selected a cell and hit the A-Z Z-A Sort button, but it only works on column 1. Thanks! Right click on the column and select Field Settings | Advanced to access the sort options... -- HTH... Jim Thomlinson "srpd" wrote: > How can I sort a Pivot Table by the 2nd (or any other) column? I've selected > a cell and hit the A-Z Z-A Sort button, but it only works on column 1. > Thanks! Difficult to answer without knowing the structure of your underlying data and what, exactly...

Exporting Money 2006 to OA 2007--No New Fiscal Years
When I attempt to import MS Money 2006 data to OA 2007, none of the bank transactions (~400) migrate, and the error log tells me: "The financial date of a document must be within an existing open fiscal year." In OA 2007, I find that only fiscal year 2002 has been created...hence the error message. Suggestions? -- Charlie Find an OA newsgroup? Not trying to be flippant, but there's next to no OA experience that has been evidenced in this NG. "cwfox" <> wrote in message

Outlook 2007 doesn't understand vCalendar entries any more !?!
I occasionally need to copy/paste calendar entries from another calendar application into my private Outlook. Until recently - using Outlook 2002/XP - this worked fine. Since I switched to Outlook 2007 this does not work any more. Instead of pasting a new appointment I only get a new appointment with no title, no date, no other field filled except the comment field which contains the entire (and - as far as I can see: valid!) vCalendar entry. Can one somehow teach OL2007 to accept and properly interpret such entries? Michael I just learned that I can paste that calen...

Converting database from Access 2002 to 2003
I am upgrading a pc from access 2002 to 2003 and an Access database I am using is not recognizing a field in 2003. Below are details to find where I am stuck: There is a form "FrmMembers" in Arbor.mdb and you look at the buttons "Print Acknowledgement" and "Print Ack Env". The buttons try to print a report that in 2003 just displays errors. If you go to the design view of "FrmMembers" and look in the field "PKID" there is an error for "Members.PKID" saying "Invalid Control Property: Control Source" "No Suc...

Word Pad 2007 Won't remember Font choice
WordPad 2007 on Windows 7 RC refuses to remember the font that I choose. I can't see how to make a particular font default either. It constantly reverts to default font which I don't like as it is hard to read. I can change the font size and that stays changed. I am using WordPad to copy excerpts from web articles and each time I copy another excerpt and place it in the open document the font reverts from my Verdana choice to the default one. I recall having this same problem on Word 97 and my current Word 2002 on other machines. I can't remember how I fixed the p...

Excel 2007
Hi, I wanted to post a fix to a common problem I've seen. There are many posts on the internet concerning this but I've only seen the true fix once. Here's the problem, someone installs Office 2007 and then when they try to double click on an Excel file, it opens Excel but the Excel file itself does not open. So, you are left looking at the Excel 2007 screen, but it is blank with no workbooks open (not even the default one that usually opens). Here is the official Microsoft solution: - however, this only fixes about 20% of the case...

OWA login fails with Error: Access is Denied.
We are in the process of moving from one exchange server to another one, but having some troubles with OWA and login. On the first exchange server everything works fine with OWA, but on the second server OWA login fails after three atempts with "Error: Access is Denied." I have tried to compere settings on the two servers, but have not found differences. If I move an account from 2.server to 1.server OWA works, if I move it back OWA stops working. Reading email with Outlook works fine. We have Windows2003Server with sp1 and Exchange 2003 with Sp2 on both servers. The only difference ...

Automating Excel from Access
From Access I'm trying to set the vertical alignment for all cells of an Excelfile to 'Top': Dim XLApp As Excel.Application Dim XLBook As Excel.Workbook Dim XLSheet As Excel.Worksheet Set XLApp = New Excel.Application Set XLBook = XLApp.Workbooks.Open("c:\Test.xls") Set XLSheet = XLBook.Worksheets(1) XLSheet.Activate XLSheet.Cells.Select With Selection .VerticalAlignment = xlTop End With XLBook.SaveAs ("c:\Test2.xls") Set XLSheet = Nothing XLBook.Close Set XLBook = Nothing XLApp.Quit Set XLApp = Nothing but I get the message objetvariable or blockvaria...

Office 2007 and Vista
I am contemplating on buying a new updated desktop computer that has vista as an operating system, what I am worried about is the ability to use my current Office 2007 which is installed on a PC with XP on the new PC with vista or am I going to have to buy a new version of Office? Jim Hello Jim, Vista 32 bit or Vista 64 bit? With Vista 32 bit no issue but with Vista 64 bit I would certainly go for Office 64 bit. Regards, Bernd One computer. To use Office on both computers you will need to purchase another copy of Office. Gord Dibben MS Excel MVP On ...

Microsoft 2007
I am trying to share a workbook - Toolbar, Review/Share Workbook - Each time, I get a message, the file is no longer shared and will not let me save. Any thoughts? -- Patty I think another person of your shared workbook might have removed you from the sharing. Otherwise the file has been moved to some other location. Save the current file in your local system and try to open the Original File from its actual location. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Patty" wrote: > I am tryi...

Creating forms in Access or Excel that link to Visio Documents
Does anybody have any experience with creating forms in access or excel that are integrated with Visio? How would I go about doing this? Access by definition can store 'objects', which can include visio drawings. If prompted, don't select display icon a.) create table add oledb field add text field for name b.) open table select oledb field and right click => insert new microsoft visio edit the new drawing and close it c.) create a form based on new table drag the oledb field onto the form d.) open the form what are you attempting to do? al &quo...

How do you calculate persentages and formulas
I am trying to calculate persentages on excel and i am also trying to work out formulas for different cells.Can anybody help. cheers F Calculate percentages by answering the question "this as compared to that": if "this" is in cell A1 and "that" is in cell B1, the percentage calculation is =A1/B1. As for "formulas for different cells", please give more information, and we'll be glad to help! We just need some more specifics to work with. ...

Calculate How the Number of Specific word.
Valid Expired Valid Valid Expired Is there any way of how can I calculate the number of a word in a column?. For Example I want to Calculate How many [Valid] in Column A. Assume that you are having the below values in A Column Row / Col A Col Row1 Valid Row2 Expired Row3 Valid Row4 Valid Row5 Expired CHOICE 1:- Copy and paste the below formula in B1 Cell =COUNTIF(A:A,A1) Copy the B1 cell and paste it to the remaining cells of B Column based on the A Column Data CHOICE 2- Copy and paste the below formula in C1 Cell =A1&" "&COUNTIF(A:A,A1) Co...

Calculating number of days in a quering
Can I use Min and Max functions in a query? I tried to build a query where I had the fields or columns under neath. In the Column NUMOFDAYS, I tried to write an expression but it just gives me an error saying; Wrong number of arguments or something like that. Let's say I wrote a simple expression; NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function. - it would give me an error. (I even added Group by and Expression). What do I do wrong? I want to find out how many days sick in January (see FROMDATE TODATE ) SICKDATE OKDATE FROMDATE TODATE ...

Hello. I am running an environment with Exchange 2003 and 2007. Our message size limits are 50MB and have worked fine until 2007 was installed. Now, any users on the 2003 box have only a 10MB limit, even though it says 50MB in System Manager. I have also set the parameter on the command line on the Exchange 2007 server, but no dice. Users on the 2007 server work fine. I'm stuck ... any ideas? Have you checked message size limits on the Connector? Are you using an Edge server as well? -- Bharat Suneja MVP - Exchange NEW blog location: ---...

Developer Extensions for Access 2003...
Alright, This is one massive run-around. I have Office 2003 Complete. SO I have Access 2003. I have Visual Studio 2008 Professional. I downloaded the VSTO for 2005 which is supposedly the package for these Access Developer Extensions. When I went to install it, it only gave me the option to repair or uninstall. I looked through and found the folder where it updated during the repair process, (under Program Files\Common Files) but the only executable is install.exe! Where is the darn thing? I'm trying to set up my system so I can finalize the development of an Acce...

Access 2007 Switchboard buttons stop working
Environments tested: Windows7, WindowsXP, Access 2007, Access 2003 Access data project (.adp) run in development mode in all tests. SQL Server 2005 back-end database. Trusted locations set up for all tests. The trusted location is the folder where the .adp resides, sub-folders included. Default file format: Access 2002-2003. I have a .adp application with a standard switchboard form as the main menu navigation. The buttons on the main switchboard form are used to launch forms, stored procedures, macros, and other child switchboard forms. The switchboard navigation buttons a...

collapse a specific mail folder from a macro in Outlook 2007
Hi, My Outlook 2007 archives emails to a "archivel" folder tree structure that is parallel to my mailbox folders. I've written a macro that switches between a folder and its archive (see below) As a safety measure, I want the "archivel" folder collapsed altogether when I switch back to the non-archive folder, so that I don't confuse its folders with the normal ones - any Idea how I do this? Thanx in advance. ---------------------------------- Sub SwitchBetweenCurrentFolderAndItsArchive() Dim names As Collection Set names = New Collection Dim cf As Folder Set cf =...

Data Validation in 2007
Is there a way to make the Data Validation dropdown list dispaly descriptions but when you select one it puts in the corrosponding ID instead? Example: Name: Phone # Smith 360-482-4747 Bowers 360-482-7878 Gates 360-482-8877 So when I'm in the cell and hit the drop down and Select "Bowers" - it actually saves the phone number "360-482-7878" instead of "Bowers" Not directly but in an adjacent cell you could use VLOOKUP to pickup the phone-number best wishes -- Bernard V Liengme Microsoft Excel MVP r...

2 part protection in Word 2007
Can you proctect a single word doc on two levels? One level allowing users to fill in the forms fields only. Second level allow track changes to only a certain part of the document. When the user fills in the form fields, track changes does not show it as a change but if a user makes any changes to a body of text, it tracks the changes. Thank you for the wisdom! No, Word's protection mechanisms aren't that selective. Besides, if you're using a protected form, the user can't edit anywhere except in the form fields unless they turn off protection. -- Regards,...

VBA in 2007 interface programming question.
I am looking to do 2 things: 1) Capture data typed into the cell as it's typed before Enter is pushed 2) Create a box under the cell(like when you start to type in a formula and it provides the formulas that match what you type) Can someone point me in the right direction on what I should be looking for and I can figure out the code from there. Thanks! Macros do not run when you're in edit mode. You's need to position a textbox over the cell and have the user type in that. Maybe something here might work for you: ...

access database 03-16-10
I have created a siimple database project file but everytime I open or close my database, another file is created, ie I will see db1, db2 or db3 appearing in the same folder as my created database and of the same file size. Please help. When you save, save it with the same file name or it will make a new one. -- Milton Purdy ACCESS State of Arkansas "Josephine Ng" wrote: > I have created a siimple database project file but everytime I open or close > my database, another file is created, ie I will see db1, db2 or db3 appearing > in the same folder ...