Crystal Reports (Assistance with Calculated Members in a Crosstabs Report)

Posted on 2012-03-27
Last Modified: 2012-03-28
Here is my Crosstab...

The first column is HOURS (1 = pm) (so on...)

Next is a SUMMARY which is Distinctcount(BaseID)
Next is a SUMMARY which is Distinctcount(Date) *Total number days within period call for

Does anyone know how to use an embedded summary or a Calculated Member to add another summary to this crosstab report that will do the following:

If Hour = 1 then 709/21  distinctcount(BaseID)/distinctcount(Date)
If Hour =2 then 725/21

I've got to find a way to calculate this average to report on a chart.
Total      5,281      21
1      709      21
2      725      21
3      698      21
4      689      21
5      267      21
8      70      18
9      311      20
10      500      20
11      637      21
12      675      21
Question by:wshcraft70
  • 3
  • 2
  • 2
LVL 100

Expert Comment

ID: 37772711
What version of Crystal?


Author Comment

ID: 37772725
LVL 100

Expert Comment

ID: 37772746
I don't think it is possible in CR XI.  CR2008 added the capability to have calculated columns in a cross tab.

In CR XI I think you will have to build a  manual cross tab.

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.


Author Comment

ID: 37772795
manual cross tab?

You and James have helped me in the past with this darn report..  I continue to make progress but just can't get the averages..  :(

See attached report and .mdb access database..

Although currently my report is pulling from a SQL datasource..

Understood if it is simply not possible.  But so discouraging.. Especially since the data is RIGHT there..  :D
LVL 35

Accepted Solution

James0628 earned 500 total points
ID: 37775240
The problem that I keep running into is that you're interested in the totals for each hour, but the report isn't grouped by the hour.  That is one way that a subreport (as suggested in the previous question) can help.

 I'm attaching a version of your report with a subreport that produces the chart.  It seems to be working, but, obviously, you need to check it and see if the results are correct.  I created the subreport by copying your report, removing all the stuff I didn't need, changing it to just group by Hours, creating a formula that calculates the average from the distinct counts for the Hours group, and using that formula in the chart (with the "Don't Summarize" option checked).  The chart does not use a cross-tab.  FWIW, I imagine that it could use a cross-tab.  It's just not necessary.  You had used "specified order" in the cross-tab to get the hours in the correct order.  I just did the same thing in the chart.

 I put the subreport in a new section.  In theory, you could just delete your old chart and put the subreport in its place.  The only problem that comes to mind would be if the chart could get wider (included more hours, before 8 AM or after 5 PM), in which case it would be limited by the subreport's width.

 FWIW, you could theoretically change the subreport to only include distinct records (open the subreport in the Designer, go to File > "Report Options" and check the "Select Distinct Records" option), and then you wouldn't need to use DistinctCount in the subreport formula.  But when I tried to add the "Select Distinct Records" option to the report from your previous question, CR wanted to reconnect to the datasource, and rather than go through that and change the datasource, and then have you possibly have to change it back, I just used DistinctCount in the formula and that seems to be working.


Author Comment

ID: 37776716
James....  You are simply a Saint to actually put this much time into this report in an effort to help me.  I just can't begin to thank you enough.  I wish I could give you 1 million points..

FWIW.......  Which I also just learned the meaning of..  lol...

THANK YOU SOOOOOOOOOOOOOO MUCH for all your hard work..

LVL 35

Expert Comment

ID: 37780311
You're welcome.  Glad I could help.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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