Create a prompt that will show top or bottom values to use, choose the column for the prompt and in show choose SQL results and enter the SQL,
Select Case when 1=0 Then Table1.Column Else 'Top' End From “Subject Area”
Union All
Select Case when 1=0 Then Table1.Column Else 'Bottom' End From “Subject Area”
In Default to, choose specific value and enter Top. Set the presentation variable (pv_tb).
In Answers, add the columns for the request
Country Name, Amount Sold
Set the Amount Sold column as descending, to get the chart values in order.
Add a dummy column, name it as Top / Bottom in that fx enter the following,
Case When '@{pv_tb}'='Top' Then Rcount(1) Else (Max(Rcount(1))-Rcount(1)+1) End
Add a filter over this column as less than or equal to 5.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8hyphenhypheni0hr-807DRYnuUq1o9fCJ5y2lrqjbQZiIre_SKW_1GK0uk4xI3YGcr0i5AWYSFQ0LhVLXeAO8W6fUDy0VaYSRuYFX8Pext6Z0RXdyjk3QSs2XeOmZWPqv2Yd9EP3c77_d82JVh6-t9/s320/Criteria.bmp)
Go to dashboard add the prompt and the request.
This will give Top 5 or Bottom 5 values based on the prompt selection.
Top selection:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQrSv_SDssm7Iimj1XzWJxf4Ry6PRH68VPAfukgpf7wcJs56varNMaGeMtsj-fbjqpe7zVgIXGn2EgMFZp3PRXIlTDZHxTLjvGmmN40oH5wJ4E3yO__Sj1rgBlhtpqheExIKCFTU-C7y6o/s320/Img1.bmp)
Bottom Selection:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJ6GNhxxR7hF1KI6Zgh3AUM_yI2O3Oxp64kTTpYTx8iJv6Xh96svFZN-jSCaL1LNPx9UsNfB0H_-AdcpGS8B_QvgR7iErS8JomNypJQ2dyStL44qFR_jQGEZe_xYlldbQU3vJutE6UxcC3/s320/Img2.bmp)
To have dynamic value to be passed for the report, change the above prompt add another column to the prompt. Make the prompt as edit box and set a specific value and set presentation variable (pv_rk) to have the value as shown below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiuNc9bI7O5TWYkNczSMGCtamRDtHsiazbLHw6I6-O5gWCH_iCVVB9-gWwvolrezVu_pyv39uuO5mb0Xa3NA_7SwvY89sLIiNNFpNd-QuV6AfcHeX-Ubuy2QuBxokB_clV6T6aVZw9ourK/s320/prompt1.bmp)
Now change filter condition in above criteria to add the presentation variable pv_rk as filter instead of the value 5.
The report will be,
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtBNfwsH6RbIop09un9Mo6ih4vcSgORkfuRSuwmAVIEtOCJTK1W84lU8upHdl3-H_zaFQbw4ZA2ZlsvxbjnroneJ76QON0jg20KlG0ChttlUjfTUClDmH0JgmjQOOQHG4AeZnm9oLdZRFu/s320/modified.bmp)
Vino
No comments:
Post a Comment