Adobe Community: Message List - Save Data in Report to Use later

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Fri, 27 Jun 2014 18:47:11 GMT

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

Thanks for your help.

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Thu, 26 Jun 2014 13:40:05 GMT

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

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 25 Jun 2014 17:06:33 GMT

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.

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 25 Jun 2014 15:29:49 GMT

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:

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Thu, 12 Jun 2014 14:22:05 GMT

You're welcome.

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Thu, 12 Jun 2014 12:10:30 GMT

Thanks for your help and great suggestion!

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 19:55:54 GMT

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.

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 19:44:32 GMT

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

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 18:29:02 GMT

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?

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 18:23:48 GMT

I am using CF10 Report Builder.

Re: Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 15:32:21 GMT

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

Save Data in Report to Use later

forums_noreply@adobe.com — Wed, 11 Jun 2014 15:06:41 GMT

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