It’s Only the Role I’m Playing

This week I’m going to return to my favorite sample database, Contoso. If you remember, Contoso consists of sales data for a company that produces several different lines of customer electronics, from computers to phones to TVs. The data spans several years of sales. In past examples, I related the FactSales table (the table containing all of the sales records) to several tables including Channel (DimChannel), Date (DimDate), Product (DimProduct) and Product Sub-Category (DimProductSubCategory). In fact, the data source pre-defined these relations so that when I imported the data into my PowerPivot model in Excel, these relationships appeared by default as shown in the following figure.

Visually, I could switch to the Diagram view of the data model to see these relationships represented by solid lines connecting these five tables as shown below.

However, suppose I have additional information in my FactSales table. The DateKey field that I have used in the past identifies the sale date for each of the sales records. In the real world, there may be additional dates associated with each sale. For example, it is not hard to imagine that each sale would also have a Delivery Date and an Invoice Due Date. Now I might ask, does it make sense to report on sales based on the sale date or does it make more sense to report on sales based on the invoice due date or even the date of payment. Well, you might say, ‘That depends on who is asking the question.’ Very true. My Marketing/Sales manager might want to see sales by the sales date. He or she does not care about when the invoice is paid off. They just need to know if they hit their sales quotas for each month. Therefore, they might need a report that uses the default relationship and looks something like the following:

On the other hand, the Chief Financial Officer is not as concerned about when the sale took place, but when the invoice for the sale is due because only then is the income truly realized for the company. Thus the above report does not meet their needs.

Now suppose that I had a few additional columns, as mentioned earlier, that told me some other dates such as when the items were delivered and/or when the invoice for the sale was due. Let’s further suppose that these columns exist in the FactSales table and might appear like the following figure.

With these columns in my FactSales table, I would want to create relations between them and my date table (DimDate) so that I could generate reports using them. The following figure shows the Create Relationship screen in which I define a new relationship between the delivery date (DeliveryDate) column in FactSales and the date (DateKey) column in DimDate. When I click the Create button, the Power Pivot engine creates a second relationship between these two tables. (Remember the first relationship was between the sales date (Datekey) column in FactSales and the (Datekey) column in DimDate.

In a similar fashion, I create a third relation between these two tables to connect the invoice due date (InvDue) column in FactSales and the date (Datekey) column in DimDate. For the purposes of this demo, I am going to stop there. However, I could create additional relationships between any other date fields in the FactSales table and the date (DateKey) column in DimDate. Switching to the Diagram view, I could now see something like the following between these two tables.

Notice that there are three lines between FactSales and DimDate. One of these lines is solid and the other two lines are dashed. You can only have one active relationship between any two tables. That active relationship is represented by the solid line. The other two relations are inactive at the moment. These three relationships are collectively called role playing relations and DimDate is a role playing dimension because only one relation at a time can be active from the DimDate dimension. Think of it this way, the date column (DateKey) in DimDate can play one of three different roles. It can either play the part of the sales date, the delivery date, or the Invoice date. However, it can only play one role at a time. Unless all three dates were exactly the same, I have to choose which role I want DateKey in DimDate to play with the FactSales table by selecting one of the relationships.

The initial data import associate the DateKey column in DimDate to the DateKey value in FactSales which represented the date the item was sold. As long as the report requested wants to allocate sales to the sales date, I need to do nothing other than generate the Pivot Table report as shown below.

However, if I now have to generate a report for my CFO, I would have to go into the model and change the role that DateKey in DimDate plays. I might try to simply right click on the dashed line representing the connection between the DateKey in DimDate with the InvDue column in FactSales and select: Mark as Active.

This action would generate the following error message.

The problem is clearly stated. You can only have a single active relationship between the two referenced tables. Therefore, I must first right click on the current active relationship to deactivate it before activating a different relationship between the two tables.

Once the original relationship is deactivated, I can go back and activate the new relationship between DateKey and InvDue.

Knowing that only one role (relationship) can be active at a time, I am a little surprised that Microsoft did not automatically deactivate the original relationship when a new one is set active. However, like many things, they did not ask me. Plus they may have had other reasons for not automatically deactivating the current relationship. In any case, after making this change and returning to my pivot table (without making any changes to the pivot table itself), the data automatically updates using the new relationship to show the sum of sales by month of the invoice due date rather than the sales date.

If you examine the sales totals for either the years or individual months, you can quickly see that this role change for the DimDate dimension makes a significant change in the sales numbers reported each month.

That’s all for this week. I hope that now you have a better idea how you can use a single dimension to play different roles. (You could also have added the DimDate table three times to the model, once for each date column in FactSales so that each instance of the DimDate table could have a single relationship to FactSales. Then by changing which dimension you select to display in the Pivot Table, you can achieve essentially the same result. This may be easier for the end-user who does not have access to the data model to make the changes listed above. However, your power-users may prefer a simpler model with fewer tables and can change the role played by those dimensions as needed.)

C’ya next time.

Advertisements

Show Report Filter Pages

One of the interesting things to see when working with Pivot tables is to look at the differences between regular pivot tables that Excel has been able to create since about 1997 with Excel 97 (actually Excel 5 in1993 had basic pivot table functionality, but no wizard yet) and Power Pivot tables which has been a relatively recent addition (Excel 2010 with an add-in). Standard pivot table functionality still exists in Excel 2013 and serves as an alternatively tool for simple pivot tables that only require a single data source and less than a million rows of data.

In regular pivot tables, you might want to create a pivot table and use one of the dimensions as a filter. In the following example, I use a version of the FactSales table ripped from Contoso and I load it directly into Excel as one of the worksheets. Then I click on the Pivot Table command from the Insert ribbon to create a basic pivot table.

I added the Sales Amount field to my Values area. Being a numeric value, Sales Amount makes a good choice as a measure for a pivot table. I then added the dimensions for store and product as my horizontal and vertical dimensions generating a reasonable pivot table. However, I also added channel as the filter.

By default, after adding a filter, the pivot table still displays all values for the filter in the table. However, by using the dropdown, I can view the pivot table with one or more of the filter values at a time.

With only four possible values, it would not seem to be a big deal to view each of the channels one at a time. However, Excel provides another way to display the pivot table showing the pivot table on a series of pages with each page representing a different filter value. To do this, I can go to the PivotTable Tools menu group and select the Analyze ribbon. Then select Show Report Filter Pages… from the Options dropdown menu as shown in the image below.

When I select this option, Excel displays a dialog that let me pick the filter I want to expand. In this case, I only have a single filter on the Channel field so I select that filter and click the OK button.

Excel then generates a separate worksheet for each of the filter values and labels the worksheet tab with the filter value so you can easily click on a table to view the pivot table with the selected filter value applied.

This option makes it easier to go back and forth between views of the pivot table with different filter values applied. If you give a copy of the workbook to someone who might not be as familiar with pivot tables as you are, you can expand out the pivot table by the filter values and then lock the workbook so they cannot accidentally make changes to it. It also makes it easier to simply print a series of the pages to your printer if hardcopy is necessary.

So using the same data, I attempted to add the original data to the Power Pivot Data Model by using the Add to Data Model option in the PowerPivot ribbon

I then defined a Pivot Table from the data model using the same fields as before. When I then opened the Options dropdown menu from the Analyze ribbon of the PivotTable Tools group as I did before, I was surprised by the fact that the option to Show Report Filter Pages was greyed out as you can see below.

Apparently, this option is not available when displaying the data through the Power Pivot model, but is available from the basic Pivot table model. I’m sure there is a way to brute force some code to expand each filter value into a table in a new worksheet and rename that worksheet appropriately, but that kind of defeats the purpose of creating a “BI Tool for the Masses” which is the goal of most of my Pivot Table blogs.

C’ya next time.

Some Additional Word Comments

Last week I showed you how to use a Word template as a form in a SharePoint library. I kept the form fairly basic, and did not have the time to cover some additional considerations when choosing to use Word to create your forms. This week I will cover a few of those considerations.

The first consideration is that since form text is stored in SharePoint lists, SharePoint 2013 does not support standard text columns of more than 255 characters by default. Sure you can use a single line of text data type or a multiple line of text, both of which default to 255 characters. At first I was surprised by this since I expected the multiple lines of text to automatically support substantially more text than 255 characters because by default, SharePoint 2010 does.

Therefore, when I created the SharePoint metadata in the library where the form would reside, I created the Lesson Learned Description as a multi-line text data type. Granted, I did not read the 2013 properties carefully because I fully expected that I would be able to store long detailed descriptions in that column. After I added the column to the Word form and saved the Word template back to the library, I created my first form record as shown in the following figure.

After entering the data I wanted into the form, I tried to save the data back to the data store, which in this case consists of the metadata columns in the SharePoint document library where I modified the form template. Notice that the value for the Lesson Learned Description column has a dotted red line around it. This means that there is an error with the value. There is nothing to indicate what the error is. However, since the column is a text field, there are very few things that can cause an error. The most likely cause for the error, and in this case the correct cause, is that the value supplied is larger (in total characters) than the program can save back to the data store.

To test this theory, I deleted a few characters from the description and tried to resave the form data. This time, as you can see in the following image, the save succeeded.

But wait just a second, this problem is even easier to solve. Just go back to the library settings and open the column settings for the multi-line column. By default this data type limits values to 255 characters or less. (No it really does not say that.) However, look for the Allow unlimited length in document libraries setting and change the selection to Yes. While you cannot specify an absolute column length in characters, you do have the ability to allow the column to accept as much data as you want to throw at it.  (Note, this was done in SharePoint 2013.  SharePoint 2010 by default allows multi-line text to be larger than 255 characters.)

However, some of the other column types present more of a problem. For example, it is not possible to surface a hyperlink field from SharePoint in a Word form. Perhaps this is because a hyperlink field actually consists of two values, the hyperlink itself and a description. You also cannot display in the form a multi-selection choice or lookup column. While these fields may appear in the Document Properties panel as shown in the following figure, I have had problems saving the result back to the SharePoint library even if the column in the library is defined as a multi-value choice field (or lookup field).

When attempting to define a multiple value choice data type, I do get the following message when creating the column.

Returning to the question of saving the data, let me show you what happens when you save the form. Unlike InfoPath which just saves an XML file containing the data. The XML definition of the form is saved separately and only once. While this greatly reduces the number of bytes saved with each instance of the form added to the library, it does limit your ability to make changes to the form fields without creating a new content type each time. On the other hand, since the entire Word form including the values are saved with each instance of the form in the library just like any other Word document, it is a simple matter to change the template to add, delete, or change any of the columns on the form. All new forms will use the new template when you create new documents. Furthermore, you can still open the old form instances since they are nothing more than regular .DOCX or .DOC files depending on the form template from which you started.

The following figure shows the first part of the Save As dialog in which I can select where I want to save the completed form. By default, the Current Folder points to the SharePoint library where the Word template has been saved. However, there is nothing to stop me from saving the document in a local drive or network drive. (I can also click on SharePoint to the left of this figure which should also show the current folder at the top of the list on the right.)

If I select the Current Folder, Word opens the Save As dialog as it usually does, but notice in the right panel that the default location points to the SharePoint library. Also note that the form is saved as a regular .docx file. The File Name defaults to XXX.docx where XXX is the first line of text in the template. You will probably want to change this as I have in the following figure. Note however that even here I can select any other location where I have permissions to save files including SkyDrive, Google Drive and other locations.

The last thing I want to point out before ending this week is that when you display the contents of the library, text strings, no matter how long their values may be, do not get any preference during display over columns that have smaller values (in terms of characters). In fact, as the number of columns that SharePoint attempts to display increases, the widths of the columns appear to be controlled more by the name of the column than the data included within the column as shown in the following figure.

If this is a problem because you need to display the data in a report, the easiest solution is to export the data to Excel. Just click on the Export to Excel button in the Connect & Export group of the Library ribbon of the library.

A dialog appears that prompts you whether you want to open the .iqy file or save it. Use the Save option only if you want to copy the file to open the Excel spreadsheet to a different computer.

Opening this file first opens Excel and then loads the data from the current library as shown in the following figure.

At this point, a few simple formatting adjustments to the columns in Excel including possibly the turning off of the grid lines, adding a style, removing some unnecessary columns and you have a presentable report.

Wait a second, what happened to the Lesson Learned Title? Remember that when you export data from a list or library to SharePoint, the export only includes the columns and rows you specify. In this case, the default view displayed the FileName so users could click on it to view the data in the form. However, you probably want to hide this field and display the Lesson Learned Title field. Note also that any filtering on rows carries through to the export. Even if you have your SharePoint view set up to only display pages of 30 rows at a time, export ignores this functionality and exports all rows that match the filter criteria, not just the first 30.

To take this example one step further, you can copy and paste the final Excel spreadsheet into a Word document if it is relatively small. Of course, you could also use Reporting Services to generate a report from the SharePoint library or even use Report Builder with the Excel spreadsheet to create multi-page reports.

Well, that’s it for this time. C’ya next week maybe. I’ll be at the SQL Saturday in Tampa, FL (http://www.sqlsaturday.com/273/eventhome.aspx ). If you are in the neighborhood, stop by to say, ‘Hi!’ I’ll be presenting in the afternoon on building cubes with either PowerPivot or Analysis Services Tabular model (I haven’t decided which way I’ll go yet.)

Building SharePoint Forms with Microsoft Word

Microsoft has announced that InfoPath is at the end of it’s life. Sure they promised to support InfoPath as it currently exists until 2024 which may seem a long ways off. If you have the typical form application, you may think that it will be replaced within the next ten years anyway so why not use InfoPath for now. There are a couple of commercial alternatives to InfoPath such as K2 or Nintex. I’ve not taken a look at either of these two products yet, but if I can get my hands on a trial of either of them, I’ll let you know what I think.

In the meantime, another often forgotten alternative is to create your forms in Microsoft Word. While Word based forms may not provide the functionality that you are familiar with from using InfoPath, you can create basic forms that even reference data in other SharePoint lists and build workflows to support the forms using SharePoint Designer. So for this week, I’ll check out the possibilities in Word so at least I can later compare this alternative to some of the other commercial products. Of course, one of the immediately obvious downsides of this technique is that all Word form users must have Microsoft Word on their local machine to open and fill in the form. Therefore, this is not a preferred solution for forms that I want the general public to use.

Before I begin working on the form, I need to define a few lists and libraries. The figure below shows the schema for a simple four table database to track lessons learned for a company. (These were used in the last couple of weeks to illustrate calculated columns and referential integrity between lists.) I will use the three tables on the left (Projects, Status, and Lesson Types) as lookup lists. The table on the right shows the additional metadata I want to add to a basic SharePoint document library.

Just like I created lookup columns and referential integrity links between lists last week, I can do the same thing between lists and a document library. To begin, I first create the lookup tables as custom lists. I will not go through the steps here on how to create custom lists because I assume that you know how to do that. I next create a new document library named LessonsLeaned.

I next went into the Library Settings found in the Settings group of the Library ribbon. I first need to add the metadata columns that will link to the lookup lists.

As mentioned before, I will not go through the details here of adding the columns as lookup columns since I have done that before. For your benefit, the following figure shows what the column schema should look like for the LessonsLearned document library when it is complete for this example.

I am now ready to edit the Word template that SharePoint uses to create a new document. To do this, I need to still be in the Library Settings page. Then in the General Settings section, I click the Advanced settings option.

Then I look for the Document Template section. On the right side I see the Template URL. This is where SharePoint puts a copy of the default blank Word template for document libraries based on Microsoft Word. (Yes, I can build document libraries with different default document types, but that is another story.) Don’t try to navigate to the URL. It is not directly visible when you navigate through SharePoint. However, I can click on the Edit Template link shown immediately below the URL.

SharePoint now opens the template in my local copy of Microsoft Word. I can now create my form using normal Microsoft Word skills, perhaps by creating a table and then placing text in some of the cells and leaving some of the cells empty where I want to add user prompts for data. I can also include any additional text in the document outside of the table if desired, I can format the table using one of the pre-defined table styles or create my own, and I can use any of the text formatting styles, insert images, and insert hyperlinks.

Once I have finished designing the overall form, I can then begin to add the document fields. I place the cursor where I want to insert the first document field and open the Insert ribbon. I then click the buttom half of the Quick Parts button in the Text group of this ribbon.

This opens a dropdown list of available fields. Some of these fields are part of the default document library definition or even part of the default Word template. However, you will notice the names of the custom fields I added to the LessonsLearned document library such as Project, Style, and Lesson Type.

In a similar fashion, I would add the other metadata fields from my document library. My final table might look something like the following:

When I am done defining the form, I simply save it. Microsoft Word remembers where I opened the template from and saves it back to SharePoint as a template in the correct document library.

Now to use the form, a user only has to navigate to the document library and open a new document. SharePoint uses the just saved template as the default document it opens.

I can also start a new document by opening the Files ribbon and clicking the top half of the New Document button in the New group as shown in the following figure.

SharePoint now opens the new template in my local copy of Microsoft Word were I can begin to enter data into any of the fields. To enter text data, just click on the field name and begin typing. If the field is a lookup field, when the user clicks on the field, a dropdown arrow appears to the right of the field. Clicking this dropdown arrow opens a list of the possible values from the corresponding SharePoint list.

When I save the document, I must specify a file name. However Microsoft Word does default the file location to be the SharePoint library where I saved the template.

In summary, I have built a simple form that can be used with other SharePoint lists and can save the data back to the SharePoint library as metadata. Is this form as powerful as an InfoPath form. Not at this time. Microsoft may be planning on enhancing the form features of Word in the future. In fact, a few rumors have already leaked that we may see some of these enhancements later this year, perhaps as soon as an upcoming SharePoint conference.

Until then, C’ya next time.

Subscribing to SharePoint Libraries with RSS

Do you have libraries in SharePoint that you need to monitor for additions and perhaps even changes? I know that if you are checking these libraries manually, that over time, you will occasionally forget especially if you are as busy as I am. One way to receive notifications automatically is by setting up alerts within SharePoint. Alerts are a great tool that can send you an email when the contents of a library or list has been updated. You even have options that determine what types of changes you want to be notified about. Alerts can be scheduled to arrive instantly, daily, or monthly. With daily alerts, you can even specify when during the day you want to receive the notice and for weekly alerts you can specify not only the time, but also the day of the week.

With all that flexibility, what could RSS feeds possibly offer that alerts do not? One thing that RSS feeds provide is a description of each object in the feed which provides a better idea of its contents than simply receiving an alert containing the title of the object. RSS feeds can also be managed by any RSS reader of which you probably have at least two on your desktop. First you have Outlook which also receives the alert emails. However, rather than go into your traditional email box, Outlook places RSS feeds in a separate folder in the Folders section rather than the Mail section. RSS feeds can also be opened by other applications including stand-alone RSS readers and most modern browsers such as IE. Most RSS readers also let you organize your RSS feeds by their source which may not be easy or even possible with alerts coming into your email’s inbox folder.

So how would I go about adding an RSS feed from a SharePoint site?

First I have to make sure that RSS has been enabled for my site. My SharePoint administrator or I can do this by going to Site Settings of the site from which I want to create the feed and within the Site Administration group of options, click RSS.

The RSS options appear in the following figure and include the capability of adding a copyright notice, editor and webmaster names and a frequency of pushing out updates. By default, SharePoint recommends sending out RSS updates only once every hour (60 minutes). However, the most important property is the check box at the top of this figure that allows me to enable and disable RSS feeds for the current site.

Next I would open the library or list from which I want to receive the feed. Let’s assume for this article that I am creating a RSS feed from a library (the steps are similar to those for a SharePoint list). From the Library ribbon as shown in the following figure, find and click the RSS Feed option in the Share & Track group.

A page appears with some basic information about what an RSS Feed is and includes a link to subscribe to this feed. While the text refers to a generic RSS reader, the default is usually your email application such as Microsoft word.

Because the server application (SharePoint) is trying to open and use a program on my computer (Outlook) a dialog appears as part of the security features of the operating system asking if I really want to allow the website to open that program. This action is normal and should be allowed as long as I know and trust the URL that is trying to make the connection.

After opening Outlook, Sharepoint tries to add the RSS Feed to Outlook. Again, security within Outlook requires permission for another program to add a subscription within Outlook.

While there are some advanced options when creating a RSS feed to Outlook, most of the time, I can safely ignore these options and just click the Yes button as shown in the above image. However, for future reference, you may want to know just what those advanced options are just in case you might need them. So the following figure shows the advanced options.

The feed name is generally a concatenation of the site name and the library or list name with a colon separating the two. I can change the text the user sees by changing the Feed Name in this dialog. Note that this has no effect on the feed source. I also see a location reference. Notice that the location URL contains a GUID to uniquely identify the library or list. If I delete and recreate the source library or list, the new version gets a new GUID. Therefore, any RSS Feeds pointing to the old location will fail and the location URL must be regenerated.

It is also possible to change the folder in which Outlook saves the RSS feed.

The next two check boxes let me determine whether the RSS Feed automatically downloads any enclosures, otherwise known as attachments, from the library or list. This allows me to open the attachments even if I do not have an active internet connection to SharePoint. In addition, I can ask Outlook to download the full article as an .HTML attachment so that I can read the article without having to be connected to the Internet to get to my SharePoint server. This feature can be especially useful when using an RSS feed to an intranet site so that I can read the feeds outside of the office without using a VPN connection.

Finally, by default, Outlook only queries the RSS feed source once every hour. Therefore notification of additions or changes do not occur immediately, but do occur within at most 60 minutes.

After clicking OK, Outlook creates the RSS Feed in the Folders section of Outlook as shown in the image below.

When I click on the folder representing my feed, the center section of Outlook displays the items in the feed much like the Mail section of Outlook displays individual emails. To read an article from the feed, I double click on it. I can also easily delete an article from your Outlook feed. Note that deleting the article from my local feed does not delete the source article in SharePoint so I can feel free to get rid of the articles I have already read.

In addition to displaying the feed in Outlook, I can have the feed appear in both Outlook and IE using what is called the Common Feed list. I can get to this setting from Outlook’s properties. On the File submenu and select Options as shown in the following image.

From the Outlook Options dialog, I click the Advanced group of option in the left navigation. Then in the right panel, I scroll down to the RSS Feeds section. Click check box before the option: Synchronize RSS Feeds to the Common Feed List (CFL) in Windows. I may also want to check the option to redisplay as new any RSS feed item that has been modified.

After saving my property changes in Outlook, all RSS feeds that I subscribe to in the future will appear in both Outlook and IE under the Feeds tab of the View Favorites dropdown. To open a feed, I click on the feed name. Note, adding feeds to the CFL will not affect existing feeds already in Outlook.

That’s all for today. C’ya next time.

Using Microsoft Word to Write Your Blogs

The first thing to know about creating a blog in SharePoint 2013 is that blogs are separate sites, not just web parts on an existing site. If you haven’t created a blog site yet, you need to go to the Site Contents page for the site that will be the parent site for the blog. In the second half of the Site Contents page under the Subsites section, click New Subsite. On the following page, supply a title and web address for your site and then select the Blog template from the Collaboration tab in the Template Selection group. I recommend inheriting parent permissions initially. You can break inheritance and create custom permissions later. You can also decide on this page whether you want the blog name to appear in the top link bar. If not, you must supply another way to navigate to the blog site on your own. The following image shows a completed blog site form.

After creating the blog site, SharePoint automatically opens it for you and you should see a welcome blog entry. In the left navigation area, SharePoint also provides a set of four categories which you can use to group your blog entries. Of course you can easily remove these and add categories of your own that may be more appropriate for your blog site. SharePoint also keeps an Archive by month of your blogs to make it easy for readers to go back in time to find an older blog entry.

In the top-right column SharePoint displays a set of blog tools that allow you to do everything from create a new post, manage existing posts, manage comments to the posts, manage the categories used to group posts, change the post layout, and even launch a blogging app. What is a blogging app you ask? Let’s click the link and find out.

After a few seconds, a dialog appears asking you to enter information, a URL, to register your SharePoint blog account. Fortunately, you do not have to figure out what the URL is as SharePoint automatically fills in the Blog URL text box for you. Simply click the OK button to continue.

You should get another dialog informing you that the account registration for the blog site was successful. In the title bar of this dialog, you get your first clue that the blogging app referred to by SharePoint is Microsoft Word.

At this point Microsoft Word opens, not with a blank page, but with a blank blog template as shown below. Since you are creating the blog entry from within Word, you have all the standard

Word functionality from formatting your text to inserting tables, pictures, hyperlinks, and more. In fact, if you want to create a blog with images in it, using Microsoft Word can actually save you time.

How? Normally you have to upload any images you want to use on a web page into an image library before you try to display the image on a page. That is because the image must be on either the same server as SharePoint or on another web server that your SharePoint Server can reference. SharePoint will not display an image on a page that only exists on your local computer. After all, what would happen if your computer was not turned on or perhaps not even connected to the network anymore? Therefore, you have to first upload the image to a picture or image library. Then you need to capture the shortcut link to the image to the clipboard. Returning to the page where you want to display the image, you first must edit the page, then insert an image web part or perhaps a content editor web part into which you can paste the link for the image now stored in a library. That is a lot of work.

However, using Word, you can simply create your blog entry and include the image directly in the text as shown below:

Next, click the Publish option from Publish dropdown menu in the Blog group of the Blog Post ribbon shown to the right.

Finally, returning to my blog site and refreshing the page, I see my most recent blog entry at the top of the blog that displays not only the blog text, but also the embedded image.

What did SharePoint do with the image? Open the Site Contents and look in the Photos library. More next time on advanced permissions for blogs and how to create a private vs. public blog site.

As an interesting side note, I can also use Microsoft Word to publish to other common blog sites such as WordPress. To do this, begin a new document using the Blog Post template. If I did not see this template, I could download it from Microsoft’s template site by using the search box to search for: Blog Post.

After opening a new document with the Blog Post template, click the Manage Accounts button in the Blog Post ribbon.

In the Blog Accounts dialog, click the New button to display the New Blog Account dialog. The dropdown for the blog provider already contains several common blog sites. If my provider is there, simply select it (i.e. WordPress). If not, I would select: Other and Word prompts for additional connection information.

For the common providers, Word already knows the Blog Post URL and will automatically supply it. I will have to enter my specific blog URL along with my user name and password and any other information my provider requires.

After clicking OK, Microsoft Word attempts to connect to the blog provider. If all the information is correct, I should see the following dialog after a few moments.

I can now set the new blog provider as my default provider.

Or I can simply change the account when I begin a new blog post. Note in the following figure that I can also insert a category for my blog. However, I do not have the ability to select multiple categories like I may do when logged into my actual blog provider.

As with a SharePoint blog, the main advantage of creating my blog in Word, especially if I have embedded pictures like this example and all of my technical blog posts, is that Word handles uploading not only the blog, but also the images to the appropriate media library. (You are looking at the final result.)  I don’t have to first upload the text, then upload all the images and finally reinsert the images back into the text.  This is a huge time saver.  Using the dropdown for the publish button in Word, I can either publish immediately, or I can publish as a draft. Publishing as a draft allows me to connect to my provider and select additional categories for my post as well as schedule the publication for a different time.

That’s it for this time. C’ya.

Going Digital

Are you ready to go all digital?  Is your workplace ready?  Over the past several years, various organizations have taken the plunge to go all digital.  The publishing industry has probably been the most successful.  I don’t think I bought more than 1 or 2 paper books in the last year or so but I have bought at least two dozen electronic books and I carry them around with me all of the time.  I also use the electronic version of best sellers from our local library which I can check out on-line rather than having to physically go down to the library.  What a fantastic resource.

Newspapers and magazines are starting to go the same way.  At first they offered both printed and digital versions of their publications, but now some of them are cutting out the printed versions.  For example, the June 24th issue of Information Week, a free magazine that many of us in the IT industry have subscribed to for years, was the last paper version of the magazine.  In fact, for the last year I have only subscribed to the electronic version of SQL Server Magazine because it is much more convenient to carry around not only the most current issue, but the complete last year of issues on a tablet.  Furthermore, there are interactive features available in the digital versions that do not and can not exist on the paper magazines.  Stories can be linked to similar stories, to further information, and to vendors of products described in the article.  Advertisements can be interactive with links to videos to demo the product.  You can even email the editors and authors sometimes directly from the digital pages of the magazine.

Many of the national newspapers have digital subscriptions available today.  Even our local newspaper started offering digital subscriptions.  For those people who like to clip coupons to save money at the store, I’m starting to see more of the coupons go digital as well so that you only have to display the barcodes on your smartphone to get money back from restaurants, department stores, and other places.

I use to do all of my writing first on paper and then type in what I created for editing and formatting.  There was always something about the feel of a smooth flowing pen on paper as you write that created a sense of enjoyment.  Ok, maybe that is a little weird for some of you, but anyone who is a writer and began their love of writing more than 10 years ago knows exactly what I mean.  However, now I’ve switch to writing all of my blogs, documentation, newsletters, etc. directly in a word processor.  It is not always Microsoft Word either.  Any word processor will do because the basic functions are the same.  I learned how to type in high school and I find that I can type much faster than I can write the same text out freehand.  Thus I save time not having to first write it down and then type it up.  I still haven’t gotten use to writing on a tablet and I much prefer a standard keyboard, but hey, who knows.  After all, I learned how to use a mouse with my left hand so that I could write with my right hand without having to keep switching what I was holding.  (Yes, I occasionally would try to navigate around the screen with my pen and write with my mouse so it was the best solution I could come up with.)

I guess the only problem I have with writing electronically is the ‘darn’ spell correction in most word processors these days.  Sometimes when I misspell a word, the program tries to correct it with what it thinks I was going to say, but while it gets a correctly spelled word, it is often the wrong word.  Therefore, I sometimes send out text with what looks like stupid mistakes.  Yes, I know you are suppose to proof read your document before sending it out, but sometimes I even miss the most obvious wrong words and then have my friends question how smart I really am.

Anyway, my point is that soon you will be able to forget about recycling paper, because paper as we know it today will become ancient history like impressing characters into a wet clay tablet.  I can hear the trees celebrating.  But equally important will be the fact that we no long have to store paper.  Physical paper takes a lot of space and it weighs quite a bit.  (Pick up two copies of War and Peace and use it for your arm exercises.  It may be cheaper than a set of barbells.)  You can easily build a large library at home with practically no required space.  You can also carry that library with you and read on the commuter train to work or while waiting in line somewhere or even during that dull staff meeting that you have to go to every week.

Yes, sometimes there is just something comforting about holding a real book with real pages.  But with the improvements in Kindles, Nooks, iPads and even most smart phones, I find that I miss real books less and less.

I’ve always been a Star Trek fan and many of the things used in Star Trek have become possible over the last several decades since the original series.  Ok, we don’t have transporters or warp drives yet (although wouldn’t that be fun), but the flip phone style communicators exist as do the pads that were used and are now called tablets or even the iPad.  Some of the medical devices for imaging a person’s body to diagnose disease exist or are on the horizon.  However, never once in all of the episodes did I see Kirk, Picard, or Janeway need to fall back on sheets of paper for information.  If you haven’t tried it, try going a week without using paper, or at least a day.  At the very least cut back on your paper usage and rely on digital versions of the information.  It is not as hard as you may think.

C’ya next time.

How to Hide Your Sheet

This week I want to talk a little about Excel and how to hide your sheets, but first a shameless plug.  I will be speaking at the South Florida SQL Saturday this Saturday, June 29th.  My first presentation will be on how to clean your data using SQL Server 2012 DQS (Data Quality Services).  Good clean data is vital when performing good data analysis with your favorite BI tool (SSAS or Excel with PowerPivot).  My second presentation will be an introductory presentation on using SSAS to build cubes.  For those who may say it is not different than working with PowerPivot in Excel, you are maybe 90% correct.  There are some differences, not just in the organization of the menus, but also in the way you build things.  But I’m not going to give everything away here.  Come to Ft Lauderdale to find out.

Ok, over the past 3 years, I’ve gained a new respect for the use of Excel in the work that I do.  As you know, it began with the introduction of PowerPivot for Excel.  Over the months I learned some new tricks when using Excel and I will share one with you today.  When using the original PowerPivot for Excel, every time I created a new Pivot Chart, I would get an extra sheet in my workbook which essentially built the data needed for the chart.  Normally I would not want to display that sheet to end-users because a) there is nothing for them to do on that sheet, and b) I did not want them to accidently change anything that might affect the chart.  I suppose at the time, the code needed that extra physical sheet in order to serve as the data source for the chart just like any other chart in Excel.  (In Excel 2013, that no longer appears to be the case, but keep reading anyway please.)

Anyway, I started to hide these extra sheets so the typical user would not know they were there.  Initially, I used a very basic way of hiding these sheets.  I would first open the Review ribbon and then select Protect Sheet from the Changes group.

ExcelSheet01

By protecting the worksheet with a password, even if a user found the sheet I was about to hide, they would not be able to open and modify anything on it.

Next I would right click on the sheet tab (at the bottom of the spreadsheet) and select Hide.

ExcelSheet02

This causes the sheet tab to disappear from the list of tabs at the bottom of the screen, but the data was still there and could be used by the chart.  Of course you could use this technique to hide any sheet in your workbook.

To restore a hidden sheet, all I would have to do was to right click on the tab and select Unhide and pick the sheet (if there is more than one hidden) from the dialog.  Obviously hiding a sheet provides no security.  That is why I first protect the sheet with a password.

ExcelSheet03

While this works fine for most cases, I found there was another way to hide a sheet.  Microsoft, in their infinite ability to name things clearly, calls this Very Hidden.  To do this, you must go to the Developer ribbon.  If you do not see the Developer ribbon, you may not have turned it on.  To turn on the Developer ribbon, go to File à Options.  Then select Customize Ribbon.  Check the box before Developer in the Customize the ribbon Section and click OK.

ExcelSheet04

Now in the Developer ribbon, click on Properties in the Controls group.

