XSJS select query from calculation view with input parameter

[former_member189009]

Dear friends,

       I am create an XSJS to select data form calculation view with input parameter. In this way ,it works:

var query1 = "SELECT TOP 200 \"SO\", \"ORDER_TYPE\", \"FISCAL\", \"SALE_AREA\", \"COMPANY_CODE\", \"PC\",\"AMOUNT\" " +

              "FROM \"_SYS_BIC\".\"Kevin_Demo.UI5_DEMO1/CAL_SO\" " +

              " ('PLACEHOLDER' = ('$$I_ORDER_TYPE$$', 'YBB1'), 'PLACEHOLDER' = ('$$I_FISCAL_YEAR$$', '2014')) " +

              "GROUP BY \"SO\", \"ORDER_TYPE\", \"FISCAL\", \"SALE_AREA\", \"COMPANY_CODE\", \"PC\",\"AMOUNT\" ";

        But now I want to get the input parameter form my UI5  textfield, then this values will as my placeholder value, how do I write my query ?

         Thanks for your sincerely answer.

Best Regards

Zhang

[pfefferf · Accepted solution]

Hello,

is your question how you prepare the query or how you pass the values from the UI5 source to the XSJS service?

Preparing the query is straight forward, because it is a string. So if you have the values from the UI5 source you can use it for the preparation (e.g. ... "'PLACEHOLDER' = ('$$I_ORDER_TYPE$$', '" + orderTypeParameterValue + "') ...).

To have access to the value entered in the UI5 source you have to pass it as parameter to the XSJS service.

Sample XSJS service call on UI5 side. Here you see that in the "data" attribute a parameter "orderType" is passed to the XSJS service with the entered value (of course you have to replace the placeholder "<entered value>" with the real entered value.

$.ajax({
       url : "<XSJS service URL>",
       type : "GET",
       data : {"orderType": <entered value> },
       success : function(data){
            // success handling;
       },
       error : function(){
           // error handling
       }
  });
});

Access to parameter in XSJS service can be done like following:

var orderTypeParameterValue = $.request.parameters.get("orderType");

The retrieved parameter value you can use to prepare your query string. Please consider that you should do some checks if the parameter is valid (if necessary).

Best Regards,

Florian

[former_member189009 · Accepted solution]

Hi Florian,

    Your answer is very helpful. But I solve my problem like this :

var order1 = "'" + $.request.parameters.get('cmd1') +"'";

  var year1 =  "'" + $.request.parameters.get('cmd2') +"'";

var query2 = "SELECT TOP 200 \"SO\", \"ORDER_TYPE\", \"FISCAL\", \"SALE_AREA\", \"COMPANY_CODE\", \"PC\",\"AMOUNT\" " +

                     "FROM \"_SYS_BIC\".\"Kevin_Demo.UI5_DEMO1/CAL_SO\" " +

                     " ('PLACEHOLDER' = ('$$I_ORDER_TYPE$$', " +

                     order1 +

                     "), 'PLACEHOLDER' = ('$$I_FISCAL_YEAR$$', " +

                     year1 +

                     ")) " +

                     "GROUP BY \"SO\", \"ORDER_TYPE\", \"FISCAL\", \"SALE_AREA\", \"COMPANY_CODE\", \"PC\",\"AMOUNT\" ";

     But I think you can help me with another problem , I have a XSJS to download excel, but when I create an button to realize this function, it can not call the xsjs. I check the development tool in chrome, it have no error.but I don't know what's wrong with this code:

new sap.ui.commons.Button({text: "Download in Excel",

                                         press: //oController.display

                                          function() {

                                              var I_ORDER_TYPE = oInput9.getValue();

                                                 var I_YEAR = oInput10.getValue();

                                                 var Durl = "http://10.99.200.194:8000/Kevin_Demo/UI5_DEMO1/service/dbvisit.xsjs?cmd=Excel"+

                                                             "&cmd1="+I_ORDER_TYPE+"&cmd2="+I_YEAR;

                                                 jQuery.ajax({

                                                  url: Durl,

                                                  method: 'GET',

                                                     success : function(data){ 

                                                             // success handling; 

                                                        }, 

                                                        error : function(){ 

                                                            // error handling 

                                                        } 

                                                 });

                                          //alert("Button pressed!");

                                          }                                   

                                      }),

[pfefferf · Accepted solution]

Can you check the request on the "Network" tab in the chrome dev tools? Do you get a response?

By the way, why are you using an absolute path for the service URL?

Regards,

Florian

[former_member189009 · Accepted solution]

Hi Florian,

     this is the Network, I think it had the response, but it just had no excel download.

    I am a new UI5 learner, do you have any suggestion about this service URL ?

[pfefferf · Accepted solution]

Hi,

your excel is not downloaded (assuming that your XSJS is correct) because the result of the ajax call is handled in the success callback method. So it is not directly interpreted by the browser. You can either implement a logic which does the download in the callback routine, you an implement a button which acts like a link or you go the very easy way and replace your button by a link which calls the excel download XSJS.

Example for a sap.m.Link which calls a URL on a separate tab:

- For a JS view.

var oLink = new sap.m.Link( { "text":"Press me", "target": "_blank", "href": "http://www.google.com"} );

- Same for XML view (which you should prefer).

 <Link text="Press me" target="_blank" href="http://www.google.com" />

So your excel download XSJS will be called and the result is interpreted by the browser which causes the download.

Regards,

Florian