Solved

Divide cell in crosstab with a total within the crosstab

Posted on 2010-11-08
1
1,030 Views
Last Modified: 2012-05-10
I have attached an image of what I am working on, please refer to the image for the example.

I have a crosstab that is grouped by quarter and employee title. The 3 summary fields are $ of accounts, # of Accounts, and distinct count of employee title. i want to to create a new cell that takes the distinct count of the employees and divides it by the of $ of accounts.

Example from the attached image: The # of PB's for Q12008 is 2 and the total for the PBs' is $16,860 so I would want the new cell to display $8,430.

Also would it be possible to take the $16,860 and divide it by the total for the row ($18,120) to show 93%?

I am using version 2008.

 crosstab
0
Comment
Question by:wilpitz
1 Comment
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 34091985
One way to do this would be to use saherd variables and pass teh values around teh crosstab

To calculate the Average $ of accounts follwo these steps

1. select the $ of accounts summary field -> right mouse click -> format field -> common tab
open the tooltip text conditional formula (x->2) -> add in formula
Shared NumberVar Rev := CurrentFieldValue;

2. Select the # of accounts summary field  and add a tooltip text formula
Shared NumberVar counter := CurrentFieldValue;

3.in the crosstab expert add in the $ of accounts summary a second time at the bottom of the summary list, right mouse click -> format field -> common tab,
open the display string conditional formula (x->2) -> add in formula

Shared NumberVar Rev;
Shared NumberVar Counter;

If Counter <> 0 Then
    Cstr(Rev / Counter, 2, ',')

HTH
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question