Archiv

Posts Tagged ‘SSRS’

Read multiple images/files from folder and insert as blobs in DB table (for SSRS usage)

21. September 2012 1 Kommentar

In SSRS you can integrate picture-resources from different sources:

image

External      = URL to an existing image stored on a webserver
Embedded = serialized blob of image will be stored in RDL
Database    = Get image blob from a dataset

Personally i would always recommend to store pictures in database, instead of embedding them in each report. This approach supports central management accross multiple reports.
By storing the pictures in database you also insure that your RDL files stay “small”, if you have an RDL File with multiple images embedded you will get “huge” RDLs which decreases performance at report rendering time.

Step1: Import pictures from filesystem into DB-Table
To make the import and update of images easy i created a “helper” procedure which can be executed as follow:

EXEC dbo.ST_ReportImagesImport      
         @PictureFolder=‚D:\TEMP\YourImagesFolder\‘,@IncludeSubFolders=1

The procedure uses SQL-internal procedure “dbo.xp_DirTree” to get recursive list of all files in specified folder, read the files and store them in a  “ReportImages” table.

Step2: Create Dataset in Report and lookup your image via expression

This table can then be used as an dataset in SSRS. To get a picture from this dataset you can use the Lookup() formula:

For example use following expression in “Image Properties” dialog to get the picture with ID 1
=Lookup(Fields!ImageBlob.Value),1, IIF(IsNothing(Convert.ToBase64String(Fields!ImageBlob.Value)),"",Convert.ToBase64String(Fields!ImageBlob.Value)), "DsReportImages")

image

TSQL-Code for helper procedure:

 

— ——————————–
— Description:
–this proc imports/syncs imagefiles from a folder in filesystem into table dbo.ReportImages
–If the image already exists in table it will be updated otherwise a new row in table will be generated
— ——————————–
— Changes:
–   2012-09-21 JJuelke initial
——————————–
/*Testcase
EXEC ST_ReportImagesImport @PictureFolder=’D:\TEMP\SUP\types\‘,@IncludeSubFolders=1
*/

CREATE PROC ST_ReportImagesImport
      @PictureFolder nvarchar(2000) = ‚D:\TEMP\SUP\types\‘ –define input directory here which contains your image-files
     ,@IncludeSubFolders int = 1
     ,@OutputFileList int = 1
     ,@OutputTargetTable int = 1

as

–check if directory exists
    declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int)
    insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @PictureFolder
    IF NOT EXISTS (select * from @file_results WHERE file_is_a_directory=1)
    BEGIN
        Print ‚WARNING! Directory "’+@PictureFolder+’" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!‘
        RETURN -1
    END ELSE
BEGIN

–create target table if not exists already
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME = ‚ReportImages‘)
BEGIN
    CREATE TABLE [dbo].[ReportImages](
        [ReportImagesID] [int] IDENTITY(1,1) NOT NULL,
        [Filename] nvarchar(2000) NULL,
        [ImageBlob] [image] NOT NULL,
        [MimeType] nvarchar(200) default(‚Image/jpeg‘) NULL,
        CreateDT datetime default(getdate()),
        UpdateDT datetime NULL)
END

DECLARE @files table (ID int identity, FileFQN nvarchar(2000), FileName nvarchar(200), mimetype nvarchar(100), depth int, [isFile] int, blob [image] )
DECLARE @filename nvarchar(2000), @mimetype nvarchar(100), @FileFQN nvarchar(2000)

–now get folder content (recursive) and store this information in @files table
    INSERT INTO @files (FileName,depth,isfile)
    EXEC master.dbo.xp_DirTree @PictureFolder,@IncludeSubFolders,1
    DELETE FROM @files WHERE isFile = 0
    UPDATE @files set
        FileFQN   = @PictureFolder + FileName
        , mimetype= CASE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename)  )))
                                         WHEN ‚jpg‘ THEN ‚Image/jpeg‘
                                         WHEN ‚jpeg‘ THEN ‚Image/jpeg‘
                                         WHEN ‚bmp‘ THEN ‚Image/bmp‘
                                         WHEN ‚gif‘ THEN ‚Image/gif‘
                                         WHEN ‚png‘ THEN ‚Image/png‘
                                         ELSE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename)  )))
                                         END
    –outpuf read filelist                                    
    IF @OutputFileList=1 select * from @files

