columns in crosstab reports

Posted on 2006-06-29
Last Modified: 2008-01-09
I have a crosstab report needs to look like the one below.  Mine is working pretty well but notice on the 5/14 visit my report is only showing the first of the two columns not both.  I need to see all columns when they are there but suppress those that would be totally blank.  The only difference between the two is a sequence number and it is not always consistent. IE if there is a 2nd or 3rd wound a record is created, if not then no record thus the sequence numbers vary.  Any suggestions would be appreciated.  I can't send too much info as it medical info.  Sorry for the crummy formating!

Hope points are fair if not LMK.


Visit Date      5/19/2006      5/14/2006      
Visit Time      2:30 PM      4:00 PM      
Nurse Name      peterpan      wendy      
Wound #      1      1      2
Location      arm      arm      leg
L             2      24
W             1      0
D            0.2      0
Type      Surgical      Dehisence of surgical wound      Surgical wound
Ulcer Type                  
Closure      Sutures, Other      staples, sutures      
Drains      No      No      
Drainage Amt      Scant      Small      None
Drainage Type      Serous      Serosanguious      
Tunneling      None      None      None
Bed Appear      Pink/Red            Pink/Red
Surro Appear      Pink      Pink      Pink
Odor      No      No      No
Culture      No      No      No
Care Provided      Wound cleansed with Dermal Cleans            
      Wound dressing applied DSD            
      Other - ABD SECURED WITH TAPE            
Tol. Procedure      Yes            
Question by:LBarrett

Expert Comment

ID: 17016230
Did you create a report that looks like a crosstab, or did you insert a crosstab in the report?
If it is the latter, on which fields have you grouped the columns?


Author Comment

ID: 17019281
Inserted crosstab into report.  Report is grouped by patient id.  Crosstab is in patientID header.  Crosstab columns are:
Date of Visit
Visit Type
Visiting Nurse ID

Rows are:
Question group
Question number

LVL 42

Expert Comment

ID: 17019495
It sounds like you're saying you want to see a column on the crosstab even though your dataset doesn't contain data for that column - is that accurate?  If so, the answer is that you can't do it - you'll have to do a join to a dummy table to supply data in order to see that column.
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 17019722
The dataset does contain data for the column in some cases.  Depends on whether the patient had 1, 2, 3 up 6 wounds. It should look like what's below.  It is pretty close in terms of formatting but the 2nd wound for Mary does not show.  My crosstab intersection or summary field is MaxValue for the answer field.  I think that is why it is only showing one column.  Is there another way to use the summary field for a text value?

BTW the wounds are not really numbered 1,2,3 etc.  They are in fields called WOUND1LOC , WOUND2LOC, etc.  If there is only one wound there will be a record for the patient with an answer in the WOUND1LOC but there will not be a record for that patient with the field WOUND2LOC.  Every answer is a seperate record.

                   6/1/2006                        6/1/2006
                  John Smith                    Mary Jones
wound #          1                             1                     2
Location         arm                           leg           shoulder
Question2      answer                     answer       answer

Below is an example of 1 visit 1 patient 2 wounds showing 7 records per wound - this number acutally varys between 5 and about 15 depending on which questions are asked for each wound.  

AssSys      AssItmSeq      QID                      Des                     QSeq
43      4      WOUND1DEP         Depth of Wound (1)      1
43      4      WOUND1LEN         Length of Wound (1)      1
43      4      WOUND1TYP         Type of Wound (1)      1
43      4      WOUND1TYP         Type of Wound (1)      3
43      4      WOUND1TYP         Type of Wound (1)      8
43      4      WOUND1WID         Width of Wound (1)      1
43      4      WOUND1CLOS        Wound Closure (1)      5

AssSys      AssItmSeq      QID                      Des                   QSeq
43      6      WOUND2DEP         Depth of Wound (2)      1
43      6      WOUND2TYP         Type of Wound (2)      8
43      6      WOUND2CLOS        Wound Closure (2)      2
43      6      WOUND2LOC         Location of Wound (2)      1
43      6      WOUND2TYP         Type of Wound (2)      4
43      6      WOUND2TYP         Type of Wound (2)      6
43      6      WOUND2CLOS        Wound Closure (2)      1

I hope I'm giving enough info here without overdoing.  Thanks for the help so far.


Accepted Solution

kmarf earned 250 total points
ID: 17073145
Credit due to

Note that this is circa 2002, maybe current CR versions have some new toys to help with this, but so far as I know, here are the 6 options (none of which are pretty!) available to you:

