Multiple data points in one column put into separate columns

[#1]

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

[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!