Tuesday, February 16, 2016

Running Totals / Cumulative sum in Reporting Services

How to get a running total in SSRS?

Use this Expression

=RunningValue(Fields!Gemiddeld.Value, Sum, nothing)



Result (normal SUM vs Runing Totals)


Friday, February 12, 2016

Cumulative sum in SQL

This is one of the possibilities to calculate cumulative sum in SQL.
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
Result
 ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |
When grouping is required, it is better to use this solution (only SQL2012 and later)
select 
 cte.EPICSystem_Title
 ,cte.Uren
 ,cte.Realisation
 ,cte.Datum
 ,cte.Budget
    ,sum(cte.uren) over (partition by cte.EPICSystem_Title order by datum) as CumUren
 ,sum(cte.Realisation) over (partition by cte.EPICSystem_Title order by datum) as CumRealisation
from cte

Wednesday, February 10, 2016

Add date stamp to SSIS export files

When creating a file in SSIS it can be useful to incorporate a date and/or a time stamp into the name of the file, like this:



1) Select the connection manager:


2) Go to properties, Expressions, click on  the dotted button



3) In the popped up screen, choose ConnectionString:


4) Put the following expression in the Expression Builder:


"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + 
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

Every backslash in the path needs to be typed twice so \\sftp.xxx.nl\xxx\csv\ becomes  \\\\sftp.xxx.nl\\xx\\csv\\ 

5) Save and run

6) When you also want the time use this:

"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + "_" +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2)+
RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".csv"

Interoperability and Coexistence (Integration Services)

The following table lists some of the differences between the current and earlier versions of Integration Services.