none
SSRS Column Name?? RRS feed

  • Question

  • I have a scenario like, I have  a tabular report and name of the field is customer_Id, Customer_name, Customer_address etc through datasets. . but their respective titles are CustomerID, CustomerName and CustomerAddress.

    Can SSRS provide such features where I can receive RDL name, its column name through datasets, and Title of the column in ssrs report.

    I have a query which returns RDL name and Filed name through dataset only but trying to get Titlefieldname eg  following query shows rdlname,  customer_id but column title in ssrs report is CustomerID  I need report_name(rdl), columnnamevia datasets, and existing column title -- three column needed. help is appreciated......

    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)
    ),
    --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
    --     Name AS RDL--,[Type],TypeDescription,ContentXML
    --    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') ASCommandType
    --    ,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)
    
    INSERT INTO WorkingDB.AJ
    SELECT DISTINCT
         Name AS RDL--,ContentXML
        --,ISNULL(Fields.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') ASCommandType
        ,Field.value('(./*:DataField/text())[1]','nvarchar(max)') AS Field
    FROM ItemContentXML
    --Get all the Query elements (The "*:" ignores any xml namespaces)
    CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Field') Queries(Field)
    ORDER BY 2
    
    --SELECT
    --     Name AS RDL
    --    ,Fields.Field.value('(./*:DataField/text())[1]','nvarchar(max)') AS Fields
    --	,Queries.DataSet.value('@Name','nvarchar(max)') AS DataSet
    --FROM ItemContentXML
    ----Get all the Query elements (The "*:" ignores any xml namespaces)
    --CROSS APPLY ItemContentXML.ContentXML.nodes('//*:DataSet') Queries(DataSet)
    --CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Field') Fields(Field)
    --ORDER BY 3,2
    
    select * from WorkingDB.AJ order by ID

    • Moved by Kalman Toth Tuesday, November 11, 2014 2:05 AM Better fit
    Tuesday, November 11, 2014 1:53 AM

Answers