Skip to Content
Jun 14, 2021 at 02:54 PM

Custom Total Column With Cross-Tab in Crystal Report

109 Views Last edit Jun 13, 2021 at 05:47 PM 3 rev

I've an ASP.NET project where crystal report is integrated and used cross tab feature to show day wise data. Everything is fine except the total column at the end. So data is displayed as follows in the report:

Name       Day1     Day2     Day3   Day4      Total
user1     07:30am  07:32am  07:34am           P(3) (Expected Output - The Total Column)
            P         P        P

user2     07:32am  07:34am         07:36am    P(3)
            P         P               P

As you can see, I can manage to display individual details of each user expect the total column. My requirement is to calculate number of P for each user at the total column. I tried to use the format object of the total column value and in the display string section, put the following code to count the total number of P. Unfortunately nothing shows up at the total column. N.B: The below code only works when all the fields have the value P from Day1 to Day4 for every user and using Crystal Report 13.

numberVar i := 0;

numbervar counterP := 0;

for i := 0 to CurrentColumnIndex -1  step 1 do 
    if GridValueAt(CurrentRowIndex, i , CurrentSummaryIndex) <> '' then
            if GridValueAt (CurrentRowIndex, i , CurrentSummaryIndex) = 'P' then
                counterP := counterP + 1;
'P(' & counterP & ')'  

Now the problem is (What I analyzed so far), in the column Day3 and Day4 it has null values for each user and for those days users don't have values from database (Those data retrieved from database with a query). So for those, cross-tab generated blank values individually and when it counts the total number of P, counts the null or blank value as well. What I tried, using Format Object of that specific field and in the display string used the following:

if CurrentValueFied = '' Then

The above only works when there is a field value and doesn't apply when there is no data from database or cross-tab generated blank. Is there any way that I ignore the blank values while calculating the total column for number of P?