• 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

Category Archives: Reporting Services

Reporting Services Related Articles

Answers to SQL Server (SQL,SSIS,SSRS,SSAS) Interview Questions – # 6

23 Wednesday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, Integration Services, Reporting Services, SQL Server Interview Questions

≈ 1 Comment

Tags

Advanced SQL tutorial pdf, Advanced SSRS tutorial pdf, Are table variables only stored in memory - SQL Server Q&A, Can we apply style sheet in SSRS and how?, Can we debug SSIS package. If Yes, Count puzzle, Count(*) VS Count(ColumnName) VS Count(1), Define below transformation in DFD?, deployment mechanism for ​​SSRS deployment, Difference between Cached Report and Snapshot Report, Difference between NONEMPTY vs NON EMPTY IN MDX?, Difference between Report Server and Report Manager:?, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download SSRS Questions, Download SSRS Server Interview Question in PDF, Download SSRS SERVER Interview questions, download SSRS server interview questions and answers, download SSRS server interview questions and answers pdf, download SSRS server interview questions by Pawan Khowal, download SSRS server interview questions by Pawan Kumar, download SSRS server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Download T-SSRS Interview Questions, Free Download SQL SERVER Interview questions, Free Download SSRS SERVER Interview questions, How check point works in for loop?, How many type of protection level in SSIS package?, How to create Temporary Table using SSIS?, How to design a Drilldown report?, How to make connection with a FTP server?, How to make connection with SFTP server?, How to render a report to a user email?, How to show "No Data Found" Message to end user?, How we can ignore failure and continue loop?, How?, I have a sql table that I need to split into more 90 excel sheet based on a code. I could create an ssis package and use conditional split and create more than 90 excel sheet. But creating more than 9, Sometime we need to debug out SSIS Package but we do not want to insert records in destination but still we want to use all the transformations and dump these all records in some dummy destination. Th, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL count puzzle, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL SERVER – Watching Table Variable Data in TempDB, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SSIS Interview questions, SSRS, SSRS Common Interview Questions, SSRS Common Interview Questions and answers, SSRS FAQ, SSRS FAQs, SSRS Interview Q & A, SSRS Interview Questions, SSRS Queries asked in interviews, SSRS questions, SSRS Server - General Interview Questions and Answers, SSRS Server developer Interview questions and answers, SSRS Server developer Interview questions with answers, SSRS SERVER Interview questions, SSRS SERVER Interview questions & Answers, SSRS SERVER Interview questions and Answers, SSRS Server Interview Questions and Answers - Free PDF, SSRS server interview questions and answers for net developers, SSRS SERVER Tips, SSRS Tips & Tricks, SSRS Tips and Tricks, SSRS Tricks, T-SQL Server Interview Questions, T-SSRS Server Interview Questions, Table Variable are created and stored in tempdb, Table Variables are Created in TempDB, Table Variables Are Only in Memory: Fact or Myth, table variables created and held in memory or in tempdb?, Tables Variables ARE Stored in tempdb, We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.?, What are Attunity Driver and why do we user in SSIS?, What are check point and how they work?, What are Lazy aggregations?, What are Partition processing options?, What is a sub report?, What is the difference between Table and Matrix?, where is table variables stored in the database?, where table variables are stored in sql server


Answers to SQL Server (SQL,SSIS,SSRS,SSAS) Interview Questions – # 6

Question 1 : I have a sql table that I need to split into more 90 excel sheet based on a code. I could create an ssis package and use conditional split and create more than 90 excel sheet. But creating more than 90 excel sheet one at a time will be time consuming and if I have to use that package again for another table then I would have to make changes. Is there an easier/faster way to achieve split a table into more than 90 excel tabs? Is it possible to use foreachloop and dynamically split and create excel tab?

Answer :

1. Assuming that have some rules to split the data. Best way to save this information in a table.
2. Create a optimized stored procedure which will accept the input parameters (above) and return the data
2. Add a script task
2.1 Here connect to the DB
2.2 Create a for loop
2.2.1 Call the SP created in step 2 and get the data in the dataSet
2.2.2 Add a new worksheet to your workbook
2.2.3 Insert data to the new worksheet

Question 2 : What are Attunity Driver and why do we user in SSIS?

Answer :

Attunity provides 2 high speed connectors. One for Oracle and one for Teradata. They have been selected by Microsoft to be included with SQL Server 2008 Integration Services (SSIS) SQL 2008 Enterprise Edition. These drivers are highly optimized and very easy to use.

Optimized, best-in-class performance
The connectors deliver unparalleled throughput for extracting and loading data to and from Oracle and Teradata. Architected with Microsoft, the connectors use direct integration into internal SSIS buffering APIs, cutting through .NET and other layers, and also use the high speed load/extract utilities offered by Oracle and Teradata.

Ease-of-use
The connectors are fully integrated into the Business Intelligence Development Studio (BIDS), part of Microsoft Visual Studio 2008, offering a user experience similar to that of the SSIS generic OLEDB Connector, with intuitive capabilities including configuration, metadata browsing, and error routing.

Question 3 : How many type of protection level in SSIS package?

Answer :

It is a package level property. It is used to specify how sensitive information is saved inside the package. It also specify whether to encrypt the package or the sensitive portions of the package.

Each SSIS component designates that an attribute is sensitive by including Sensitive=”1″ in the package XML. When the package is saved, any property that is tagged with Sensitive=”1″ gets handled per the ProtectionLevel property setting in the SSIS package. The ProtectionLevel property can be selected from the following list of available options (click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties):

  • DontSaveSensitive
  • EncryptSensitiveWithUserKey
  • EncryptSensitiveWithPassword
  • EncryptAllWithPassword
  • EncryptAllWithUserKey
  • ServerStorage

Question 4 : Difference between Cached Report and Snapshot Report?

Answer :

Cached Report

Here the system will save the last executed report. It is saved in the temp DB. It is not persisted. It has a lifetime e.g. 1 hour or so. We can have 1 only one “instance” per report (if you have parameters, you will have one per combination of parameter)

Snapshot Report

It is a persisted copy of the report. It is stored for good on the report database. You can have as many as you want. You can configure for example to save a snapshot of a report per day, so if you want to see how was your data 1 month ago, you just access the snapshot of that day.

When to use which one

Most of my reports, I cache them for 2 hours, so the first user who runs it will experience a small delay and the next will get the report on demand (with the data from when the report was ran, of course)

For large reports, execute them at night and configure them to be run from a snapshot (option “Render this report from a report execution snapshot”).

Question 5 : What are check point and how they work?

Answer :

SSIS 2005 included a feature called checkpoints, which allows you to restart the package if it fails for any reason. During package execution, the last successfully completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

You need to set three package properties:

CheckpointFileName. For this property, you need to provide a path and filename for the checkpoint file. If you plan to keep checkpoints implemented when you put a package into production, it’s a good idea to use a Universal Naming Convention (UNC) path.

CheckpointUsage. This property has three possible values: Never, Always, and IfExists. The default is Never, which prevents checkpoint creation. When you specify the Always option, the package uses the checkpoint file if it exists. If it doesn’t exist, the package fails. Therefore, the Always option isn’t recommended for a package in production because the package shouldn’t be failing regularly. (A package failure is the only way a checkpoint file gets created. Once the package completes successfully, the checkpoint file is removed.) The best option to use is IfExists. When you select this option, the package uses the checkpoint file if it exists. If it doesn’t exist, the program starts from the beginning of the package.

SaveCheckpoints. This property must be set to True. Otherwise, the previous settings won’t have any effect. By default, it’s set to False.

For details please refer – http://sqlmag.com/sql-server-2008/use-checkpoints-restart-failed-ssis-packages

Question 6 : How check point works in for loop?

Answer :

The Foreach Loop container is another atomic unit of work that can be restarted. However, the checkpoint file does not contain information about the work completed by the child containers, and the Foreach Loop container and its child containers run again when the package restarts.

Question 7 : Can we apply style sheet in SSRS and how?

Answer :

There are two ways to apply style sheet in SSRS
1. Hard Code in RDL File
2. Dynamically – You can save your style in DB and pull that in a DataSet and then apply.

For details please refer – http://www.keepitsimpleandfast.com/2011/11/how-to-implement-style-sheets-in-your.html

Question 8 : How to show “No Data Found” Message to end user?

Answer :

Add a text box. Set expression of the text box = IIF(Count(,”DataSet1″)=0,”No Data Found”, Nothing)
and set the visibility of this text box = IIF(Count(,”DataSet1″)=0,False,True)

Question 9 : Sometime we need to debug out SSIS Package but we do not want to insert records in destination but still we want to use all the transformations and dump these all records in some dummy destination. The goal can be to check the extraction performance from source OR view data at different points of Package but we do not want to insert data in destination at all.

Answer :

In this kind of scenario you can use below solutions-

1. Multicast transformation
2. Row Count Transformation

Question 10 : Can we debug SSIS package. If Yes, How?

Answer :

You can debug a Package by Setting Breakpoints on a Task or a Container

To set breakpoints in a package, a task, or a container follow below-

  • In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
  • Double-click the package in which you want to set breakpoints.
    In SSIS Designer, do the following:
  • To set breakpoints in the package object, click the Control Flow tab, place the cursor anywhere on the background of the design surface, right-click, and then click Edit Breakpoints.
  • To set breakpoints in a package control flow, click the Control Flow tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • To set breakpoints in an event handler, click the Event Handler tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • In the Set Breakpoints dialog box, select the breakpoints to enable.
    Optionally, modify the hit count type and the hit count number for each breakpoint.
  • To save the package, click Save Selected Items on the File menu.

Question 11 : How to create Temporary Table using SSIS?

Answer :

You can use Execute SQL task to create temp table and set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

Question 12 : We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.?

Answer :

Its simple create 2 variables, folderpath and filename. Now in script task, create a loop and find out the latest file. Now you can read the latest file in the script task itself and insert the data into the table.

for details please refer – http://www.techbrothersit.com/2013/12/ssis-how-to-get-most-recent-file-from.html

Question 13 : What are Lazy aggregations?

Answer :

Processing mode property of a partition/measure group determines how partitions will be available to users. Processing mode has two possible options – Regular and Lazy Aggregations.

Regular – Default. When set to regular, partitions will be available to users after data has been loaded and aggregations are created completely.

Lazy Aggregations – When set to lazy aggregations, partitions will be available to user queries immediately after data has been loaded. Aggregations will be created as a separate background process while users start to query the partition.

Process Full will internally executes Process Data and Process Index before the partition can be used for queries. If processing mode is set to Lazy Aggregations, partition will be released for user queries after Process Data is completed. Process Index will be executed in the background. As aggregations don’t exist while users begin to query the partition they may experience slow performance.

Question 14 : How we can ignore failure and continue loop?

Answer :

The Propagate variable in SSIS is used to determine whether an event is propagated to a higher level event handler. This allows package execution to continue once an event is handled in the container that has generated an Error.

https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/

http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

Question 15 : What are Partition processing options?

Answer :

When you process objects in Microsoft SQL Server Analysis Services, you can select a processing option to control the type of processing that occurs for each object. Processing types differ from one object to another, and by changes that have occurred to the object since it was last processed. If you enable Analysis Services to automatically select a processing method, it will use the method that returns the object to a fully processed state in the least time.Processing settings let you control the objects that are processed, and the methods that are used to process those objects.

