SSRS Limitations

1. We cannot bind the Report Variables to a dataset.

2. If we have a dataset holding data for two different categories sales details, in the report if we are using two tablix controls to display the data, One tablilx for each category, using the same dataset as source to both the controls, by using appropriate filtering at details group level, if we try to sum the sales amount for each category using totals at each tablix, by default the sum(sales amount) of both categories appears instead of the sum(sales amount) of that category.

Report Control Visibility based on Parameter selection

Lets assume we have a report with two filters with values as below:

Filter 1  --> X, Y
Filter 2 --> A, B

i have 4 rectangle controls (Place Holders)

Rectangle 1 has some controls that work for input X,A
Rectangle 2 has some controls that work for input X,B
Rectangle 3 has some controls that work for input Y,A
Rectangle 4 has some controls that work for input Y,B

By default all the sections should appear. and id user changes the selection only those sections should appear:

Below is how we can acieve it in SSRS:

=IIF(Parameters!Filter 1.Count > 1,IIF(Parameters!Filter 2.Count > 1, FALSE, IIF(Parameters!Filter 2.Label(0) = "A", FALSE, TRUE)), IIF(Parameters!Filter 2.Count > 1 AND Parameters!Filter 1.Label(0) = "X", False, TRUE))

=IIF(Parameters!Filter 1.Count > 1,IIF(Parameters!Filter 2.Count > 1, FALSE, IIF(Parameters!Filter 2.Label(0) = "A", FALSE, TRUE)), IIF(Parameters!Filter 2.Count > 1 AND Parameters!Filter 1.Label(0) = "Y", False, TRUE))

=IIF(Parameters!Filter 1.Count > 1,IIF(Parameters!Filter 2.Count > 1, FALSE, IIF(Parameters!Filter 2.Label(0) = "B", FALSE, TRUE)), IIF(Parameters!Filter 2.Count > 1 AND Parameters!Filter 1.Label(0) = "X", False, TRUE))

=IIF(Parameters!Filter 1.Count > 1,IIF(Parameters!Filter 2.Count > 1, FALSE, IIF(Parameters!Filter 2.Label(0) = "B", FALSE, TRUE)), IIF(Parameters!Filter 2.Count > 1 AND Parameters!Filter 1.Label(0) = "Y", False, TRUE))

Replace the last delimiter with some word in a delimited string

SELECT REVERSE(STUFF(REVERSE('ABC, CDE, EFG'), CHARINDEX(',', REVERSE('ABC, CDE, EFG')), 1, ' dna '))

Next 3 , 6, 9, 12 months

SQL SERVER:

DECLARE @3MB DATE = CAST(DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS DATE),

@3ME DATE = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,2,GETDATE()))+1,0))AS DATE),

@6MB DATE = CAST(DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,2,GETDATE()))+1,0)AS DATE),

@6ME DATE = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,5,GETDATE()))+1,0))AS DATE),

@9MB DATE = CAST(DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,5,GETDATE()))+1,0)AS DATE),

@9ME DATE = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,8,GETDATE()))+1,0))AS DATE),

@12MB DATE = CAST(DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,8,GETDATE()))+1,0)AS DATE),

@12ME DATE = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(mm,11,GETDATE()))+1,0))AS DATE)

SELECT @3MB AS TMB, @3ME AS TME, @6MB AS SMB, @6ME AS SME, @9MB AS NMB, @9ME AS NME, @12MB AS TWMB, @12ME AS TWME


SSRS:

=Today.AddDays(1-Today.Day) -- 1st day of current month
=Today.AddDays(1-Today.Day).AddMonths(3).AddSeconds(-1) -- end of 3rd month from today
=Today.AddDays(1-Today.Day).AddMonths(3) -- begin of 4th month from today
=Today.AddDays(1-Today.Day).AddMonths(6).AddSeconds(-1) -- end of 6th month from today
=Today.AddDays(1-Today.Day).AddMonths(6) -- begin of 7th month from Today
=Today.AddDays(1-Today.Day).AddMonths(9).AddSeconds(-1) -- end of 9th month from today
=Today.AddDays(1-Today.Day).AddMonths(9) -- begin of 10th month from today
=Today.AddDays(1-Today.Day).AddMonths(12).AddSeconds(-1) -- end of 12th month from today