locked
How to get a dataset within RDL report file to decrypt and rebind to it? RRS feed

  • Question

  • User820687296 posted

    Hi All,

    Few of data columns coming in report is encrypted , i want to get dataset/datatable from rdl file at runtime to decrypt the records and rebind it to report?

    I am using reportviewer on Asp.net page. Is it possible?

    i meant to ask how to get the datatable from report data source in C# code , once i have the datatable i can decrypt each row , i have that code.

    Wednesday, December 13, 2017 10:19 PM

All replies

  • User991499041 posted

    Hi NaveenC,

    Few of data columns coming in report is encrypted , i want to get dataset/datatable from rdl file at runtime to decrypt the records and rebind it to report?

    I am using reportviewer on Asp.net page. Is it possible?

    Yes. It is possible.

    To decrypt the data depends on how you encrypt.

    As long as you know how to encrypt, it's easy to decrypt.

    Regards,

    zxj

    Thursday, December 14, 2017 1:33 AM
  • User820687296 posted

     i meant to ask how to get the datatable from report data source in C# code , once i have the datatable i can decrypt each row , i have that code.

    Thursday, December 14, 2017 2:31 AM
  • User347430248 posted

    Hi NaveenC,

    you had asked,"how to get the datatable from report data source in C# code".

    you can try to refer query below may help you.

    --The first CTE gets the content as a varbinary(max)
    --as well as the other important columns for all reports,
    --data sources and shared datasets.
    WITH ItemContentBinaries AS
    (
      SELECT
         ItemID,Name,[Type]
        ,CASE Type
           WHEN 2 THEN 'Report'
           WHEN 5 THEN 'Data Source'
           WHEN 7 THEN 'Report Part'
           WHEN 8 THEN 'Shared Dataset'
           ELSE 'Other'
         END AS TypeDescription
        ,CONVERT(varbinary(max),Content) AS Content
      FROM ReportServer.dbo.Catalog
      WHERE Type IN (2,5,7,8)
    ),
    --The second CTE strips off the BOM if it exists...
    ItemContentNoBOM AS
    (
      SELECT
         ItemID,Name,[Type],TypeDescription
        ,CASE
           WHEN LEFT(Content,3) = 0xEFBBBF
             THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
           ELSE
             Content
         END AS Content
      FROM ItemContentBinaries
    )
    --The outer query gets the content in its varbinary, varchar and xml representations...
    SELECT
       ItemID,Name,[Type],TypeDescription
      ,Content --varbinary
      ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
      ,CONVERT(xml,Content) AS ContentXML --xml
    FROM ItemContentNoBOM

    other example:

    --The first CTE gets the content as a varbinary(max)
    --as well as the other important columns for all reports,
    --data sources and shared datasets.
    WITH ItemContentBinaries AS
    (
      SELECT
         ItemID,Name,[Type]
        ,CASE Type
           WHEN 2 THEN 'Report'
           WHEN 5 THEN 'Data Source'
           WHEN 7 THEN 'Report Part'
           WHEN 8 THEN 'Shared Dataset'
           ELSE 'Other'
         END AS TypeDescription
        ,CONVERT(varbinary(max),Content) AS Content
      FROM ReportServer.dbo.Catalog
      WHERE Type IN (2,5,7,8)
    ),
    --The second CTE strips off the BOM if it exists...
    ItemContentNoBOM AS
    (
      SELECT
         ItemID,Name,[Type],TypeDescription
        ,CASE
           WHEN LEFT(Content,3) = 0xEFBBBF
             THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
           ELSE
             Content
         END AS Content
      FROM ItemContentBinaries
    )
    --The old outer query is now a CTE to get the content in its xml form only...
    ,ItemContentXML AS
    (
      SELECT
         ItemID,Name,[Type],TypeDescription
        ,CONVERT(xml,Content) AS ContentXML
     FROM ItemContentNoBOM
    )
    --now use the XML data type to extract the queries, and their command types and text....
    SELECT
         ItemID,Name,[Type],TypeDescription,ContentXML
        ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
        ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
    FROM ItemContentXML
    --Get all the Query elements (The "*:" ignores any xml namespaces)
    CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

    Reference:

    Extracting SSRS Report RDL (XML) from the ReportServer database

    Regards

    Deepak

    Thursday, December 14, 2017 5:50 AM