Multiple data points in one column put into separate columns

[tim_harper]

I looked for answers to this question because I'm sure it's been answered before but everything I looked at was either not easy to follow or applied to slightly different scenarios.

I have multiple status messages (data points) for contracts with dates. Management wants to see each status and the date of that status. To make matters more complex, each contract can have multiple inventory items which in my current report creates duplicate entries. Yes, I know I can group, and that removes the duplicate entries, but I am not seeing each status (data point) in the report. Here is an example of two of the tables I am working with.

Here is how they want to see the report when it is exported. Each Status Description should have it's own column and status date column. No need to comment on the extra columns showing the description, I know it's duplication of information but it's what was requested.

I was hoping that a simple formula would give me the status.

Example: IF {ContractStatusHistory.Status} = "Booked" THEN "Booked"

Date column Example:

IF {ContractStatusHistory.Status} = "Booked" THEN DATEVALUE ({ContractStatusHistory.StatusDate})

Here is what it looks like when I group on Contract ID. Only the Sales Rep Pending column appears.

Any help would be greatly appreciated! Thanks

[DellSC]

If you have a fairly limited number of set statuses, you could try this:

1. Group by Contract ID.

2. Suppress the Group header and footer sections.

3. Set up a sort (not a group) on StatusDate.

4. Create a set of formulas with a pair for each status. Even though some of these are dates, you're going to show them as strings. This is due to the quirks of Crystal's Excel export. When a column value is null, data tends to shift left - especially with the "Data Only" versions of the exports, so we can output a string with just a couple of spaces in it to avoid this issue. Here's an example pair of formulas

{@QuoteNew}
if {ContractStatusHistory.Status} = "Quote New" then
  {ContractStatusHistory.Status}
else
  "  "

{@QuoteNewDate}
if {ContractStatusHistory.Status} = "Quote New" then
  {ToText({ContractStatusHistory.StatusDate}, "MM/dd/yyyy")
else
  "  "

5. In the report, put the column headers for each pair of status columns in the page header.

6. In the Details section, put each of the formulas you created in step 4 in the correct column based on the header - all of them will be in a single details section.

7. In the Report menu, turn on "Select Distinct Records".

This should get you what you're looking for.

-Dell

NOTE: Let me know if you've not familiar with configuring a report for export to Excel - there are some tricks to it.

[tim_harper]

Hi Dell,

Thank you for the quick response. I followed your steps, but it didn't seem to work, same results. I grouped by Contract ID. Surpressed the Group header and Group footer. Setup a sort on StatusDate (NOTE: I did not remove the existing sort by Group.ContractID). Created the formulas for each Status type and the Status type Date. NOTE: The date formula didn't work as you suggested it, so I added the CDATE. Status Type Formula: IF {ContractStatusHistory.Status} = "Booked" THEN "Booked" ELSE " " Status Type Date Formula: IF {ContractStatusHistory.Status} = "Booked" THEN TOTEXT(CDATE({ContractStatusHistory.StatusDate}), "MM/dd/yyyy") ELSE " "

I turned on Select Distinct Records. Here is how the report looks.

[DellSC]

What I see is that you're getting blank rows in some instances. Is that what you mean when you say it didn't work?

[tim_harper]

Sorry, no. What I meant by "it didn't work" was that there are multiple rows for the same Contract ID instead of one row per Contract ID with all of the columns in that row populated with data (if the data exists). I probably should have mentioned that some of the Status and Status dates will not have data and in the subset of data I am currently working with the Quote New columns do not have any data.

I believe the blank rows are appearing because of the NULL values in the columns not showing a Status or Status Date.

I did try Suppressing the Details section instead of Suppressing the Group Header and that does give me single rows per Contract ID, but it only shows data in the Sales Rep Pending and Sales Rep Pending Date columns. My theory is the data only appears in those columns because they are the first columns with Data.

[DellSC]

Ahhh.... Now I understand. Let's tweak this a bit. Your formulas will change to something like this:

{@QuoteNew}
WhilePrintingRecords;
StringVar quoteNewStatus;
if OnFirstRecord or {ContractTable.ContractOid} <> Previous({ContractTable.Oid}) then quoteNewStatus := "  ";
if {ContractStatusHistory.Status} = "Quote New" then
  quoteNewStatus := {ContractStatusHistory.Status};
""

{@QuoteNewDate}
WhilePrintingRecords;
StringVar quoteNewDate;
if OnFirstRecord or {ContractTable.ContractOid} <> Previous({ContractTable.Oid}) then quoteNewDate := "  ";
if {ContractStatusHistory.Status} = "Quote New" then
  quoteNewDate := CDate({ContractStatusHistory.StatusDate});
""

{@ShowQuoteNew}
WhilePrintingRecords;
StringVar quoteNewStatus;
quoteNewStatus

{@ShowQuoteNewDate}
WhilePrintingRecords;
StringVar quoteNewDate;
quoteNewDate

The first pair of formulas sets the value of a status and a status date variable - resetting to a blank string at the start of each contract. The second pair shows the value of the variables.

1. Suppress the group header and details sections.

2. Put the formulas that set the values in the appropriate columns in the suppressed details sections - they'll still calculate, but it won't show.

3. In the group footer, put the formulas that show the values in the appropriate columns.

Note that in this solution, if a contract is in a given status more than once, only the most recent date will show for that status. I don't know if this scenario is possible in your data, but I wanted to mention it.

-Dell

[tim_harper]

Hi Dell, I can't tell you how much I appreciate your answer and the clarity with which it was written. That worked perfectly! The only adjustment I had to make was to the {@QuoteNewDate} formula to remove the CDate, which was giving me an error. I was able to change the string format to a date format in the {@ShowQuoteNewDate} by adding DATEVALUE to the last line. NOTE: I did get an error after adding the DATEVALUE if that column didn't have any data, which is the case with the subset I am working with during testing. In that case, I left the DATEVALUE out. It's a lot of formulas, but I would rather work with those then subreports 🙂 Thanks again for all your help!