–now loop/read each file and import
DECLARE @insertString nvarchar(3000)
    DECLARE row_cursor CURSOR FOR SELECT FileFQN, FileName,mimetype FROM @Files
        OPEN row_cursor
        FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
        WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @insertString = N’WITH Source (BulkColumn,Filename,mimetype) as (                               
                                    SELECT BulkColumn,“’+@FileName+“‘ as FileName, “’+@mimetype+“‘ as mimetype
                                    FROM OPENROWSET(BULK N“‘ + @FileFQN + “‘, SINGLE_BLOB) as tempImg)
                                   
                                    MERGE into dbo.ReportImages t
                                        USING Source s ON s.FileName = t.FileName
                                        WHEN MATCHED
                                        THEN UPDATE SET
                                                 t.ImageBlob = s.BulkColumn
                                                ,t.mimetype = s.mimetype
                                                ,t.UpdateDT = getdate()
                                        WHEN NOT MATCHED by target then
                                            INSERT  (FileName,ImageBlob,mimetype)
                                            VALUES  (s.FileName,s.BulkColumn,s.mimetype);‘
                EXEC SP_ExecuteSQL @statement=@insertString
            FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
            END
    CLOSE row_cursor
    DEALLOCATE row_cursor

–output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages

END

Advertisements
Kategorien:Allgemein Schlagwörter: ,

SSRS Helper Function for Open Report in new window

6. Oktober 2011 1 Kommentar

In SSRS you can define “URL-Actions” for nearly every reportitem (textbox,tablecell..). You Can use this to navigate to another (drilldown) report. BUT URLs DO NOT OPEN BY DEFAULT IN NEW WINDOW AND URL-format for SSRS in native mode is different then in integrated mode. This Helper-function solves both problems:

 

1. Put this code in report.code:

public function OpenRepURL(byVal mode as string, byVal reportname as string, byVal rp as string) as string
dim outURL as string
dim rv as string
dim rs as string

outURL = System.Uri.EscapeDataString(Report.Globals!ReportFolder & "/"&reportname ).ToString

SELECT CASE mode
Case "native"
  rv = "&rc:Toolbar=false&ParamMode=Hidden"
  rs = "&rs:Command=Render"
  rp = "&" & rp

OpenRepURL = Report.Globals!ReportServerUrl & "/Pages/ReportViewer.aspx?" & outURL & rv & rs &  rp
 

Case "integrated"
  rv = "&rv:ToolBar=None&HeaderArea=None&rv:ParamMode=Hidden"
  rs = "&rs:Command=Render"
  rp = "&" & rp
OpenRepURL ="hallo"

End Select

OpenRepURL = "javascript:void(window.open(‚" & OpenRepURL & "‘,’_blank‘,’toolbar=no,menubar=no,status=yes,location=no,width=1200,scrollbars=yes,resizable=yes‘),false,false)"

End Function

 

2. Use this expression in textfield.actions or tablecell.actions:

=Code.OpenRepURL("native","MyDrilldownReport.rdl", "pAlertID="&Fields!AlertID.Value)

Kategorien:Allgemein Schlagwörter:

HowTo sort Date-Dimension (SSAS) parameter values in SSRS ?

12. April 2011 1 Kommentar

Challenge:
Cube has a time-dimension where members (years,quarters,month) are ordered in ascending order. In SSRS Report the members should be ordered in descending order…

Orginal Query (produced by SSRS-wizard):

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS

,[Time].[Calendar].ALLMEMBERS
ON ROWS
FROM [myCube]

image

Change Order of Years:

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS
 ,ORDER
     ( [Time].[Calendar].ALLMEMBERS
      ,[Time].[Year].CurrentMember.MemberValue, DESC)
ON ROWS
FROM [myCube]

image

Change Order of all levels (Year,Quarter,Month):

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS

,Order
  ( [Time].[Calendar].ALLMEMBERS
   ,[Time].[Calendar].CurrentMember.Properties("ID",TYPED)
   ,DESC   )
ON ROWS
FROM GBMS

image

Hint: Use Mosha’s free Online-MDX-Studio  to format SSRS-generated MDX !

Kategorien:Allgemein Schlagwörter: ,