Error,Object reference not set to an instance of an object

A message that indicates that the object reference is not set to an instance of the object is typically caused by one or more of the following reasons:

•         There are soft page breaks within empty lists in the report.

•         There are hidden groups within the report and you tried to export it to PDF.

·         The report has a link to a subreport that is not published on the report server and you tried to export it to CSV.

·         There are text boxes with a width and/or height of zero in the report.

·         A text box is either hidden or the NoRows property is set to true.

·         A text box spans table cells where some cells have a collection of null values.

·         The report has static columns and/or rows and, in the Visibility properties, Hidden is set to true.

·         The report has static columns and/or rows and there are grand totals in the report.

·         The report has static column headings and, in the Visibility properties, Hidden is set to true.

·         The matrix report has multiple columns and rows and, in the Visibility properties, Hidden is set to true and you tried to export to PDF or TIFF.

For More detil Visit this  rrRenderingError

 

SSIS

Core component of SSIS is Packages

• Packages
• Control Flow
• Data Flow
• Connection Managers
• Package Configurations
• Data Sources

Package

Package is a collection of task snapped together to execute in an orderly fashion to meet the business requirements. Precedence constraints are used to connect the task together and manage the order in which the task will execute. The package is compiled into a .DTSX file that is actually an XML structured file with collection of properties.

Control Flow

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow

Data flow

consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

Connection Managers

Connection managers is gateway to connecting with different data sources, such as relational databases, Analysis Services databases, and files in CSV and XML formats. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager.

Package Configurations

After development your package and before deploying the package in production environment from UAT you need to perform certain package configurations as per production Server .This relates to storing certain aspects of the packages like variable values, database connections, and flat file/ftp connection information. There are different ways to perform configurations

Data Sources

A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store

Passing a Multi value TSQL Parameter to MDX Report

I am assuming that you are familiar with the basic report design, So in below post, I am going to give details regarding the use multi value parameter only.

I have shared .RDL with respect to this post Download

Steps to pass multivalue parameter to MDX SSRS report

1.    Create your basic report with MDX query.

2.    I am using Adventure works DW cube as datasource to describe the below steps.

3.    Go to the .rdl code and write your code between <CommandText> your MDX Query </CommandText> tag.

<CommandText>= "Select {"+

"[Measures].[Average Unit Price],[Measures].[Order Quantity],[Measures].[Total Product Cost]"+

"} on columns ,"+

"Nonempty ([Sales Territory].[Sales Territory Country].[Sales Territory Country],[Measures].[Internet Order Count])*"+

"Nonempty( [Sales Territory].[Sales Territory].[Region],[Measures].[Internet Order Count])*"+

"Nonempty( [Product].[Product].[Product],[Measures].[Internet Order Count])"+

"on rows from"+

"(SELECT({[Sales Territory].[Sales Territory].[Region].&amp;["+Replace(Join(Parameters!SalesRegion.Value,"],")+"]",",",",[Sales Territory].[Sales Territory].[Region].&amp;[")+"})on columns "+

" from [Adventure Works])"</CommandText>
Change the higlighed expression as per your requirement for parameter passing 

4.    If you are using expression based query, in that case report designer will not add required parameter list automatically. You need to add it manually. In our example, the parameter list will like below image.

Let’s describe above picture.

Parameter

1.    SaleRegion is parameter we have used in our MDX .(1)

2.    Product parameter which is taking TSQL query to pass the parameter value to our MDX.  (2)

3.    Go to the parameter properties and  Allow multiple value

Data sources

4.    MultiVlaue associated with Cube (3)

5.    SQL associated with TSQL. (4)

Data Set

6.    Multivalue to MDX (5)

7.    SQL Value is for  TSQL (6)

 SQL Value query I have used is

SELECT DISTINCT SalesTerritoryAlternateKey, SalesTerritoryRegion

FROM            DimSalesTerritory

you go to the product parameter (1) and map the query value to this parameter.

1. Choose to set the Available Values.

2. Specify Dataset from value is coming

3. Choose the value that we are going to pass our MDX

4. Specify the label name.

5. Now preview your report.

 

Efficient way of using ALL as a parameter value in multivalued parameter

Most of the time, we came into a situation where we need to pass ALL values of a multivalued parameter to our stored procedure/T-Sql and generally we pass it as a comma separated value to our query. But passing a long set of values to our SP/T-Sql under IN(..,..,..) clause decreases the performance of the query because in this case query engine needs to look into all set of available values in IN(..,..,..) clause against every record. Let’s see step by step that how we can overcome from this issue.

Step 1: Add a Select statement like below in your dataset query that you are using for the parameter binding.

Select -1 as Employee_ID,'ALL' as Employee_Name /*Change the field names as per your requirement*/

Union All

<Your T-Sql Statement>

Here we are passing -1 as a value for ALL. You can choose any other value as per your requirement but do not use NULL. Because if there will be NULL in the multivalued parameter list, you will experience an error in 2008 or below versions whereas R2 will exclude NULL records from the parameter list. This SSRS behavior makes sense because if we concatenate any value(s) with NULL, resultant will be NULL.

In support of this step, you can refer “Employee_Parameter” dataset in attached sample.

Step 2: Pass the selected multivalued parameter in your details dataset’s parameter like following expression =IIF(Parameters!Employee_ID.Value(0)=-1,Nothing,Join(Parameters!Employee_ID.Value,","))

In support of this step, you can refer the expression of the @Employee_ID parameter in “Details_Dataset” dataset in attached sample.

Step 3: Now you need to modify the <where> - clause of your resultant query in such a way that will maximize the response time of your output query like following where condition.

Select <Columns List>

From

<Tables with required joins>

Where

( (@Employee_ID IS NULL) OR (Employee_ID in (<Multivalued parameter values in comma seperated form>)) )

In support of this step, you can refer the where clause of the Details_Dataset” dataset in attached sample

You can download the sample report from following link.

Use of ALL parameter value report sample

You need to only modify the datasource of the sample report.

Remarks

If you are using this concept in your report, you need to convey your end user that they will see all the possible records on the report if they select other available values with ALL.

ERROR: An item with the same key has already been added

During the process of creating Multi value report. I received the following error while previewing the report

“An item with the same key has already been added.”.

I got irritate when fetch the report MDX through Sql server profiler it ran with no error. After doing lot of research my friend gave a hint (he was having the same problem using TSQL Stored procedure) and resolved my issue. The issue was due to I  added the two fields with same name .

Quick Solution

1.    Go to the report query, stored procedure and identify the duplicate fields and remove it.

2.     If the report is using MDX go to the .rdl code and identify the duplicate fields and remove it.

3.    In MDX report if the field name is different but Unique name is same as highlighted in red below than we can also experience this error

<Field Name="Average_Unit_Price">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Average Unit Price]" /&gt;</DataField>

<rd:UserDefined>true</rd:UserDefined>

</Field>

<Field Name="Average_Unit_Price">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Average Unit Price]" /&gt;</DataField>

<rd:UserDefined>true</rd:UserDefined>

</Field>