Running Totals in CrossTab Report 1

[tcgoth]

Crystal 11

I have the following report layout. Gist of the data is that each "service" can be offered to multiple suppliers for bid, one of those suppliers is then selected to actually perform the service.

The suppliers that can be offered or Assigned might change on a day-to-day basis, so I believe a crosstab is the only reasonable solution.

GH1 Client
GH2 Customer
GH3 Service
Detail

The data is displayed in the report like this:

GH1: Client A (group On Client)
GH2: Customer 1234 (group On Customer)
GH3: Service1, Assigned to Organization (group On Service)
Detail: Offered to Organization1, Organization2, Organization3
GH3: Service2, Assigned to Organization (group On Service)
Detail: Offered to Organization1, Organization2, Organization3


Desired Output in Report Footer:

Service1 Offers Summary (from Details section)
Organization1 CountOfferedtoOrganization1
Organization2 CountOfferedtoOrganization2
Organization3 CountOfferedtoOrganization3
etc.

Service1 Assigned Summary (From GH3 Service)
Organization1 CountAssignedtoOrganization1
Organization2 CountAssignedtoOrganization2
Organization3 CountAssignedtoOrganization3
etc.

Service2 - Offers and Assignment Counts same as Service1.

Approach so far:

I added a formula @OfferCount to count each offer in the detail section:"If Service=service1 then 1 else 0" Each Offered Organization under the Detail Section for "Service1" shows a "1" so looks OK at this point.

I then created a Running Total @OfferTotal based upon the @OfferCount, Evaluate on Change of Service, Reset Never. I then added a crosstab to the Report footer with Offered Organization field as a Row and @OfferTotal as the Summarized Field.

I've tried every possible variation of the crosstab/runningtotals that I can think of to get the correct counts to no avail. Any ideas?

 

[lbass]

Why are you using running totals? Are there duplicate values?

What happens if you just use sum (NOT count) of your conditional formula? If you use a running total, remember to sum, not count, your formula. Counting a formula just returns the number of times it executes, not the number of times the criteria are met.

-LB

 

[tcgoth]

lbass,

Thanks for your reply. Using Sum for the "Offered to Organization" seems to be working perfectly.

However, I cannot get a correct count of the "Assigned Organization" that is in GH3. I setup a simple count formula

@service1assignedcount
if service=service1 and not isnull(assigned organization) then 1.

I inserted the formula on GH3 and it displays "1" on the records I would expect it to. I then inserted a CrossTab into the Report Footer with "Assinged Organization" as the row and Sum of @service1assignedcount as the summary (no column indicated).

The crosstab seems to sum the count of the detail records based upon the number of "Offered to organization" instead of just the Assigned Organization (1 per Service).

The "Assigned Organizations" are not grouped and are dynamic in nature (new Assigned Organizations could be added daily).

Sample Data:

GH1 Client ABC
GH2 Customer 1234
GH3 Service1 AssignedOrganization1
Detail:
Offered Organization4
Offered Organization1
Offered Organization3

GH1 Client XYZ
GH2 Customer 3456
GH3 Service1 AssignedOrganization2
Detail:
Offered Organization3
Offered Organization2
Offered Organization4

GH1 Client CDE
GH2 Customer 9876
GH3 Service1 AssignedOrganization1
Detail:
Offered Organization1
Offered Organization4
Offered Organization6

Desired Crosstab Output

Assigned Organization1 = 2
Assigned Organization2 = 1

Any ideas on how I can end up with correct counts of Assigned Organizations?

 

[lbass]

Is there always only one organization assigned per customer?

-LB

 

[tcgoth]

Not necessarily. Each Customer can have multiple Services. There is one organization assigned per Service.

Thanks again!

 

[tcgoth]

I should have added that I need to perform separate counts on the different services, not just total of all services.

Output would look like:

Service 1
Assigned Organization1 = 2
Assigned Organization2 = 1

Service 2
Assigned Organization1 = 3
Assigned Organization2 = 4

etc....

 

[lbass]

I think you could concatenate the customer field and the service field:

{table.customer} & " " & {table.service}

Then insert a crosstab that uses {table.assignedorg} as the row field and a distinctcount of the concatenated formula as the summary field. If ccustomers/services can appear in more than one client group, you can add the client into the concatenated formula as well.

-LB

 

[tcgoth]

lbass,

Thank you for your kind assistance. I reorganized my data and implemented your suggestion using DistinctCount - it works!