Get Last 12 Months and their Start Dates

CREATE PROCEDURE P_Last12Months
AS
BEGIN
    DECLARE @Months TABLE
    (
    MID INT IDENTITY(1,1),
    MonthNumber INT,
    MonthDesc VARCHAR(15),
    StartDate DATE
    )

    DECLARE @Loop INT = 0, @Today DATETIME = GETDATE()

    WHILE (@Loop > -12)
    BEGIN

    INSERT @Months
    SELECT      IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) AS MonthNumber
            , DATENAME(MONTH,DATEADD(MONTH, @Loop, @Today)) AS MonthDesc
            , DATEFROMPARTS(IIF(MONTH(@Today) + @Loop <= 0, YEAR(@Today) - 1,YEAR(@Today)),IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) , 1)

    SET @Loop = @Loop - 1

    END

    SELECT * FROM @Months
END

Displaying Powerview report using a sharepoint silverlight webpart

1. Edit your page and click on "Add a Web Part".
2. From the "Media and Content" category, select "Silverlight Web Part" and click the "Add" button.
3. You will now need to enter a URL to the Silverlight XAP file. I found this location by opening the Power View editor and looking in the HTML source to see where it was loaded. In any case, it is something like this (your mileage may vary):
• http://[sharepoint_server]/_layouts/ReportServer/ClientBin/Microsoft.Reporting.AdHoc.Shell.Bootstrapper.xap?v=11.0.2100.60
4. You will see that SharePoint tries to load the Silverlight component, but fails. Not to worry, there are more settings to fill in before it actually works!
5. First of all, you should see the Web Part's properties on the right hand side of your screen. If not, select the little drop down in the title bar of the Web Part and click on "Edit Web Part".
6. I gave my Web Part a fixed width and height, but you can set it as you like.
7. Chrome Type can be set to None to give a nice integrated look and feel.
8. The most import settings are to be found under the heading "Other Settings".
9. Click the (empty) textbox "Custom Initialization Parameters" and click on the button with the ellipsis (...).
10. Here are my settings:
    • ItemPath=<LINK TO RDLX file>,
      ReportServerUri=http://<SHAREPOINT_SERVER>/_vti_bin/reportserver/,
      ViewMode=Presentation,
      ServerTraceLevel=1,
      AuthenticationMode=Windows,
      ReportSection=ReportSection,
      Fit=True,
      PreviewBar=False,
      BackgroundColor=White,
      Border=True,
      AllowEditViewMode=False,
      AllowFullScreenViewMode=False
    • The tricky bit is of course the url to the ItemPath, but the easiest way to find this is by opening the Power View editor, open "View Source" and search for the text: param name="InitParams"
    • Right after this text is the value parameter. Its contents pretty much gives you the ItemPath you need. Be sure to translate the encoded characters such as %3A to :, %2F to /, %20 to a space, etc.
11. Finally, hit OK or Apply to make sure that it actually works!

If you would rather start with Page 2 (the second view) of your Power View report, simply change the value of the parameter ReportSection. For instance, ReportSection=ReportSection2 will show you the second view.

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

SSRS References

SSRS Real Time Scenarios