Solved

Cross Tab Reports in CR ver. 2011

Posted on 2013-06-20
9
425 Views
Last Modified: 2013-06-21
Hello all,
I've been using Crystal Reports for a few years, but I'm new to the world of Cross-tab reports.  My columns are divided by month, using the {created.date} and my rows are calculated by {product.type}. (Fyi, We have many product types, but I'm using the group sort feature to condense them down into 2 main products.)  At any rate, I'm trying to find an "average count, per business day, per product type".  I've been able to create a formula that calculates business days per month, but haven't figure out how to apply that to my cross-tab report.  Any help on this subject would be greatly appreciated!  Thanks!
Cross-tab Report
0
Comment
Question by:fragulajim
[X]
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
  • 5
  • 2
  • 2
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39264749
CR2008 allows you to add a calculated column.  Instructions are here

http://scn.sap.com/thread/3308737

mlmcc
0
 

Author Comment

by:fragulajim
ID: 39264823
Thanks, I'll check it out and let you know....
0
 

Author Comment

by:fragulajim
ID: 39264886
Well, I'm not having much luck.  Again, the calculation I'm looking is probably simple for an advanced cross-tab user.  (total orders-per month) / (business days - per month)  Again, I have the equation for business days, just do not know how to tie it all together in the cross-tab format.  Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 100

Expert Comment

by:mlmcc
ID: 39264949
Try creating a formula that divides each order amount by the number of days in the month then using that in the cross tab

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 39265340
mlmcc,

 I think he's looking for an average order count, not amount.


 fragulajim,

 This is something of a shot in the dark, but try creating a formula like this and adding it to the cross-tab (to get a total for each month).

1 / {business day formula}


 FYI, it's the same basic idea that mlmcc was suggesting, but it's dividing the "count" for each order (1), instead of the order amount.  I think that might work (emphasis on "might").

 James
0
 

Author Comment

by:fragulajim
ID: 39265884
James,
Your shot in the dark is VERY close. (closer than I've been on my own so far).  At any rate, it's giving me the average daily order count I was looking for, but for whatever reason, when I plug my {business day formula} in to the cross-tab by itself and select distinctcount I get the exact business days per month however, when I add it using the formula you suggested, it divides everything by "24".... not sure where the "24" is coming from??
0
 

Author Comment

by:fragulajim
ID: 39266224
GOT IT!!  My {Businessdayformula} worked good outside of the cross tab, but inside the cross tab it was calculating average days from the {order.createddate} rather than the {@firstdayofmonth}.  Once I made that change, we were golden!!  Thanks again for everyone's help!!
0
 
LVL 35

Expert Comment

by:James0628
ID: 39266294
Glad that worked for you.  I've seen similar formulas work in a cross-tab, but cross-tabs are weird <g>, so I'm never too sure what's going to work in them.

 James
0
 

Author Comment

by:fragulajim
ID: 39266363
"Cross tabs are weird".... AGREED!  Thanks again!
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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