Creating Calculations in Reports

[ChrisSedgwick]

Hi All,

I've created a report that shows quotation values for each of our customer. I want the report to be a summary rather than show a line by line figure. So I've grouped by Customer and then created a =Sum([QuoteValue]) which I've placed in my Customer header. All works fine and I have my desired output.

What I now want to do is work out the total of Quotes that are "Won", "Lost" and are still outstanding which are left blank, or null.

How would I create a formula that will do this, yet still keep the report summarised. I've attached a snapshot of the report I have currently. I've included the Won/Lost field in my query already, I'm not sure how to incorporate it into my report and get the right formula.

Any help on this would be great!

Thanks in advance.

 

[plog]

You're hacking together a totals query. My advice is to do this in an actual query:

https://support.office.com/en-au/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8

Get the data like you want it in a query, then use a Report to display and format the data. As for your issues, it really depends on how your underlying data is set up. Most likely you will use a conditional statement like such:

TotalWon: SUM(Iif([QuoteStatus]='Won', [QuoteValue], 0))

Basically if a record was Won, it adds it to the total, if not, it adds 0. You would do this for each category.

 

[ChrisSedgwick]

Hi plog,

Thanks for your response, I've used the Totals function within the query and managed to total all the quote value for each customer.

Would I also include the formula you providedin your comment in the query aswell?

Would this mean also that I would have to include the Won/Lost field in the query? I tried this originally and it showed each customer multiple times.

Thanks

 

[plog]

Yes, you would use my function (or a version of it using your fields). You would make one calculated field for every category you wanted to total.

Can you post the structure of the underlying data source of your query?

 

[ChrisSedgwick]

I've done it! Superb thank you.

If I wanted to see what was outstadning, so Won + Lost then minus that off the total QuoteValue, how would I do that. Would I be able to use the same formula but just use 'Null', or would that look for the text Null in the field?

Or, could I create another exprression that totals the Won and Lost together and then another that subtracts it from the total Quote Value?

Hope I've explained this well enough.

 

[ChrisSedgwick]

I've tried to total the won and the lost together but I get an error stating that subquiers cannot be included in the expression?

 

[plog]

Again, I don't know what the structure of your data is, so I can't help you specifically--I don't know what fields have what values. However if outstanding is Total - Won - Lost, then you could use a similar formula:

Sum(If (Not Won and Not Lost, Value, 0))

 

[ChrisSedgwick]

Apologies, what do you need from me?

 

[plog]

Nothing if you are getting there. If you need specific help, you need to post what the relevant fields are.

 

[ChrisSedgwick]

I'm almost there, just the final expression isn't working. It doesn't display any totals when I run the query, they're all £0.00.

I've attached a snapshot to show you where I'm up in the query, hope this helps?

This is the expression that isn't giving me the correct data.

Sum(IIf(Not 'Won' And Not 'Lost',[ContributionValue],0))

 

[plog]

You didn't tell your Iif statement what field to compare to.

 

[ChrisSedgwick]

Hi plog,

I've upadted the expression, however I'm receiving an error message stating that the expression you entered contains an invalid syntax.

This is the expression

TotalOutstanding: Sum(IIf([Project!WonLost]) Not 'Won' And Not 'Lost',[ContributionValue],0))

 

[ChrisSedgwick]

Hi,

I've managed to get it to run the query, I realised I have closed off the expression after the [Project!WonLost]

This is what I have now, however each record shows £0.00.

TotalOutstanding: Sum(IIf([Project!WonLost]=Not 'Won' And Not 'Lost',[ContributionValue],0))

 

[plog]

[Project!WonLost]=Not 'Won' And Not 'Lost'

First, you need to use the correct comparison operator "=Not..." is incorrect. Second you can't just string conditions together, you need to tell it what you are comparing each and every time:

[YourField]<>'Won' AND [YourField]<>'Lost'

 

[ChrisSedgwick]

plog,

Apologies, pardon my stupidity, I'm really struggerling with this one. So I'll list everything I have data wise and try and explain it better, I admit I have been a little vague.

In the query I have 4 tables: Quotes, Projects, Tenders, CustomersMain - I want to build a query that will

• show me the total contibution (which is sort of profit from each job) of all the quotes, then
• another column that calculates the total contribution of all the projects that are Won, then Lost and finally
• what the Outstanding Total Contribution minus the Won and the Lost totals.

The field names that I'll need and their respective tables are as follows:

• TotalContribution [Quotes]
• CustomerName [CustomersMain]
• WonLost [Projects]

So far, I've been able with your help to create 2 columns that show TotalWon & TotalLost. I now just need help building the expression that will show the the OutstandingContribution, which is the TotalContribution minus the TotalWon & TotalLost.


I've not really worked with expressions and formulas before so please forgive me.



I've attached some screen shots for you to see the query and how it looks now.

 

[plog]

You're so close:

TotalOutstanding: Sum(iif([Project!WonLost]<>'Lost' AND [Project!WonLost]<>'Won', [ContributionValue], 0))

 

[ChrisSedgwick]

It's still show zero values (£0.00)

See screenshots....

 

[plog]

What values can Projects!WonLost have?

 

[ChrisSedgwick]

Won or Lost or Null

There is no Default Value

 

[plog]

Nulls make things weird. You have to explicitly test for them. Try this:

TotalOutstanding: Sum(IIf(IsNull([Project!WonLost]), [ContributionValue], 0))