How to sub string a string value using a character value in SSRS

In a report you may want to get a part of a string value by considering a character or characters.
Such a situation we have to use Mid and InStr functions.

Assume that the Description filed value contains "/".
If we want to get the string before that "/", we can use the below expression in the report text box field.

   1: Mid(Fields!Description.Value,1,InStr(Fields!Description.Value,"/")-1)

InStr function provides at which location that the "/' character exists.
You can get the last part after the "/" character by using the below expression

   1: Mid(Fields!Description.Value,InStr(Fields!Description.Value,"/")+1,Len(Fields!Description.Value))

How to avoid data is moving to the next page in SSRS even though the space is available in the previous page...

Recently I faced an issue that in a SSRS table, data in a particular row is moving to the next page if the content with in that row is not fit enough to the available space.

We can overcome this issue by setting the KeepTogether property of the Row Cell to False.
Select the particular Row Cell and go to the Properties window. In that you can find the KeepTogether property. Default the value is set to True, change it to False.
That is it. Just run the report and you will see the content is moved to that free space and additional content is  moved to the next page.

Split first word from a field in SQL table

We can use SUBSTRING and CHARINDEX functions to split the first word from a value in SQL.
As an example, if there is a field as FullName in UserInfo table and if we want to get the first word of it, then we can use the below query to get it.

   1: SELECT  FullName , SUBSTRING( FullName ,0,CHARINDEX(' ',Name+' ',0)) AS FirstName
   2: FROM            dbo.UserInfo

In the CHARINDEX function I added ' ' to it, because otherwise it will return null if the FullName value only has a single word.

SSIS Row Count Component...

Recently I used the Row Count component in SSIS toolbox. I used it in a Data Flow to set the row count of a Lookup match output. When I tried to get that set value in a Conditional Split component in the same Data Flow, I found that the variable has the default value and the row count was not set for that variable.

But when I get the variable value using Script Task, I found that the variable has the row count.
Actually the issue is the row count value is available after the completion of the Data Flow task. We cannot access the variable inside the same data flow task which sets the row count value.

Due to that I have to use two Data Flow tasks. In the first data flow task I set the row count value to a variable using Row Count component and in the other data flow task, the variable value is checked and do the required processing.

Replace double quotes in SSIS...

Recently I had a requirement to load csv file data which is generate from FoxPro.
But when I checked the csv file, found that it has additional double quotation mark at the start and the end of the value. As an example if the actual branch value is Branch1, the csv file has that as "Branch1".

Therefore to get the actual value I had to remove those double quotation mark and able to do it using REPLACE function.

I added a Derived Column component to the package and in that replace the branch column value after removing double quotes. For that I used the below expression.
   1: LTRIM(RTRIM(REPLACE([branch],"\"","")))

That is it, It will give you the actual branch value correctly as Branch1.

How to show the All Member hierarchy value in a SSRS Filter...

For some filters we may need to select all the values at once. As an example if the SSRS parameter is a single valued one and user should be able to filter all the values if needed, then we need to provide All member value in the filter as well. We can achieve this easily by retrieving the all member value to the data set of the filter.

For an example we can write the query for the Branch data set, which loads the all the branches available in the [Sales-Branch] hierarchy as

   1: WITH MEMBER [Measures].[ParameterCaption] AS [Sales-Branch].CURRENTMEMBER.MEMBER_CAPTION
   2: MEMBER [Measures].[ParameterValue] AS [Sales-Branch].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Sales-Branch].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {[Sales-Branch].[All],[Sales-Branch].[Branch].MEMBERS} ON ROWS  FROM [Sales]

In that you can see the ParameterLevel for the top record as 0 and indicates it is the parent level. Therefore it is the all branches record and it is given by the [Sales-Branch].[All] in the mdx query.

Configuring SSRS Report Subscription using a Remote SMTP Server...

In one of my previous post, I mentioned how to Configure SSRS Report Subscription using Gmail as the SMTP Server.
But in some situations we may need to configure it using Remote SMTP Server. It is bit easier than the previous one.
Open the Reporting Services Configuration Manager and connect to the reporting service instance. Then go to the E-mail Settings. In that provide the appropriate Sender Address and SMTP Server values.
That is it.
If you check the rsreportserver.config file, you can see that the sender address and smtp server values are in the file. Also you can see that the SendUsing value is set to 2, indicating that the smtp server is a remote server.

Filtering Dimension Members having the same name in a MDX Query...

Sometimes different dimension members having different key values may have the same name. This kind of situations mainly occurs when we set a default value to those unknown dimension member values. As an example if we set the value as "Unknown" for the items in the sales records, which do not match our existing items, then the key value for those items in the cube will be different but the name value will be same.

But if we try to filter those "Unknown" member values using the query designer, it will only filter the selected members. Otherwise we have to select all the members having the name value as Unknown, to filter those. But if there are large number of members having the same name, then it will be difficult and not feasible to select all those.

We can filter those in the MDX query using INSTR function. The query will looks as below.

   1: SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS, NON EMPTY { (
   2: [Items—Items_Vendor_Item].[Item Name].ALLMEMBERS ) } ON ROWS FROM
   3: [SalesCube]
   4: WHERE FILTER([Items—Items_Category_Item].[Item].Members, INSTR([Items—Items_Category_Item].CurrentMember.Name,'Unknown')=0)

Since it is not allowed to use the same hierarchy more than once in the query, two dimension hierarchies are used which contains Items.

SSRS table border double is not working...

I wanted to add double line for the bottom of the each row in a SSRS table. But when I set the Border Style for the Bottom of the Row as Double and preview the report, I was unable to see the double line at the bottom of any row.

Actually the reason was even though it was applied, it was not visible. To fix it just change the Border Style for the Top to None while keeping the Border Style for the Bottom as Double. Then increase the Border Width for the Bottom as 2pt. Then try to preview the report. I was able to get the double line by setting the border width as 2pt.
But if still you are not getting it, try to increase the border width for the bottom as 2.5pt or 3pt. Then you should be able to see the double line at the bottom of each row.

How to make SSRS Indicator as fix size...

If you just add the Indicator in SSRS 2008 R2 or 2010 to a cell in a table and try to preview the report, you will see that the Indicator will be stretched if the row has multiline records.

To fix this what you have to do is just add a Rectangle to the cell and after that add the Indicator.
Then try to preview the report and you will see that even though the row has more than single line, Indicator size is fit to the height of a single line and it is not stretched.

Use of UrlRoot property in SQL Report Server Config file...

In SQL Report Subscription, you may want to send the report with the link and you can do it by selecting Include Link value. But some times you will get the link include the computer name and not the domain name. Due to that the given is not working if you try to it from outside.

This happens since you have not set the value for the UrlRoot property in the rsreportserver.config file.
To fix it, open the rsreportserver.config file in a text editor and find the UrlRoot property.
Set the value for it as the report server URL with the domain name as below.

That is it and you will get the link with the given domain name.

Allow E-mail address to be changed for the Users having Browser permission in SSRS Subscriptions...

Normally Users having Browser permission cannot changed the e-mail address (To: field) in the report subscription page. Default the user name is applied as the value for the To: field.
To allow this to be modifiable by users having Browser permission, you need to modify the rsreportserver.config file.

Open the rsreportserver.config in a text editor.
Then set the SendEmailToUserAlias to False and Save the rsreportserver.config file.

That is it.
Now just try to create a New Subscription and you will see there is no value in the To: field and it is editable.

Configuring SSRS Report Subscription using Gmail as the SMTP Server...

Recently I was trying to configure SSRS Report subscription. I checked some links in the MSDN and by following those steps I was unable to configure it. The reason was I used the SMTP server as since we are not having a local smtp server. To use the as the smtp server we need to follow some additional steps other than the steps mentioned here.

The first step is we need to configure a Virtual SMTP server.
First make sure that the SMTP Feature is installed on your server, if not installed it.
Then use IIS Manager to configure the virtual SMTP server. I am using Windows Server 2008 and it has both the IIS 6 and IIS 7.
To configure virtual SMTP server we have to use IIS 6.
Open the IIS 6.0 Manager and you will get a screen as below.

In that if the SMTP Virtual Server is not started, then start it. Then right click on it and select Properties.
You will get a screen as given below.

In the General tab select the IP Address value and unmarked the Limit Number of Connections to field.
Then go to the Access tab and click on the Relay button at the bottom. Then select All except the list below and click OK.
Then go to the Delivery tab you will get a screen as below.

Click on the Outbound Security... button. In that select Basic Authentication and provide the gmail user name and the password as shown below. Also select the TLS encryption as well.

Click OK and then click on the Outbound connections... button.
In that change the TCP port value to 587 and click OK.

