Skip to Content

This Document will help you to populate mulitple values in bex variable using Check boxes.

Requirement:User wants pass multiple values in Bex variable from  IPAD.

Create a dashboard with Bex query as a source. As in my example I have used query which gives to zone wise sales depending upon report type. We have variable in BW as Report Type.

Report type  LOV are : W,X,Y,Z

1. Steps to create Dashboard please refer: Creating BO Dashboard from BEx Queries

2. Create a query using Query browser option in Dashboard design. Drag and drop the required fields in Result objects screen. Keep Refresh options as REFRESH ON TRIGGER OF CELL “Sheet1!$A$1”

/wp-content/uploads/2014/08/2_513576.png

3. Map the data to visualize to scorecard component.

4.  Map LOV of BW Variable “Report Type”  in excel cells say at cells Sheet1!$B$3:$B$6 

/wp-content/uploads/2014/08/1_513577.png/wp-content/uploads/2014/08/3_513602.png

5. Now take 4 check box for each report type. Keep Cells in column “C” Empty

/wp-content/uploads/2014/08/3_513602.png

CHECK BOX 1

  • Source Data Sheet1!$B$3:$C$3
  • Destination Sheet1!$D$3

CHECK BOX 2

  • Source Data Sheet1!$B$4:$C$4
  • Destination Sheet1!$D$4

CHECK BOX 3

  • Source Data Sheet1!$B$5:$C$5
  • Destination Sheet1!$D$5

CHECK BOX 4

  • Source Data Sheet1!$B$6:$C$6
  • Destination Sheet1!$D$6

6. Use Following Formulas in cell:


     E3 =IF(D3=0,0,1)

     E4=IF(D4=0,0,1)

     E5 =IF(D5=0,0,1)

     E6= IF(D6=0,0,1)

/wp-content/uploads/2014/08/5_513626.png

Add following Formulas:

F3 :=IF(OR(E4=0,SUM(E4:$E$6)=0),””,”;”)

F4 :=IF(OR(E5=0,SUM(E5:$E$6)=0),””,”;”)

F5 :=IF(OR(E6=0,SUM(E6:$E$6)=0),””,”;”)

F6 :=IF(OR(E7=0,SUM(E6:$E$7)=0),””,”;”)

G3= IF(E3=0,””)

G4= IF(AND(E3=0,E4=0),””)

G5= IF(AND(E3=0,E4=0,E5=0),””)

G6= IF(AND(E3=0,E4=0,E5=0,E6=0),””)

H3= IF(G3=0,””,F3)

H4=  IF(G4=0,””,F4)

H5= IF(G5=0,””,F5)

H6=  IF(G6=0,””,F6)

7. In cell A1(Trigger cell) = CONCATENATE(D3,H3,D4,H4,D5,H5,D6,H6)

8. When We click Check Box 1 , and 3, Value in D3 = W ,D4 = “”,D5=Y,D6 =””

/wp-content/uploads/2014/08/6_513655.png

9. Value in Trigger Cell will be “W;Y” whichis passed to bex Query .

10. Whenever you select the check box , Query got refreshed with selected values(Depending Upon selected item: Checked/Unchecked)

Hope it Helps!!

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply