Highlighted

Save Data in Report to Use later

Explorer ,
Jun 11, 2014

Copy link to clipboard

Copied

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

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.

TOPICS
Reporting

Views

675

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Save Data in Report to Use later

Explorer ,
Jun 11, 2014

Copy link to clipboard

Copied

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

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.

TOPICS
Reporting

Views

676

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 11, 2014 0
Advocate ,
Jun 11, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 11, 2014 0
Explorer ,
Jun 11, 2014

Copy link to clipboard

Copied

I am using CF10 Report Builder.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 11, 2014 0
Advocate ,
Jun 11, 2014

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 11, 2014 0
Explorer ,
Jun 11, 2014

Copy link to clipboard

Copied

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

        

NameStart DateTransfer DatePromote Date

Sue

01/15/2000

01/15/2005

Mitch

01/15/2004

Sam

01/15/2003

01/15/2006

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 11, 2014 0
Advocate ,
Jun 11, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 11, 2014 1
Explorer ,
Jun 12, 2014

Copy link to clipboard

Copied

Thanks for your help and great suggestion!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 12, 2014 0
Advocate ,
Jun 12, 2014

Copy link to clipboard

Copied

You're welcome.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 12, 2014 0
Explorer ,
Jun 25, 2014

Copy link to clipboard

Copied

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:

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2014 0
Advocate ,
Jun 25, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2014 1
Explorer ,
Jun 26, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 26, 2014 0
Cozmo2 LATEST
Explorer ,
Jun 27, 2014

Copy link to clipboard

Copied

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

Thanks for your help.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 27, 2014 0