Creating a web font to be used in SharePoint (Internet Explorer)

I was recently asked to embed a custom font in a CSS file that will be used in a branding project for SharePoint 2013. I was given a TrueType Format file (TTF) to accomplish this. The problem is that Internet Explorer will not take TTF files however Firefox will. To make these both compatible, you will need a few different file types (EOT, TTF, OET) to ensure cross-browser compatibility.

Here is what I did to get my font working.

1) I went to to use its webfont generator. This tool will create those other file types for you.

2) In the top tabs, select “WEBFONT GENERATOR”.

3) You will be taken to a screen where you will add the font (the TTF file you have). Select either the basic option or advanced.

4) After uploading your TTF file, click “Download Your Kit”

5) You will download a zip file all of the files

6) Unzip the zipped folder

7) Add the files to the Hive in a specific location under a folder or within a document library if you would like. For this example, I added the files to the 15 Hive for SharePoint 2013. If you use a document library, just update the locations in step 8 to point to the files in the document library.

8) Edit your custom CSS style sheet used for branding (replace “FontName” with the name of your font and replace the “CustomFolder” with the name of the folder you use) and use the code below. I put the code below at the top of my CSS file.

@font-face {
font-family: ‘FontName’;
src: url(‘/_layouts/15/1033/styles/CustomFolder/FontName.eot’);
src: url(‘/_layouts/15/1033/styles/CustomFolder/FontName.eot?#iefix’) format(’embedded-opentype’),
url(‘/_layouts/15/1033/styles/CustomFolder/FontName.woff’) format(‘woff’),
url(‘/_layouts/15/1033/styles/CustomFolder/FontName.ttf’) format(‘truetype’),
url(‘/_layouts/15/1033/styles/CustomFolder/FontName.svg’) format(‘svg’);
font-weight: normal;
font-style: normal;

9) Refer to the fonts that you would like changed by doing something like the following. The font name used should be the same name used above in the line “font-family: ‘FontName’;”. In the example below, I’m referring to an element with an ID of “pageTitle” and the font being used will be my custom font loaded from the top of my CSS file. In addition, the code below is written after the code in step 8 to ensure the font is loaded before it is being called.

#pageTitle {
font-family: ‘FontName’;

After that, you will be done. You may notice a little bit of load time and a delay the first time your custom font gets downloaded but it should get cached after its first download. 

Posted in CSS

Removing Left Navigation Bar in SharePoint 2013 Master Page

When using PerformancePoint 2010 and 2013, you are given the option to create a dashboard which stores reports and filters. Another option that you are given is the option of assigning the dashboard to use a specific master page. This is a terrific feature in PerformancePoint. For example, you may find yourself loving your typical master page used on your site but the spacing and margins will squeeze all of the reports for your dashboard.

That’s where this blog posting comes in. The steps below will outline how to manipulate the current default master page (seattle) to remove the left navigation bar and the title bar that contains the search control. As a result, it will remove all of the default spacing on the left and at the top to provide more space to see the reports on the dashboard!


I’m using in-line style changes for this solution that will override the CSS styles that are applied to the divs outlined below. Typically this is bad practice but this is a quick way to get a BI master page up and running the fastest way possible.

Also, this shouldn’t impact things moving forward. However, if you plan on using this in an organization or enterprise production environment, it is best to make a copy of the default CSS file used in the seattle master page and make the necessary changes in the CSS file instead of in line styles. I won’t be covering the CSS file in my example but my changes should be easily found in the CSS file as long as you understand why we are making the changes.


Hide Title Row

I wanted to hide the title bar on my master page including images and the search control. To do that I had to set the div with ID “s4-titlerow” to “display: none”.

 Find Title in Firebug

Inline Style to Remove Title in Firebug

Hide Side Nav Bar

The next thing I wanted to do was remove the left navigation bar. There is a div box with ID “sideNavBox”. I want to set the display property to none for the div box.

Find Left Navigation in Firebug

Remove Left Navigation in Firebug

Change the margin for the Content Area

You will notice that we still have quite a bit of margin to the left. This is no good for our dashboards. We want to adjust this area so we aren’t loosing so much space for our reports.

To do this, we need to adjust the div with ID “contentBox”. This has a default margin-left property set to 220px. The margin is the reason there is so much space. In order to do this, let’s change the margin-left property to 20px instead of 220px.

Show Extra Space in Left Navigation

Change Left Navigation Margin in Firebug

And there ya go. Those are the properties that need changed in the seattle master page. Open it up in SharePoint Designer 2013 and change the mark up.

Title Row

Edit Title Row in Master Page in SharePoint Designer


Edit Side Nav in Master Page in SharePoint Designer

Content Box

Edit Content Box in Master Page in SharePoint Designer

Setting a dashboard to a new Master Page

Now that you have created the master page, chances are you want to use it. From my previous example, I created the master page at the root web for my site collection. However, I have a sub site that is using the BI Center site template which is where I want to use my master page. So I still want to use the default Dashboards library to store my dashboards but I want to use my master page. What’s the solution? First I’ll show you the problem.

So I open up PerformancePoint Dashboard Designer.

Open SharePoint Designer

Next, I go to the dashboard I want to use the custom master page. Then I right click on the dashboard and click “Deploy to SharePoint…

Deploy to SharePoint in SharePoint Designer

A pop up window will display. It will let you navigate to any subsites. For me, I have a BI Center subsite with a dashboards library where I want to put my dashboard. When I click the dropdown location you will see I don’t have my BI master page. Its because I created it at the root of the site collection and not the sub site level. If I click “Site Pages”, I’ll find my master page.

Deploy to SharePoint in SharePoint Designer Pop Up

Solution to the Issue

The solution is to add the master page to the sub site. So the first step is to open up the Master Pages library in SharePoint Designer 2013 and Export File the master page somewhere locally.

Export File from SharePoint Designer

The next step is to open up the sub site in SharePoint Designer 2013. Select the Master Pages library.

Select Sub Site in SharePoint Designer

Click on the Import Files button. Click Add File from the pop up window and browse to the local copy of the master page you just exported in the step above. Then click OK.

Add local copy of Master Page

The master page is now added.

Add Master Page in SharePoint Designer

Try deploying the dashboard to SharePoint again and now, you will see the BI master page we created before as a master page to use now.

Use Master page in SharePoint Designer

Now there is no left navigation (quick navigation) or title row and the report can now stretch across the page.

Master Page without Left Nav and Title

Tagged with: , , ,
Posted in CSS, HTML, SharePoint

Connecting an Excel Sheet to SharePoint

If you were lucky enough to attend the Microsoft SharePoint Heartland conference last month, then you were able to see the keynote speaker Dux Raymond Sy present. He was very entertaining and insightful regarding how a business uses SharePoint. During his presentation, he showed how easy it is to create an integrated Excel file that has values updated automatically from a SharePoint list or vice versa. This is a great tool to take advantage of because it bridges the gap between users who are hesitant to use SharePoint and users who prefer SharePoint. The steps below outline how easy it is to get this started. This feature is easy to set up but unless you know where to look it may be hard to get started. There is one caveat to this example, you will need an Excel add in to do this. It is a free add in that was made available in 2007. Hooray!

Setting Up the Excel Add In (SynchronizeWSSandExcel)

First thing is first, go to the link below to download the add in. Once that is done, run the executable file that gets downloaded from your downloads folder.


Click Run in the pop up window


Click Yes on the next pop up


Click Yes to agree the licensing agreement.


You may have to extract/unzip the files. Use the default file path location and click Unzip or Extract.


The next thing we need to do is run the Excel Add in file that we just downloaded and unzipped. Within Winzip go to the text box with the label Unzip to folder (see image above). Copy and paste the file path into the location of a windows explorer window. Then double click the icon named SynchronizeWSSandExcel.

SynchronizeWSSandExcel File Path

The Excel Add in will open up Excel and display a pop up that enables Macros in the worksheet. Click Enable Macros.

Macro Pop Up

When you go into your Excel file, you should now see under the Design tab a SharePoint section with a button called Publish and allow sync. Please note, you may have to restart Excel in order for the installation to work correctly.

Design Tab

Connecting your Excel Spreadsheet to SharePoint

Open up a new Excel file and put some information into it.

Excel Data File

Next, make that information into a table. Highlight the information and under the Insert tab, click Table.

Insert Tab

A pop up window will display asking about the header columns, click Ok.

Create Table in Excel

Click on the table that you created and go to the Design tab. Click Publish and allow Sync.

Design Tab and Table

Next, a pop up window will display. Provide the URL of the SharePoint site you want to create a list in the Address text box. Provide the name of the list and a description for the list then click Publish.

Create a new List in SharePoint

The table will update in the Excel file with an ID column now.

New ID Column in Table

Also, our list has been created in SharePoint.

New SharePoint List

Lets add a new row in our Excel file. Note: Be sure not to provide the ID value. For my example, I would provide a value in a new row for columns Department and Amount. Let the table auto increment the ID number. You can see if you did this right or not by looking for the little blue icon in the bottom right of the last record. Hopefully you can see that in the screen shot below.

Add new Record

Now let’s synchronize our changes to our SharePoint list. Right click into the Excel table and select Synchronize with SharePoint under the Table category.

Synchronize with SharePoint List

Go to your SharePoint list and refresh the page. You will now see your new record in the SharePoint list!

New Record in SharePoint List

Now we are going to test updating our SharePoint list and then synchronize our changes to our Excel book. Let’s add a new record to our SharePoint list.

Create new record in SharePoint List

Another Record added to SharePoint List

After adding our record to our SharePoint list, let’s go back to our Excel file and synchronize our records. Click in the table and right click, select Synchronize with SharePoint under the Table category.

Synchronize with SharePoint List

Now we can see our finance record that we just created in our SharePoint list is now in our Excel file. We just made Excel and SharePoint work in perfect harmony. All it took was an Excel Add in called Synchronizing Tables with SharePoint Lists.

All Records in Excel

Tagged with: , ,
Posted in Excel, SharePoint

BI Semantic Model?

With the release of SQL Server 2012, Microsoft offers a second new data model to their BI solutions. They are calling the overall OLAP solution the BI Semantic Model. It’s a framework to give companies more options for offering their business users a front-end interface to interact with data and build their own insights.Image

Looking at the diagram above, we can see two different data models. One is the traditional multi-dimensional model. This has been what Microsoft has traditionally offered with previous versions of SSAS. This model supports MDX querying, and for data access, can support ROLAP, MOLAP and a hybrid version of the two as well. The newer data model introduced is the Tabular data model.

This is Microsoft’s In-memory OLAP solution. We can start building out a Tabular data model by opening up Excel, and building a Power Pivot model. The language supported is called DAX (Data Analysis Expressions) and is meant to be very similar to the excel language used. This is also how business users can start building out their own BI solutions and incorporating their own data feeds and insights. This is where we can see the advent of what Microsoft deems “Personal BI”. Moving this Power Pivot solution to SharePoint falls more in the range of “Team BI” and allows you to share this tabular model with others to be opened up in Excel. Last we can move the Power Pivot model into an SSAS Tabular model instance, and distribute for other users to connect to via SSRS, PerformancePoint, PowerView, and Excel. So to re-iterate, there are two ways to build a BI Semantic model using the tabular model, through Power Pivot, or through SQL Server Data Tools and loading it into SSAS.

A few quick notes on the difference between Tabular/Multi-dimensional:

  • You cannot combine an SSAS instance to support both. If you have a multi-dimensional instance and you want to load a tabular model, you have to install a separate instance in tabular mode.
  • PowerView can only connect to tabular
  • Data mining can only be done on multi-dimensional
  • Translations only apply to multi-dimensional

I’ll write more posts in the future breaking down the advantages/disadvantages of using the different models and when you might want to choose one over another.

Tagged with: , , , ,
Posted in Business Intelligence, PowerPivot, SSAS

CSS Sheet Limit in Internet Explorer?

The Issue

I have recently come across an issue that is both annoying and hard to detect. Let me give you a little background, I was testing my visual web part in a SharePoint standard farm. Part of my visual web part is that it has its own CSS file used only for the web part and only gets loaded on the screen if the web part is present on the web page.

While testing, I noticed the formatting was completely off on my web part. I quickly looked at developer tools to see what the issue was. It turns out that my CSS was not getting loaded on the page. I next went to Firefox to see if the same issue was there and sure enough, Firefox works great!

The takeaway?

It turns out there is a CSS sheet limit of 31 in Internet Explorer 6-9. The frustrating thing is that the browser doesn’t inform the developer/end user that it is ignoring additional CSS files on purpose. This is something built into the browser. So if you are heavily dependent on Internet Explorer in your organization, you should either put additional CSS styles into an existing style sheet or combine existing ones together. Chances are if the environment has this many CSS files, then the environment is probably not best utilizing performance. In regards to best practice, try to limit the number of CSS files. By the way, it looks like IE 10 is increasing this threshold by a lot.

Below is a good link that explains the specifications even more. Happy coding.

Tagged with: , ,
Posted in CSS

Microsoft SSIS Package Configurations

My name is DJ Maley and I’m going to be contributing to this blog more from a Microsoft BI perspective and how to utilize their BI stack for your organization. Some of these posts will be dealing with the reporting tools that integrate very well with SharePoint and others will be dealing with strictly more BI related stuff from a data warehousing perspective. This first post is dealing with a common problem of how to setup your SSIS ETL packages so you can edit them without having to directly edit the package by using package configurations.

Microsoft provides multiple ways to store information about properties and objects associated with your SSIS ETL packages through package configurations. This provides a way to edit objects inside the packages, without actually opening them up and saving them. It also enables a way to dynamically change objects depending on the environment and settings used to help migrating ETL through the development process. Using the wizard, there are 5 general configuration types we can choose from:

  • XML configuration file
  • Environment variable
  • Registry entry
  • Parent package variable
  • SQL Server

I’m going to explain a little bit of each one and tell you one of my preferred ways for setting up your packages to enable seamless migration between environments.

XML Configuration file

  • This stores any of your package information in the XML format in a physical file. The package will need access to this file, so ensure that you place it either on a common drive between the different environments or that you use an environment variable to point to where the file will be located.

Environment Variable

  • You can store a property of an object in an environment variable.
  • Note: You can only store one property at a time with each Environment Variables, Registry Entries, and Parent Package variables.

Registry Entry

  • Storing the property within a registry entry for configuration

Parent Package Variable

  • This relies on a master package calling the child package and passing a variable to it

SQL Server

  • This stores any of your package information in an SSIS Configuration table. The user executing the package will need access to the database to read the configuration information.

Preferred methods for moving between different environments.

When setting up typical ETL migration environments, one typically has a Development box, QA, UAT, and Production. Sometimes these can be combined (such as Dev/QA or QA/UAT) but we need to build an infrastructure for moving packages between the environments without having to edit the packages. This is my preferred approach for setting up an easy to use infrastructure for ETL.

SQL Server + Indirect XML configuration file:

  1. Note: most of these steps are for getting started, if you already have your XML file and your environment variable pointing towards it, then you can skip most of these steps until the end.
  2. Primary connection – First we setup a primary connection for connecting to our SSIS Configuration table. When first setting this up, we will be on our development machine, so we will want to connect to the development environment database that will be hosting our SSIS Configuration table. For this example, our server will be called DevServer and our database will be called ETL_Admin.
  3. Next rename the connection. This connection can be named “Primary” or “SSIS_Config” or whatever you prefer, but the primary purpose of this connection is to just make sure it’s connecting to the SSIS Configuration table.
  4. After this, we are going to enable package configurations by selecting the checkboxImage
  5. Now we are going to add a configuration, and make the configuration type an XML configuration file. Specify a directory where you are going to access it. You can put it wherever because it will probably change on different environments and this is what the environment variable will be for later. For my example, I’m putting it in C:\SSIS_Config\ and naming it SSIS_Config.dtsConfig.
  6. Now scroll up to the Connection Managers category and expand your Primary connection properties. This is where you will select all of the different properties you want to store in your connection such as ConnectionString. Now name your connection and click finish.
    1. This step stored all of the properties in the file you specified in the last step, now we are going to point to that file, and change it for all of our environments.
  7. Next create an environment variable on all machines and servers that will be working with the development/migration process. We can name this ETL_Primary, or SSIS_Config, or whatever you need to make sure you know what it is when looking for it. You are going to change the value of this environment variable depending on where you are storing your XML file. For example, I may have a different structure on the actual servers that is different from my machine, so it could look like this:
    1. My machine: C:\SSIS_Config\SSIS_Config.dtsConfig
    2. Dev Server: E:\SSIS_Config\SSIS_Config.dtsConfig
    3. QA Server: E:\SSIS_Config\SSIS_Config.dtsConfig
    4. Etc…
  8. The next step involves copying this XML file to the different servers, and changing the configuration to fit that environment. So I will copy my SSIS_Config.dtsConfig verbatim from my machine to the Dev server, since it’s pointing at the same location. But for QA we may change the Data Source part of our connection string to point to:
    1. For QA: QAServer
    2. UAT: UATServer
    3. Prod: ProductionServer
    4. You can also use IP addresses as well
  9. Now after placing the XML configuration files with the respective changes into each environment, and after creating an environment variable in each environment to point towards it, we need to restart the machines for the environment variables to show up.
  10. After the machines have been restarted, we can go back into our Package Configurations Organizer, and edit the previous configuration we made. Now that we have an environment variable to point to, we can choose it from the drop down below.Image
  11. We now have a dynamic primary connection that will find the correct SSIS Configuration table depending on which environment it is running in. Now we can start using SQL Server connections for storing all of the rest of our configuration information, and we only have to worry about migrating changes between environments through SQL scripts without having to edit packages directly.

For a breakdown of how this is happening, I made a diagram below.

Test.dtsx wants to load into the DataWarehouse database.

When running it in Development, it wants to use the server DevServer.

When running it in QA, it wants to use the server QAServer.


Tagged with: , ,
Posted in Business Intelligence, SSIS

How to Export Excel File Data to a SharePoint List

The other day I was working in an Excel file in which I created a few rows of data. I realized that I wanted to put the data into a SharePoint list without having to deal with copying and pasting into datasheet mode. Luckily, there is a feature built into Excel 2010 and Excel 2013 that makes exporting information easy. It should be noted that this is a one time push of information. If you are looking for something that is connected with Excel and a SharePoint list that allows data changes, I’ll be posting about how to make this happen in the next few days.

  • Open up Excel (I’m using Excel 2013 but this should work Excel 2010 as well)
  • Create a header row of data
  • Create a few rows of data


  • Highlight the rows of data including the header then click the Insert tab


  • A pop up window will display like the image below, click OK


  • The data will now be in a Pivot Table


  • Under the Design tab in Excel, Click Export and select Export Table to SharePoint List


  • Next, run through the wizard that displays


  • Provide the URL to the SharePoint site and provide a name for the list then click Next
  • On the next step of the wizard, the columns and data types will be listed, verify and then click Finish


  • A success window will display


  • A new list is created that connects with this Excel Sheet (Check SharePoint)


  • Click into the list to view the same records in the Excel sheet


Tagged with: , ,
Posted in Excel, SharePoint