Home / Tech Tip / Crystal Tech Tip / Crystal Reports – In Row (Detail) Charting

Crystal Reports – In Row (Detail) Charting

Question: Wouldn’t you like a way to visually show/compare your data right at the detail-level?

Answer: With a little preparation and some simple formulas, it can be yours.

Explanation This Tech Tip uses the Customer Table from the Sample Database that comes with Crystal Reports. Also, I am working with a percentage bar chart. Most of what is explained here works with counts as well.

  1. Steps Choose a field or create a formula to display in bar chart form. In this example we will be showing the percentage of the customer’s “Last Year’s Sales” when compared to the whole. The formula for the “Percentage” is:
    {Customer.Last Year’s Sales} % Sum({Customer.Last Year’s Sales})
  2. Create a new formula to display the bar. The trick here is that the bar is just blank squares added together. The solid square is one character in just about any font. To display it, use the ChrW() function. To put several of them together in a string, use the ReplicateString() function. For our percent bar, the numbers range from 0 to 100. So, we don’t need to worry much about space. We can use one square for each percent. The formula for the “Bar Chart” is:
    ReplicateString(ChrW(9608), Round({@PctOfTotal}, 0))
  3. Place the new formula field in the Detail section where desired.

At this point, you are probably seeing something that doesn’t resemble a solid bar. Also, it is most likely too long to fit where you need it. It the example above, I am using Arial 10 pt font. In order to show a bar equal to 100 of those, I would need over 6 inches of space. So, now we have to shrink it.

  1. Shrink the length of the bar. We still want the height of the bar to be 10 pts. So, lowering the font size will not work. Here we need a little math. Since we want a chart that can go up to 100, we need to fit in a possible 100 characters. If we shrink it down to 1 pt, we can do this in 100 pts or about 1.33 inches. Using 2 pts per character would double the space requirements and so on.
    • Right-click on the field and choose Format Field…
    • Click on the Font tab.
    • Change the Character Spacing Exactly: value to the number of points desired. 0 means to leave the font the way it is. Here I choose 1, which will shrink the width of the font to 1 pt.
    • Click OK to close the Format Editor.

 

Now, your bar should look solid and it should have shrunk a lot. The only thing left is to make the field the appropriate length.

  1. Change the width of the field. As noted above, we want a field that can hold 100 characters. We now have the characters spaced at 1 pt each for a total of 100 pts. This is the equivalent of 1.33 inches.
    • Right-click on the field and choose Size and Position…
    • Change the Width to 1.33.
    • Click OK to close the Object Size and Position screen.

You’re done!

So what happens if:

  1. What if my percentage is between 0 and 1? When creating your bar formula, multiply the percentage by 100 or any desired amount. Just be mindful of the maximum bar size and choose your width accordingly.
  2. What if I am using something other than Percent like Count or Sum? Here, you want to determine the maximum number that can be returned by your query.  This is where you need to know your data. If your numbers are generally low, then you can multiply the character by a large factor like 30. If your numbers get rather large, you will want to divide that number by a factor before using it in the ReplicateString() function.
  3. What if I have an odd length in points? How will I know the number of inches that I need? Several conversion sites exist on the Internet. You can use those or the general rule-of-thumb that 100 pts is about 1.33 inches.
  4. What if I want to dynamically change the width of the bar chart field? You can do this using the X-2 button next to Width on the Object Size and Position screen. Warning: using the X-2 feature adds to what is in the Width box. You will want to zero it out first. Also, the X-2 formula does not use inches as displayed in my example. Instead, you are using twips. 1 inch is equivalent to 1440 twips.

 

About ckruger

Check Also

Let's Speak BO Webinar Now You See Me, Now You Don’t! August 8 2017

Webinar: Now You See Me, Now You Don’t!

Join us and Creative Technology & Training Solutions’ Michael Ward, as he demonstrates and discusses …

Leave a Reply

Your email address will not be published. Required fields are marked *