Save Data in Report to Use later

[Cozmo2]

I have several names to report I need to combine data from several records of the same type with different codes. Not all names have the same codes. I want to report the data on one line. Is there a way to save date to be used later in the report?

For Example:

Name              Code   Start Date        Code Transfer Date  Code              Promote Date          

Sue	SD	01/15/2000	TD	01/15/2005
Mitch	SD	01/15/2004
Sam	SD	01/15/2003			PD	01/15/2006

[EddieLotter]

Firstly, what are you using to create the report? cfreport, cfdocument or what?

[Cozmo2]

I am using CF10 Report Builder.

[EddieLotter]

Okay, that limits your options somewhat.

Now, your example isn't clear what you want to do. What data in the three different records do you want to display on one line in the report?

[Cozmo2]

Using the three records above I want the report to print (I'm using the Name Group Footer):

Name	Start Date	Transfer Date	Promote Date
Sue	01/15/2000	01/15/2005
Mitch	01/15/2004
Sam	01/15/2003		01/15/2006

[EddieLotter]

Cozmo2 wrote:

Using the three records above I want the report to print (I'm using the Name Group Footer):

From this reply it seems that by "one line" you mean "one report band".

The best way to show multiple records in a non-detail report band (like your Name Group Footer) is to use a sub-report.

[Cozmo2]

Thanks for your help and great suggestion!

[EddieLotter]

You're welcome.

[Cozmo2]

I finally created the sub-report. I am getting the correct output but it is not printing the data across the band. I created calculated fields to only show if the appchk_cod matched the column heading.

How can I save the dates for the different appchk_cod and print them in the same band? I want the report to look like:

                         Disposition     Filing     FAFSA       Award              Award         Jump Start     Housing             Housing

                                              FAFSA    Date        Letter Sent     Letter Rec'd        Date      Contract Rec'd     Deposit Rec'd

Student Name     Accepted                    05/15/2014                          3/14/2014       06/14/2014     06/14/2014          06/14/2014      

It looks like this:

[EddieLotter · Accepted solution]

I think I now have a better idea of what you are trying to accomplish.

You don't need a sub-report to get each person's dates on the same line. It is best to do that in your query, for example:

select table_name.UserName, dtFafsa.FafsaDate, dtLetterSent.LetterSentDate, dtLetterReceived.LetterReceivedDate
from table_name left outer join
(select userID, FafsaDate from table_name as table_name_fafsa where appchk_cod = 'FF') as dtFafsa on table_name.userID = dtFafsa.userID
left outer join
(select userID, LetterSentDate from table_name as table_name_LetterSent where appchk_cod = 'AS') as dtLetterSent on table_name.userID = dtLetterSent.userID
left outer join
(select userID, LetterReceivedDate from table_name as table_name_LetterReceived where appchk_cod = 'AR') as dtLetterReceived on table_name.userID = dtLetterReceived.userID

This is obviously not syntax checked, but it illustrates the concept.

Each derived table returns the date of each code. You will need a derived table for every code you want to report on.

[Cozmo2]

I created part of the query and tested it. It ran great, however, when I try to open my report I get:

500 - Internal server error.

There is a problem with the resource you are looking for, and it cannot be displayed.

My query is below. Why won't the report work? Right now the only data I display is the Name and FAFSA-Date

SELECT name.soc_sec, name.last_name, name.first_name, name.nickname, name.mi, name.camp_cod, dtFAFSA.comp_dt as FAFSA_Date

FROM Name

LEFT OUTER JOIN

(SELECT soc_sec, comp_dt FROM nmappchk as nmappchk_FAFSA WHERE appchk_cod = 'FF') as dtFAFSA

on name.soc_sec = dtFAFSA.soc_sec

[Cozmo2]

Is working now. I found some left over code on my report!

Thanks for your help.