Set up the F&SCM hybrid connector with Jet Reports

You can use Jet Reports with F&SCM using the F&SCM hybrid data source connector. With this connector, you can combine the power of F&SCM entity information with the robustness of BYOD. This article explains how you can set up the F&SCM hybrid connector with Jet Reports.

Prerequisites

• Make sure that your F&SCM environment is running.

• Make sure that the ISW data entities are deployed and exported to your BYOD SQL Azure Database. This action needs to be performed by your F&SCM Administrator.

• Get the following information from your F&SCM administrator:
  • F&SCM Dynamics 365 information (this can be found on the F&SCM website):
    • URL
    • Client ID
  • SQL Azure information:
    • Server name (get this from the SQL Azure settings in the Azure Portal)
    • Username
    • User password
    • Database name
  • Microsoft Entra ID credentials:
    • Microsoft Entra ID username
    • Microsoft Entra ID password

To set up your F&SCM hybrid connector

1. Open Excel and select the Jet tab.
2. Add the data source for the F&SCM hybrid connector:

   1. Select Settings > Data Source Settings > Add. The Add Data Source dialog box opens.

      

   2. Enter a Name for the new data source, and select the database type Dynamics 365 F&SCM w/Universal Data Source
   3. Click Add to accept your changes.
   4. By default, the technical names of the tables will be displayed. To change this, open the Display tab and select Enable language captions for this data source.

      

   5. Click OK.
3. Set up the connection to SQL Azure:  
   1. In the Data Source Settings, go to Connection.

      

   2. Click Configure. The Data Link Properties dialog opens.

   3. On the Provider tab, select the Microsoft OLE DB Driver for SQL Server connector from the list.

      

   4. On the Connection tab:

      1. Enter your connection settings:

         • If you are using Active Directory (recommended):

           You can use any of the Active Directory options. In this procedure, one of the options is used as an example. Ask your IT admin if you are unsure about your company's required authentication method.
           1. Enter the SQL Azure or SQL Server url.
           2. Select Active Directory - Password from the drop-down list.

              

           3. Enter your Active Directory User name and Password
           4. Make sure that Blank Password is not selected.
           5. Select Allow Saving PasswordIf Blank password is selected or Allow saving password is not selected, you won't be able to browse the data source.

         • If you are not using Active Directory:

           1. Enter the SQL Azure server name.
           2. Select SQL Server Authentication from the drop-down list.
           3. Enter the SQL Azure credentials in the User name and Password fields.
           4. Make sure that Blank Password is not selected.
           5. Select Allow Saving PasswordIf Blank password is selected or Allow saving password is not selected, you won't be able to browse the data source.

              

           6. Select Select the database and enter the SQL Azure database name.

              

      2. Click Test Connection , and it should report Connection Successful.
      3. Click OK to accept your changes.
4. Set up the connection to Dynamics 365 F&SCM (via OData):
   1. Open Excel and select the Jet tab.
   2. Go to Settings > Data Source Settings > Connection
   3. Enter your Dynamics 365 F&SCM url and your Dynamics 365 F&SCM Client ID
   4. Click sign inYou will be asked to authenticate with Microsoft. Use your Microsoft Entra ID credentials to sign in. After logging in, your username will be displayed on the Connection tab. The F&SCM connection will automatically refresh the user's token after an extended time period of no activity.

      

You can peruse the tables by opening the Browser