ExcelSheet05

Find the property Visible which should be at the bottom of the list.  Open the dropdown box of possible values and select the value: 2 – xlSheetVeryHidden.  (Yes, I know.  This option is also very hidden.)

Immediately the screen will update and it may not appear that anything happened unless you notice that the sheet you were looking at is gone.  Another sheet is displayed and the original sheet’s tab does not appear anymore at the bottom with the other page tabs either.

ExcelSheet06

So how do you get the sheet back if you need it?  Again the steps are a bit hidden from normal users.  You begin by opening the VB Editor.  You can do this by pressing Alt-F11 or by selecting the Visual Basic icon in the Developer ribbon.  Next select sheet that was hidden from the Microsoft Excel Objects and look in the Properties window (open the Properties window by pressing F4 or by selecting it from the View menu).  Again find the Visible property and change it back to: -1 – xlSheetVisible.

ExcelSheet07

The sheet will now be visible again.  The bad news is that since I told everyone that reads this blog how to recover a very hidden sheet, I guess you will still want to use the Protect Sheet option with a password that I mentioned at the beginning of this article.

C’ya next time.

Send E-mails from Word

Send E-mails from Word

At a recent meeting at work the question came up about a person wanting IT to create a program that would mass mail to a list of people a common email.  Some people suggested we needed to create a .NET application.  Others suggested that we look for a 3rd party solution to buy that would do the mailings for us.  Being the resident renegade who believes in using first what we have rather than looking to the outside for a solution, I first asked…

“Does this list of people include only employees or must the email go to people on the outside?”  You see my first thought was defining a distribution group within Active Directory and then using that distribution group to send out the emails.  But then they told me that most of the emails would be going to people outside of the organization.  Well, it would not make sense to add non-employees to our Active Directory so I suggested…

“What about doing a mail merge from an Access database or an Excel spreadsheet?”  No one was sure if that would really work.  However I remember doing mail merge several versions back in Microsoft Word and thought that it would at least be worth a bit of research.

After the meeting ended, I went back to my desk and started to play around a little.  This is the solution I found:

First I opened Excel and created a simple spreadsheet with the columns that I would need for the mail merge.  The first row consisted of a header with the names of the fields that I would reference from the mail merge.  Finally, I converted the range of cells to a table with the result looking like the following before saving it.

MailMerge01

Then I opened Microsoft Word.  One of the ribbons that few people ever look at, much less know what it includes is the Mailings ribbon.  The following figure shows a portion of that ribbon.

MailMerge02

The command group I want to focus on first is the Start Mail Merge group, specifically, the Start Mail Merge option.  From this dropdown menu, I selected E-Mail Messages since I wanted to send email to the people in my merge file.

MailMerge03

Next I clicked the Select Recipients button from the same ribbon group.  From the dropdown list, I chose the option to: Use Existing List…

