How I can choose conditionally parameter from multiple dependent combo boxes in SpagoBI

Question

How I can choose conditionally parameter from multiple dependent combo boxes with subqueries and send it to a report?

I have a BIRT report which use a stored procedure which expect parameter @category_id The input should come from one of three combo boxes:

1. Combo with categories level 1
2. Combo with categories level 2
3. Combo with categories level 3

When you select something in combo 1 combo 2 is populated executing subquery which depends on value from combo 1.

When you select something in combo 2 combo 3 is populated executing subquery which depends on value from combo 2.

If combo 3 has a selected value this is sent for parameter @category_id

If combo 3 has not a selected value and combo 2 has selected value this is sent for parameter @category_id

If combo 2 and 3 are not selected combo 1 is sent for parameter @category_id

Any idea how to do it?

Answer 1

this is ruff code hope it will helps you :

$("#combobox1").change(function(){
var selval1=$(this).val();
var dataString="combo1data="+selval1;// get data from ajax related to this value from ajax
$.ajax({
    url: "somepage",
    type: "POST",
    data: dataString,
    success: function(cbdata2){   
        fillcombo2(cbdata2);
    }
});
)};

$("#combobox2").change(function(){
var selval2=$(this).val();
var dataString="combo2data="+selval2;// get data from ajax related to this value from ajax
$.ajax({
    url: "somepage",
    type: "POST",
    data: dataString,
    success: function(cbdata3){   
        fillcombo3(cbdata3);
    }
});
)};

$("#submitbutton).click(function(){
var val3=$("#combobox3 option:selected").val();
if(val3=="")
{
    var val2=$("#combobox2 option:selected").val();
    if(val2=="")
    {
        var val1=$("#combobox2 option:selected").val();
        if(val2=="")
        {
            var val1=$("#combobox1 option:selected").val();
        }
        else
        {
            //use val1
        }
    }
    else
    {
        //use val2
    }
}
else
{
    //use val3
}
});

Answer 2

You are going to want to use cascading parameters.

Set the default value to % (wild card)

Use a like Statement in your SQL Query, if 2 and 3 remain % then it searches for everything. If they are populated is searches for the specific value.