Help With A Crosstab Report

Ron Cates · February 10, 2012

I've read through several different posts, articles and tutorials about crosstab reports but I'm struggling to get it and I haven't been able to find any that have adjustable columns. So here I am. First step is to see if I can explain our business.

We do fundraising wall calendars for Police, Sheriff and Fire departments. Our primary function is to solicite sponsors for the calendar and sponsors recieve an ad space.

Tables

Departments

Jobs - A Job is how we refer to a calendar project

Employees

Sales Assigned - Join table between Jobs and Employees

Customers

Tickets - Tickets is our term for Invoices.

Lines,

Products

A Department has many jobs. One per year. Each calendar is assigned one or more sales associates. Each job has one or more tickets, Jobs is related directly to Tickets instead of customers because a customer can sponsor one or more jobs but each job has to have it's own ticket. So a customer who sponsors two jobs has to have a ticket for each one. They are never combined in one ticket because each job has to have it's funding tracked seperately.

The cross tab report i need to produce would have

- All the sales people assigned to all the Jobs for a Department down the left.

- A column for each year we have done a calendar/job for that department. One job per year.

- Showing summary of the number or tickets and amount for each job for each sales person.

I have never actually used repeating fields so there's some unfamiliarity with them.

I have set up a summary report layout based on the Sales Assigned table and sorted by sales person, so I have the left column easy enough of course.

The first stumbling block i've come to is how to set up the columns and headers. There is a varied number of jobs per department but each one is represented by a year. There are some we just started last year and others that go back 5 or 6 years. There are even a couple that skipped a year here and there. So, when i do a find for a department, I would like to populate the column headers with the years we did the jobs for them. and of course the columns should contain the corosponding summaries.

I beseach the oh wise and wonderful developers of this forum. A little help? :worship:

comment · February 10, 2012

Showing summary of the number or tickets and amount for each job for each sales person.

i am looking at your RG and I see that a job can have many sales people assigned to it. So how exactly will the tickets of a job be divided among its sales people?

I haven't been able to find any that have adjustable columns

The only way to have truly "adjustable" columns is to write the entire report into a global text field. Otherwise you need to set an arbitrary fixed number of columns (which could be portals or repetitions of a repeating field or similar) and leave the unused ones empty.

Ron Cates · February 10, 2012

Each ticket is given the primary key of the sales person who sold it. I relate them in other parts of the database for other reports. The RG shown is just the primary base tables, my full graph is a nightmare. If only I had known about anchor bouy or some other organizational technique three years ago.

I suspected it would have to be a fix number of culumns. We have decided that the most recent 5 years would be sufficient to see the trends we're looking for.

comment · February 10, 2012

OK, then since you cannot use filtered portals, and I don't suppose you want to add 5 more relationships to your "nightmare" of a graph, I'd suggest you script this.

First, do you know how to produce this report without cross-tabs, i.e. in the form of:

Adam:

• 2008: 24

• 2009: 18

• 2010: 29

Betty:

• 2008: 19

• 2009: 14

• 2010: 31

...

The other question is, are you familiar with Edoshin's Fast Summaries technique?

Ron Cates · February 10, 2012

First, do you know how to produce this report without cross-tabs, i.e. in the form of:

Adam:

• 2008: 24

• 2009: 18

• 2010: 29

Betty:

• 2008: 19

• 2009: 14

• 2010: 31

...

Yes

The other question is, are you familiar with Edoshin's Fast Summaries technique?

I think i've read about it but i wouldn't say i'm familiar with it. I will look it up and read it again.

comment · February 10, 2012

Well, the idea is for the script to write a line for each person, e.g.

24 <tab> 18 <tab> 29¶

19 <tab> 14 <tab> 31¶

...

Then use a calculation field in Employees to "download" the applicable line and display it.

Ron Cates · February 10, 2012

Just for simplification and to make sure i clearly understand, are you saying that when i run a script for this report it should go to a summary report layout first and using Fast Summaries, gather up the summaries needed in a tab delimmeted array to parse them and deposit them in the apropriate fields and repetitions in the crosstab report layout? I hope so, because that makes sence. And sounds like fun :)

comment · February 11, 2012

There are many possible variations. I would write into a "tabular" array (i.e both tab and return delimited). I would also write the EmployeeID (i.e. the row identifier) into a second variable, return-separated. Then each record can fetch its row by:

