I am trying to create crosstab report to display production quantities in the following format in the report footer of my report in CR 9:
Date OB Mined MaterialA MatB MatC Total Mat Comments
4/02/04 200 500 600 300 1400 comment 4
5/02/04 100 200 200 200 600 comment
where Total Material is The sum of all columns (number of columns varies) except OB Mined, ie Material A + Material B + Material C. Any ideas on the best way to do this? What I have done is add three grouping levels on my column:
Top level: constant value of 1 - basically this exists so I have an extra total field on the end of each row - to use as my 'comments' column. I currently use the display string property on the common tab of the format field dialog box to display the comments for each date that I have previously stored in an array. By using a counter that increments on each row I retrieve and display the corresponding comment for each date. This works well.
Second Level Grouping: Material Type. There are two possible values for material type: OB Mined is the only material in the first type and all other Materials appear in the second type. This grouping level is basically here so my OB appears on the left hand side of my report - i supress subtotals for this group level as OB Mined appears in a Material Type by itself and having a total doesnt make the report look very professional.
Third Level: Material - are the columns I have identified in the sample format above.
The 'Total Material' column is the row total Crystal displays for the Material Type grouping. However as I want this to sum all Materials excluding OB Mined I am using the display string to change what is displayed by the program. I subtract the corresponding row value for OB Mined from the current field value for 'Total Material'. I have the following display string for the summary field:
global numbervar RowNumber2;
global numbervar array SummaryValues;
global stringvar ColumnValue;
if GridRowColumnValue ("Material.MaterialCode1") = OBMaterialCode then
(
RowNumber2 := RowNumber2 + 1;
Redim Preserve SummaryValues[DistinctCount({sqlProduction.ShiftDate})];
SummaryValues[RowNumber2] := CurrentFieldValue;
);
totext(truncate(round(CurrentFieldValue)),0)
The display string of the 'Total Material' column is as follows:
Global numberVar array SummaryValues;
Global numberVar RowNumber3;
totext(truncate(round(CurrentFieldValue-SummaryValues[(RowNumber3:= RowNumber3 + 1)])),0)
In this way I am creating an array containing the values for OB Mined, subscripted by the row number of the crosstab it appears in. I am able to count the rows in the Total Material row and do the subtraction. Where I am running into trouble is when my crosstab runs over to a second page. One of my variables counting rows resets to zero and the other one doesnt, hence I cannot synchronise my row numbers and find which OB Mined Value corresponds. As soon as a new page is taken my variable RowNumber3 continues counting as it should, but RowNumber2, enumerating the rows for the summary values in the crosstab (OB Mined column) starts numbering the first row on the new page as 1 and keeps counting. Any ideas on what the problem here is, how I can fix it, or a better method of trapping this data would be appreciated!? I have tried resetting RowNumber3 to zero in the page header of my report although this was not successful - at what stage of running the report are crosstabs evaluated?
Thanks in advance for your help..
Date OB Mined MaterialA MatB MatC Total Mat Comments
4/02/04 200 500 600 300 1400 comment 4
5/02/04 100 200 200 200 600 comment
where Total Material is The sum of all columns (number of columns varies) except OB Mined, ie Material A + Material B + Material C. Any ideas on the best way to do this? What I have done is add three grouping levels on my column:
Top level: constant value of 1 - basically this exists so I have an extra total field on the end of each row - to use as my 'comments' column. I currently use the display string property on the common tab of the format field dialog box to display the comments for each date that I have previously stored in an array. By using a counter that increments on each row I retrieve and display the corresponding comment for each date. This works well.
Second Level Grouping: Material Type. There are two possible values for material type: OB Mined is the only material in the first type and all other Materials appear in the second type. This grouping level is basically here so my OB appears on the left hand side of my report - i supress subtotals for this group level as OB Mined appears in a Material Type by itself and having a total doesnt make the report look very professional.
Third Level: Material - are the columns I have identified in the sample format above.
The 'Total Material' column is the row total Crystal displays for the Material Type grouping. However as I want this to sum all Materials excluding OB Mined I am using the display string to change what is displayed by the program. I subtract the corresponding row value for OB Mined from the current field value for 'Total Material'. I have the following display string for the summary field:
global numbervar RowNumber2;
global numbervar array SummaryValues;
global stringvar ColumnValue;
if GridRowColumnValue ("Material.MaterialCode1") = OBMaterialCode then
(
RowNumber2 := RowNumber2 + 1;
Redim Preserve SummaryValues[DistinctCount({sqlProduction.ShiftDate})];
SummaryValues[RowNumber2] := CurrentFieldValue;
);
totext(truncate(round(CurrentFieldValue)),0)
The display string of the 'Total Material' column is as follows:
Global numberVar array SummaryValues;
Global numberVar RowNumber3;
totext(truncate(round(CurrentFieldValue-SummaryValues[(RowNumber3:= RowNumber3 + 1)])),0)
In this way I am creating an array containing the values for OB Mined, subscripted by the row number of the crosstab it appears in. I am able to count the rows in the Total Material row and do the subtraction. Where I am running into trouble is when my crosstab runs over to a second page. One of my variables counting rows resets to zero and the other one doesnt, hence I cannot synchronise my row numbers and find which OB Mined Value corresponds. As soon as a new page is taken my variable RowNumber3 continues counting as it should, but RowNumber2, enumerating the rows for the summary values in the crosstab (OB Mined column) starts numbering the first row on the new page as 1 and keeps counting. Any ideas on what the problem here is, how I can fix it, or a better method of trapping this data would be appreciated!? I have tried resetting RowNumber3 to zero in the page header of my report although this was not successful - at what stage of running the report are crosstabs evaluated?
Thanks in advance for your help..