Would like to display one field in CR details horizontal rather than vertical

I know I have seen some ideas and threads about this, but it is not really what I need. I have a report in CR XI that seems to be on the right path. The problem I am having is one field that needs to be in details could provide multiple results (products and services) which duplicates all the records. Some customers I'll have 3 lines, the next 9 ... depending on how many P&S they have.

What I have done is moved all fields from the details section to group1 which so far seems fine and left my P&S field in details.

The output is normal:
prod1
prod2
prod3
prod4
etc.....
What I need is this: prod1, prod2, prod3, prod4, etc.

Any ideas greatly appreciated.
MAGSGQAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
That or  change the formula that adds them

WhilePrintingRecords;
Global StringVar ProductList;
If {ProdField} in ["prod1", "prod2", "prod3"] then
(
    If ProductList  = '' then
       ProductList  := {ProdField}
    else
       ProductList := ProductList & ', ' & {ProdField};
);
''

mlmcc
0
 
kenwagersCommented:
Have you tried creating a Cross Tab report?  That would allow you to flip the data the other way in the report.  The Cross tab could be in a sub report is you want a separate set for each customer.


0
 
MAGSGQAuthor Commented:
I have not considered a cross tab report.  10k+ customers so it's going to be a large excel export when I am done.  The only reason I want the P&S codes side by side is to tighten up the output and minimize the pages.

Plus, I have no clue or remember how to create an efficient crosstab w/sub-report.  I'm a novice CR guy.

You'll see in the attached PDF file the yellow results that are vertical and I would like them horizontal.
Crystal-Reports---Full-Subscribe.pdf
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
kenwagersCommented:
Using sub reports will likely be slow.  And unfortunately, cross tab reports don't export well to Excel.

Is there a maximum number of sub values you can expect for a single customer?  If it's not too many, then this can be done in SQL and then each of the values for the customer would show up as a separate field.

What you would do is write the SQL statement first, getting the results you're looking for, and then use the SQL Statement as a command object instead of linking in individual tables.

The most efficient way is probably to use PIVOT / UNPIVOT.  Here's some documentation:

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
MAGSGQAuthor Commented:
@ Ken:  And this procedure would output within CR and export to excel?  There really aren't many P&S codes.  Once the formatting is in place, I am going to have filter out those P&S codes that do not need to be displayed.  I have 20 out of hundreds that need to be considered; the remaining although are relevant as they sit in the same field do not need to be displayed.  Problem I ran into was a customer record for each P&S code.  So you can imagine how many lines of records starting off with the same name :) were unnecessary.

I'm reviewing the information @ the provided URL and I am not sure I am able or knowledgable to do that.   But I'll give it a try.  Thanks
0
 
mlmccCommented:
You could use a formula in the details and display in the group footer.

In the group header add a formula
WhilePrintingRecords;
Global StringVar ProductList;
ProductList := '';

in the details
WhilePrintingRecords;
Global StringVar ProductList;
If ProductList  = '' then
   ProductList  := {ProdField}
else
     ProductList := ProductList & ', ' & {ProdField};
''

In the group footer
WhilePrintingRecords;
Global StringVar ProductList;
ProductList

mlmcc
0
 
MAGSGQAuthor Commented:
@mlmcc:  so far it looks as though it is displaying horizontal :)  Thank you.
Next question:  keeping in mind those P&S codes, how or what should I do if am really looking to display only certain P&S Codes?

I still need every customer no matter what, but because my query as of now pulls ALL of the P&S codes, there are only specific ones I need to display and the rest to disappear.
0
 
kenwagersCommented:
Make sure you're connecting the Customer and Product tables with a LEFT OUTER JOIN so that you pick up all customers regardless of codes.

Then, set a Report Selection formula something like this:

{ProdField} in ["prod1", "prod2", "prod3"]
0
 
MAGSGQAuthor Commented:
@Ken:  thank you.  Will try to add to that tomorrow.   I would assume I need to list the P&S codes that I want to look at in that array?
0
 
MAGSGQAuthor Commented:
So I could replace prod1, prod2, etc with the actual P&S codes that I care about?
0
 
mlmccCommented:
Yes.

mlmcc
0
 
MAGSGQAuthor Commented:
@mlmcc:  it's working nicely - I think.  As long as I have my linking correct.  I do have another question.  Because these P&S codes reside in the same field and any customer, depending on their services with us could differ, the "Brass" now want me to place specific codes in two separate columns.

Is that doable?
0
 
MAGSGQAuthor Commented:
I forgot to mention, they want me to export this to an excel file.
0
 
mlmccCommented:
2 columns - Sure, just have 2 formulas like above with th eappropriate products in each set.  Just change the variable name to ProdList1 and ProdList2

Export - You can export Crysal to Excel through thje viewer or code.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.