Reporting on data that isn't there:
Crystal will not create a group unless there is at least one record for that group in the report.  This is also true for Cross-tabs and Bar/Line Charts.  However, it is not unusual for users to request that missing groups show up with a zero.  There are several ways to do this and the approach to use depends on a number of factors.  I will give a brief description of 6 different techniques below.  If you need help applying these techniques to a specific report, drop me a line.

As an example, lets say that you need a report to list total sales for each salesman in a month.  We want each salesmen listed - even those with no sales in the month.  Here are your options:

1) Manually enter a zero sales record for each salesman each month:
This is not usually practical, but it is the easiest short term solution if the report is a one time item.  Charts and cross-tabs will incorporate the zero groups automatically.

2) Expand your selection criteria to include irrelevant records to create the groups:
Assume that the transaction table includes both sales and calls, and that every salesman has at least one call per month.  Your report could select the records for BOTH sales and calls.  Then you can write a formula like:

  if {Type} = "Sale"
  then {Amount} else 0

When you subtotal this formula by salesman you will have a group for every salesman, but a total of only the sales transactions.  If you are printing the details you would suppress the call details and only show the sales details.  Again, charts and cross-tabs will includes the zero groups automatically.

3) Join a primary table that has all groups, and use an outer join:
If you have table that lists all of the salesman, you can use this table as the reports primary table.  Link this table to your transactions with a "Left Outer" join, which tells the database that you want all records from the primary table and only matching records from the transactions.  Again, charts and cross-tabs will includes the zero groups.

However, there is a weakness to this approach.  If you put ANY criteria on the transaction fields, you cancel the effect of the outer join.  So in our example, when you select transactions for one month, you would then lose all salesmen who had no sales in that month.  To keep the outer join behaviour you have to eliminate ALL criteria from the 'Outer' table.  That means including ALL transactions for ALL months.  You can suppress the details of the records you don't want to see or use, but if your database is large, this makes for a very slow report.

4) Hard code a conditional total for each salesman:
If you don't have a primary table for salesman you could hard code the list into a set of formulas.  Say you have 10 salesman.  You could create 10 conditional formulas that each look like this:

  If {Sales.Salesman} = "Ken Hamady"
  Then {Sales.Amount} else  0

You write one formula for each Salesman and then create a grand total of each Formula.   You can arrange the totals in the Report footer in any way you like, with labels next to them.  The report no longer requires a group.

This technique is often used with dates or numbers in a series.  Say you wanted to display the 14 days in a user specified 2-week period, including empty days.  You could create 14 formulas that calculate the 14 required dates.  Each formula would calculate a different date by adding a number of days to the start date selected by the user.  Each of these formulas could then be used in a conditional formula like the one shown above.  You would add a corresponding grand total for each formula.

The main downside to this technique is that you have to change the report every time you add a new Salesman or change the number of days.  Another down side is that these 'groups' can't be used as row / column fields in Cross-tabs or as groups in Group Charts.  They can be used in advanced charts, but the chart labels will include either the words "Sum of " or the @ sign.

5) Use Subreports to look for the details.
You can create a simple report from the Salesman master table that includes one record per salesman.  This report could then have a linked subreport on the detail section which would launch a new  subreport to get each salesman's transactions.  The main report would show every salesman even if the subreport was empty.  

The downside is that running multiple subreports is inefficient.  If the list was long, the time needed to process all of the subreports might be prohibitive.  Also, data from multiple subreports cannot be used to create a cross-tab or any type of chart.

6) Create imposter sections that fill in gaps:
This technique works when you have a consecutive series that can be calculated, like dates or numbers.  You group by the date or numeric field and then you create several extra group footer subsections.   Group footer "B" will print only when the gap between one group and the next is greater than one.   It displays the missing value by adding one to the current group value in a formula:

  {table.DateOrNumber} + 1

If you add a zero next to the formula, the two objects can be formatted and aligned to look like the group name field and the subtotal from the original group footer.    To allow for a gap of 2 or 3 consecutive items, you would add group footer 'C' to display when the gap is greater than two, and a similar formula.  Keep adding sections until you have accounted for the largest gap that will likely occur.  Of course these imposter groups will not show up in cross-tabs and charts of your data.
LVL 100

Expert Comment

ID: 17077527
kmarf  - In the future please post only the link to the article (as you did).  It may be copyrighted and therefore EE doesn't want it posted.


Author Comment

ID: 17077528
What finally did work was a combination of #2 and #4.  I hard coded the sequence numbers since they don't change very often.  It forces the crosstab to give me all the info I need but instead of 2 columns for 1 person I have two groups, i.e. 2 groups of rows which show separate wound numbers instead of columns.  This actually works really well since it keeps each one separated across the date columns.

Thanks for your help and the link to Ken Hamady's site - that will come in handy in the future.


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now