Ask Search:
Shaya KrautShaya Kraut 

calculated field in a report

test

For reports, it looks like the 'formula' field option is only available if you display a report summarized in some way. Is there any way to add a 'calculated field' (that's what it is called in Access I believe); i.e. an additional column with a formula that calculates separately for each row. In this case, I wish I could add a column that would show a simple subraction--difference between expected and total value won opportunities. I think I can add a custom field to campaigns to do this math and then just display this custom field...but I wondered if there is a way to do this on the report end, without messing with other settings--this would be less intrusive. I guess I'm used to doing it in Access, and it is so useful to be able to do calculations like this on the fly, for each record.
Mark Passovoy @sfdc_markMark Passovoy @sfdc_mark
Unfortuantely, reporting in Salesforce isn't quite that advanced. You can have formula's in your report, but it will apply to each record.

What I would recommend trying to do s create a formula field on the object that you are working with (looks like Campaigns here), and try to make the calculations you are looking for on each object. You can use some IF statements to use different calculations depending on criteria.
Steve MolisSteve Molis
I agree with @MarkPass on this, a custom Formula Field is the way to go.

What is the formula/result you're trying to display? (Object, Fields, calculations, etc.)
Nik PanterNik Panter
The only thing I will add is that if you or anyone at your organization has familiarity with developmentand has played with Apex and Visualforce, you create create a visualforce report with the custom formula calculated at report run.  However @MarkPass and @SteveMo have the answer for native, clicks not code salesforce.
Shaya KrautShaya Kraut
Thank you, I did make a custom field, for campaigns, and then just displayed it in the report.
User-added image

Next problem is that I am unable to show this report as is in a dashboard--which was the purpose of it. I tried to make a table element in our dashboard, using this report as the source--first it told me I needed to either use a summary view in the report, or specify row limit and dashboard settings. I set row limit and filled in the dashboard settings on report, and got the strange dashboard result below. The I played with summarizing the report in various ways (even though the data does not need summarizing) to appease it and didn't really get anywhere. It would only show 2 columns (not 4 as in the report).
User-added image
Steve MolisSteve Molis
Hi Shaya,

Can you make a mock-up of what you'd like the DB Chart to look like and post a screenshot?
Shaya KrautShaya Kraut
Thanks,
I can make a mockup but really I wanted the dashboard to look EXACTLY like the report. Only purpose is so people in office that don't want to use SF will see it without having to go looking for the report--some of them also use blackberries. The 'table' in the dashboard seems to only want to display 2 columns, no matter what I do. I just want to show the report just the way it looks in the report window--with the 4 columns.
It seems like a super simple thing, but I can't seem to get it to work.

User-added image
Steve MolisSteve Molis
Kinda like this?  


User-added image
Shaya KrautShaya Kraut
yes! Something like that would be perfect. Did you get that to show in a dashboard?
Steve MolisSteve Molis
Here you go  

User-added image
Shaya KrautShaya Kraut
Steve,
Could you possibly send screenshot of how your report is set up--

I had noticed these 'table columns' before, but mine don't offer option of all the columns that are in the report. It only gives me 'campaign name' (the first field) and record count (not a field, but what SF started showing when I summarized by campaign name).

Sorry to be so dense, obviously very new to SF. Thank you so much for your help.
Steve MolisSteve Molis
Hi Shaya,
No worries, you're not dense just new to the system, just like I was when I first became an SFDC Admin (although some folks would argue that I'm also quite dense).  

For my report I created a Summary Report grouped by Account Name, and I summarized 3 seperate $Amount fields fields on the Opportunity.  

Where are the $Amount fields that you're trying to display in your chart located?  Is everything on the Campaign object?   
Shaya KrautShaya Kraut
Thank you!
yes, the $amount fields are all on the campaign object. When I created the report I made it as a 'campaign, campaign' type of report--in the window where you have to specify that. We have a campaign to hold opportunities for each fiscal year--the opportunities are memberships people have purchased. I tried making a 'campaign, campaign with opportunity' report--and for some reason in this report the 'expected revenue' field for campaign was not available, only expected revenue for the opportunities was available to pick. However my custom 'revenue difference' campaign field is available. wierd. It just occurred to me that maybe it doesn't let me access the field because it has the same name? Maybe if I make a custom field that holds the same expected revenue figure but is named something else it will let me use it? The 'Total Won opportunties' field is not available either, but I can get that by summing the opportunity  'Amount' column. hmm. I will play with it some more.
Shaya KrautShaya Kraut
I tried turning the report into a 'matrix' report, and am able to get a graph on the dashboard to show the information, but the table (which is really what we need) won't cooperate. I wish I could just show the report on the dashboard EXACTLY as it looks here.

User-added image


User-added image
Steve MolisSteve Molis
Hi Shaya,
You're gettin' there...  I think you need to just use a Summary Report (grouped by Campaign Name) and summarize the $Amount fields that you want to appear as columns in your DB Chart.
Steve MolisSteve Molis
Shaya, are you all set now, or do you still need help with this?
Shaya KrautShaya Kraut
Thank you so much for all your help, Steve Mo!
I did get this to work more or less, thanks to your help.
User-added image

At lower right I really wanted to show actual records, names, of people/companies about to expire. That way our director could see the names and call them when he has a bit of spare time in between meetings. It seems that SF wants you to summarize everything, rather than show records. When I tried to make the table in the dashboard based on a report that was NOT summarized it would only show 2 columns. Oh, well.
Steve MolisSteve Molis
Thanks @ShayaKraut 

You're really on the cusp of the whole Dashboard/Report thing.  At some point you just have to say "If the user needs to see that level of detail, then they really should be looking at the underlying report and not a dashboard chart"  
Don LaCoyDon LaCoy
Help.  I need to do this exact thing.  Seems like the formula field is the way to go.  But noone seems to have indicated how to add a formula field.

In opportunities I have:
$/ system
Systems/ year

I want to multiply these together to get $/ year

So basically I already have fields A and B.  I need to add field C = A * B

How do I do this?  In set up?  In developer?  In C++?

Pls help!  Thanks! D
Mark Passovoy @sfdc_markMark Passovoy @sfdc_mark
This is very easy as long as you are a systems administrator in Salesforce.

Create a new formula field of type currency ($). Then use your fields to do the calculation. For example:

Field_A__c * Field_B__c

Just replace the temp field names with the actual field names that you can get from the Insert field button on the Formula editor.
Steve MolisSteve Molis
Listen to your friend @MarkPass, he's a cool dude.