Then click on the Advanced button in the Delivery tab. Then provide the Smart host value as and also check whether the Fully-qualified domain name is correct.
Then click OK on both the Advanced Delivery window and the Properties windows. That is it and now you completed configuring virtual SMTP server.

The next step is to use that virtual SMTP server to configure Reporting Services.
To configure it run the Reporting Service Configuration Manager. Connect to the Reporting Service instance and select Email Settings. You will get a screen as shown below.

In that provide the Sender Address which is used to send mails. For the SMTP Server value, provide the IP Address given for the virtual SMTP Server. Click Apply.

But still it is not completed. You have to modify the rsreportserver.config file which can be found at  C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

The rsreportserver.config contains a property as SendUsing which specify whether a local SMTP Server (SendUsing =1) is used or a Remote SMTP server (SendUsing =2) is used to send mails.
Since we are using virtual SMTP server we need to set the value as 1.

The other property we need to set is SMTPServerPickupDirectory value which specifies the local pickup directory. We need to set that value in the rsreportserver.config file as well and for my example it is as C:\inetpub\mailroot\Pickup. Then save the rsreportserver.config file.

You will complete the configuring Reporting Service E-mail Delivery after saving the rsreportserver.config file. That is it.

Now go to the Report Server and click the down arrow of the relevant report and select Manage. In that select Subscriptions and create a New Subscription. In that page select the report delivery option as E-mail and provide other required values. 

After that you will get e-mails according to the given schedule.

How to get the installed SQL Server details...

We can get the installed SQL Server details such as SQL Server Edition, Product Version and Product Level using the below query. Just open a query window using SQL Server Management Studio and run it.

   1: SELECT SERVERPROPERTY ('edition') AS SQLEdition,SERVERPROPERTY ('productversion') AS ProductVersion, SERVERPROPERTY ('productlevel') AS ProductLevel

Passing Parameter value for a rdlc report in ASP.Net and Windows Forms......

In most of the times we need to filter report data. Recently I had a requirement to create a rdlc report and add it to an ASP.Net page. In that I need to filter data using the name.

In ASP.Net and Windows forms, creating data set and reports parts are same. The different is passing the parameter value from the code.

In ASP.Net, steps used are

  • Create a Data Set by right click on the project and select Add → New Item
  • Then select Data → DataSet
  • When the DataSet is added to the solution, right click on the DataSet Designer and select Add→TableAdapter.
  • In that you need to provide the query to retrieve data and in this example I assume it as SELECT Name, Address, ContactNo FROM Users WHERE (Name LIKE @name + '%')
  • @name is the parameter which we need to pass.
  • Then add a new report and select the DataSet as the one which we created earlier.
  • Then add a ReportViewer component and select the Report as the one created earlier.
These steps are common for the Windows Forms as well. But next steps are different in ASP.Net and Windows Forms.

In ASP.Net
  • When the report is added to the report viewer, ObjectDataSource is automatically added. You need to configure it to pass the parameter.
  • Click on the arrow in the ObjectDataSource and select Configure Data Source. In the wizard you will get a step to define parameters as shown in the below figure.

  • Provide the required details as shown in the figure and click on Finish.
  • Then go to aspx.cs file and add the below code in the View button click event. Here View button is used to view the report by passing parameters. To enter parameter value a TextBox is used.

    protected void btnView_Click(object sender, EventArgs e)
            string name = txtName.Text;

            ObjectDataSource1.SelectParameters["name"].DefaultValue = name;

  • Then when you click the View button, the value provided in the text box is passed as the parameter value and report data is filtered for it.
In Windows Forms
  • When the report is added to the report viewer, TableAdapter is automatically added.
  • You can pass the parameter values for the table adapter. You can pass the parameter values in the View button click event as below.

    protected void btnView_Click(object sender, EventArgs e)
            string name = txtName.Text;

            this.DataTable1TableAdapter.Fill(this.SampleDataSet.DataTable1, name);

  • When the View button is clicked, the parameter values are passed and data set is filtered according to those values.

Get Week Numbers for Quarter and Month in SQL...

I needed to get the Week Number for the Month and the Quarter for a BI Application.
I was able to retrieve it using the below query.

SELECT DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2012-03-17'), 0), '2012-03-17') +1 AS WeekNoMonth,DATEDIFF(WEEK, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, '2012-03-17'), 0), '2012-03-17') +1 AS WeekNoQuarter

