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

Group By VS Compute BY

GROUP BY produces a single result set. There is one row for each group containing only the grouping columns and aggregate functions that show the subaggregate for that group.
The select list can contain only the grouping columns and aggregate functions.
COMPUTE produces multiple result sets. One kind of result set contains the detail rows for each group containing the expressions from the select list.
The other type of result set contains the subaggregate for a group, or the total aggregate for the SELECT statement.
The select list can contain expressions other than the grouping columns or aggregate functions.
The aggregate functions are specified in the COMPUTE clause, not in the select list.

Visibility based on Parameter selection in SSRS

Add a function called "IsColumnSelected" that accepts the multi-value parameter and a column name and returns a boolean value:
''' <summary>
''' Return whether the passed column name has been selected
''' in the multi-value parameter, whether it should be visible
''' or not.
''' </summary>

Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean
    ' return whether the passed column name is in the multi-value
    ' parameter array
    Return 0 < InStr(vbNullChar & Join(objMultiValueParam, vbNullChar) & _
        vbNullChar, vbNullChar & strColumnName & vbNullChar)
End Function

using an expression that needs to be put in each column's "Visibility" property which calls "IsColumnSelected". Put the expression below in the "Visibility" property of the first column:

=Not Code.IsColumnSelected(Parameters!ColumnsToDisplay.Value, "Column1")

Alternate Row Coloring

= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

OR

= iif(RunningValue(Fields![rowgroupfield].Value.ToString,CountDistinct,Nothing) Mod 2,"Gainsboro", "White")

OR

=IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2)

OR

Private bOddRow As Boolean'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
         ByVal EvenColor As String, ByVal Toggle As Boolean) As String
    If Toggle Then bOddRow = Not bOddRow
    If bOddRow Then
        Return OddColor
    Else
        Return EvenColor
    End If
End Function

=Code.AlternateColor("AliceBlue", "White", True)

Pagination in SQL

-- SQL Server stored procedure - SQL pagination - SQL row_number function

CREATE PROCEDURE uspContactInfoByPage

                @PageSize   INT,

                @PageNumber INT

AS

  BEGIN

    WITH cteContact

         AS (SELECT ContactID,

                    FirstName,

                    MiddleName,

                    LastName,

                    EmailAddress,

                    Phone,

                    ROW_NUMBER()

                      OVER(ORDER BY LastName, FirstName, ContactID) AS SEQUENCE

             FROM   Person.Contact)

    SELECT Name = replace(FirstName + ' ' + isnull(MiddleName,'') + ' ' + LastName,

                          ' ',' '),

           [Email Address] = EmailAddress,

           Telephone = Phone

    FROM   cteContact

    WHERE  SEQUENCE BETWEEN @PageSize * @PageNumber + 1

    AND @PageSize * (@PageNumber + 1)

  END

GO

EXEC dbo.uspContactInfoByPage

  50 ,

  100

GO

Ranking Functions

-- Microsoft T-SQL ranking functions comparison

------------

-- MSSQL row_number (row number sequence) - NTILE

-- MSSQL rank and dense_rank functions - over order by clause

-- Data is rounded down to the thousands for ranking

USE AdventureWorks;

SELECT  

         c.AccountNumber                  AS CustAccount,

         FLOOR(h.SubTotal /1000)          AS [SubTotal (Thousands $)],

         ROW_NUMBER()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS RowNumber,

         RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS Rank,

         DENSE_RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS DenseRank,

         NTILE(5)

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS NTile

FROM     Sales.Customer c

         INNER JOIN Sales.SalesOrderHeader h

           ON c.CustomerID = h.CustomerID

         INNER JOIN Sales.SalesTerritory t

           ON h.TerritoryID = t.TerritoryID

WHERE    t.Name = 'Germany'

  AND    YEAR(OrderDate) = 2004

  AND    SubTotal >= 4000.0

ORDER BY RowNumber;

GO

/* Results

CustAccount	SubTotal (Thousands $)	RowNumber	Rank	DenseRank	NTile
AW00000230	100	1	1	1	1
AW00000230	88	2	2	2	1
AW00000302	77	3	3	3	1
AW00000320	68	4	4	4	1
AW00000536	68	5	4	4	1
AW00000536	64	6	6	5	1
AW00000266	58	7	7	6	1
AW00000302	44	8	8	7	2
AW00000687	43	9	9	8	2
AW00000482	36	10	10	9	2
AW00000176	36	11	10	9	2

Import & export images in SQL

USE AdventureWorks;

-- Create image warehouse for importing image into sql database

CREATE TABLE dbo.ImageWarehouse (

  ImageWarehouseID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  ImageName        VARCHAR(100),

  Photo            VARBINARY(MAX))

GO

-- SQL Server import image - sql storing images database sql server

INSERT INTO dbo.ImageWarehouse

           ([ImageName])

VALUES     ('5THAVE.JPG')

UPDATE dbo.ImageWarehouse

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'e:\image\photo\5THAVE.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = '5THAVE.JPG'

GO

-- Check table population

SELECT *

FROM   dbo.ImageWarehouse

GO

-- SQL Server export image

DECLARE  @SQLcommand NVARCHAR(4000)

-- Keep the command on ONE LINE - SINGLE LINE!!! - broken here for presentation

SET @SQLcommand = 'bcp "SELECT Photo FROM AdventureWorks.dbo.ImageWarehouse"

                   queryout "e:\image\photo\exp5THAVE.jpg" -T -n -SPROD\SQL2005'

PRINT @SQLcommand -- debugging

EXEC xp_cmdshell   @SQLcommand

GO

------------

-- T-SQL Export all images in table to file system folder

-- Source table: Production.ProductPhoto  - Destination: K:\data\images\productphoto\

------------

USE AdventureWorks2008;

GO

DECLARE  @Command       VARCHAR(4096),   -- dynamic command

         @PhotoID       INT,

         @ImageFileName VARCHAR(128)

DECLARE crsImage CURSOR  FOR             -- Cursor for each image in table

SELECT ProductPhotoID,

       LargePhotoFileName

FROM   Production.ProductPhoto

WHERE  LargePhotoFileName != 'no_image_available_large.gif'

OPEN crsImage

FETCH NEXT FROM crsImage

INTO @PhotoID,

     @ImageFileName

WHILE (@@FETCH_STATUS = 0) -- Cursor loop 

  BEGIN

-- Keep the bcp command on ONE LINE - SINGLE LINE!!! - broken up for presentation

    SET @Command = 'bcp "SELECT LargePhoto FROM

    AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = ' +

    convert(VARCHAR,@PhotoID) + '" queryout "K:\data\images\productphoto\' +

    @ImageFileName + '" -T -n -SYOURSERVER'

    

    PRINT @Command -- debugging 

/* bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto

WHERE ProductPhotoID = 69" queryout

"K:\data\images\productphoto\racer02_black_f_large.gif" -T -n -SYOURSERVER

*/

    

    EXEC xp_cmdshell @Command     -- Carry out image export to file from db table

    

    FETCH NEXT FROM crsImage

    INTO @PhotoID,

         @ImageFileName

  END  -- cursor loop

CLOSE crsImage

DEALLOCATE crsImage

/*output

NULL

Starting copy...

NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 16     Average : (62.50 rows per sec.)

NULL

.....

*/