Forum Stats

  • 3,854,952 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

Running Total in Crosstab

660777
660777 Member Posts: 27
edited Sep 22, 2008 11:52AM in Discoverer
Hello all,

I've been unable to find any solutions for my issue, and I'm desperately hoping someone smarter than me can help me out. I'm trying to create a running total across columns for a crosstab report.

The report looks like the following, as best as I can format it:

(date) JAN-09 FEB-09 MAR-09 APR-09
7 7 24 13 <--
(order type) (order #)
Sales Order ACK12342 -4 -3 0 -1
AHSKFD34 -2 -3 -2 0
SSJDHD23 0 0 0 -10
SJDDG1298 -5 0 0 0
KDJD20937 -7 -8 0 0
Work Order WO12620 9 0 0 0
WO12827 8 7 0 0
WO13743 3 7 19 0
Stock N/A 5 0 0 0

I want a running total of each month column. I've added the running total row I'm trying to create above. I haven't been able to create a running total that will give that to me.

I hope this is enough information. I'll post answers to any followup questions.

Thanks.

EDIT: I apologize for the crosstab layout. I've butchered it, and I don't know how to go about making it more readable.

Edited by: user3537345 on Sep 19, 2008 8:35 AM
Tagged:

Answers

  • 524753
    524753 Member Posts: 3,873
    Hi ,

    have a look at this samples.


    http://winrichman.blogspot.com/search/label/cross%20tab
  • 660777
    660777 Member Posts: 27
    Appreciate the link, but none of the examples appear to solve my issue.

    And if I understand the examples, they are for BI Publisher. I'm using BI Discoverer Plus 10g.
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Hi,

    You create running totals using the analytic sum function, e.g.

    SUM(Quantity) OVER (PARTITION BY OrderNumber ORDER BY Month)

    This creates a running total of the quantity for each OrderNumber using the Month field to determine the order in which the quantities are summed.

    Rod West
  • 660777
    660777 Member Posts: 27
    Hi Rod,

    Appreciate your response. Unfortunately, the solution you've posted doesn't go quite far enough.

    Your function does give me the running quantity, but it creates a new column for each OrderNumber, and then sums it. I've looking for a sum of all OrderNumbers (i.e., the whole column). Each column represents a month. So it's a running total of OrderNumbers, by Month. Hope that makes sense.

    I might be attempting to do something that can't be done in Discoverer. I'm just trying every suggestion I can get. I'm hoping it's something I've missed.

    I'll continue to experiment with your suggestion.

    Thanks.
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Hi,

    You say that it creates a new column for each OrderNumber. It shouldn't be doing this. You should create a new calculated item in the report for the running total. That should then be included in the report as another data point. You should then get an extra column for each month in the report.

    If you want something different then I am afraid you will have to explain again.

    Rod West
This discussion has been closed.