• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Daily Archives: March 20, 2015

Reporting Services – How to add a custom renderer for SSRS 2012 with SharePoint/Native Mode

20 Friday Mar 2015

Posted by Pawan Kumar Khowal in Reporting Services

≈ 5 Comments

Tags

How to add a custom renderer for SSRS 2012 with Native Mode, How to add a custom renderer for SSRS 2012 with SharePoint, How to add a custom renderer for SSRS 2012 with SharePoint/Native Mode, InterviewQuestions, InterviewQuestions for SQL, Puzzles, Queries for SQL Interview, SQL Puzzles, SQLSERVER, SSRS, SSRS Interview Questions, T SQL Puzzles, TSQL, TSQL Queries


How to add a custom renderer for SSRS 2012 with SharePoint/Native Mode

A rendering extension is a component or module of a report server that transforms report data and layout information into a device-specific format. SQL Server Reporting Services includes seven rendering extensions: HTML, Excel, Word, CSV or Text, XML, Image, and PDF. You can create additional rendering extensions to generate reports in other formats.

Writing Custom Rendering Extensions

Before you decide to create a custom rendering extension, you should evaluate simpler alternatives.

You can:
• Customize rendered output by specifying device information settings for existing extensions.
• Add custom formatting and presentation features by combining XSL Transformations (XSLT) with the output of the XML rendering format.

Writing a custom rendering extension is difficult. A rendering extension must typically support all possible combinations of report elements and requires that you implement hundreds of classes, interfaces, methods, and properties.

If you must render a report in a format that is not included with Reporting Services and decide to write your own managed code implementation of a rendering extension, the rendering extension code must implement the Microsoft.ReportingServices.OnDemandReportRendering.IRenderingExtension interface, which is required by the by the report server.

Build the custom extension renderer dll

Open the appropriate project for your environment. The 2012 project is usable for both SSRS 2008 and 2012. Because this is a normal .NET assembly project, BIDS is not necessary.

1. Add required references. E.g.-
Microsoft.ReportingServices.ExcelRendering.dll
Microsoft.ReportingServices.ProcessingCore.dll
Microsoft.ReportingServices.Interfaces.dll

all of these are in [SQL Reporting Services folder]\ReportServer\Bin\ on the SQL Server machine. I found it easiest to copy the entire contents of that bin folder to the development machine for investigation and future use in extensions.

The ExcelRendering and ProcessingCore will give a warning about a newer .NET version, you can safely ignore this. (see http://support.microsoft.com/kb/2722683).
Implementing the IRenderingExtension Interface

The rendering extension takes the results from a report definition that is combined with the actual data and renders the resulting data to a format that is useable. The transformation of the combined data and formatting is done by using a common language runtime (CLR) class that implements IRenderingExtension. This transforms the object model into an output format that is consumable by a viewer, printer, or other output target.
The IRenderingExtension has three methods that must be implemented:
• Render – renders the report.
• RenderStream – renders a specific stream from the report.
• GetRenderingResource – gets additional information, such as icons, that are required for the report.

Sample code for Render Method-

--

public bool Render(Microsoft.ReportingServices.OnDemandReportRenderingReport report, System.Collections.SpecializedNameValueCollection reportServerParameters, System.Collections.SpecializedNameValueCollection deviceInfo, System.Collections.SpecializedNameValueCollection clientCapabilities, ref System.Collections.Hashtable renderProperties, CreateAndRegisterStream createAndRegisterStream)
{

	//Reader for excel file
	IExcelDataReader excelReader = null;
	//Excel file
	FileStream fileStream = null;
	try
	{
		//Byte array to copy Excel to Memory Stream
		byte[] buffer = new byte[32768];

		//Final output stream which wil write all the data to CSV file
		Stream outputStream = createAndRegisterStream(report.Name, "CSV", Encoding.UTF8, "text/csv", true, StreamOper.CreateAndRegister); 

		using (MemoryStream outputMemoryStream = new MemoryStream())
		{
				foreach (SubRenderer sr in subRenderers)
				{
					if (sr.Render(report, reportServerParameters, deviceInfo, clientCapabilities, ref renderProperties))
					{
						while (true)
						{
							int read = sr.RegisteredStream.Read(buffer, 0, buffer.Length);
							if (read <= 0)
							{
							break;
							}
							outputMemoryStream.Write(buffer, 0, read);
						}
						sr.CloseStreams();
					}
				}

				//Write to the excel file
				using (Stream file = File.Create(excelFileName))
				{
				outputMemoryStream.WriteTo(file);
				}
		}

		//Read the excel file into a Dataset
		fileStream = File.Open(excelFileName, FileMode.Open, FileAccess.Read);
		excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream, ReadOption.Loose);
		DataSet dataSet = excelReader.AsDataSet(true); 

		string connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

		string query = "SELECT * from [" + report.Name + "$]";
		DataTable dt = new DataTable();
		DataSet ds = new DataSet();
		
		using (OleDbConnection conn = new OleDbConnection(connectionString))
		{
			conn.Open();
			using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn))
			{
				dataAdapter.Fill(ds);
				dt = ds.Tables[0];
			}
			conn.Close();
		}


		foreach (var column in ds.Tables[0].Columns.Cast<DataColumn>().ToArray())
		{

		if (ds.Tables[0].AsEnumerable().All(dr => dr.IsNull(column)))
		ds.Tables[0].Columns.Remove(column);
		}

		//Convert this DataSet to csv file
		string csvText = converToCSV(ds, report.Name);
		
		//Write the CSV data to a file
		StreamWriter csvStreamWriter = new StreamWriter(csvFileName, false);
		csvStreamWriter.Write(csvText);
		csvStreamWriter.Close();

		//Now read the output of local CSV file and write to the output stream
		using (FileStream csvFileOutput = File.Open(csvFileName, FileMode.Open, FileAccess.Read))
		{
			using (MemoryStream newMemoryStream = new MemoryStream())
			{
				newMemoryStream.SetLength(csvFileOutput.Length);
				csvFileOutput.Read(newMemoryStream.GetBuffer(), 0, (int)csvFileOutput.Length);
				newMemoryStream.WriteTo(outputStream);
			}
		}
	}

	catch (Exception ex)
	{
		//Close all the connections
		excelReader.Close();
		fileStream.Close();
		throw ex;
	}
	finally
	{
		//Close all the connections
		excelReader.Close();
		fileStream.Close();
	}

	//Finally return false
	return false;
}


