cancel
Showing results for 
Search instead for 
Did you mean: 

End-to-End Example for Adding Icons to Your Power BI Report

End-to-End Example for Adding Icons to Your Power BI Report

You can download the example Power BI solution from the end of this blog post.

Upload images

  • If you have thousands of pictures in database, you may consider using Filestream. In this example, I haven’t used Filestream additions.
  • Copy your images to a folder such as C:\SQLData\Import
  • Execute this SQL script against your database. This script includes all of the preparation tasks to-be-executed once only:

USE master

GO

 

-- This is needed only if you use FILESTREAM:

-- EXEC sp_configure filestream_access_level, 2 

-- RECONFIGURE

 

-- This is needed only if you use script to read file *names*

-- To allow advanced options to be changed. 

EXEC sp_configure 'show advanced options', 1; 

GO 

-- To update the currently configured value for advanced options. 

RECONFIGURE; 

GO 

-- To enable the feature. 

EXEC sp_configure 'xp_cmdshell', 1; 

GO 

-- To update the currently configured value for this feature. 

RECONFIGURE; 

GO 

 

-- Create table for uploading files

USE AdventureWorks2017

GO

CREATE TABLE [dbo].[Pictures](

            [pictureName] [nvarchar](40) NOT NULL,

            [picFileName] [nvarchar](100) NULL,

            [PictureData] [varbinary](max) NULL,

PRIMARY KEY CLUSTERED

(

            [pictureName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

-- Create upload procedure for uploading files to DB, see more from https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server...

CREATE PROCEDURE dbo.usp_ImportImage (

     @PicName NVARCHAR (100)

   , @ImageFolderPath NVARCHAR (1000)

   , @Filename NVARCHAR (1000)

   )

AS

BEGIN

   DECLARE @Path2OutFile NVARCHAR (2000);

   DECLARE @tsql NVARCHAR (2000);

   SET NOCOUNT ON

   SET @Path2OutFile = CONCAT (

         @ImageFolderPath

         ,'\'

         , @Filename

         );

   SET @tsql = 'insert into Pictures (pictureName, picFileName, PictureData) ' +

               ' SELECT ' + '''' + @PicName + '''' + ',' + '''' + @Filename + '''' + ', * ' +

               'FROM Openrowset( Bulk ' + '''' + @Path2OutFile + '''' + ', Single_Blob) as img'

   EXEC (@tsql)

   SET NOCOUNT OFF

END

GO

 

  • If you are doing just a demo, then you can insert images one by one:

exec dbo.usp_ImportImage 'United States','C:\SqlData\Import','united-states-of-america-flag-icon-32.png'

exec dbo.usp_ImportImage 'Canada','C:\SqlData\Import','canada-flag-icon-32.png'

exec dbo.usp_ImportImage 'United Kingdom','C:\SqlData\Import','united-kingdom-flag-icon-32.png'

  • But if you have many images (or any binary files) to upload, execute this script:

USE AdventureWorks2017

GO 

SET NOCOUNT ON

 

-- Deletes everything from the table!

TRUNCATE TABLE [dbo].[Pictures]

 

-- 1 - Variable declaration

DECLARE @dbName sysname

DECLARE @importPath NVARCHAR(500)

DECLARE @cmd NVARCHAR(500)

DECLARE @fileList TABLE (importFilename NVARCHAR(255))

DECLARE @importFile NVARCHAR(500)

 

-- 2 - Initialize variables

SET @importPath = 'C:\SqlData\Import\'

 

-- 3 - get list of files

SET @cmd = 'DIR /b "' + @importPath + '"'

 

INSERT INTO @fileList(importFilename)

EXEC master.sys.xp_cmdshell @cmd

 

-- 4 - Upload image

DECLARE importFiles CURSOR FOR 

   SELECT importFilename 

   FROM @fileList

   WHERE importFilename IS NOT NULL  -- importFilename LIKE '%.PNG' 

 

OPEN importFiles 

 

-- Loop through all the files

FETCH NEXT FROM importFiles INTO @importFile 

 

WHILE @@FETCH_STATUS = 0 

BEGIN 

--          SET @cmd = 'EXEC dbo.usp_ImportImage ''' +  LEFT(@importFile, CHARINDEX('.', @importFile)-1)

            SET @cmd = 'EXEC dbo.usp_ImportImage ''' +  LEFT(@importFile, CHARINDEX('-flag', @importFile)-1)

                         + ''', ''C:\SqlData\Import'', ''' + @importFile + ''''

   PRINT @cmd

   FETCH NEXT FROM importFiles INTO @importFile 

END

 

-- SELECT * FROM [dbo].[Pictures]

 

  • Result from that should be several lines of commands. Copy them to another query window and execute them.

Create report with icons

  • If you import this new table to Power BI, you should see something similar to this. If you can’t find the new table press Refresh All.
    image.png

let

    Source = Sql.Database("localhost", "adventureworks2017"),

    dbo_Pictures = Source{[Schema="dbo",Item="Pictures"]}[Data],

    dbo_Pictures2 = Table.AddColumn(dbo_Pictures, "encodedImage", each "data:image/png;base64," & Binary.ToText([PictureData], BinaryEncoding.Base64)),

    #"Replaced Value" = Table.ReplaceValue(dbo_Pictures2,"-"," ",Replacer.ReplaceText,{"pictureName"}),

    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"pictureName", Text.Proper, type text}})

in

    #"Capitalized Each Word"

 

  • Let’s go to the DAX side to define the Model. Drag the relationship, in this case PictureName to Country Region Name. By default, this relationship is created as a single direction, but that means that countries without icon will be blanked. So, let’s change the relationship to both of the directions:
    image.png
  • You can define icons on table and matrix, but at least for now (2020-02), you can’t do that to any (?) other chart because they don’t have a conditional formatting feature for the values.
  • Let’s make a simple UI table where we table First Name and Country [Region Name] from sales data table
  • As we have two fields in values, but we want only one of them to have the icon, do not go to Format tab. Instead choose the arrow from the Country Region Name field and select Conditional formatting -- > Icons:
    image.png
  • Choose Format by Field value and select the encoded image column:
    image.png
  • Thanks to the model connection, Power BI knows which image to use. Because the connection is bi-directional, values without icon won’t get blanked. As I haven’t imported all flag images, some of the icons are missing, as expected from the result:
    image.png