Creating URL and Picture Columns in PowerPivot

There are a number of different types of columns types that you can define within the PowerPivot manager, which act differently based depending on where they are used. One example are the Image column types. If you have a column that links to an image file then you can use it within your dashboards to spice things up a little.

NOTE: This is part of the Using PowerPivot to Analyze Your Dynamics AX Data blog series where we show how PowerPivot can assist you with advanced data analysis and reporting, making those extracts from Dynamics AX to Excel even more valuable. If you want to see all of the other posts in this series, click here.

GETTING READY

For this example we will convert the year into the corresponding symbol from the Chinese Zodiac. To do this I added all of the images into a shared library within SharePoint for reference.

You can now add a new column that calculates the sequence in the Zodiac that the year falls into.

To reference the image, you can look at the properties of the image so that you can get the template for the URL.

HOW TO DO IT

To use PowerPivot to define image columns, follow these steps:

  1. Create a new column that creates a URL string that matches the links to the images in SharePoint.
  2. Then from within the Advanced ribbon bar, make sure that the Data Category is set to Image URL, otherwise the PowerView will not know to render the link as an image.
  3. If you don’t want to store the images on SharePoint then you can also create relative file paths that will work the same way.

HOW IT WORKS

Now when you use the Image URL fields, the images will be rendered rather than the URL itself.

Advertisements
1 comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: