August 3, 2015 at 8:31 am
Hi, I have a need to display a running value along rows by a column group
Widget type is on rows with the number of widgets as the measure
Column groups are month, quarter and year
At the moment it looks like this, with each month's value
Year20152016
Quarter341
MonthOctNovDecJanFebMarAprMayJun
Widget a898467321
Widget b987255456
I can use RunningValue along the row but this just returns an cumulative value of all columns to the left without reference to the group
Like so
Year20152016
Quarter341
MonthOctNovDecJanFebMarAprMayJun
Widget a81725293542454748
Widget b91724263136404551
What I want is this: -
Year20152016
Quarter341
MonthOctNovDecJanFebMarAprMayJun
Widget a81725293542356
Widget b917242631364914
Where the value start again from zero for the new year
Any help will be appreciated
Thanks
Duncan
August 3, 2015 at 12:01 pm
Now that I've finally gotten this to work, this is how I did it. (I'm using 2012 but compatibility is 2008).
In all honesty, since I completely forgot how to do subreports (shameful, I know!), I watched the WiseOwl video on it. Well worth the watching, and super easy to follow. It can be found here.
First you create the outer report, for the Year(s).
SELECT DISTINCT YEAR(orderdate) AS OrderYear
FROM Sales.Orders
ORDER BY OrderYear
Then you create the subreport... this is the stored procedure I used... won't work unless you're using SQL Server 2008R2 at least.
CREATE PROC horizRunTotal AS
SELECT
orderDate
,DATEPART(QUARTER,orderDate) AS Qtr
,MONTH(orderDate) AS OrderMonth
,YEAR(OrderDate) AS OrderYear
,productid
,qty
, SUM(x.qty) OVER (PARTITION BY OrderMonth, ProductID
ORDER BY OrderYear, OrderMonth, ProductID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunTotal
FROM
(SELECT
so.orderDate
,DATEPART(QUARTER,so.orderDate) AS Qtr
,MONTH(so.orderDate) AS OrderMonth
,YEAR(so.OrderDate) AS OrderYear
,sod.productid
,sod.qty
FROM Sales.Orders so INNER JOIN Sales.OrderDetails sod
ON so.OrderID = sod.OrderID) x
I'm cheating and using a dataset from Itzik Ben-Gan's TSQL 2012 windowing functions book[/url] (only because it's super simple and clear... unlike that AdventureWorks monstrosity).
If you don't have 2008R2, then you'll likely have to use one of the tricks on http://www.sqlauthority.com, like this one[/url].
Now that I've explained this backwards, ...
step 1 is from SQL Authority,
step 2, build the report/subreport.
August 4, 2015 at 2:06 am
Hi, thanks for the response, it's not quite what I had in mind as I was hoping to use SSRS functions to present the data on the face of the report
I have a single data-set that returns a list of all the widgets over two years. The idea is to display a summary of the data in a matrix below which would then be a table with the detailed data
On the matrix, the cumulative, or, running totals should display from left-to-right along each row where each row represents a type of widget and the column groups are month within quarter within year. The running or cumulative totals are the year-to-date values for each year
If I use the SSRS RunningValue function referencing the row group the result is a simple accumulation along each row that ignores the column groups. Consequently the result for column d is the sum a+b+c+d, which is fine but if column d is in the year after a, b & c I want the column value to be simply the value of d (if c and d are in the next year to a and b the value for column d would be the sum of c plus d)
I had thought there would be a way of referencing the Year column group in the value expression but cannot find any information on this
Thanks
August 4, 2015 at 2:44 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply