Using a date range to limit line item aggregate summary information

Discussion created by stevegleason on Nov 22, 2013
Latest reply on Dec 3, 2013 by stevegleason

As part of a larger project, I am trying to create a dynamic dashboard layout to show client activity within a user-specified range of time, and I am having trouble getting summary information to show accurately within each line of the portal.


There are three TOs involved:

A GLOBAL TO set set the date ranges.

global_SHIFTLINEITEMS lists each billable activity, with an associated job number, client, and revenue generated. The relationship is defined by the date of the shiftlineitems record falling within the starting and ending range specified in the global table.

global_shiftitems_CLIENTS is used to aggregate the unique jobs to a "rolled up" list of client activity.

The layout is based on the GLOBAL TO – so I can display records from many different clients at the same time.


A portal on that page shows the related records from the global_shiftitems_CLIENTS TO.

The portal contains a field for the client name, and what I would hope would be a count of the related records in the period and the amount of revenue within the period.

What works:

The client list generates dynamically, only listing the clients who had activity within the specified time period.


What doesn't:


The aggregation within each line of the portal. I can use the "one line portal" method to have a portal listing the combined revenue for every item in the period below the main portal.

But within the portal, I can only get that aggregate information (for ALL clients within the set time period - the same summary totals are listed in each line) using a summary field (s_revenue) from the base SHIFTLINEITEMS table.

or an aggregate field (from the global_shiftitems_CLIENTS TO) that IS specific to the client, but it ignores the parameters of the set time period and lists all records ever entered: Unstored, from global_shiftlineitems_CLIENTS, = Sum ( global_SHIFTLINEITEMS::Revenue)



Other things I've tried:

Trying to use the GetSummary isn't working either. I have tried doing this from the context of each table:

In the base ShiftLineItems table:

c_revenue_in_date_range: unstored, from global_SHIFTLINEITEMS, = GetSummary (s_revenue; _kf_ClientCode)

Result: empty field

In the base Clients table:

c_revenue_in_date_range: unstored, from global_shiftlineitems_CLIENTS, GetSummary ( global_SHIFTLINEITEMS::s_Revenue ;__kp_ClientCode )
(Note: i tried using the breakfield from each table, with no results). Result: empty field


Also in the base Clients table, i tried the Sum calculation:

c_sum_revenue_in_date_range: unstored, from global_shiftlineitems_CLIENTS, Sum ( global_SHIFTLINEITEMS::Revenue)
Result: empty field

I know that cross tab reports - within a Portal - are a stretch. But I would really like to end up with this format instead of a list report. I'm missing something, but I can't figure it out. I've tried various self-joins, too. But I'm at the point where I feel like I am not doing anything to increase my understanding, but just flailing hoping that something works. I would appreciate feedback.