Tuesday, 27 March 2012

Retrieve Label/Values of Option Set/Two Options Attributes using SQL Script

When you want to retrieve the list of  Label and Values of Option Set and Two Options attributes for SSRS report parameter, documentation, development or whatever purpose, you can query by using the following SQL Script.
You will have to remove/modify the two lines of conditions ("new_testentity" and "new_testoptionsetattribute") accordingly to your requirement.

 SELECT Value AS [Text], AttributeValue AS [Value] FROM FilteredStringMap  
 WHERE FilteredViewName = 'Filterednew_testentity'  
 AND AttributeName = 'new_testoptionsetattribute'  
 AND LangId = 1033 -- Language Code, English U.S is 1033  
 ORDER BY DisplayOrder  

This is the alternative query over Schema tables but that won't work in SSRS.

 SELECT DISTINCT  
         Entity.LogicalName AS EntitySchemaName  
 ,        Attribute.LogicalName AS AttributeSchemaName  
 ,        OptionSet.Name AS OptionSetName  
 ,        AttributePicklistValue.Value AS OptionValue  
 ,        LocalizedLabel.Label AS OptionText  
 FROM    MetadataSchema.LocalizedLabel  
     INNER JOIN MetadataSchema.AttributePicklistValue ON LocalizedLabel.ObjectId = AttributePicklistValue.AttributePicklistValueId  
     INNER JOIN MetadataSchema.OptionSet ON AttributePicklistValue.OptionSetId = OptionSet.OptionSetId  
     INNER JOIN MetadataSchema.Attribute ON OptionSet.OptionSetId = Attribute.OptionSetId  
     INNER JOIN MetadataSchema.Entity ON Attribute.EntityId = Entity.EntityId  
 WHERE LocalizedLabel.Label <> ''  
 AND Entity.LogicalName = 'new_testentity'  
 AND Attribute.LogicalName = 'new_testoptionsetattribute'  
 ORDER BY Entity.LogicalName, Attribute.LogicalName, AttributePicklistValue.Value  

No comments:

Post a Comment