The results are WeekNoMonth=3 and WeekNoQuarter=11

Mid function is not working in SSRS...

I tried to sub string SSRS data set field to have new calculated field. But when I see the output I did not get the desired one and original string value was there. Also I did not get any error message too.
The issue was with the Start Position. I provided the start position as 0. But the correct start position should be greater than 0. When I changed it to 1, I got the correct output. The correct formula is Mid("StringVal",1,6). It gives the first six characters of the provided string.

Error in PerformancePoint 2010...

While trying to create a new data source in PerformancePoint 2010 Dashboard Designer, I got an error message as This data source cannot be used because performancepoint services is not configured correctly. The error message is shown in the below figure. I got it after providing the server and while trying to expand the available databases.

This is because that the PerformancePoint is not configured correctly. While configuring PerformancePoint, we need to configure Unattended Service Account. The error occurred due to that account is not configured correctly.
To fix that issue we need to re-configure that unattended service account correctly.
To configure unattended service account, you need a Secure Store Service Key.

Steps to fix the above issue are
  • To generate a secure store service key, go to SharePoint 2010 Central Administration →Application Management → Manage Service Applications → Secure Store Service
  • Then click on Generate New Key
  • Once that is done go to the SharePoint 2010 Central Administration → Application Management → Manage Service Applications → PerformancePoint Service Application
  • Then select Performance Point Service Application Settings
  • In the Unattended Service Account section, enter the user name and password for the account that you want to use as the unattended service account. This is the account that PerformancePoint Service will use to connect to the data source unless you use Per User Identity.
  • Click Ok and if the provided account details are correct, it will be successful.
  • Then go to the Dashboard Designer and try to create the data source again and you will not get that error message.

An error occurred during local report processing...

I modified an existing report by deleting some dataset  fields and adding new ones. Then in the report I removed the deleted fields from each reference and added the required new ones. But when I tried to preview the report, I got the report processing error as
The Hidden expression for the grouping "Group1"  refers to the field "Field1". Report item expressions can only refer to the fields within the current data scope or, if inside an aggregate, the specified dataset scope...

I checked the report and already the reference for Filed1 is deleted from all the locations. But finally I was able find the reason. Error was in the Static Hidden Groups.
In some of the those hidden static groups, Field1 is used. To view those groups, select the Tablix and click on the Down arrow in the bottom right hand corner as shown in the below figure and select Advanced Mode.

Then you will those Static groups as in the above image. In that check the relevant static groups for the group mentioned in the error message. You will be able to find that the Field1 is still used in that group. Remove or change Field1 from that and try to preview the report. If no other static groups use any of the deleted fields, you will be able to view the report successfully.

[Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered...

While trying to execute a package in 64 bit SSIS, I got the below error
[Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

To fix it just right click on the SSIS project in the solution explorer and click Properties. Then click on the Debugging of the Configuration Properties. You will find Run64BitRuntime under Debug Options. Set it to False and execute the package. You will not get the above error anymore. 

SSIS Toolbox in SQL 2012...

When we open a Integration Service Project created using SQL 2008, all the required components are available in the Toolbox tab. I tried to upgrade this SSIS package to use the SQL 2012 environment. Upgrading process was successfully completed and I was able to open the package. But the Toolbox tab is empty and could not find any component as in the BIDS 2008. Those components are in the SSIS Toolbox tab. Click SSIS -> SSIS Toolbox to view it.

Unspecified Error in Microsoft Access Data Engine...

While trying to access Excel 2007 file using SSIS Excel Source component, I got an error in the Microsoft Access Data Engine as Unspecified Error. I was using SQL Server Business Development Studio 2008. I got this error trying to get the Sheets in the excel file after creating the connection.

I tried to find the reason and the solution for this issue and able to find a helpful blog.

According to that post the this is related to the driver issue. After following that post I was able to fix is by re-installing the driver in the below link.

After the installation completes, the issue will be fixed. If you already opened the Business Development Studio project, just close it and re-open it. 

How to show column headers on all pages in SSRS Report

If a report contains several pages it will be useful to have column headers on each page. In tablix properties we have options as Repeat header columns on each page as shown in below figure.

But even though we put a tick on that property, sometimes it is not applied and and column header are not repeated in other pages. This is really confusing and I found useful link in the internet.

It mentions the work around as well the reasons that may cause for it.

Work around is
  • Select the Tablix that you want to repeat column headers for by clicking on it.
  • At the bottom of the screen, find the "Row Groups" and "Column Groups" section.
  • Click the small drop-down-arrow on the right side of that section.
  • In the "Row Groups" section, locate the top-outermost "static" row and click on it.
  • In the properties pane, you need to set the following TWO properties:
    -RepeatOnNewPage = True
    -KeepWithGroup = After

Extracting QuickBooks data using SSIS Package...

To extract data from QuickBooks, I used QODBC driver.
You can download a trial version of it using below link

When we installed the QODBC driver, if ODBC entry is not available for it, we can add it as System Data Sources. For that go to the Control Panel --> Administrative Tools -->Data Sources(ODBC)

Then you will get the ODBC Data Source Administrator window. In that click on the System DSN and you will get a screen as below

In that click on the add button and select the QODBC Driver for QuickBooks as shown in the below figure.

Then you have to configure the ODBC connection. Initial set up window is as given below.

If you want to set a different company profile, click on the Browse button and select the qbw file as shown in the below figure.

Then you can use the created ODBC connection in SSIS package.
Create a new Integration Service Project and add a Data source. In the New Data Source window, select the Provider as Odbc Data Provider as in the below figure.

Then select the relevant ODBC data source name for the QuickBooks and if any authentication is required provide those too. Then click on the Test Connection button and verify the connection is possible.

Then add a new package to the project and add a Data Flow task. Then add a ADO NET Source and provide the created data source as the ADO .NET connection manager. If you created the connection correctly, you will be able to select the required table as shown in the figure.

In this example I just added a DataReader Destination component to view the data using a Data Viewer.

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.

SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))

Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.

Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gives an error since it only consider the first word before the space.
As an example if you send the branch value as Head Office, it only take the Head part. Therefore you have to pass the UNIQUENAME.

As an example you can take the data set for the Branch using below query.

MEMBER [Measures].[ParameterValue] AS [IDimBranch].CURRENTMEMBER.UNIQUENAME
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , [DimBranch].[Branch].ALLMEMBERS ON ROWS FROM [Sales]

Then you can use the ParameterCaption as the Label field and ParameterValue as the Value field for the Branch parameter.

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.
In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.

Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

FROM clause have the same exposed names...

When I tried to get the matching item records from two databases, I got the below error.

The objects "Database1.dbo.Sales" and "Databse2.dbo.Sales" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

The query used was
SELECT DISTINCT Database1.dbo.Sales.ItemName FROM Database1.dbo.Sales INNER JOIN Databse2.dbo.Sales ON Databse1.dbo.Sales.Id=Databse2.dbo.Sales.Id

It is because that the table name is same in those two databases. To avoid it we have use aliases.
The query is shown below

SELECT DISTINCT Table1.ItemName FROM Databse1.dbo.Sales AS Table1 INNER JOIN Databse2.dbo.Sales ON Table1.Id=Databse2.dbo.Sales.Id

Databse1.dbo.Sales AS Table1 will fix the error.

Cube File is Corrupted...

While trying to view SSRS reports, which load the SSAS cube data I got the below error message.

File system error: The following file is corrupted: Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales.0.db\Customer.0.dim\5.English Education.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Customer dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\ Sales.0.db\Customer.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the A Sales cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\ Sales.0.db\Adventure Works DW.9.cub.xml'.

The reason for this error message is the the cube file in the file system is corrupted.
You can't this issue by just redeploying the cube. When you try to deploy the cube, you will get the same error message and deployment process will get failed.

You can fix the above issue by following below steps.
1. Stop the Analysis Service
2. Delete everything in the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\%YOUR_PROJECTNAME% folder
3. Restart the Analysis Service.
4. Connect to the Analysis Service using SQL Server Management Studio.
5. Delete the Analysis Service database which you got the error.
6. Re-deploy the SSAS project.

Those steps will fix the above issue.

How to Change the Start Up xaml file in a Silverlight Application

To change the Start Up xaml file for a Silverlight Application, open the App.xaml.cs file.
In that you will find the below code

private void Application_Startup(object sender, StartupEventArgs e)
this.RootVisual = new MainPage();

Change the "MainPage" to your new xaml file.

Run the test page and you will see the content of your new page...

Stop running SSIS Script Task after some time...

There is a SSIS Script Task which use to run another program. But due to the errors in the other program, some times script task is in the ...