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>

Execute MDX Query with TSQL

To start with execution of MDX query with TSQL first we need to create a Linked Server. Let’s discuss few points regarding this

Linked Server

  Linked server enables the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle, mysql, MS Analysis services etc. Many types of OLE DB data sources can be configured as linked server.

Now let’s proceed with creation of linked server within the sqlserver instance

Create a linked server

·         To create a linked sever you must have admin right of SqlServer.

·         Navigate to Server Objects > Linked Servers

·         Right click on Linked Servers and select New Linked Server.

Let’s take a sample cube database AdventureWorksDW2008R2 and create a linked server on this.

EXEC

sp_addlinkedserver

@server='Test', /* Here specify a name to linked server */

@srvproduct='',/*Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL.*/

@provider='MSOLAP',/*Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer.*/

@datasrc='localhost',/*'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'*/

       @catalog='Adventure Works DW 2008R2' /*Cube Database Name*/

4.       Once the linked server is set, we need TSQL Rowset OPENQUERY function.

Open Query

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax: - OPENQUERY (linked_server,’query’)

Now you can write your Script

USE

AdventureWorksDW2008R2

GO

Declare @MDXExpression as Varchar(MAX)

Create Table  #temp(

                        Country varchar(Max),

                        Sale_Amount  float,

                        Product_cost float,

                        Tax_amount Float,

                        TProduct_Cost Float

                        )

Set  @MDXExpression =

'SELECT

NON EMPTY

      {

      [Measures].[Sales Amount]

      ,[Measures].[Standard Product Cost]

      ,[Measures].[Tax Amount]

      ,[Measures].[Total Product Cost]

      } ON 0,

NON EMPTY

      {

NonEmpty ( [Sales Territory].[Sales Territory Region].[Sales Territory Region])

      } ON 1

FROM

      [Adventure Works]';     

insert into #temp     

