Author |
Message |
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
Topic: possible to combine only 2 records in a group? 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.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}
Edited by DBlank - 22 Jan 2013 at 6:17am
|
IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
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).
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
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.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Jan 2013 at 7:47am |
if {customer.customer_id} in [100261, 208749] then 100261 else {customer.customer_id}
|
IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
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!
|
IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
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.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
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...]
|
IP Logged |
|
|