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.

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


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 34

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 34

Expert Comment

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


Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

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 …
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

777 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