| Author |
Message |
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
  
Topic: Record Selection by summed field
Posted: 11 Sep 2014 at 9:46am |
|
Report is grouped by item types, then by customers. Report shows a total of those items purchased by each customer over a user-entered date range. I also have a parameter where the user needs to enter a minimum total of items purchased - so they can run the report only showing customers who purchased a minimum of (x) items. The problem I am running into is what to put in the record selection formula. It doesn't give me a summary field as an option in the record selection. I tried to create a formula field called 'Total Items' which I think might work if I knew the correct syntax. Is there a way to include a summarized field in the record selection formula?
|
 IP Logged |
|
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 11 Sep 2014 at 10:11am |
|
Summaries run after parameters, so that approach is not going to happen. If you can generate the results through a command or a stored procedure, then you might be able to work with that.
|
| IP Logged |
|
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
Posted: 11 Sep 2014 at 10:36am |
|
Thank you for the response. I felt like I was probably going down a dead-end road. I can't use a stored procedure as I would prefer not to get the programmer who works with the database involved if I can manipulate the report. Any advice you can give me with an SQL field would be great? I haven't used these before and am afraid may just be over my head. Thanks again.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Sep 2014 at 12:10pm |
you can use group select here.
Keep in mind that the group select runs after the main sleect and after summaries so the group still exists in the report for all summarizations. You can use Running Totals or shared variables to do calculations on the remaining groups.
Create your numeric param and use it in the group select
Count ({salesID}, {Customer}) > {?My Parameter}
|
| IP Logged |
|
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
Posted: 12 Sep 2014 at 3:24am |
|
DBlank - Group selection worked perfectly, was exactly what I was needing. Thank you!
|
| IP Logged |
|
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
Posted: 12 Sep 2014 at 3:51am |
|
Thought I had it but I didn't. The issue I am having is with the counts. I guess I can't use the count function because it seems to be simply counting the # of records where I need it to add the numbers within the records. For example, one customer bought 5 items in one transaction. Another customer bought 1 item 5 times in 5 separate transactions. If I set my parameter to be >5 the customer who bought 5 items in 1 trans doesn't show in the report. Is there another function I can use besides count? Thank you.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2014 at 3:52am |
|
SUM
|
| IP Logged |
|
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
Posted: 12 Sep 2014 at 4:19am |
|
I tried sum and put in parameter of 10 but for some reason the report is generating all records instead of just customers who bought more than 10 items. However, I think I found another solution. I went to the section expert and put in a 'suppress formula'
{#RTotal0}<{?Min # Items to Print}
So far, this seems to be working like I want it to.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2014 at 4:31am |
Glad you got it working.
as an FYI-
RTs give you more granular control on the summarizations. many people prefer Shared Variables in Formulas over the use of RTs.
The SUM should work unless you are dealing with duplicate rows in the system which did not sound like the case here.
You wanted to use sales of a customer so you need to do the sum of the sales# value at the customer group level:
SUM ({table.sales#field}, {table.CustomerGroup}) > {?My Parameter}
Edited by DBlank - 12 Sep 2014 at 4:34am
|
| IP Logged |
|
crystalnewbie33
Newbie
Joined: 08 Jan 2014
Online Status: Offline
Posts: 29
|
Posted: 12 Sep 2014 at 4:46am |
|
Thanks for the info. I figured out what I was doing wrong with the sum. I was trying to put the Group first, then the field and was getting an error. Since I was getting the error, I just removed the group, which is why I was getting all records to show. Thanks again, greatly appreciate it.
|
| IP Logged |
|
|
|
