Crystal Reports v. 9.2.3
No matter how hard I try, I just know that I'm not going to explain this well...so PLEASE bear with me!
I'm am working on a report that will produce an invoice. I have run into a problem when a particular customer has tier based pricing.
EXAMPLE
- Customer X has 10 children (Child1, Child2, Child3...)
- Each Child has 3 billable elements (a, b, c)
- Each month, volume per element is reported
- Element 'b' has tier based pricing
- Based on cumulative volume for 'b', the first 5000 are priced at .60 and the remaining at .50.
- One child each month ends up with 2 'b' line items. Some of their 'b' element at .60 and some at .50
The invoice would look like this assuming Child2 had total 'b' volume of 4000:
Customer Element Volume Price
Child1 a 2000 .20
Child1 b 4000 .60
Child1 c 1000 .30
Child2 a 2000 .20
Child2 b 1000 .60
Child2 b 3000 .50
Child2 c 1000 .30
Child3 a 2000 .20
Child3 b 4000 .50
Child3 c 1000 .30
I'm not sure how to go about working this. I inherited this process and SQL DB. So I have complete discretion to make DB changes if that's the best way to make it happen. Currently pricing is applied manually.
CustomerTable: all entities - parents defined when necessary.
PriceTable: base pricing for all parent/child/elements. Does not currently include multiple tier pricing - only the base price. Maybe tier pricing needs to be here? (Even if the tier pricing was in the pricetable, I can't visualize how to break up Child2's 'b' element.)
DataTable: monthly volume numbers for Parent/child/element.
I'm sure I missed some vital bit of information... This challenge just makes my head hurt. So what do you think? Doable?
Thanks!
-Deej
No matter how hard I try, I just know that I'm not going to explain this well...so PLEASE bear with me!
I'm am working on a report that will produce an invoice. I have run into a problem when a particular customer has tier based pricing.
EXAMPLE
- Customer X has 10 children (Child1, Child2, Child3...)
- Each Child has 3 billable elements (a, b, c)
- Each month, volume per element is reported
- Element 'b' has tier based pricing
- Based on cumulative volume for 'b', the first 5000 are priced at .60 and the remaining at .50.
- One child each month ends up with 2 'b' line items. Some of their 'b' element at .60 and some at .50
The invoice would look like this assuming Child2 had total 'b' volume of 4000:
Customer Element Volume Price
Child1 a 2000 .20
Child1 b 4000 .60
Child1 c 1000 .30
Child2 a 2000 .20
Child2 b 1000 .60
Child2 b 3000 .50
Child2 c 1000 .30
Child3 a 2000 .20
Child3 b 4000 .50
Child3 c 1000 .30
I'm not sure how to go about working this. I inherited this process and SQL DB. So I have complete discretion to make DB changes if that's the best way to make it happen. Currently pricing is applied manually.
CustomerTable: all entities - parents defined when necessary.
PriceTable: base pricing for all parent/child/elements. Does not currently include multiple tier pricing - only the base price. Maybe tier pricing needs to be here? (Even if the tier pricing was in the pricetable, I can't visualize how to break up Child2's 'b' element.)
DataTable: monthly volume numbers for Parent/child/element.
I'm sure I missed some vital bit of information... This challenge just makes my head hurt. So what do you think? Doable?
Thanks!
-Deej