--

Build the project.

  1. Move [Your].dll from the output folder to [SQL Reporting Services folder]\ReportServer\Bin\.You may notice there are a -lot- of other dlls in the output folder. These are all reportserver related and do not need to be copied to the reportserver. (In fact, they originated there).

Server Configuration you need perform for SharePoint Mode

You can modify SSRS 2012 with SharePoint Mode using PowerShell Cmdlets.

To achieve the above functionality we need to modify the config files – rsreportserver.config, and rssrvpolicy.config. Run the below PowerShell Cmdlets on SharePoint command prompt to modify the config files.

--

$apps = Get-SPRSServiceApplication | where {$_.name -like “NAME OF YOUR SSRS APPLICATION“}
$GetName = Get-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER”

if ($GetName)

{

Remove-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER”

}

New-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER” -TypeName “NAME OF THE NEWLY CREATED DLL” -CodeGroup “<CodeGroup class='UnionCodeGroup' version='1′ PermissionSetName='FullTrust' Name=' NAME OF THE CUSTOM RENDERER‘ Description='This code group grants Zip Renderer code full trust.'>

<IMembershipCondition class='UrlMembershipCondition' version='1′ Url='C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\ReportServer\bin\[YourDLL].dll' />

</CodeGroup>” -ExtensionAttributes “<Visible>True</Visible>” -ServerDirectives “<OverrideNames>

<Name Language='en-US'>CustomRendererName</Name>

</OverrideNames>” 

--

Note- In order to execute the above PowerShell cmdlet, a local farm must exist, and you must have farm-level administrator privileges.

2. Uses below PowerShell to copy the new DLLs to SharePoint hive 14 folders.

--
Copy-Item $DeploymentPackageFolder\[Your].dll "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin"

--

Uses below PowerShell to copy the new DLL to windows assembly folder.

--
$dllpath = "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin\[Your].dll"

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") | Out-Null

$publish = New-Object System.EnterpriseServices.Internal.Publish

$assembly = [System.Reflection.Assembly]::LoadFile( $dllpath )

$publickey = $assembly.GetName().GetPublicKey()

$aicstring = [System.Reflection.Assembly]::LoadFile( $dllpath ).FullName

$publish.GacInstall( $dllpath )

--

After running the PowerShell Cmdlets please restart reporting service and reset IIS only if you are not able to view the new rendering extension.Note- Adding new rendering extension parameters only affects rendering operations on the report server. You cannot override rendering extension settings in report preview in Report Designer or Visual Studio.Specifying rendering extension parameters in the configuration files affects rendering extensions globally means all the reports on that server will get the functionality of new renderer.

Server Configuration you need perform for Native Mode

Copy your assembly from your staging location to the bin directory of the report server on which you want to use the rendering extension.

The default location of the report server Bin directory is %ProgramFiles%\Microsoft SQL Server\MSRS11.\Reporting Services\ReportServer\Bin.

  1. After the assembly file is copied, open the rsreportserver.config file. The rsreportserver.config file is also located in the report server bin directory. You need to make an entry in the configuration file for your extension assembly file. You can open the file with Visual Studio or a simple text editor.
  2. Locate the Render element in the Rsreportserver.config file. An entry for your newly created extension should be made in the following location:
--

<Extension Name="CustomRendererName" Type="[Name Space].[Class Name],[DLL NAME]" Visible="true">

<OverrideNames>

<Name Language="en-US">CSVExtension</Name>

</OverrideNames>

</Extension>

--

Add an entry for your rendering extension in rssrvpolicy.config.

--

<CodeGroup>

class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="[Name of YourDLL]"