MailMerge04

This opened a dialog box that allowed me to navigate to and select the Excel spreadsheet I created earlier with the names and email addresses of everyone I wanted to send the email to.

MailMerge05

After selecting an Excel spreadsheet, I still have to select which sheet in my workbook to find the data table.

MailMerge06

Now more of the commands on the Mailings ribbon become enabled.  Since this is an email, I want to add a Greeting line at the top of the email.  I could build this manually, but there is an option in the ribbon that will do this for me.  Specifically, I want to click on the command button Greeting Line in the Write & Insert Fields group of the ribbon.

MailMerge07

This opens the Greeting Line dialog which shows me what fields it thinks I might want to use in the greeting line.  This is where naming your headings can save you step because it correctly identifies my First Name and Last Name columns.

MailMerge08

However, even if I had named the columns something else, I could click on the Match Fields button near the bottom of this dialog to open the Match Fields dialog to identify the fields needed for the Greeting line as shown here:

MailMerge09

This adds a field to my Word document which will become my email called <<GreetingLine>>.  But let me add the rest of my text for a simple email which might look like the following:

MailMerge10

The only think I need to do yet is to add the field for person’s phone number which I had in as a column in the Excel spreadsheet.  I can position the cursor where I want to add the field, then click on the lower half of the Insert Merge Field button.  This opens a dropdown list of the fields in my source data table.  I can simply select the Phone_Number field from this list.

