Attractive reports are not only showing the hard material statistics and numbers. Visualization of your data leads more and more to graphical decision making. One particular part of your reports are images for logo’s, product images etc. All this data and images were stored on-premise in the past. But when moving to the cloud, where do we need to store and find our data and images? How do we connect to it how do we use it?
New dashboards are made with powerBI, where you can connect to whatever kind of data stored on whatever kind of storage provider. You can embed pictures in your dashboard or get pointed to them by some URI you specify. But what do you do with existing SSRS reports? Do you convert them all to a powerBI dashboard? There are a lot of reasons why you will stay put on them in the current SSRS reporting serving for now, waiting to transform…
Next, I’ll explain what you can do when your data and images are transformed to the cloud.
In the azure portal, create a SAS token
This results in a long string, something like,
If not already done, create mater key:
CREATE MASTER KEY ENCRYPTION BY PASSWORD='****’;
Create database scoped credential: (use part from sas token beginning from ‘sv=…’)
create DATABASE SCOPED CREDENTIAL RW_hotblobcontainer WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,SECRET='sv=********************************' GO
Create external data source:
CREATE EXTERNAL DATA SOURCE HotBlobContainer WITH ( TYPE = BLOB_STORAGE, LOCATION = https://myaccount.blob.core.windows.net/sascontainer', CREDENTIAL = RW_hotblobcontainer ) GO
CREATE proc [SSRS].[up_GetLogoImage] (@id int ) as begin set nocount on; /*grant grant execute on [SSRS].[up_GetLogoImage] to [SSRS_user]; Grant ADMINISTER DATABASE BULK OPERATIONS TO [SSRS_user]; --*/ declare @SQLstmt nvarchar(max); select top (1) @SQLstmt=N'select imagedocument.BulkColumn as BinaryData from openrowset(BULK '''+lofi.logoimagepath+''', DATA_SOURCE=''HotBlobContainer'', SINGLE_BLOB) as imagedocument;' from dbo.vwLogoFiles lofi where lofi.id=@id ; declare @tblImageData TABLE(binarydata varbinary(max) ); begin TRY insert into @tblImageData(binarydata) exec sp_executesql @SQLstmt; end TRY begin CATCH insert into @tblImageData(binarydata) values(NULL); end CATCH select top(1) binarydata from @tblImageData; end GO
The view vwLogoFiles contains:
CREATE VIEW [dbo].[vwLogoImages] AS SELECT baseT.[LogoImagePath] FROM [dbo].[Establishment] as BaseT ; GO
And that’s it.
The logo image is viewed on the report.