shakespeare5677
Programmer
Hello everyone,
I have created a nice little bar chart based on a crosstab in crystal reports. The chart is supposed to present daily averages of two values for each week in a month, along with an overall daily average for that month all on one chart. In the end of things, the chart loooks like this:
Month
|
| A B
| AB AB AB avgB
| AB AB AB B avgAavgB
| AB AB AB AB avgAavgB
-------------------------------------
wk1 wk2 wk3 wk4 averages
Since the crosstab alone does not allow me to do this, a command was used to retrieve records ordered by date even before crystal reports orders and groups things by month. Because the records are ordered by the command, I was able to use a formula which keeps track of the current week and month, and base by crosstab on that. The crosstab calculates the sum of that formula's values and the formula returns a difference between the desired value (current average for week and month) and the current value in the crosstab (a variable calculated and kept by the formula). In other words, if I expect a column value in the crosstab to contain a value of 10, and the current average that I wish to display is suddently 8, the formula simply returns a value of -2 in order to make the sum in the crosstab exactly what I want it to be. Every value was cleared out as soon as a new month was detected.
Although it does not look too friendly, the whole thing worked quite well until it was discovered that there should be another level of grouping making the chart look like this:
Month
Hour
|
| A B
| AB AB AB avgB
| AB AB AB B avgAavgB
| AB AB AB AB avgAavgB
-------------------------------------
wk1 wk2 wk3 wk4 averages
Still amazed that the whole thing worked before, I simply added another group underneath the monthly one. Instead of clearing the values as soon as a new month was discovered, I started clearing them out as soon as either a new hour or new month was discovered, having ordered the records returned by the command by time and date.
That's when things went bad. I tracked and altered the code making sure that everything cleared and calculated properly by the code, until the only problem remaining was that of the last column values (the daily averages per month) being returned as negative values. The only explanation for this is that the values the crosstab should have accumulated are lower than those expected by the formula. How that can be I simply cannot answer. I suspect the problem lies in the printtime grouping of readtime values accumulated by the crosstab, but since the values returned and traced in the detail are proper, I simply cannot see what is going on.
Please help!!!
I have created a nice little bar chart based on a crosstab in crystal reports. The chart is supposed to present daily averages of two values for each week in a month, along with an overall daily average for that month all on one chart. In the end of things, the chart loooks like this:
Month
|
| A B
| AB AB AB avgB
| AB AB AB B avgAavgB
| AB AB AB AB avgAavgB
-------------------------------------
wk1 wk2 wk3 wk4 averages
Since the crosstab alone does not allow me to do this, a command was used to retrieve records ordered by date even before crystal reports orders and groups things by month. Because the records are ordered by the command, I was able to use a formula which keeps track of the current week and month, and base by crosstab on that. The crosstab calculates the sum of that formula's values and the formula returns a difference between the desired value (current average for week and month) and the current value in the crosstab (a variable calculated and kept by the formula). In other words, if I expect a column value in the crosstab to contain a value of 10, and the current average that I wish to display is suddently 8, the formula simply returns a value of -2 in order to make the sum in the crosstab exactly what I want it to be. Every value was cleared out as soon as a new month was detected.
Although it does not look too friendly, the whole thing worked quite well until it was discovered that there should be another level of grouping making the chart look like this:
Month
Hour
|
| A B
| AB AB AB avgB
| AB AB AB B avgAavgB
| AB AB AB AB avgAavgB
-------------------------------------
wk1 wk2 wk3 wk4 averages
Still amazed that the whole thing worked before, I simply added another group underneath the monthly one. Instead of clearing the values as soon as a new month was discovered, I started clearing them out as soon as either a new hour or new month was discovered, having ordered the records returned by the command by time and date.
That's when things went bad. I tracked and altered the code making sure that everything cleared and calculated properly by the code, until the only problem remaining was that of the last column values (the daily averages per month) being returned as negative values. The only explanation for this is that the values the crosstab should have accumulated are lower than those expected by the formula. How that can be I simply cannot answer. I suspect the problem lies in the printtime grouping of readtime values accumulated by the crosstab, but since the values returned and traced in the detail are proper, I simply cannot see what is going on.
Please help!!!