Hi All,
I have a problem in implementing one of the requirement,using Crystal Report Designer 2008, which is given to me and hence am writing to get some advise on the problems.
Imagine that we have a SP which returns us this kind of sample data:
Country State Population
Country1 State11 1000
Country1 State11 2000
Country1 State12 1000
Country1 State12 150
Country1 State12 200
Country1 State12 1750
Country2 State21 1000
Country2 State22 2000
Country2 State22 1600
Country2 State22 650
Country2 State22 200
Country2 State23 1450
Country2 State23 1000
Country3 State31 2000
Country3 State32 1000
Country3 State32 150
Country3 State32 200
Country4 State41 550
The expected output of the report should look like
{
Chart1 Chart2
Chart4 Chart3
Chart1
Chart2
Chart3
Chart4
}
Here Chart means plotting a bar chart for the data of each Country such that X Axis becomes State and the Sum(Population) is plotted on the Y Axis.
Thus, Chart1 means plotting Sum(Population) against the States of Country1. Similarly, all charts are to be plotted for individual Country's.
Note that All 4 charts should come in one page beside each other and also they will come one after another as well.
There will never be a case wherein the SP's resultset would contain any other country other than Country1,Country2,Country3 and Country4.
This is what I have tried till now.
Approach1 : A group is applied on Country column. A Chart object is placed in the Group Footer and hence Chart1,2,3 and 4 can be plotted correctly but they do not come besides each other. Also for showing the Charts again(2nd time) I am using a subreport which has the same call to the same SP and then plotting all the charts again in the subreport.
*Question :
Is there a way such that I can plot a chart only for specific countries. If i keep 4 charts besides each other in the Report Footer and give a condition for each chart such that
Plot sum(Population) against State where Country = "Country1" ? and so on ...
I did not get this kind of a property for Charts. When I placed 4 charts in Report Footer then it plotted all 4 countries in each of the chart.
Approach2 : I tried to create a Formula whose code is
if {Population.Country} = "Country1" then
formula = {Population.Country}
end if
Then 4 charts were placed besides each other in the report Footer and this formula was plotted(on X Axis) against the Sum(Population). It resulted in 2 Bars only, one of which had the Total Population for Country1 whereas the other bar had total population for all other countries together.
*Question :
It assigns a blank value to the formula for all countries other than Country1 and hence my purpose of creating a formula to get different Countries in different formulas is not acheived. Is there i need to do something else in the formula to get only Country1 in the formula.
Apart from these 2 approaches, is there any other way also which i m not aware and which will get me to the desired output. Creating subreports and firing the same query in every subreport(since passing the entire resultset from main to subreport is not possible) is a big performance constraint as well.
Hope that i am clear on most of my requirements and will explain further if required. Thanking all in anticipation.
Regards.
Ajit