http://www.briandunning.com/cf/908

Ron Cates · February 13, 2012

Good Monday morning. Thanks Comment, I'm going to see if I can wrap my head around all this and get it started. It might be a little clearer after some coffee :)

Ron Cates · February 13, 2012

First, do you know how to produce this report without cross-tabs, i.e. in the form of:

Adam:

• 2008: 24

• 2009: 18

• 2010: 29

Should i be doing this report based on Assigned_Sales or from the Tickets TO that I am drawing the summary data from which is related to Sales_Assigned by Job ID and Sales Person ID?

comment · February 13, 2012

The report needs to be done in the table that holds the values to be summarized - I believe that is the Tickets table?

Ron Cates · February 13, 2012

Ok, i'm starting to understand getsummary a little bit. I believe I found out through testing that getsummary only works when the summaries are in the table the report is based on. Is that correct?

I'm also finding out that I am confusing myself by using existing TOs rather than setting up a TO group specifically for this report.

So I created a new TO group.

Ron Cates · February 13, 2012

Using GetSummary, does the breakfield have to be in the same base table as well?

Ron Cates · February 13, 2012

Using GetSummary, does the breakfield have to be in the same base table as well?

Testing shows this is true. Kind of a pain that the report can sort and summarize by a related field, for instance the name field from the Employees table rather than the EmployeeID in the Tickets table, but a GetSummary only works when all referenced fields are in the base table. In this case I'll have to figure out how to sort the results by Employee Name at the end of the process.

Please correct me if i'm wrong. ( I know you will )

Ron Cates · February 13, 2012

Making good progress. I created the report and wrote a script based on the Fast Summaries technique. Here are the results I produce so far, stored in $$report.

EMP0000000|House|2010|2|550

EMP0000000|House|2009|1|0

EMP0000000|House|2008|3|325

EMP0000001|Jamie S|2012|9|2425

EMP0000001|Jamie S|2011|101|18020

EMP0000001|Jamie S|2010|22|5575

EMP0000001|Jamie S|2009|223|35200

EMP0000002|Scott C|2013|4|500

EMP0000002|Scott C|2012|156|19100

EMP0000002|Scott C|2010|111|14075

I'm using the "|" delimiter rather than tab but the data represents;

ID | Name | Year | Total Tickets | Total Amount

Am I on the right track?

comment · February 13, 2012

My preference would be:

$$Years =

2012|2011|2010|2009|2008|

$$EmpIDs =

EMP0000000

EMP0000001

EMP0000002

$$CountTickets =

|||2|1|3

9|101|22|223||

...

$$SumAmount =

...

I didn't realize you had so many "holes' in the data.

Ron Cates · February 13, 2012

I didn't realize you had so many "holes' in the data.

By holes are you refering to the fact that not all sales people listed will have sold in all years listed?

Working on setting up variables broken down as you suggest...

Ron Cates · February 13, 2012

Well, $$EmpIDs is easy enough. For the rest it looks like I'm going to have to check during the loop to see if the previous record is equal to the current to detirmine when to start a new line. But then, how do I populate years that a sales person did not sell in?

comment · February 14, 2012

By holes are you refering to the fact that not all sales people listed will have sold in all years listed?

Yes. This complicates things, because you need to also look at the list of years and count them off; if the current group (by year) is the next year in the list, write it down and count it off; else stay on the same group, write a blank, and move to the next year on the list.

Another option is to do this from the Employees table, using a global gYear field to fetch the summaries for employee in a year (one year at a time). This can be rather slow, but you do get a 0 for the years with no sales.

Ron Cates · February 14, 2012

I'm making progress. I'm close to producing the $$CountTickets and $$SumAmount taking the missing years into consideration. It's complicated, looping through each year sunmmary and comparing it to a GetValue($$Years; $a) where $a is a counter. It's pretty tough but I think I've just about got it and I am enjoying the challenge emensely

I'll keep you posted.

Ron Cates · February 14, 2012

:bang: Well after a day and a half of turning my brain into spaghetti trying to follow the logic of multiple nested loops, I am FRIED!!

Here is my script so far

