Transposing rows and columns in a subreport 1

[aradia926]

I have a report set up for printing labels, but the labels require more than just an address. The labels also have the addressee's ID number and a field that holds from 1 up to 15 1-letter codes that we use for mailing purposes. In order to retrieve these multiple codes, I set up a subreport that is linked on the addressee's ID number. The codes are retrieved perfectly, but I need them to display horizontally rather than vertically. i.e.:

ABC

rather than:

A

B

C

Also, multiple codes might be entered on multiple days. I must retrieve only those codes that were entered on the most recent day. So, if I entered DEF on 2/8/06, ABC on 2/9/06 and today is 2/10/06, I should only get ABC.

How can I do this?

I tried inserting the results from the subform into an array and then creating a string from the array elements, but I could not get this to work.

I also tried doing a cross-tab, but it never displayed on my parent table and I don't want any summary fields anyway.

If you haven't figured it out, I'm a beginner with Crystal Reports. Please help!! Thanks in advance.

 

[Durango122]

hi
check Ken website
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

Durango122
Remember to used all fingers when waving to policemen

 

[synapsevampire]

People new to software are better served (everyone is) to post technical information:

Crystal version
Database
Example data (show the fields and what's in them)
Expected output

And what is this subform you speak of, do you mean a subreport, or are you using some programming language as a front end?

I don't see how these codes would be related to the address data, so if it's a separate query in a subreport, use a formula in the subreport such as:

In a section in the main report prior to running the subreport use a formula such as:

whileprintingrecords;
global stringvar SubCodes:=""

Then in the subreport->Report-Selection Formulas->Record use:

{table.date} = maximum({table.date})

In the subreport details use:
whileprintingrecords;
global stringvar SubCodes:=Subcodes+{table.field};

Now anywhere after the subreport has ran you can reference the code, as in:

whileprintingrecords;
global stringvar SubCodes

-k

 

[aradia926]

You are right, I should have posted more technical information. For the record:

Version: Crystal Reports in Visual Studio 2005
Database: Sql Server 2000

As for my use of the term subform, that was a slip. I program in several different languages in several different environments throughout the day and sometimes when changing gears I use the wrong terminology. The correct word should have been 'subreport'.

With that said, I still haven't quite figured it out and have a few more questions.

synapsevampire, you said that I should use the code:

whileprintingrecords;
global stringvar SubCodes:=""

in the main report prior to running the subreport. At what point does the subreport run? Does this mean I should have a formula field in the header perhaps that includes this code?

When I use the code: {table.date} = maximum({table.date}), I get an error of "This function cannot be used because it must be evaluated later." Have I done something wrong? Do you know how I can fix this?

Again, you say anywhere after the subreport has ran I should reference the code:

whileprintingrecords;
global stringvar SubCodes

Does this mean in a field, and in the subreport or in the main report?

I did some investigation and combined your ideas with some that I found at:

http://www.contactreview.com/mb/showflat.php/Cat/0/Number/20217/an/0/page/30

What I came up with a subreport that is grouped by the ContactID number and a formula field in the details section on my subreport that references:

whileprintingrecords;
global stringvar SubCodes,

as well as a suppressed formula field in the details section of my subreport that references:

whileprintingrecords;
global stringvar SubCodes:=Subcodes+{tblMktMaterial.LabelCode};

and another formula field in the details section of my subreport that references:

Maximum ({tblMarketing.DateAdded}, {tblContact.ContactID}).

I get a printout on my report that appears to print the stringvar SubCodes each time a new code is added, and it is not giving me the most recent codes.

I appologize for being so slow on this, I'd never touched Crystal Reports until last Tuesday.

Thank you so much for your help and patience!

 

[aradia926]

Is there anyone else that can shed some light on my problem or the solution that synapsevampire offered?

I'm completely lost as to where to go from here, and I need to get this solved ASAP.

Thank you!

 

[lbass]

Change all of SV's formulas by replacing "global" with "shared". This will allow you to collect the codes in the subreport and display them in a row in the main report. T

o get only the most recent codes, in the subreport go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date})

You don't really need to group the subreport, since you are linking the subreport to the main report on the contact ID anyway--although it doesn't hurt anything.

-LB

 

[synapsevampire]

Ooops, thanks for the SHARED find LB/

-k

 

[aradia926]

Thanks lbass, I'm definitely making progress now!

Everything is working great, but I'm still not getting the most recent codes. Instead, I'm getting all of the codes.

I used the expression

{tblmarketing.dateadded} = maximum({tblmarketing.dateadded})

in the subreport's report->selection formula->GROUP as you instructed.

