How to Execute SAP Query and get its Result in UiPath

SAP Query is a very interesting and easy to use approach to create reports which contains data outputs from SAP tables. With SAP Query you have the possibility to use tables, combine tables with joins and other data sources to get the data you need. You can find a very good description how to handle SAP Query at the SAP Community.

Yesterday at the UiPath DevCon I had a very interesting discussion in the AMER room about this approach. And the question here was: How can I use this approach also in UiPath? I remembered that there is a remote enabled function module (RFM) that can be used to do this. The name of the RFM is RSAQ_REMOTE_QUERY_CALL.

With the combination of SAP Query and this RFM you have the possibility to define queries on an easy and flexible way and to use the results in your UiPath workflow. In my example I use only one table, in this case SFLIGHT. Via transaction code (TAC) SQVI I choose as data source Table and the table SFLIGHT with a few fields.

To try how to call the RFM I use TAC SE37 and set the necessary parameters. The QUERY name, the USERGROUP, which is generated automatically with SQVI, and the VARIANT of the generated report. Important is here to set SKIP_SELSCREEN and DATA_TO_MEMORY.

image

After the execution we see the results in the export parameters and in the tables.

image

The table LISTDESC contains the information about the fields.

The table LDATA contains the result of the query. This format looks a little bit weird.

image

With the RFM Code Generator I generated the following C# code . Only in the section to output the content I have added a few lines manually.

//-RFM Code Generator Begin (C#)----------------------------------------

RfcConfigParameters cfgParams = new RfcConfigParameters();
cfgParams.Add("NAME", "Test");
cfgParams.Add("ASHOST", "YourSAPHost");
cfgParams.Add("CLIENT", "099");
cfgParams.Add("USER", "Stefan");
cfgParams.Add("PASSWD", "secret");
cfgParams.Add("LANGUAGE", "EN");

try {

  RfcDestination destination = RfcDestinationManager.GetDestination(cfgParams);
  IRfcFunction rfcFunction = destination.Repository.CreateFunction("RSAQ_REMOTE_QUERY_CALL");

  //-Tables-------------------------------------------------------------
  IRfcTable tblFPAIRS = rfcFunction.GetTable("FPAIRS");
  IRfcTable tblLDATA = rfcFunction.GetTable("LDATA");
  IRfcTable tblLISTDESC = rfcFunction.GetTable("LISTDESC");
  IRfcTable tblSELECTION_TABLE = rfcFunction.GetTable("SELECTION_TABLE");

  //-Import-------------------------------------------------------------
  rfcFunction.SetValue("DATA_TO_MEMORY", "X");
  rfcFunction.SetValue("DBACC", 0);
  rfcFunction.SetValue("EXTERNAL_PRESENTATION", "");
  rfcFunction.SetValue("QUERY", "ZTEST");
  rfcFunction.SetValue("SKIP_SELSCREEN", "X");
  rfcFunction.SetValue("USERGROUP", "SYSTQV000001");
  rfcFunction.SetValue("VARIANT", "ZTEST");
  rfcFunction.SetValue("WORKSPACE", "");

  //-Invoke-------------------------------------------------------------
  rfcFunction.Invoke(destination);

  //-Export-------------------------------------------------------------
  string strLIST_ID = rfcFunction.GetString("LIST_ID");
  string strLISTTEXT = rfcFunction.GetString("LISTTEXT");
  string strPROGRAM = rfcFunction.GetString("PROGRAM");
  string strUSED_VARIANT = rfcFunction.GetString("USED_VARIANT");

  //-Output of SAP Query------------------------------------------------
  string Output = string.Empty;
  foreach(var Record in tblLDATA) {
    string strLINE = Record.GetString("LINE");
    Output += strLINE;
  }

  string[] Lines = Output.Split(';');
  foreach(string Line in Lines) {
    Console.WriteLine(Line);
  }

} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

//-RFM Code Generator End-----------------------------------------------

This code can now paste into an Invoke Code activity.

There is still some work to do here now, such as splitting each row into the columns and extracting the value. But this is not witchcraft.

Conclusion

As can be seen, this approach can be used to achieve a very high level of individualization in get the data from an SAP backend system and use the data in an UiPath workflow. In the course of a further development I could imagine that only the necessary parameters are to be passed (QUERY, USERGROUP and VARIANT) and that as a result a data table is returned, because the column names and types are well known in the table LISTDESC. However, this is only one step to see if it basically works. And yes, it does. :smiley:

4 Likes

Here a tiny addendum which writes the result from a SAP Query into a data table. All columns are string format, but with the field FTYP you can differentiate that, if you like. As input parameters you can use QUERY, USERGROUP and VARIANT. And it delivers as output the data table with the correct column names.

//-RFM Code Generator Begin (C#)----------------------------------------

RfcConfigParameters cfgParams = new RfcConfigParameters();
cfgParams.Add("NAME", "Test");
cfgParams.Add("ASHOST", "YourSAPHost");
cfgParams.Add("CLIENT", "099");
cfgParams.Add("USER", "Stefan");
cfgParams.Add("PASSWD", "secret");
cfgParams.Add("LANGUAGE", "EN");

try {

  RfcDestination destination = RfcDestinationManager.GetDestination(cfgParams);
  IRfcFunction rfcFunction = destination.Repository.CreateFunction("RSAQ_REMOTE_QUERY_CALL");

  //-Tables-------------------------------------------------------------
  IRfcTable tblFPAIRS = rfcFunction.GetTable("FPAIRS");
  IRfcTable tblLDATA = rfcFunction.GetTable("LDATA");
  IRfcTable tblLISTDESC = rfcFunction.GetTable("LISTDESC");
  IRfcTable tblSELECTION_TABLE = rfcFunction.GetTable("SELECTION_TABLE");

  //-Import-------------------------------------------------------------
  rfcFunction.SetValue("DATA_TO_MEMORY", "X");
  rfcFunction.SetValue("DBACC", 0);
  rfcFunction.SetValue("EXTERNAL_PRESENTATION", "");
  rfcFunction.SetValue("QUERY", "ZTEST");
  rfcFunction.SetValue("SKIP_SELSCREEN", "X");
  rfcFunction.SetValue("USERGROUP", "SYSTQV000001");
  rfcFunction.SetValue("VARIANT", "ZTEST");
  rfcFunction.SetValue("WORKSPACE", "");

  //-Invoke-------------------------------------------------------------
  rfcFunction.Invoke(destination);

  //-Export-------------------------------------------------------------
  string strLIST_ID = rfcFunction.GetString("LIST_ID");
  string strLISTTEXT = rfcFunction.GetString("LISTTEXT");
  string strPROGRAM = rfcFunction.GetString("PROGRAM");
  string strUSED_VARIANT = rfcFunction.GetString("USED_VARIANT");

  //-Read header data and create data table-----------------------------
  DataTable dtDATA = new DataTable();
  dtDATA.Clear();
  foreach(var Record in tblLISTDESC) {
    //string strFTYP = Record.GetString("FTYP");
    string strFNAMEINT = Record.GetString("FNAMEINT");
    dtDATA.Columns.Add(strFNAMEINT);
  }

  //-Output of SAP Query table into one string--------------------------
  string Output = string.Empty;
  foreach(var Record in tblLDATA) {
    string strLINE = Record.GetString("LINE");
    Output += strLINE;
  }
  //-Delete last line with the slash------------------------------------
  Output = Output.Remove(Output.Length - 3);

  //-Copy the data into the data table----------------------------------
  string[] Lines = Output.Split(';');
  foreach(string Line in Lines) {
    string[] Fields = Line.Split(',');
    int i = 0;
    DataRow drDATA = dtDATA.NewRow();
    foreach(string Field in Fields) {
      string[] Values = Field.Split(':');
      drDATA[i] = Values[1];
      i += 1;
    }
    dtDATA.Rows.Add(drDATA);
  }

} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

//-RFM Code Generator End-----------------------------------------------

image

2 Likes

@StefanSchnell You rock ! thanks for getting this out. Really helpful. I will give this a shot. As always thanks for helping the community :).

1 Like

Hi @StefanSchnell - How do I extract the dtDATA data table directly into UiPath? Its giving error BC30616.
image
image

@vikram.v

Hello Vikram,

you can use this approach to handle that.

But it is necessary to comment the line which creates a dtDATA as new data table.

Best regards
Stefan

Here as a tiny addendum a package which contains the this code as activity Execute Query.

Add the package SAPQuery via Manage Packages.

After the successful installation you can find the activity Execute Query in App Integration > SAP.

image

As necessary input parameters QueryName, UserGroup and Variant and of course the connection parameters to the SAP system.

image

Here the same result, but only as activity in a package.

Here the package:

SAPQuery.1.0.2.nupkg (8.4 KB)

1 Like

would be very useful for my process this package!
my problem that i use single sign on and not username and password…
it’s possible to use single sign on?

thank you

Loris

@l.sambinelli

Hello Loris,

yes, it is possible, but I have never tried that. You can find more information in the technical documentation of NCo.

Best regards
Stefan

@l.sambinelli

Hello Loris,

I have updated the package above with the possibility to use, instead of user and password, an SSO ticket. I don’t have the possibility to try that. Please check if it works and let me know your results.

Best regards
Stefan

1 Like

Hello Stefan!
i would like to use this app but i never used an SSO ticket.
In my company i use SS0 without ticket
actually i use this settings
image

so,
i don’t know how to connect with ticket. do you have some guide? i searched online but i can’t find any right guide

1 Like

@l.sambinelli

Hello Loris,

I will extend my library with all these parameters and update it shortly.

Also I will delete the SSO ticket parameter, because as an external application it is quite difficult to get an SSO ticket, only as an RFC server program. Of course, this is not very handy.
Thank you very much to let me know this. But as I wrote above, I never use SSO and I don’t have the possibility to try that.

Best regards
Stefan

Hello Stefan,
if you add the SSO i will happy to test it for you!

1 Like

@l.sambinelli

Hello Loris,
thanks for the reply, the package is updated.

I added now an SNC parameter section, if you now add

SNCMode = “1”
SNCSSO = “1”
SQNQOP = “9”

it should work like in your example. All parameters are string type.

image

Thanks and best regards
Stefan

1 Like

works perfectly!!! great work! !!!

1 Like

Thank you very much for your support Loris. :+1: