Using Parameter values in calculation? 1

[jenfromjax]

I am using CR v10 & SQL 2000 and have been asked to do something in Crystal that I don't know is possible. I am hoping someone can advise me.

Scenario:
I am reporting on sold deals in our sales db. These deals can contain any number of products, at different prices, at whatever discount % the salesman set. Only the sale price is contained in the db, the list price & discount % are not. I do not have access to edit the tables.

Task:
A commission form in CR that lists the product name, sale price, list price & discount %. The user gets the discount % from the contract and somehow needs to be prompted for this info for each of the products & then have crystal calculate the list price based on this info.

Example:
Here is what a typical deal looks like (assume quantity = one)
Prod ID Product Name Sale Price Discount % List Price
12345 EFT $100.00 (entered by user) (calculated by CR)
54321 Teller $50.00 (entered by user) (calculated by CR)
23456 ATM $100.00 (entered by user) (calculated by CR)
65432 Loans $50.00 (entered by user) (calculated by CR)

I am wondering if this is possible and if so, can you help? Please let me know if I need to provide additional information. I was trying not to be too wordy & probably left out critical info.

Thanks very much,
Jen

 

[vxxv]

Hi Jen,

Yes, it's perfectaly possible for you to do such operation.
First, you have to create a parameter, in the CR, for the Discount value. Then you can put that parameter in the report, in the place you want.
For the List Price field, you have to do a formula that calculates the respective List Price.
Example:
{SalePrice_field(from DB)-({SalePrice_field(from DB)}*({parameter created}/100))

Then that formula will calculate the List Price you want.

I think that's what you need.

 

[jenfromjax]

Thank you for responding, I was starting to worry no one would.

I tried putting a discount % parameter in the details section and it only returned the first value entered in the paramater. So I created a group on the product & moved all of my details there, including the parameter, and it still only gives me the first parameter entered.

Suppose there are 5 products in a deal. I enter 5 different discount percentages when prompted (10%, 15%, 20%, 25% & 30%). This is what shows up:

Prod ID Product Name Sale Price Discount % List Price
12345 EFT $100.00 10% (calculated by CR)
54321 Teller $50.00 10% (calculated by CR)
23456 ATM $100.00 10% (calculated by CR)
65432 Loans $50.00 10% (calculated by CR)
15432 Core $100.00 10% (calculated by CR)

How do I tell it to go to the next parameter value on the change of product?

 

[ShortyA]

How you are using the parameter is not really as Crystal normally uses it. In this instance it may be worthwhile considering having a separate table for the product/discount combinations and adding that to the report. Then you would get the results you require and wouldn't have to use a parameter.

For info, a parameter would normally be used to limit down the records for a user EG a parameter called ?Region could have values of NORTH, SOUTH, EAST and WEST. If a user selected NORTH then the record selection formula would look like:

{MyTable.Region}={?Region}

Alternatively, if you wanted to select multiple regions and had setup the parameter to allow this then the record selection could look like :

{MyTable.Region}in {?Region}

Apologies if I am stating the obvious but thought it may be helpful!

ShortyA

 

[jenfromjax]

No apologies necessary. Sometimes it helps to have someone validate your thoughts, ya know? I’ve never used a parameter in this way, but have to exhaust all of my resources before I can ask to have the db modified… politics.

I have to admit I am a little bummed. I’ve seen the Crystal gurus around here do some pretty amazing things – I had hoped this might be doable too.

Thanks to all for your input. If I do stumble on a solution I will post it.

~Jen

 

[Kebabmeister]

A solution would be have the parameter set to allow more than 1 value. This would work fine for just a few records but would be unworkable for a large number. You would, of course, need to know what order the records would be presented in.

You then need to refer to the parameter in a formula:

param[RecordNumber]

A new table would however be much preferable

 

[vidru]

The percentages would normally be set at the application level for something like this. By the time the data hits the report, you should already have the percentages for each product from the db.

That said, there are a couple of workarounds I can think of, one being to change the data type of the parameter to String. The end-user would type in the ProductID, a dash (or some other character), then the Discount %. For your example, the user would enter the five parameters like this:
12345-10
54321-15
23456-20
65432-25
15432-30

When you hit each line of the Product detail, you would search the parameter for the current product, then parse the parameter string to get the percentage.

Not a very elegant solution at all, merely a suggestion.

-dave

 

[jenfromjax]

Kebabmeister,

Would you mind telling me a little more about the formula you recommended? I follow the concept, but not 100% sure how to write it. Referring to "param[RecordNumber]"

Currently my parm allows multiple values & I'm OK with having to know the order the records would be presented in. The formula is where I'm having trouble.

Thanks. -Jen

 

[Kebabmeister]

Jen

You would need to create a couple of formula fields and place them on the detail section with your db fields

//@discount
ToText(param[RecordNumber]) + "%"

The above would display the discount entered against each element of the parameter

//@listprice
Original price * (param[RecordNumber] / 100)

Two point to make
1) The parameter would need to be of type Number
2) The record numbers should not exceed the number of parameters you entered as the report will fail.

 

[jenfromjax]

Kebabmeister,

That worked beautifully! I’m still trying to pursue having a field or 2 added to the database to house this info, but this is a great work-around!!

I had to make a few adjustments due to new information I was given by the user. I’ll post the info here just in case anyone ever needs to do this type of thing.

The user told me she had the discount dollar figure and the sale price, but not the list price or discount percentage. She could, of course, easily calculate that herself, but she wants as much as possible to be filled in for her. For this to work, she’ll always have to enter the Discount Amount in the order the products appear & the same number of Discount Amounts as products. Below is my end result:

1. Create a parameter called {?Discount Amount}
*Allow multiple values
*Type: Number

2. First formula @Discount Amount:
//@Discount Amount
"$" + ToText({?Discount Amount}[RecordNumber])

3. Second formula @List Price:
//@List Price
{table.sale_price} + ({?Discount Amount}[RecordNumber])

4. Third formula @Discount %:
//@Discount %
If ({?Discount Amount}[RecordNumber]) = 0 then 0 else
{@List Price} / ({?Discount Amount}[RecordNumber])

5. Add all 3 formulas into the details section where you want them to appear.

6. Tip for formatting formula @Discount % to display percent symbol (versions 10 & 11):
Format field
Number tab
Select your preferred number style
Make sure [blue]Display Currency Symbol[/blue] is checked
Click Customize...
Currency Symbol tab
Make sure [blue]Enable Currency Symbol[/blue] is checked
For the position, use drop-down box to select the symbol to the right of the currency
Change the currency symbol to the percent sign (%)
Click OK until all of the dialog boxes are closed

[red]Thanks again for all of the help!!![/red] I'm sure this isn't the prettiest way to go about it and welcome any critiques.

~Jen