Need to build a bar chart based on summary data

[crash4ever]

Hi, I have this problem on building a bar chart based on a summary table. In crystal report I have build this summary table on the footer section.

Each row contain 4 different elements. This is how the table looks like:
A Count B Count C Count D Count
Jan 1 2 3 4
Feb 5 6 6 7
Apr 8 9 10 11
Total 14 17 19 22

Based on this table I need to build a quarterly summary report/chart.

My question is how could I build a horizontal bar chart with 4 different bar elements that represent the total count for each group? In Lotus or Excell you could just select the total cells and use them as the y-axis.

Any help will be greatly appreciated

Thanks,
Vicky
US Script

 

[kenhamady]

Do you want a stacked bar chart?

Is this a cross-tab object or group totals with hidden details? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crash4ever]

All I need is just regular bar (not a stacked bar) that show a comparison of group A has 14, B has 17, etc. So each group has their own bar to represent the total. It's not a cross-tab object just a regular table.

Basically I put all those Jan, Feb, Mar data on the detail section and use formula to calculate the totals (there are other factors that determine the totals. Here I just a simple addition for example). I think it's close to group total with hidden details since I use other calculations to generate those totals.

The rows will be generated dinamically (depends on the input date range. So it could have more or less than 3 row).

Thanks Ken for quick response. I don't know how to build this chart based on this fixed data.

Vicky

 

[IdoMillet]

Sounds like you need to use the 'Advanced' option
under the 'Data' tab for your chart (since you don't
group on what you want to base your chart on).

Cheers,
- Ido ixm7@psu.edu

 

[crash4ever]

Hi Ido,
I've tried to use the 'Advanced' option but do not know which field/formula should I put as 'On Change of' input value.

Basically the table from Oracle database that I used to generate those data above has 5 different columns (the group count for monthly data). Here is the table structure look like:

Field Names:
Month
A_Count
B_Count
C_Count
D_Count

That is why I could not use a cross-tab to build the table. The output row that I showed in the detail section basically is a representation of each rowset in the Oracle table. If you think I should use the 'Advance' chart option, how can I use this information to make it work? Should I build a special formula to do that and use that formula as the y-axis so it will show four different horizontal bar?

Thanks,
Vicky

 

[crash4ever]

Hi,
To make it clearer, here is the chart that I want to build

A ----------- 14
B ---------------17
C ----------------- 19
D ---------------------22

So those group names will be used as Y-axis and the total count as x-axis.

Hope this will make my question clearer.

Regards,
Vicky

 

[kenhamady]

What version of CR?
Later versions have a "FOR ALL VALUES" that you can use for OnChangeOf. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crash4ever]

Hi Ken,
It is Version 8. I've tried to use All Values but the chart shows the total for each group as a 'Stacked bar' (1 horizontal bar that show all the groups with different colors) where the chart I want to build is a chart with 4 horizontal bar that represent each group.

Thanks
Vicky

 

[IdoMillet]

There may be a simpler solution but one
option I can see immediately is to create
a UNION query (either as a VIEW or using
Crystal SQL Designer) that converts each monthly
record into Four records:
---------------------------------------------
Select Month, A-Count as COUNT, 'A' as 'Count_Type'
from My_Table
UNION ALL
Select Month, B-Count as COUNT, 'B' as 'Count_Type'
from My_Table
UNION ALL
Select Month, C-Count as COUNT, 'C' as 'Count_Type'
from My_Table
UNION ALL
Select Month, D-Count as COUNT, 'D' as 'Count_Type'
from My_Table ;
---------------------------------------------

Now, all you need to do is Group on Count_Type
and get a sum of COUNT for your chart.

Cheers,
- Ido

ixm7@psu.edu

 

[crash4ever]

Thanks Ido for the clue,
I think that should work. One last question: Is it possible to have a separate SQL statement to build the chart? Or there could only be 1 SQL statement used in one subreport for all the data, chart, etc.

I'm kind of new for this Crystal Report Appl. The report that I'm trying to build is a sub report.

Thanks again,
Vicky

 

[IdoMillet]

Vicky,

The Chart itself can be a subreport with its
own SQL statement placed after the other subreport
you had in mind.

Cheers,
- Ido ixm7@psu.edu

 

[kenhamady]

If I select:
a horizontal bar chart
4 grand totals as "show values" fields
on change of "For All Records",

I get separate bars, not a stacked bar.
Not sure why yours behaves differently. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crash4ever]

Thanks a lot for all the helps.

I use the Crystal SQL designer to build the query and it works fine. Crystal SQL designer will produce a file that could be used as a source of the report.

Do you think this is the best solution if I want to make this report a dynamic report? Since (unless I'm miss-understood here) we have to run the SQL statement everytime we update the master database to produce the crystal *.QRY file that we used as a report source, then the report would not be a flexible report that could be run anytime we want.

Is there an automatic way to run this *.QRY file from Crystal Report itself or is it executed automatically everytime we run the report?

Thanks,
Vicky

 

[kenhamady]

If you don't save the QRY with data, then the report should launch the QRY when it is run. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crash4ever]

Thank you for all the help,
You all are very helpful....

Vicky