Something like a running total...

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Something like a running total...

Something like a running total...

Rate Topic

Display Mode

Topic Options

Author

Message

marty.seed

Posted Monday, November 9, 2009 9:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:32 AM
Points: 93, Visits: 192

Hello!

I am tracking our company's inventory in a SSRS 2008 Tablix report. I am displaying the inventory for each day, at the end of the Month I need to display the Month inventory...basically the inventory on the last day of the month.

Select invDate, Branch, Month(invdate) as Month_,
InvBalance
From DailyInventory

Matrix row group=Branch
column group=Month_, invDate

Post #815869

Nick_UK

Posted Monday, November 9, 2009 9:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 11, 2015 4:32 AM
Points: 1,350, Visits: 1,029

Could you do a sum on the values where the invdate equals the max invdate within the month grouping, something along the lines of,

SUM(IIF(invdate = MAX(invdate,"Month"),InvBalance,0)

Post #815904

marty.seed

Posted Monday, November 9, 2009 2:31 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:32 AM
Points: 93, Visits: 192

That's not working, I am trying to do this in a Matrix so it is not recognizing that "cell" as having any data

Post #816139

marty.seed

Posted Monday, November 9, 2009 2:45 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:32 AM
Points: 93, Visits: 192

We are definitely on the right track here, I need to find the value for the max(date) and pass it to the "Total" cell...now how to do this??? w00t

Post #816152

marty.seed

Posted Tuesday, November 10, 2009 11:50 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:32 AM
Points: 93, Visits: 192

I figured out how to do it for the weekly total rows...

=iif(InScope("Date2"), sum(Fields!Sales.Value), last(Fields!Sales.Value))

Now the conundrum lies in trying to total the total columns correctly

Post #816720

Jeff Moden

Posted Friday, July 16, 2010 5:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 36,578, Visits: 33,331

marty.seed (11/10/2009)

Hi Marty,

Did you ever get this sorted out?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." Wink

--22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems

Post #954269

« Prev Topic | Next Topic »

Permissions