MailMerge11

This completed my sample email.  To send the email, I next look at the Preview Results group.

MailMerge12

Here I can click on the Preview results button and the use the controls to the right of this button to step through each of the recipients.   The following figure shows a single example:

MailMerge13

If everything looks good, I can complete the merge process and send out the emails by clicking the Finish & Merge button in the finish group.  Note that this button also has a lower half which when you click there opens a dropdown.  From this dropdown, you can tell Word to send the  e-mail messages.

MailMerge14

That was it.  It only took a couple of minutes to develop this solution.  But most importantly, it cost nothing, was immediately available, and made further use of the tools we already owned.  What do you think?  Was it a good, quick solution?

C’ya next time.

What is Up With PowerPivot in Office 2013?

Just about two years ago I discovered this remarkable tool called PowerPivot and was blown away by two important facts.  First this add-in to Excel 2010 allowed me to manipulate millions of rows of data that came from a variety of different data sources to create analysis tables and charts in mere hours that would have required days of programming in other tools prior to that. Second the tool was a free download from Microsoft.

PowerPivot had some of its roots in the old Pivot table capabilities of Microsoft Excel that goes back over a decade and several versions.  For that reason, it was relatively easy to get started using it.  As I was learning how to use it, I started doing SQL Saturday presentations on Pivot tables from the simple PIVOT command in SQL Server to the Excel basic pivot tables of earlier versions to the latest PowerPivot features.  Over the first few months, I began to emphasize more of the PowerPivot features as I explored them and before long I was looking for different ways I could use PowerPivot in my everyday analysis.

In the fall of 2011, I was at a speaker reception the night before the Orlando SQL Saturday and ran into someone named Rob Collie.  I did know him at the time.  We were both going to give PowerPivot presentations the next day and Rob wanted to make sure that we did not walk over each other’s presentation.  As we talked, I found out that he was a prior Microsoft employee who worked on the Excel team.  I was impressed enough with his discussion of the inner workings of the PowerPivot engine and his enthusiasm for the product that I came to believe that PowerPivot could be a BI game changer in Microsoft’s grand scheme of bringing BI to the average business.

Since then I’ve written quite a few blogs about using PowerPivot, both the 2010 version for Excel 2010 and the version for Office 2013.  In fact, over the last year, most of my public presentations have been about PowerPivot and the Tabular model of SSAS in SQL Server 2012 which looks and acts very much like PowerPivot itself.  I even presented a PowerPivot/Tabular model presentation on the DAX language at the 2012 PASS Summit in Redmond.  I’ve been telling everyone how great PowerPivot was (and still is), but then the bad news hit.

Microsoft was pulling PowerPivot from the standard office SKU for Office 2013.  After making such a big deal about the fact that the latest version of Excel for 2013 would include not only PowerPivot but also Power View right out of the box (with the possible exception of having to turn on the COM add-in), I was stunned to hear that these tools would not be available in the final standard edition rollout of Excel 2013.  In fact, I found out that it would only appear in the Professional Plus edition or in the Office 365 subscription version.  After telling all of these people how great this free tool was, I now felt betrayed by having the product yanked out of reach by most potential Office 2013 users who will only have the standard SKU.

One of the first things I did when I heard this was to go off to Rob Collie’s blog to see what he was saying about this situation.  He wasn’t pleased either, but he had an opinion as to why it happened.  I’ll let you read his blog at: http://www.powerpivotpro.com/2013/02/hey-who-moved-my-powerpivot-2013-cheese/.  Rob’s basic premise is that the Office team at Microsoft decided to put PowerPivot and PowerView only in the Professional Pro SKU to add value to that SKU which previously was only differentiated by the inclusion of Microsoft Access which has greatly dropped in popularity over the years, especially since SQL Server Express can be downloaded for free.

So today I’m in Tampa giving a presentation on the DAX language for PowerPivot and the SSAS Tabular model at a SQL Saturday.  What can I tell them?  On possibility is that they should stay on Microsoft Office 2010 for which they can still download the 2010 version of PowerPivot.  For some people, that may work.  If they work for a company that has a volume license with Microsoft, they could still get the 2013 Office SKU and will be able to start using PowerPivot and Power View right out of the box.  But what abou the rest of us?

Interestingly, there is a guest post on Rob’s site from Ken Puls who explains how to buy PowerPivot 2013 using a $30 Volume Licensing Workaround.  The link to this blog is:  http://www.powerpivotpro.com/2013/02/guest-post-from-ken-puls-how-to-buy-powerpivot-2013-including-the-30-volume-licensing-workaround/.  (Sorry for the long link, but I wanted to make sure you see the full link.)  This trick seems a little too good to be true, but it is based on the fact that to get a volume license, you only need to buy 5 qualifying licenses of any Microsoft products including something like the Microsoft DVD Playback Pack for Windows Vista Business which costs about $7.00.  Then you should be able to get a copy of Professional Plus through volume licensing.

Still, it seems like too much work and I suspect that most developers will not want to play that game.  On the other hand, if this begins to limit the use of PowerPivot and Power View by corporate America and especially medium to small business, this could also result in eventual marginalization of what could have been a great product.

In my comment on Rob Collie’s site, I remind the reader of the way Access was hyped during the beta of SharePoint 2010.  The beta versions promised a way to migrate your Access applications to SharePoint to solve many of the concern with Access applications like security, multiple and different copies, multi-user limitations, etc.  But when SharePoint 2010 was finally released, they moved the ability to migrate Access to the Enterprise edition which effectively eliminated this feature from most SharePoint users who only use standard or foundation versions.  As a result, three years later, this feature is practically dead whereas it could have been a game changer moving countless Access applications to a more secure SharePoint platform.

In any case, I will continue to support PowerPivot and hope that Microsoft and the Office team re-consider their decision to hide PowerPivot in the Pro Plus SKU.   What are your thoughts?

C’ya next time.