Custom sort in LOV using Information Design Tool

[kamalessh]

List of Values (LOV) could be large in number for a particular object. In that scenario, we can custom sort the LOV, so that the frequently accessible values can be moved up and less accessible values can be moved down. In order to achieve this, we have to create Static list of values in the Business Layer. This blog will detail about the steps in achieving this.

Step 1: Double click on the Business Layer (my Universe.blx) under Local Projects Window. Expand the folder in the Business Layer and double click on the Dimension (Country) for which Static List of Vales has to be created. This will open the properties window of the Dimension Object (Country). Click on Show Values.

Step 2:  This will list all the values associated with that Dimension Object as shown below. The list of Values are arranged in alphabetical order for Country Dimension here. Now we want to move US to the top of the list and sort all the values based on their usage. In order to Custom sort this LOV, these values have to be saved first. In this window choose Save as -> Delimited Text file.

Step 3: By Default, Delimiter Options will be options will be chosen as Comma delimiter and options will be marked for Generate table header. This can be customized according to the needs. Click on Browse button along the File Name field.

Step 4:  Provide a name and click on Save. The LOV will be saved as a text file in the preferred location.

Step 5: Click on Finish.

Step 6:  In the business Layer Window, there will a tab called Parameters and List of Values. Click on that tab. Now it will show the Parameters and List of Values Panel. Under List of Values, choose Static list of values.

Step 7: Provide a name and click on Import.



 

Step 8: Browse to the location where the Country Custom LOV file is saved. By default, Data Separator will be given as ‘;’ and Text Delimiter will be given as ‘None’. ‘First row contains column names’ option has to be checked here as we have saved the list of values with the table header in Step 5. Click on Ok.

Step 9:  This is the step where we will sort the LOV. After importing the LOV file, it will list all the values from the imported file as shown like below and we can sort them in whatever order we want by using the up arrow and down arrow button. After sorting these values, Save the Business Layer.

Step 10:  Expand the folder in the Business Layer and double click on the Dimension (Country). Go to Advanced tab. Click on browse button along the Associate List of Values.

Step 11: This will list all the manually created List of Values created  by the user. Select the Country Custom LOV and Click on Ok. This will associate the Static LOV created under the Business Layer with the Country Dimension. Save the Business layer as well as Data foundation layer and Publish the universe to the repository.

Step 12: Create a webi report with the universe in which we have made these changes.Use County dimension as a prompt and run the query.

Step 13: Now, it can be seen that the List of Values are coming up in the custom sorted order.