Any ideas why I'm not getting the correct results?

 

[lbass]

Please show the contents of the shared variable formulas you are using in the subreport.

-LB

 

[aradia926]

lbass- my shared variable formulas as you requested...

In the subreport:

ReportFooterSection1:

whileprintingrecords;
shared stringvar SubCodes

DetailSection1:

whileprintingrecords;
shared stringvar SubCodes:=Subcodes+{tblMktMaterial.LabelCode};

In the Main Report:

Section3 (Details a):

whileprintingrecords;
shared stringvar SubCodes:=""

 

[lbass]

Where is the subreport located in the main report, and where are you placing the display formula for the shared variable in the main report (you didn't show this formula, but it should look like the following):

whileprintingrecords;
shared stringvar SubCodes;

I think you may have placed the reset formula in the wrong place.

-LB

 

[aradia926]

The subreport is located in DeatilSection (Details b) of the main report.

I placed the formula for the shared variable in the main report's DetailSection2 (Details b), but only so I could try to see what was going on. Synapsevampire didn't really specify where to put it, so I put it in the subreport and in the main report. Maybe I did put it in the wrong place!

 

[lbass]

Why do you have the subreport in a detail section? If your main report is grouped on contact ID, and you have linked the subreport to the main report on contact ID, then place the subreport in a GH_b section, place the reset formula in GH_a, and place the shared variable formula as I showed in my last post in GH_c. The reset should be in a section before the subreport executes, and the display formula, in a section after.

-LB

 

[aradia926]

lbass-

After your first post when you said it was not necessary to have the grouping, I took it out.

As for why the subreport was in the details section, I guess it seemed logical to me since the result of the subreport should appear with the other fields that I have in the details section for my labels. Also, synapsevampire didn't really specify so I didn't think it was important.

For the sake of following your suggestions in your last post, I put the grouping back in and placed each of the fields as directed. Unfortunately, I'm still getting the same results.

To give you a better idea of what I'm getting, it looks like:

AFPACFPR

In my database, I have:

Codes DateAdded
AFP 2/6/2006
ACFPR 2/10/2006

So what I should see is just ACFPR.

Any other ideas as to why I'm not getting the correct results?

 

[lbass]

I said you do not need the group in the subreport. Please describe your report structure--what the groups are, where the subreport is located, how it is linked to the main report, and where the reset and display formulas are located.

-LB

 

[aradia926]

Main Report:

GROUPS: ContactID - Ascending

REPORT > SELECTION FORMULA > RECORD: {tblContact.PrintLabel} (This notes in the database that a label needs to be printed for this record)

STRUCTURE:

GroupHeaderSection1 (Group Header #1a: tblContact.ContactID - A):
@ClearSubcode - Formula = whileprintingrecords;
shared stringvar SubCodes:=""

GroupHeaderSection2 (Group Header #1b: tblContact.ContactID - A):
Subreport

GroupHeaderSection3 (Group Header #1c: tblContact.ContactID - A):
@DisplaySubcodes - Formula = whileprintingrecords;
shared stringvar SubCodes

Section3 (Details a):
Fields:
ContactName
Contact ID

DetailSection1 (Details b):
Fields:
Department
Address
City, State, Zip
Country
Territory
Postal Zone
Subject Number

Subreport:

GROUPS: None

REPORT > SELECTION FORMULA > RECORD: {tblContact.ContactID} = {?Pm-tblContact.ContactID}

REPORT > SELECTION FORMULA > GROUP: {tblmarketing.dateadded} = maximum({tblmarketing.dateadded})

SECTIONS:

DetailSection1 (Details)
Fields:
@BuildCodes - Formula = whileprintingrecords;
shared stringvar SubCodes:=Subcodes+{tblMktMaterial.LabelCode};

Ok, I think that is everything. Please know that I am trying my best to help you help me. I really appreciate your patience. Thank you again so much!

 

[lbass]

What you have done looks perfect to me, and when I test this approach, it works. If you add a display formula to the report footer of the subreport, does is display the correct codes?

-LB

 

[aradia926]

I placed a display formula in the report footer of the subreport and not only does it not display the correct codes, for some reason the report in rendered in total jibberish. Most of it is not even recognizable characters.

I'm getting really frustrated, I can't even imagine what you're thinking!

 

[aradia926]

Success!

So after you said that the approach worked for you, I started to investigate some other reasons why it might not work.

I found that a link between two of my tables in my database was wrong. After changing that link, it worked perfectly!

Thanks to synapsevampire for getting me started, and special thanks to lbass for being patient, helpful, and seeing me through to the end!