Solved

Divide cell in crosstab with a total within the crosstab

Posted on 2010-11-08
1
1,041 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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