Exec ('SELECT * FROM OpenQuery(Test,''' + @MDXExpression + ''')')

/* Here you can join your MDX result set with your respective Sql server database tables and can extract expected result set */

SELECT      SalesTerritoryGroup,  #temp.*

FROM         DimSalesTerritory inner join #temp   on #temp.[Country]= DimSalesTerritory.SalesTerritoryCountry

#ERROR/NAN / error: Input string was not in a correct format issue in SSRS

While working on reports, we usually get “#ERROR”,”N/A” or “error: Input string was not in a correct format”error.

The major problem behind these issues is data type mismatches and some additional reasons that cause these issues.

Let’s talk about how to resolve these issues and why we get these issues.

1.    If you are performing any aggregate operation like SUM, AVG etc  on the report and also you know that your input field might have string value then never use an expression like

=sum(iif(isnumeric(Fields!YourFieldName.Value), cdbl(Fields!YourFieldName.Value),0))

OR

=sum(iif(isnumeric(Fields!YourFieldName.Value), cint(Fields!YourFieldName.Value),0))

OR

=sum(iif(isnumeric(Fields!YourFieldName.Value), iif(isnumeric(Fields!YourFieldName.Value),cdbl(Fields!YourFieldName.Value),0),0))

The reason behind this is ISNUMERIC() method works in a mysterious way. If we will try to explorer this method a bit more in detail then we will find that is ISNUMERIC()returns true for any expression if there will be any possibility to convert the expression in numeric. For example, if you test ISNumeric("1,234.55") will return true whereas SUM("1,234.55")will give you #Error. To resolve this sitution

Always use expression like =SUM(VAL(Fields!YourFieldName.Value))

2.    If you are using cube/MDX report and also using your aggregate expression like above but still getting an error or an unexpected result then you need to check these points

·         Start your sql server profiler (Tools menu-> Sql Server Profiler) with Analysis services as service type. Execute/Preview your report and extract/copy the MDX from the profile. Now execute this MDX on the Analysis Services query browser. Make sure that your report’s aggregate column is available in the output. If you see your column unavailable in the output then you need to play a workaround to get include your missing column in the output.

            The reason behind this missing column is if the resultant of your entire column is NULL and you are using Non empty/Nonempty then query engine exclude the column from the output to increase the query response time.

To resolve this problem, you need to add a calculate Member in your MDX query and append the expression by adding +0 at the end like :

With

Member CalculatedMember as [Measures].[Measure Name]+0

·         Sometime due to mishandling the expression in the MDX query/Calculated measure, we get “Infinity”,”N/A” or scientific numeric format like 1.00E+12 in the MDX output.

To see the expected result at report level, we need to handle these problems at MDX level by using appropriate expression/fields/methods.

Exclude Week End Days From Calendar

Today I found a post at MSDN to exclude Sunday using Datediff(). I worked around the query and found a solution

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9b3efb7f-2015-432f-8624-c78a5c535f1c

declare @start datetime,

              @end datetime

set @start = '2006-01-01'

set @end =   '2006-01-08'

;

with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as

(

select @start ,

case when datepart(dw,@start) in (1,7) then 0 else 1 end,

year(@start),

datepart(qq,@start),

datepart(mm,@start),

datepart(dd,@start),

datepart(dw,@start),

datename(month, @start),

datename(dw, @start),

datepart(wk, @start)

union all

select date + 1,

case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,

year(date + 1),

datepart(qq,date + 1),

datepart(mm,date + 1),

datepart(dd,date + 1),

datepart(dw,date + 1),

datename(month, date + 1),

datename(dw, date + 1),

datepart(wk, date + 1) from calendar where  date + 1<= @end

)

select * into #test from calendar option(maxrecursion 10000)

Select  DATEDIFF(DD,@start,@end)+1-Count(case when #test.isweekday=0 then #test.isweekday end )

from #test where #test.date between @start and @end

You can also find the solution in this way

SELECT startDate,EndDate,DATEDIFF(DD,startDate,EndDate) FROM Production.WorkOrder WHERE ((DATEPART(dw, StartDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

How to restrict/set number of records per page in SSRS report

Sometimes we came into a situation where we need to show only a specific number of records on per page.

Let’s see how we can restrict the number of records on per page. I am assuming that you are good with your report design. So I will focus only on the required expressions/steps that will need us to achieve required page break in the report.

Step 1: Add a Parent Group under Row Group section. Now on popup window under “Group by:” dropdown, use this expression:

Image -1

=Int((RowNumber(Nothing)-1)/10)

In this expression, you need to change 10 to

any desire number as you wish to see number of records on per page

Image-1 will help you regarding this step.

Step 2: Open the group properties window(Right Click on group name-> select Group Properties) of this newly added group.             

Image -2

           Go to Page Breaks and checked “Between each instance of the group”.

           Now go to Sorting and delete column/sorting expression (if any).

           You can also delete the 1^st column of your table that has been created automatically on action of add new group by choosing “Delete Columns only” option from the prompt.

           Image-2 will help you regarding this step.

Now you are done. Preview your report and check report output.

You can download the sample report from following link.

Number of records per page Sample Report

An error occurred during report processing

"An error occurred during report processing. Index was out of range. Must be non-negative and less than the size of the collection. Parameter Name: index"

I did copy paste of my existing RDL that was using SSAS as datasource and made my all the required changes in the report. But during the report preview I was getting this error “An error occurred during report processing. Index was out of range. Must be non-negative and less than the size of the collection. Parameter Name: index”.

I thought since this is an index related error so there would be some custom code or DLL reference that was throwing this error. But there was neither custom code nor any DLL reference on my report. After a lot of efforts I have found that:

There was a Filed Source that was bind with two different Field Name. After correcting the Field   Source, this error has disappeared.

I am sharing this issue with you so that it will save your couple of valuable hours.

Sample RDLS

1.       Bar Chart Sample

Sample RDL for the Bar chart can be downloaded from

 BAR CHART SAMPLE REPORT

You need to only change the datasource of the RDL.

2.      Line Chart Sample

Sample RDL for the line chart can be downloaded from

LINE CHART SAMPLE REPORT

You need to only change the datasource of the RDL.

3.      Place Bar Chart inside a table

Sample RDL for the Bar chart can be downloaded from

BAR CHART INSIDE A TABLE

You need to change the datasource of the sample RDL.

4.      Place Bar Chart inside a table and consume un-pivot data

Sample RDL for the Bar chart that is consuming un-pivoted data can be downloaded from

BAR CHART INSIDE A TABLE Using Un-Pivot data

You need to change the datasource of the sample RDL.

5.      Questionnaire report using Matrix with Indicator control

       

        Sample RDL that illustrate the indicator control can be downloaded from

        Use of Indicator Control

        To execute this RDL, you need to only change its datasource.

Export Matrix data into a Tabular format in CSV export

Generally we need CSV file format for further operations like in data analysis or as an input file for any data processing etc. But usually we design the report in such a way that makes it rich in visualization and information. So the question comes in our mind is how can we achieve both the things in the one report?

Let’s discuss a scenario where we have placed matrix control on the report and want the data in tabular format in CSV export.

Image-1 is the report output  

Image- 1



and Image-2 is the expected CSV output.

Image - 2



I believe that you can design the matrix as per your requirement. So below I am going to explain the method that will help you to get this expected CSV output.

Step 1:

Select the matrix control and open the property window using F4 key. In the property window, you will see the DataElementOutput property. Set DataElementOutput’s value to NoOutput.

Step 2:

Place a table control on the report and design it for CSV output. Do not place any header row in the table. If you see any header row then delete it. Now give the appropriate name to detail row cells. If you want a specific name for a column in the CSV then you needs to either give that name to cell’s Name property or specify that name in cell’s DataElementName property.

At the end, select this table open the property window using F4 key and set Hidden=True and DataElementOutput= Output.

Image - 3

 In our scenario, table design will look like image-3

Now preview the report and export it into CSV. You will see the desire output in CSV as well as in report view.

Let’s discuss couple of properties of the controls that helps us in getting desire CSV output.

·         DataElementOutput : If you don’t want to see a specific control/cell/column in the CSV export then set it to NoOutput

·         DataElementName : If you specify a value in this property, the same value will appear as a column name in the CSV

·         Visibility/Hidden : If you set a control/cell/column hidden=True then it will not appear in the report preview but it will appear in the CSV export. So apart from hidden=True, you need to set  DataElementOutput= NoOutput

You can download the sample RDL from

Download MatrixToTableInCSV Sample Report

You need to only change the DataSource of this sample RDL.