cancel
Showing results for 
Search instead for 
Did you mean: 

Custom Total Column With Cross-Tab in Crystal Report

0 Kudos

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
ToText('P')

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?

former_member34
Product and Topic Expert
Product and Topic Expert
0 Kudos

Thank you for visiting SAP Community to get answers to your questions. Since you are new here, I recommend that you familiarize yourself with our Q&A Tutorial: https://developers.sap.com/tutorials/community-qa.html, as it provides tips for preparing questions that draw responses from our members. Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your profile you encourage readers to respond: https://www.youtube.com/watch?v=46bt1juWUUM

Many thanks!

Accepted Solutions (0)

Answers (1)

Answers (1)

ido_millet
Active Contributor
0 Kudos

Crate a formula that returns 1 when P and 0 otherwise.

In the CrossTab, simply SUM that formula. Note: use SUM rather than Count for this.