Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2012-03-27
Medium Priority
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
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
  • 3
  • 2
  • 2
LVL 101

Expert Comment

ID: 37772711
What version of Crystal?


Author Comment

ID: 37772725
LVL 101

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.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 2000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

610 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