Description="This code group grants your dll full trust.">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\[YourDLL].dll" />

</CodeGroup>

--

 

The value for Name is the unique name of the rendering extension. The value for Type is a comma-separated list that includes an entry for the fully qualified namespace of your IRenderingExtension implementation, followed by the name of your assembly (not including the .dll file extension). By default, rendering extensions are visible. To hide an extension from user interfaces, such as Report Manager, add a Visible attribute to the Extension element, and set it to false.

Verifying the deployment

You can open Report Manager and verify that your extension is included in the list of available export types for a report.

Keep Learning.

Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Like this:

Like Loading...

Reporting Services | Configuring Data Alerts in Reporting Services for Share Point Mode

20 Friday Mar 2015

Posted by Pawan Kumar Khowal in Reporting Services

≈ Leave a comment

Tags

Configure data alerts, Configuring Data Alerts in Reporting Services for Share Point Mode, Create a Data Alert in Data Alert Designer, Data Alerts in SSRS, Demo: Configuring Data Alerts with SQL Server 2012, InterviewQuestions, InterviewQuestions for SQL, Puzzles, Queries for SQL Interview, Reporting Services Data Alerts, SQL Puzzles, SQLSERVER, SSRS, SSRS Data alerts, SSRS Interview Questions, T SQL Puzzles, The Art of Business Intelligence: Use SSRS Data Alert, TSQL, TSQL Queries, Working with Data Alerts in SQL Reporting Services 2012


Reporting Services data alerts are a data driven alerting solution that helps you be informed about report data that is interesting or important to you, and at a relevant time. By using data alerts you no longer have to seek out information, it comes to you.

Data alert messages are sent by email. Depending on the importance of the information, you can choose to send messages more or less frequently and only when results change. You can specify multiple email recipients and this way keep others informed to enhance efficiency and collaboration.

Follow below steps to configure data alerts.

1.1. On the SharePoint Central Administration website home page, in the Application Management section, click Manage service applications. You will be landed to Manage Service Applications page, click the SQL SERVER Reporting Services service application.

1

1.2. Once you select the SQL SERVER Reporting Services service application “Manage Reporting Services Application” settings page will be landed. Here click on Email Settings to configure e-mail settings.

2

1.3. Here provide Outbound SMTP server and From Address. Here provide below details and click to save details

1. A Simple Mail Transfer Protocol (SMTP) server or gateway
2. From Address
Note – “From Address” should have permission to send e-mail from the SMTP Server. Depending on security policies in your organization, you might need to request permission in advance to send e-mail from a corporate SMTP server.

3

1.4. Once you configured data alerts successfully please follow below steps to check whether data alerts are enabled or not.

1.4.1. Locate the SharePoint library that contains the report.

1.4.2. Click the report. The report runs. If the report is parameterized, verify that the report shows the data that you want to receive alert messages about. If you do not see the columns or values you are interested in, you might want to rerun the report, using different parameter values.

1.4.3. Click on the “Actions” menu. In the below picture “New Data Alert” is enabled.

4

1.4.4. If the New Data Alert option is disabled like in the below picture, please follow below step.

5

1.4.5. If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials.

6

Keep Learning

Please provide your suggestions / Comments

Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Like this:

Like Loading...

T-SQL Query | [ Developer Salary and the Manager Puzzle ]

20 Friday Mar 2015

Posted by Pawan Kumar Khowal in T SQL Puzzles

≈ 2 Comments

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries


T-SQL Query | [ Developer Salary & the Manager Puzzle ] – Write a query which will find the developers with salary greater than their manager.

Sample Input

EmpID EmpName EmpSalary MgrID
1 Pawan 80000 4
2 Dheeraj 70000 4
3 Isha 100000 4
4 Joteep 90000 NULL
5 Suchita 110000 4

Expected Output

EmpID EmpName EmpSalary MgrID
3 Isha 100000 4
5 Suchita 110000 4

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE [dbo].[EmpSalaryGreaterManager]
(
[EmpID] [int] NULL,
[EmpName] [varchar](50) NULL,
[EmpSalary] [bigint] NULL,
[MgrID] [int] NULL
)
GO

--Insert Data
INSERT INTO [EmpSalaryGreaterManager](EmpID,EmpName,EmpSalary,MgrID)
VALUES
(1,    'Pawan',      80000, 4),
(2,    'Dheeraj',    70000, 4),
(3,    'Isha',       100000,       4),
(4,    'Joteep',     90000, NULL),
(5,    'Suchita',    110000,       4)

--Verify Data
SELECT * FROM [dbo].[EmpSalaryGreaterManager]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


SELECT e1.EmpID,e1.EmpName,e1.EmpSalary,e1.MgrID, e.EmpName Mgr FROM EmpSalaryGreaterManager e
INNER JOIN EmpSalaryGreaterManager e1 ON e.EmpID = e1.MgrID
WHERE e1.EmpSalary > e.EmpSalary

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Blog Stats

  • 1,074,528 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

March 2015
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
« Nov   Apr »

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.

    %d bloggers like this: