locked
Use Image from binary column in SSAS Cube in a SSRS Report RRS feed

  • Question

  • Hello,

    I am using SQL Server 2008 R2 Developer Ed SP1 with BI development studio SSAS and SSRS.

    In my database I have an image column that should be loaded in a cube and then be printed in a SSRS Report.

    First I tried to get the picture in the report from the database directly. Works fine!

    Then I tried to get it from the cube. No chance to get it work! The follwoing things are happened:

    1. I gave the image column in the dimension "Test" an ID as key column.

    2. I set the binary image data as name column and value column, each from type binary. The column name is "Blob".

    3. Cube deployment and processing is successful, although there is an error

    "DimensionAttribute [Test].[Blob] : The 'Binary' data type is not allowed for the 'NameColumn' property; 'WChar' should be used."

     

    4. I created a Report project with datasource on the cube and created a dataset with the following mxd-query:

    SELECT NON EMPTY { [Measures].[Wert] } ON COLUMNS, 
    NON EMPTY { [Test].[Text].[Text].members * [Test].[Blob].[Blob].members }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    FROM [Blob Test] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
    FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    It gives a binary code for column Blob as you can see here:

    5. Then I put it inside a table into the image-tool from reporting services. In some communities I found the convert expression to use but it does'nt  work:

    =System.Convert.FromBase64String(Fields!Blob.Value)

     

    I set the mime type to jpeg, and the picture has ending jpg. Could that be the problem? The table in the report gives the following:

    The first image is null so it is ok to show nothing, but the second picture shows an error (red cross).

     

    What am I doing wrong? Where is the missing part?

    I would be very happy if somebody knows how to come out of this trap.

    Thanks a lot in advance!

     

    Oertelcon

     




    • Edited by OertelCon Monday, October 17, 2011 1:15 PM
    Monday, October 17, 2011 12:28 PM

Answers

  • Hi Vinicius,

    happy new year to you and all other first :-)

    I'm sorry, as always I forgot to post as I finally found the solution after endless hours and days klicking around...

     

    The following state works:

    In the dimension TEST there are the attributes ID, Blob and Text.

    Text ist just for testing purposes, nothing to do with the blob content...

    ID is the key column of the dimension and contains a surrogate key.

    Blob contains the binary content. Below you can see the properties of the attribute:

    You must not define a NameColumn with binary data, this will result in an error message! Instead use the ID column as key for Blob and put the binary data from Blob column into the ValueColumn, set data type to Binary. AND VERY IMPORTANT!!! -> set the MimeType not only in the latter report image but set it here in the cube in the source properties as you can see from the screenshot. This was the reason for my problems.

    Then after processing the cube you can use the blob content in a report through the image tool. Just connect it to the cube as usual.

    I hope this helps you to get it solved too. If you have further questions, please just ask...

    Greetings :-)

    • Proposed as answer by Sandip Shinde Monday, January 2, 2012 2:12 PM
    • Marked as answer by OertelCon Monday, January 2, 2012 2:22 PM
    Monday, January 2, 2012 10:35 AM

All replies

  • Hi Oertelocn

    Please see the suggesion of Darren Gosbell.

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/5d98815b-996e-4a11-8fb3-bef894cdaef1


    SUHAS http://suhaskudekar.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Monday, October 17, 2011 12:57 PM
  • Hi Suhas,

    as I wrote under point 2 I already did what Darren Gosbell said and set the binary data to value column of the "Blob" attribute.

    Any other thoughts?

    Monday, October 17, 2011 1:19 PM
  • Hi,

    Does anybody got it to work? I'm facing the same issue.

    Any comments?

     


    Vinicius Paluch
    Thursday, December 8, 2011 7:59 PM
  • Hi Vinicius,

    happy new year to you and all other first :-)

    I'm sorry, as always I forgot to post as I finally found the solution after endless hours and days klicking around...

     

    The following state works:

    In the dimension TEST there are the attributes ID, Blob and Text.

    Text ist just for testing purposes, nothing to do with the blob content...

    ID is the key column of the dimension and contains a surrogate key.

    Blob contains the binary content. Below you can see the properties of the attribute:

    You must not define a NameColumn with binary data, this will result in an error message! Instead use the ID column as key for Blob and put the binary data from Blob column into the ValueColumn, set data type to Binary. AND VERY IMPORTANT!!! -> set the MimeType not only in the latter report image but set it here in the cube in the source properties as you can see from the screenshot. This was the reason for my problems.

    Then after processing the cube you can use the blob content in a report through the image tool. Just connect it to the cube as usual.

    I hope this helps you to get it solved too. If you have further questions, please just ask...

    Greetings :-)

    • Proposed as answer by Sandip Shinde Monday, January 2, 2012 2:12 PM
    • Marked as answer by OertelCon Monday, January 2, 2012 2:22 PM
    Monday, January 2, 2012 10:35 AM