Mode Applies to Description
Process Default Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Detects the process state of database objects, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state. If you change a data binding, Process Default will do a Process Full on the affected object.
Process Full Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Processes an Analysis Services object and all the objects that it contains. When Process Full is executed against an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object, for example, when an attribute hierarchy is added, deleted, or renamed.
Process Clear Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Drops the data in the object specified and any lower-level constituent objects. After the data is dropped, it is not reloaded.
Process Data Dimensions, cubes, measure groups, and partitions. Processes data only without building aggregations or indexes. If there is data is in the partitions, it will be dropped before re-populating the partition with source data.
Process Add Dimensions, measure groups, and partitions. Process Add is not available for dimension processing in Management Studio, but you can write XMLA script performs this action. For dimensions, adds new members and updates dimension attribute captions and descriptions.
Process Update Dimensions Forces a re-read of data and an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped.
Process Index Cubes, dimensions, measure groups, and partitions Creates or rebuilds indexes and aggregations for all processed partitions. For unprocessed objects, this option generates an error. Processing with this option is needed if you turn off Lazy Processing.
Process Structure Cubes and mining structures If the cube is unprocessed, Analysis Services will process, if it is necessary, all the cube’s dimensions. After that, Analysis Services will create only cube definitions. If this option is applied to a mining structure, it populates the mining structure with source data. The difference between this option and the Process Full option is that this option does not iterate the processing down to the mining models themselves.
Process Clear Structure Mining structures Removes all training data from a mining structure.

For details please refer – https://msdn.microsoft.com/en-us/library/ms174774.aspx

Question 16 : How to make connection with SFTP server?

Answer :

SFTP stands for Secure File Transfer Protocol which is a world wide accepted secure protocol to transfer and access files over a secure channel. The data and channel are encrypted in SFTP mode, which prevents unauthorized access by any intruders and it’s mainly used between companies to transfer secure and sensitive information.

You may be aware of the FTP task in SSIS which allows us to copy or paste files to/from a FTP site, but unfortunately SSIS doesn’t support communication over SFTP. A work around for this will be to use PSFTP through an Execute Process Task in SSIS to download the file to our local machine. PSFTP is a SFTP client tool provided by PuTTy (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) to transfer files between computer systems using the SFTP protocol.

A solution for this scenario will include the following steps:

Download PSFTP.exe file to our destination folder
Create a batch file with logic to download the text file using Windows command language
Create a SSIS package with an Execute Process Task to run PSFTP.exe

For details please refer – https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

Question 17 : Difference between NONEMPTY vs NON EMPTY IN MDX?

Answer : Please refer follow link

http://thatmsftbiguy.com/nonemptymdx/

Question 18 : Difference between Report Server and Report Manager:?

Answer :

The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Processors are the hub of the report server.

Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It is not available in SharePoint mode.

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SSRS Interview Questions

18 Monday May 2015

Posted by Pawan Kumar Khowal in Reporting Services

≈ Leave a comment

Tags

Advanced SSRS tutorial pdf, deployment mechanism for ​​SSRS deployment, Download SSRS Questions, Download SSRS Server Interview Question in PDF, Download SSRS SERVER Interview questions, download SSRS server interview questions and answers, download SSRS server interview questions and answers pdf, download SSRS server interview questions by Pawan Khowal, download SSRS server interview questions by Pawan Kumar, download SSRS server interview questions by Pawan Kumar Khowal, Download T-SSRS Interview Questions, Free Download SSRS SERVER Interview questions, How to design a Drilldown report?, How to render a report to a user email?, SSRS, SSRS Common Interview Questions, SSRS Common Interview Questions and answers, SSRS FAQ, SSRS FAQs, SSRS Interview Q & A, SSRS Interview Questions, SSRS Queries asked in interviews, SSRS questions, SSRS Server - General Interview Questions and Answers, SSRS Server developer Interview questions and answers, SSRS Server developer Interview questions with answers, SSRS SERVER Interview questions, SSRS SERVER Interview questions & Answers, SSRS SERVER Interview questions and Answers, SSRS Server Interview Questions and Answers - Free PDF, SSRS server interview questions and answers for net developers, SSRS SERVER Tips, SSRS Tips & Tricks, SSRS Tips and Tricks, SSRS Tricks, T-SSRS Server Interview Questions, What is a sub report?, What is the difference between Table and Matrix?


SSRS Interview Questions – SET 1 ( 50 Questions )

CLICK HERE (SSRS Interview Questions) to download the questions in PDF format.

I have been collecting interview questions from the people who have given interviews at various organizations. Please go through these questions before attending any technical Interview. Below are the list of questions on SSRS (SQL SERVER Reporting Services). Please add a comment if you have any question in mind , will add that also. Answers to these questions are coming very soon.

Question1. How to render a report to a user email?
Question2. How to join two datasets and use in a single report?
Question3. How to do paging in SSRS?
Question4. How to deal with multi valued parameters?
Question5. I want one page should be displayed in landscape and other pages in different formats in a report. Can you design a SSRS report for this requirement?
Question6. How to tune the performance of SSRS report?
Question7. How to design a Drilldown report?
Question8. What is the difference between Table and Matrix?
Question9. What is drill across through report?
Question10. How to keep header in all pages – SSRS?
Question11. How to add custom code to change the colour of a node in report – SSRS
Question12. What is a sub report?
Question13. Difference between drill through and sub reports?
Question14. ​What is a shared dataset?
Question15. Can we use shared dataset with the sub report?
Question16. What is the deployment mechanism for ​​SSRS deployment?
Question17. What are the common performance issues in SSRS?
Question18. ​Can you tell me top 5 new features in SSIS and SSRS Both in 2008 and 2012?​
Question19. What kind of dashboards you have prepared using SSRS reports?
Question20. Design a SSRS report that can support dynamic columns.
Question21. How to remove PDF from the export options in SSRS report?
Question22. What are the user roles available in SSRS?
Question23. What are the data regions in SSRS?
Question24. Have you ever used custom assembles in SSRS?
Question25. How to enhance SSRS functionality?
Question26. How to design a report to show the alternative rows in a different colour?
Question27. Write a code to customize the SSRS report. Where the code has to be written?
Question28. In a SSRS report where to write custom code?
Question29. ​How to troubleshoot SSRS report using ExecutionLog2?
Question30. Have you ever seen .rdl file? What are the different sections in .rdl file?
Question31. How to upgrade SSRS reports?
Question32. How to give estimations to your report requirements?
Question33. How to design a SSRS report that shows alternative rows in different colour?
Question34. How to manually allocate memory to SSRS service?
Question35. How to view report server logs and call stacks in SSRS?
Question36. Can you explain how to disable parallel processing or how to serialize dataset execution?
Question37. How to pass multi-valued parameter to stored procedure in dataset?
Question38. How to control the pagination in SSRS?
Question39. What are the major differences between SSRS 2008 and SSRS 2012?
Question40. You have any idea about IIF, SWITCH and LOOKUP functions in SSRS?
Question41. We know in SSRS by default datasets are executed in parallel. But in our environment data Source is under high load and can’t handle parallel requests.
Question42. Do you create your reports using the wizard or manually?
Question43. What formats can SSRS export or render to?
Question44. What servers can be used with SSRS?
Question45. Can we create a chart report using Report Wizard?
Question46. Can you edit the .rdl code associated with a linked report?
Question47. What is reporting lifecycle?
Question48. What is ReportServer and ReportServerTempDB ?
Question49. What is toggling in SSRS?
Question50. I have a report server A with more than 600 reports; Now the report Server A is down and we would like to move all the reports, their subscriptions and alerts on server B in a minimum down time. What would be your approach?

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

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...

Blog Stats

  • 1,074,524 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

February 2023
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728  
« Oct    

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: