AnsweredAssumed Answered

Cross tab report, total of found set

Question asked by SteveMartino on Mar 31, 2017
Latest reply on Apr 1, 2017 by SteveMartino

Hello forum.  I had a question about a cross tab report, hopefully the pic will help.

I set up a cross tab report, self join relationship where:

TO Service::ID = TO Service_Report_5yr::ID. 

All the totals are from the related TO, in one row, one field filtered portals.

It appears everything totals properly-rows and columns, when all records are shown-first layout.

However in layouts 2 & 3, I've created found sets, (based on Reason for Call).  The rows still total properly, but the columns do not (if at all).

Is there something about the portal filter, or the relationship I am missing?

Some more info:

Looking at the first layout, first column the in the field is a summary field, Count of 'Reason for Call'-numbers 108, 15, 8, and the portal is filtered with this expression to calculate the dynamic date range in the label:

Let ([       

  cd = Get ( CurrentDate ) ;

  offsetStart = Case ( cd < Date ( 9 ; 1 ; Year ( cd ) ) ;5 ;1 );

  start= Date ( 9 ; 1 ; Year ( cd ) - offsetStart);

  offsetEnd = Case ( cd < Date (8 ; 31 ; Year ( cd ) ) ;4 ; 0 );

  end= Date ( 8 ; 31 ; Year ( cd ) - offsetEnd )

      ];

Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end) and Service_Report_5yr::Reason For Call=Service::Reason For Call

)

 

The field for the total in the trailing summary part is a summary field, Count of (primary key ID-number 131 and the portal is filtered with:

et ([       

  cd = Get ( CurrentDate ) ;

  offsetStart = Case ( cd < Date ( 9 ; 1 ; Year ( cd ) ) ;5 ;1 );

  start= Date ( 9 ; 1 ; Year ( cd ) - offsetStart);

  offsetEnd = Case ( cd < Date (8 ; 31 ; Year ( cd ) ) ;4 ; 0 );

  end= Date ( 8 ; 31 ; Year ( cd ) - offsetEnd )

      ];

 

 

Case(IsEmpty(Service_Report_5yr::gSearch);

        Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end);

        (Service_Report_5yr::Date>GetAsDate(start) and Service_Report_5yr::Date<GetAsDate(end)) and (Service_Report_5yr::gSearch = Service_Report_5yr::Reason For Call)

      )

)

Which seems to be the problem, the proper filter that only counts that column when the found set doesn't equal the total record count.

I've done my due diligence with research, and trying many different ways, but still am unable to total the columns on the found set. 

I tried different relationships, GetSummary, etc.

I feel I'm dancing around it, but just can't get it over the finish line.

Any thoughts, help, or direction would be greatly appreciated.

Thanks

Steve

Attachments

Outcomes