0

Report with Calculation and Variable %

Jet Reports Historic Posts

June 16, 2015 13:17

None

Follow

I'm creating a table which shows a product list with current cost, but I then want to put in a calculation to work out selling price based on a variable % Margin. I've created the table and can easily do this next part manually. Is there an easy way for Jet to do this?

Thanks
Alan

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts June 16, 2015 16:06

  Hi Alan.
  
  You should be able to add a formula to the table to accomplish this. Its pretty easy to do using the table builder. If you have never used the table builder before then check out the videos on the table builder found here: http://kb.jetreports.com/article/AA-00731
  
  I attached a report that I just created using the table builder and the formula to multiply the cost by 1.5.
  
  Best
  
  Jason

  • Book3.xlsx
• 0
  
  Jet Reports Historic Posts June 17, 2015 07:56

  Jason
  
  Thanks, that works. However, what if I want to change the %? Using your example you have said Unit Cost + 1.5%, what if the next time you want this to be 5%? Do you need to write a separate report for this or could you have an option when you refresh the report to input the desired %?
  
  Regards
  Alan

• 0
  
  Jet Reports Historic Posts June 17, 2015 11:13

  Hi Aldo73,
  
  If you look in cell G4 then you can see that is where Jason has put the 1.5% into the formula, this is where you can change the formula to the desired value, or have a set of formulas in additional columns for different values, i.e. 1.5%, 5%, 10 % etc, you would need to be a designer to change this
  
  Alternatively, as you said have an option for the user to enter the Multiplier % & then reference this cell in the formula & the user enters the value when they refresh the report

• 0
  
  Jet Reports Historic Posts June 17, 2015 13:48

  Clipif
  
  So, to create an option I would then have a formula that looks something like this:
  
  =NP("Formula","([@[Current Cost]]/$H$1)")
  
  With H1 being the cell that contains my desired margin
  
  I can't seem to find much assistance within the help function regarding "options" and "formulas"
  
  You might have guessed that I am quite new to this so trying to familiarise myself with the product.
  
  Would it help if I sent you my excel workbook?
  
  Thanks
  Alan

• 0
  
  Jet Reports Historic Posts June 17, 2015 14:12

  Aldo73,
  
  Yes that is correct, but don't use Row 1, using Jason's example I inserted 2 rows & added a description to B3 & the Value to C3 & then used $c$3 in the formula as you have done below. depending on how you want to work, you could have different values in D3, e3 etc & different formulas to show 5%, 10% etc
  
  upload your example to here in case im not about & someone else wants to take a look, there are always several ways of looking at designing reports, enjoy

• 0
  
  Jet Reports Historic Posts June 18, 2015 07:38

  Clipif
  
  Thanks for your assistance with this. I'm sure I must be missing something very obvious, but can't see it myself.
  
  I'm trying to get a price list for customers based on a % of Cost price. So as an example if I want a 20% margin I would divide current cost by 80%, with the formula being "current cost/(1-0.2)". This is what I think I am asking my report to do, but for some reason it isn't working.
  
  Also, I want the users to be able to amend this % so perhaps to 25%. This should appear as an option for them when they refresh the report.
  
  I've attached a copy of my report for you to look at.
  
  Thanks
  Alan

  • Price List.xlsx
• 0
  
  Jet Reports Historic Posts June 23, 2015 10:35

  Hi Aldo,
  
  sorry been busy, I've had a quick look & if it is for end users that don't have design license, I would hard code some formulas as below & the worst case they can also do some more in excel, I'm not sure how to add a variable percentage so that the user can enter this when the report is refreshed, if there is a way someone please advise. Otherwise if I do work it out i'll update you.
  
  Based on my report then I've used the below which is similar, so in yours replace Unit cost for current cost?
  
  =NP("Formula","[@[Unit Cost]]*1.20") this is for 20%
  =NP("Formula","[@[Unit Cost]]*1.50") this is for 50% etc
  
  So you could have as many columns already in the report with formulas based on the above & the user spicks which % they require, maybe allow them to do this in a pivot report, so they pick the Item & the % they require so it is cleaner?