Excel Macro to update filter

  • User: Can we make this month filter to have by default the year to date months selected?
  • Me: … well… I guess I can have a look at it
  • Inside of me: Back to VBA… after 12 years…

So I struggled a bit, with errors 13, 1004… Search on internet and you will find long solutions (if you are lucky) to fix those errors and maybe none like you scenario. Well for me it turned out that a bit of debuging lighted up the bulb and I spotted the source of the 1004 error… just a little and simple Trim() function. The code talks by itself (How much I missed ; ) :

Note: Save first your Excel file as a Macro Enabled file (.xlsm).

Sub Auto_Open()     
End Sub

Sub YTDMonth() 

Dim i As Integer 
Dim m As Integer 
Dim months() As Variant
Dim years(1) As Variant
i = 1 
m = Month(Date) 
ReDim strArray(1 To m)

For i = 1 To m     
    months(i) = "[Dim Period].[Period Name].&[" & Trim(Str(i)) & "]" 
Next i
years(1) = "[Dim Year].[Year].&[" & Trim(Str(Year(Date))) & "]"

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Year].[Year].[Year]").VisibleItemsList = years()
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Period].[Period Name].[Period Name]").VisibleItemsList = months()

End Sub
Tagged with:
Posted in Errors, Excel

SSAS Deployment Wizard Error

This error occurs when you are trying to use the SSAS Deployment Wizard, typically when wishing to deploy a staging/development SSAS database to production. The wizard is unable to complete the deployment with the following error message:

“08001: Named Pipes Provider: Could not open a connection to SQL Server”

The error message even encourages you to have a look in SQL Server Books Online for help. I didn’t have much success, but good old Google yield the following solution:

Problem Description:The SQL Server Client Network Utility has TCP/IP or Named Pipes disabled.

Suggested Actions:

  1. Run the SQL Server Client Network Utility tool by executing cliconfg.exe from the Run window.
  2. Verify that the Enabled protocols by order column lists both TCP/IP andNamed Pipes.

Run this wizard in both source and destination servers to ensure that both servers can use the same communication mechanism.

In my particular case, after doing this step I still had a unable to connect to database X error. This error was due to impersonation configuration in the metadata. Solved by typing the password for the impersonated user in the Deployment Wizard.

Posted in Errors, SSAS

Getting rid of Excel Services scrollbars

Excel Services scrollbards in SharePoint sites… you know, I know, we know… they know! How ugly and annoying they can be. Finally I discovered how to get rig of them, and it is quite easy:

Before screenshot:


On the right hand-side of the screenshot you can see the double scrollbars. The internal one belongs to the Excel Services web part and the external to SharePoint. It doesn’t matter how big you web part is, the scrollbar is always there if you publish your entire sheet or workbook.

You can always publish selected objects in your workbook, which will solve the double scrollbars problem. But this solution won’t work if you use slicers in your Excel files to make it easier for final users to filter report’s data. For this scenario you need to use Names.

In Excel, select the cells that cover your entire report. For example from cell A:1 to Q:33. Then we will give a Name to that area by clicking in the “Formulas”tab in the ribbon and selecting the “Define Name” option:


Give a name to the new Name:


And finally publish only the new defined Name:


Named areas can include any Excel objects, graphs, tables and slicers. Once published you just need to give the enough real state to your webpart to accomodate the published named area and the double scrollbars problem will be gone:


Tagged with:
Posted in Excel, SharePoint

Why 1753?

In SQL Server the minimum date that you can enter is the 1st of January 1753. If you are extracting data from SAP, as I’m doing, you will see this particular date if a null date is extracted. This date can then pop-up on a user report making any user to ask why 1753… Well the answer is quite interesting. The year 1753 was the first full year after Britain adopted the Gregorian Calendar (our current calendar), which was 11-day shorter than the Julian Calendar at that time.  More info here: http://en.wikipedia.org/wiki/January_1

And if you are even more curious to know why countries changed from the Julian Calendar to the Gregorian Calendar then you can read this: http://en.wikipedia.org/wiki/Gregorian_calendar

Tagged with:
Posted in SAP, SSIS

Missing Access OLE DB Connector in SSIS

And finally a new production server came along with SQL Server 2012. All works fine but the only Access database connection that I have. The image that you see above is the dialog box that I see when adding a new OLE DB connection. As you can see there is no connector for Access…

This scenario is quite common in either new server installations or once you move your code to a production server. The reason behind is that the Access OLE DB connector is installed as part as Office, which probably you will have it installed in the development and UAT servers, but you should never have it installed in the production servers.

To have this connector available in the production environment you need to download and install the “Microsoft Access Database Engine 2010 Redistributable” from here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

There are 2 versions, 32-bits and 64-bits. If you plan to have Visual Studio installed in the server, so you can debug code, you need to install the 32-bits version. Remember to click the “32-bit runtime” option when scheduling the SSIS packages. But if you are not using Visual Studio in the server at all (because is a production server), you can install the 64-bits version (if your server is 64-bits) and run the SSIS package in full 64-bits. You must decide which version you need to install as these two versions cannot be installed together. You either install one or the other.

Tagged with: , ,
Posted in SSIS

Inbound Rules and SSAS

Like SSRS, SSAS also needs to be allowed to be contacted from the outside throught the Windows Firewall. In my case I have one dedicated server (A) for SSAS and a separate server (B) for SSIS and DBS. Is in this second server where I’m also storing my Visual Studio projects. When I tried to deploy the SSAS project over server A I received the following error:

To fix it you just need to create a new Inbound Rule in server A with following these steps:

  • In Windows Firewall, right-click Inbound Rules and select New Rule.
  • In Rule Type, click Port and then click Next.
  • In Protocol and Ports, select TCP and then type 2383 in Specific local ports.
  • In Action, click Allow the connection and then click Next.
  • In Profile, clear any network locations that do not apply and then click Next.
  • Finally give this rule a name and then click Finish.
Posted in Errors, SSAS

Inbound Rules and SSRS

I spent 1 day trying to figure out what was wrong in the following scenario:

–          In the new UAT environment all the BI components are working perfectly but I can’t access the Reporting Services “Reports” virtual directory from a remote machine. After reviewing all configurations, security, etc. everything seems to be working fine.

–          Using my personal user I can access the “Reports” virtual directory where I can see the reports and I can run them with no problem. But this only works if I am logged in the UAT machine itself. If I try to access the virtual directory from my desktop computer, Internet Explorer just can’t connect.

As you can imagine I tried many things, but all with no success to fix this problem, until finally I recalled that Windows Server 2008 R2 has a pretty tight closed default configuration. The only thing that I needed to do was to allow inbound connections to SSRS ports (80 and 141). That was all! J

To allow inbound connections you just need to create a new Inbound Rule in the Server Manager console: Image

Create a New Rule that allows TCP ports 80 and 141 to access the server:


In the Action step, leave the default option “Allow the connection” selected.

In the “Profile” step I select only “Domain” option as the rest don’t apply to my scenario, but feel free to select the others to fit yours.

Finally enter a name for the new rule like “Reporting Services”.

Last step to do is to limit the rule to apply only to running services instead to all running services and programs. For this just double click the new created rule, go to “Programs and Services” tab and click on the “Settings” button in the “Services” section:


In the new window choose the second option called “Apply to Services only”.

Now you can access Reporting Services “Reports” virtual directory from a remote machine 🙂

Tagged with: ,
Posted in SSRS

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

Join 24 other followers