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.

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.


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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now