SSRS Parameter Post backs

Most people consider parameter postbacks in SSRS as  an  unwanted operation, but some times they are necessary ,I had  a situation where i need to post back when any of the parameters is selected 

In this situtaion ,create  a   new parameter at the bottom of all parameters   and set some value like datetime as default ,one important  thing to notice is that , when  you set this  default parameter sourced from a dataset query , than this will not work,it needs to  be sourced manually  as shown below.

Irrespective of what you  select in advance parameters (as always refresh or never refresh), setting the default value as above ,will always result in post back of all  the parameters   above   this parameter ,as long as this parameter  is the  last parameter .

Replacing Selfjoins with analytic functions in Sql Server 2012

Sql Server 2012 has introduced a  set of new  analytic  functions ,which helps to  get  different analytical   answers   from an dataset , with  a less complex and more understandable  approach.

The aim of this article is to implement newly introduced lag function ,and the same implementation with an old approach, in my case a self join   based.


DatasetUsed:Adventure Works2008R2

Imagine I want  tocompare  the sales in each country by  current  calendar year  with the sales for  previous calendar year for the below dataset


with cte
as
(
select  d.CalendarYear,t.SalesTerritoryCountry ,sum(s.SalesAmount) as sales  from [dbo].[DimDate] d inner join [dbo].[FactResellerSales] s
on d.DateKey=s.OrderDateKey
inner join DimSalesTerritory t
on s.SalesTerritoryKey=t.SalesTerritoryKey
group by d.CalendarYear,t.SalesTerritoryCountry)


Implementing using the lag function,

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as  CurrentYearSales,lag(cte.sales,1,0) over(partition by cte.salesterritorycountry order by Cte.calendaryear) as t   from cte


Implementing using a selfjoin approach

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as CurrentYearSales,Coalesce(t.sales,0) as PrevYearSales from cte
left join cte  as t on
t.CalendarYear=cte.CalendarYear-1
and  cte.SalesTerritoryCountry=t.SalesTerritoryCountry
order by SalesTerritoryCountry,calendaryear

Note:The null values which were replaced by coalsce in this query, is done by the third attribute of lag function with a value 0(lag(cte.sales,1,0))


As you can observe analytic functions makes  the code more readable,  and we can embed more complex logic dervived from this.