possible to combine only 2 records in a group?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18839
Printed Date: 27 Apr 2024 at 9:50am
Topic: possible to combine only 2 records in a group?
Posted By: MellieD
Subject: possible to combine only 2 records in a group?
Date Posted: 22 Jan 2013 at 5:22am
|
Hey all, I am running crystal reports XI and I have recently created a report, that shows the sales total, and monthly sales goals for several of our customers. With each report being separated as an individual page. I have been asked by a sales team member, to combine 2 of these records into a single page. The company in question has 2 accounts for their 1 location, and the sales rep would like the 2 reports combined into 1. Is there a way of doing this with out jeopardizing the other customers in the report? I know i have probably forgotten to give details, so feel free to ask any questions needed. |
Replies:
Posted By: DBlank
Date Posted: 22 Jan 2013 at 6:16am
|
use a formula to combine these two into one and then group on that instead of the original field. Hopefully you have a unique number field for each customer so you don't have rely on a name field that could be duplicated. The below assumes numeric field but you can alter it to text type if needed.
if {table.CustomerID} in [x, y] then x else {table.CustomerID}
|
Posted By: MellieD
Date Posted: 22 Jan 2013 at 6:26am
Thank you for such a quick response DBlank, as i am still very new to Crystal Reports, i'm still a bit slow with understanding the formulas.  My only 2 questions so far are...1.)Is this a Select expert formula or a field formula? 2.) what do I put in for X? They have unique identifying account numbers, so that wont cause an issue with duplicate names, i'm just not sure what to put for x (Then x else). |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 6:40am
|
1. it is a field formula. (The select expert is used to totally exlcude reords from your data set.)
2. X and Y are your two account numbers that you want to combine.
|
Posted By: MellieD
Date Posted: 22 Jan 2013 at 7:42am
|
Bearing in mind, i am almost entirely self taught, so even the basics escape me at times....It does not appear to have worked the way i was hoping. Here is my version of the formula, with the x that i do not understand. if {customer.customer_id} in [100261, 208749] then x else {customer.customer_id} When I ran the Formula above, with one of the 2 account numbers as the x it did not combine the 2 records into one record. Thank you for your patience, and your help. |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 7:47am
| if {customer.customer_id} in [100261, 208749] then 100261 else {customer.customer_id} |
Posted By: MellieD
Date Posted: 22 Jan 2013 at 7:58am
|
It looks like it may have worked! Thank you very much again for all your time and help DBlank. Have a good day!  |
Posted By: MellieD
Date Posted: 22 Jan 2013 at 8:19am
|
Well, Technically speaking the formula worked! But... not in the way i was expecting. I am sorry if it was to vague in the description of what it was that i was looking for. I need the two records merged into one, so that when i click on either number, it pulls up only 1 invoice with all the numbers combined and showing as only 1 record. The formula worked, by changing the number to be the same, but this did not combine the records, it left them separate, just sharing the same identifying number. |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 8:52am
|
are you using a runtime parameter to select the one customer to run the report for?
If so, what is your current select statement.
|
Posted By: MellieD
Date Posted: 22 Jan 2013 at 9:14am
|
I am using a couple of parameters to isolate the group of customers specifically on the program the report is pulling information for. Here are the parameters I have active {sales_history_report_view.revenue_account_no} startswith "4000" and {customer.company_id} = "1" and {customer.customer_id} in [xxxxxx,xxxxxx,xxxxxx,ect...] |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 10:21am
|
well, you cannot actually alter data using crystal.
Is the issue that a user might select only one of the 2 IDs?
|
Posted By: MellieD
Date Posted: 22 Jan 2013 at 10:24am
|
The issue is that the sales rep who handles both accounts for the customer, would like to have it printed in one sheet instead of two, so he dose not have to do the extra math when presenting the customer with the information. So he was hoping there was a way to merge the two accounts onto the one page. |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 10:56am
|
what is the structure of your report?
Sounds like you are using grouping and drilling into the report so I was trying to give you a solution so that all of the detail records would be combined via grouping.
|
Posted By: MellieD
Date Posted: 22 Jan 2013 at 11:40am
|
The report is almost in a Letter format, with each customer having a full page for their information. I have the report Grouped by customer ID as per the changes you suggested, and originally had the report grouped by customer name. The report contains sales totals, and sales goals for the time length of the program (10/12-10/13). Each page is listed by company Name and customer ID. Not sure what other info you need, please ask and I will be happy to provide any more detail. |
Posted By: DBlank
Date Posted: 22 Jan 2013 at 11:54am
| you should be grouping by the formula field that converts teh customer id not the original customer ID. |
Posted By: MellieD
Date Posted: 22 Jan 2013 at 12:08pm
It looks like it's working this time. I have sent the information to the sales agent who had requested, and i will let you know if there are any other issues that pop up. Thank you again for all the time and help you have provided me today. 
|