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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.