Perform Script [ “User Abort” ]
Perform Script [ “Set Error Capture” ]
Set Variable [ $param; Value:Evaluate ( Get ( ScriptParameter )) ]
Set Variable [ $$array; Value:"" ]
Set Variable [ $$EmpIDs; Value:"" ]
Set Variable [ $$CountTickets; Value:"" ]
Set Variable [ $$SumAmount; Value:"" ]
Set Variable [ $newestyear; Value:Jobs::year ]
Set Variable [ $$years; Value: LeftValues ( List ( Jobs::year ) ; 5 ) ]
Set Variable [ $$Years; Value:LeftWords($$Years; 999999999) ]
Set Variable [ $yearcount; Value:ValueCount ( $$Years ) ]
If [ $layout = "Report" ]
Set Variable [ $dep; Value:Administration::xreport_dep ]
Go to Layout [ “Departments_Form” (Departments) ]
Enter Find Mode [ ] [ Pause ]
Set Field [ Departments::_pk_department_id; $dep ]
Perform Find [ ]
If [ Get ( FoundCount ) = 0 ]
Go to Layout [ original layout ]
Show Custom Dialog [ Title: "No Records Found"; Message: "There were no records found for this report!"; Buttons: “OK” ]
Exit Script [ ]
End If
Else
Set Variable [ $dep; Value:Departments::_pk_department_id ]
End If
Go to Layout [ “Report Sold [ Sales ] Crosstab” (SalesCrosstab Tickets Summary) ]
Enter Find Mode [ ]
Set Field [ SalesCrosstab Tickets Summary::_fk_dep_id; $dep ]
Set Field [ SalesCrosstab Tickets Summary::active; 1 ]
Set Field [ SalesCrosstab Tickets Summary::_fk_sales_person_id; "*" ]
Set Field [ SalesCrosstab Tickets Summary::year; $newestyear - 4 & "..." & $newestyear ]
Perform Find [ ]
Sort Records [ Specified Sort Order: SalesCrosstab Tickets Summary::_fk_sales_person_id; ascending SalesCrosstab Tickets Summary::year; descending ] [ Restore; No dialog ]
If [ Get ( FoundCount ) = 0 ]
Go to Layout [ original layout ]
Show Custom Dialog [ Title: "No Records Found"; Message: "There were no records found for this report!"; Buttons: “OK” ]
Exit Script [ ]
End If
Go to Record/Request/Page [ First ]
Set Variable [ $a; Value:1 ]
Loop
Set Variable [ $count; Value:GetSummary ( SalesCrosstab Tickets Summary::total_tickets ; SalesCrosstab Tickets Summary::year ) ]
Set Variable [ $name; Value:SalesCrosstab Employees::name ]
Set Variable [ $year; Value:SalesCrosstab Tickets Summary::year ]
Set Variable [ $id; Value:SalesCrosstab Employees::_pk_employee_id ]
Set Variable [ $tickets; Value:GetSummary (SalesCrosstab Tickets Summary::total_tickets ; SalesCrosstab Tickets Summary::year ) ]
Set Variable [ $amount; Value:GetSummary (SalesCrosstab Tickets Summary::total_amount ; SalesCrosstab Tickets Summary::year ) ]
Set Variable [ $recnum; Value:Get ( RecordNumber ) ]
Set Variable [ $remaining; Value:Get ( FoundCount ) - $recnum ]
Set Variable [ $$array; Value:$$array & $id & "|"& $name & "|" & SalesCrosstab Tickets Summary::year & "|" & $tickets & "|" & $amount & "¶" ] # The Loop I Refer To Below -------------------------------------------- If [ $prev ≠ $id ]
Set Variable [ $$EmpIDs; Value:$$EmpIDs & $id & "¶" ]
If [ $a > 1 ]
If [ $b < $yearcount ]
Set Variable [ $c; Value:($yearcount - $ - 1 ]
Loop
Set Variable [ $$CountTickets; Value:$$CountTickets & "|" ]
Set Variable [ $$SumAmount; Value:$$SumAmount & "|" ]
Exit Loop If [ $c = $yearcount - 1 ]
Set Variable [ $c; Value:$c + 1 ]
End Loop
End If
Set Variable [ $$CountTickets; Value:$$CountTickets & "¶" ]
Set Variable [ $$SumAmount; Value:$$SumAmount & "¶" ]
End If
Set Variable [ $b; Value:1 ]
End If
Loop
If [ GetValue ( $$Years ; $b ) = $year and $b = $yearcount ]
Set Variable [ $$CountTickets; Value:$$CountTickets & $tickets ]
Set Variable [ $$SumAmount; Value:$$SumAmount & $amount ]
Else If [ GetValue ( $$Years ; $b ) = $year ]
Set Variable [ $$CountTickets; Value:$$CountTickets & $tickets & "|" ]
Set Variable [ $$SumAmount; Value:$$SumAmount & $amount & "|" ]
Else If [ $b ≠ $yearcount ]
Set Variable [ $$CountTickets; Value:$$CountTickets & "|" ]
Set Variable [ $$SumAmount; Value:$$SumAmount & "|" ]
End If
Exit Loop If [ GetValue ( $$Years ; $b ) = $year or $b = $yearcount ]
Set Variable [ $b; Value:$b + 1 ]
End Loop
If [ $b < $yearcount ]
Set Variable [ $b; Value:$b + 1 ]
End If
#End
Exit Loop If [ $count > $remaining ]
Set Variable [ $prev; Value:$id ]
Go to Record/Request/Page [ $count + $recnum ] [ No dialog ]
Set Variable [ $a; Value:$a +1 ]
End Loop
If [ $b < $yearcount ]
Set Variable [ $c; Value:($yearcount - $ - 1 ]
Loop
Set Variable [ $$CountTickets; Value:$$CountTickets & "|" ]
Set Variable [ $$SumAmount; Value:$$SumAmount & "|" ]
Exit Loop If [ $c = $yearcount - 1 ]
Set Variable [ $c; Value:$c + 1 ]
End Loop
End If
Set Variable [ $$EmpIDs; Value:LeftWords($$EmpIDs; 999999999) ]
Set Variable [ $$Years; Value:Substitute ( $$Years ; "¶" ; "|" ) ]
Go to Layout [ original layout ]
Set Field [ Departments::test; $$Years & "¶" & $$EmpIDs & "¶" & $$CountTickets & "¶" & $$SumAmount &"¶" & GetValue ( $$Years ; $b ) & "¶" & $yearcount ]
Show Custom Dialog [ Title: "Results"; Message: $$array; Buttons: “OK” ]

I've come so close, but in can't quite find the fix for this last tweak. The data below is from a Department with 4 years of sales. I have painstakingly tweaked the script to get it to produce the correct amount of values per line. But for some reason some of the lines end up with more "|"s than they should have and I just can't figure it out.

$$CountTickets =

1|3|27|57

||30|

71|40|89|

|42|86|

74|78|78|

||32|

||44|

|30|||||

||37|

||8|

|2|32|

25|82|60|

46|29|50|

||16|

4|6|||||

27|34|||||

|15|||||

3||||

22||||

I think it has to do with the highlighted ( ok, the highlite didn't work, I'll notate it ) Loop. The loop comes into play after we switch to a new employee, I am trying to look back at the last one and say, if the last last year for that group was not the last year of $$Years then we need to ad Pipes for the empty values. It seems to work on some groups but goes overboard on others. At least I think.

Michael, I know you could probably do this script much easier and faster than I but I was trying not to ask you to do it for me. Maybe I'm completely on the wrong track with this but I'm so close. If you could take a look and see if anything pops out at you I would greatly appreciate it.

Thanks

P.S. I can't print to PDF so I ran a DDR report and copied it from there. I used a script prettier to format indents and stuff but it doesn't seem to want to take. :sad:

PSS - Still trying to clean up the script in this post...

comment · February 14, 2012

Michael, I know you could probably do this script much easier and faster than I but I was trying not to ask you to do it for me.

Maybe, but do you think it's easier to debug yours? :hmm:

Ron Cates · February 14, 2012

Sorry, I didn't mean to say that I wanted you to do it for me. I just meant that I was trying to get it myself rather than looking for the easy way out. Anyway, I have been trying to debug mine all day until I came to this :bang:

I guess I'm just asking if you might be able to see something that I'm missing. But then again, I can't seem to get the script cleaned up on here so i'm sorry it's not so easy to read.

I was also wondering if you think I'm taking the right aproach with this script?

Ron Cates · February 15, 2012

A good night sleep and a fresh perspective works miracles. Came in this morning and found the problem. It was in the loop I referred to above. It's all fixed now and I am producing the variables just as you described above.

My preference would be:

$$Years =

2012|2011|2010|2009|2008|

$$EmpIDs =

EMP0000000

EMP0000001

EMP0000002

$$CountTickets =

|||2|1|3

9|101|22|223||

...

$$SumAmount =

...

I didn't realize you had so many "holes' in the data.

All holes accounted for :yay: :chili:

comment · February 15, 2012

LOL, I bet you're doing that jig for real...

Ron Cates · February 15, 2012

I am

Now I'm working on parsing out the data. I've got the year labels populated. Now just working out how to parse the others. I'm going to do some experimenting and see what I come up with. I'll let you know if I get stuck :)

Ron Cates · February 15, 2012

ok, I'm stuck. I am using the following calculation in a reapeating calc field to populate the five possible years of data.


Let ([



pos = FindInList ( $$EmpIDs ; _pk_employee_id ) ;

string =Substitute ( GetValue ( $$CountTickets ; pos ) ; "|" ; "¶" )] ;



GetValue ( string ; Get ( CalculationRepetitionNumber ))



)

FindInList is a custom function from Brian Dunnings website that finds the position of a value in a list.

My problem now is it is only working on the first repetition. The rest of the repetitions are blank.

Why?

comment · February 15, 2012

You need to use Extend() around _pk_employee_id.

Ron Cates · February 15, 2012

Brilliant!! I was so close on my own but I had no clue about Extend()

So I take it that Extend() is used to extend the value ( _pk_employee_id in this case ) to be used by all repetitions.

comment · February 15, 2012

Yes.

Ron Cates · February 15, 2012

Stick a fork in it!! It's done!!

:party: :yay: :chili:

Thanks Comment. As always, You Rock :waytogo:

comment · February 15, 2012

Congratulations! FWIW, I think you did the right thing by insisting on doing it yourself. Make sure to comment your script generously, and come back to it later to see what steps can be removed.

Ron Cates · February 16, 2012

Well, I thought it was done. I presented it to my Boss. He loved it, but was wondering if I could add totals across the bottom. :idot:

I started to write out some questions about how to gather the totals but in the process I figured it out.

I added the total_tickets field and the total_amount field to the subsummary part sorted by EmpID which produces the totals of all years for each sales person.

I added a $$Totals variable and on the first record of each group, I'm used GetSummary(total_tickets ; _fk_Sales_person_id) and specifying the break field as the EmpID rather than the year gave me the right summaries.

And Boom Goes The Dynamite :woohoo:

@comment

I'm really glad that you held back from posting when I ran into problems at first. By working through it and examining and reexaminig to find the problems, I feel like I have a much clearer understanding of subsummary reports, GetSummary() and Repeating Calculation Fields.

I also found and loaded my first custom function and wrote my first List() calculation from scrach rather than just copying someone elses calc.

This has been such a great exercise. :D

Ron Cates · February 16, 2012

Ooops!! Someone pinch out the fuse on that dynomite. What I did gives me totals for each sales person for all years which would go in a totals column at the far right. What my Boss asked for is totals for all sales people for each year. Basically, total sold for each Job ( Calendar ).

Let's see if I can work that one out :logik:

Ron Cates · February 16, 2012

OK, for the sake of finishing out this thread, here's what I did.

I needed to gather the total for each year. So, Using the same source sub summary report, I first sorted by Year only. Bingo, summary totals for each year. Then I set up a new Fast Summaries loop to gather them up. After which I resorted by EmpID and Year to get the report to show the summaries I needed for the rest of the script.

Pretty cool that I could just change the sort order on the same report layout to produce the summaries I needed for each step.

I decided to put both count and amount totals in the same $$Totals variable with a "|" seperator. So there was a line for each year like this

198|23375

187|23400

242|37795

240|32124

Then I created a repeating calc field for total tickets and for total amount and used the following calc.


LeftWords ( GetValue ( Substitute ( $$Totals ; "|" ; " " ) ;  Get ( CalculationRepetitionNumber )) ; 1 )

I used LeftWords for total tickets and RightWords for total amount.

Time to relight that fuse......

And run!! :hyper:

Sign In

Help With A Crosstab Report

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information