Step1: Creating a Data Model
-
Navigate to > BI Catalog
> Click on Left-hand side top chart icon > Select Data Model
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUvPncYRmNnLmWAX-fFv7H0lUboTRL2ufyAX9syIE22_1yOBZVZpDcHjLE3rTXN2oBr8VSCXq3yES1RD5nX0NCwnl0a73TBx919EG1afDiuUwDhVNJNl9qgP925-ZL0860OU4Mcq6NDFYe/w595-h275/1.png)
-
Click on ‘+’ icon > SQL Query
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYvAZqpHH2wKEteVYqqk5b88uxz1ex3aFuVUKGLFGglcpybk-GVGewdEDt7kFMoweVkwSDnryD6meGUt0qxEIzznL964ProfDewXSAK610ffBubd3xS3n5MNRnJj8YC6gktMZkHQ_JBWnX/w601-h252/2.PNG)
-
Enter Name = Data Set Name (i.e
TEST)
-
Select Date Source = ApplicationDB_FSCM
-
Select Type of SQL = Standard SQL
-
Enter SQL Query.
-
For demo purpose, I have added ‘AP Invoice query’ in data set query.
-
Click on OK
-
Navigate to : Data > Click on
View > Select Tree view (Default)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7W1zTns330pnmRnlr0JeTqI9GOHQ1g6dstrqJHY-JskaQdJw2oV3pfkCDpzTlXJhGV5EBZH7q88d-ANXnqeQTvqrv8lQY8PgbLb2gn0TVBhycrm6NPImyB7Fot5irX-daMgKiyMbLvhLI/w613-h262/5.PNG)
-
We can see the data in
table format as well by selecting Table view
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDWYSc9t3_kD8A3Ij6xqg4PORx1DpKaSl7diErzpbj_sfOeA3-qkoQ_tmwvwlx0430QkDEWV0-bGhZLW26_f16f8er9xE1iK50N4tO33jpiJzpZ4cOan-Fg5_Shfbw1rJdSGsLLEsdwVL5/w626-h260/6.PNG)
-
We can export data by click
on the Export button (Data will be exported In XML format only)
-
We can save the view data
as sample data by click on Save As
Sample Data button. It will help you to design an iterative layout.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBtytrIo2EdhQ3FIMhvqCOlP-T7YCIg1fxxeqMZProVWGFclgkUw_sDYP6GrJBQgy9dF1tfpk6mgh6o653l2BPgd9zFybTZ8EbnnoyzbhbRiqSP9kKYATJZuQLY-2F6K1k12c_W66y67Ls/w617-h282/7.PNG)
Step2: Create LOV
-
For Create LOV:
-
Navigate to List of Value
Tab > Click on ‘+’ icon
-
Enter Name, Select Type as
‘SQL Query’, Select Data Source as ‘ApplicationDB_FSCM’
-
Enter the SQL query based on
what you need to prepare LOV.
-
We have added invoice
number as LOV so we added Invoice Number query.
-
Save the data.
Step3: Create Parameter
-
For creating parameter we
added bind invoice number variable (i.e p_invoice_number) in where clause of
dataset SQL query.
-
Click on OK
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig51pioHTsEaiCJEGxfbVxbkb7MpFqWdXdGMUbhDmNM8qsUDhAMGk_mrfDRg2Mw8Bga6i69Vh15msa00m3n1fE-_cwvADvprJlLF1rnD2x0YxhCNO1SDiiEyK_mkOdPulWFsHUN3xg22c8/w625-h276/9.PNG)
-
Once you click on OK.
-
A System popup message will
ask you to add newly added bind variable as a parameter.
-
Click on OK.
-
Once bind variable selected
for creating as a parameter. The system will automatically create a parameter in the parameter tab.
Step4: Assign Newly Created LOV to Parameter
-
Once Parameter and LOV
created we need to assign LOV in Parameter for run-time selection on value.
-
Select Parameter Type: Menu
-
Enter Display Label: Which
we what to show the parameter label at the time of report execution.
-
List of Values: Select
newly created LOV from the list.
-
Number of value to Display
in List: Default value is 100
-
Can Select All: By Default
Selected (If user not select value from LOV than it will pass all value or Null
value as per radio button selection)
-
Null Value passed: By
Default Selected Radio Button.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA1uK0O65WE_7AF_NBUn2uiAgTyVenh5vPkQkclX8XrymdGiliffdk30IdBkK0vvU2HP9b_f62a33VknaX0N9Y4BAJabHPnFjGiHzApD5g3N0TliZRVSr8inLOesYrOIKDAox9afYhpFAf/w654-h272/11.PNG)
-
Once Parameter level LOv
assigned. Navigate to Data tab.
-
Now we can able to see the
Invoice Number parameter and attached LOV values.
-
Once you pass a specific parameter
value and view the data, the system will fetch only specific data with respect to the passed parameter value.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZdGG_scobtxsak4s5ZLid2ZH84Pk7a_m2Kwse87MyqZWKVCsMMAe9FMlVg0Sf6zaPfKRe4v69EB9JRBmZWfjLSBfWvEakkzvlUOn_qAE0GCl9LRhjlPfoFxg9BBopockp_aPSai2BqSLc/w604-h224/13.PNG)