Summary Report to filter different figures for Summary totals

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
I am having some trouble working out the best way to do a report like this. I have a table which contains orders. The orders have two types and then many different statuses (Which are check boxes).

Example data:

Branch, Type, Status
10, Order, Invoiced
15, Deal, Awaiting Paperwork.

I want a report to show Summaries like

Branch, Orders (That are invoiced), Deals (That are not delivered).
10, £100, £200
15, £250, £450

The value is based on a field within each order.

What I can't work out how to do is filter the data in the summary columns as you can only filter on ALL the data not specific bits for each column...

Hope this makes sense, its hard to explain!!
Photo of stuaz

stuaz

  • 10 Points

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 590 Points 500 badge 2x thumb
There is more than 1 way to do this.

I suggest that that instead of trying to use a Table report, use a Summary Report and use in that report use Cross tabs. The Rows will be Branches and the columns will be Order Status.

The value being totalled will be the order value field.
Once the report is written you will be able to drill into any table body total, to see, for example Branch 156 orders which are in status "Deals". If you like you can show 2 figures in the table body - the order value and the order count (how many orders)
Photo of stuaz

stuaz

  • 10 Points
Ok that sort of helps - at least in the right direction.

However, the 'value' which I total is different field depending on the two status's.

I suppose I could create a field that gives the value based on what the status is, and then report on that value?

Also how can I get around the fact I only want certain records 'counted or summed' in one of the columns but that is different from the other?

E.g Status 1 = Orders
Status 2 = Deals (Not invoiced or cancelled) - this is two checkboxes.

I only want Deals that meet a certain criteria to be 'summed or counted' and that is different to what I want counted or summed in Status 1. If I use the filter tool in the report won't it exclude them overall?
Photo of Mark_Shnier

Mark_Shnier

  • 590 Points 500 badge 2x thumb
Why not just make 1 new formula field  for reporting purposes which has the value that you want, and ensure that it will calculate to zero if the criteria is not met.
Photo of stuaz

stuaz

  • 10 Points
Hi Mark,

This is exactly what I did and it worked perfectly.

Thanks for your help.