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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What version of Crystal?

wshcraft70Author Commented:
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.

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

wshcraft70Author Commented:
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
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wshcraft70Author Commented:
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..

You're welcome.  Glad I could help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.