How to expose tables of microsoft sql database tables to end users

Hello, Anirban!

Thank you for reaching out to us.

Please note that Flexmonster is a pivot table component that visualizes the data specified in the report. Flexmonster itself does not provide ready-to-use functionality for preselecting the fields from different tables before showing the data. However, these features can be implemented as a separate UI control, where all the tables and their column names would be preloaded independently from Flexmonster. In this panel, users can select the tables and their specific columns, which would later be displayed in Flexmonster.

After retrieving these inputs, the data needs to be retrieved from the database and passed to a client. We suggest implementing your own custom data source API server using our communication protocol to retrieve aggregated data from a server to Flexmonster Pivot. The server would need to be capable of retrieving data from the database based on user input, aggregating it, and returning it to the client. Since users may select multiple data combinations, we recommend implementing a dynamic query builder to transform the inputted data into an SQL query. The information from a client telling which data needs to be loaded could be passed via the "index" parameter, set inside Flexmonster's data source object.
You are welcome to look through more information about custom API protocol: https://www.flexmonster.com/doc/introduction-to-custom-data-source-api/

We are looking forward to hearing your feedback.

Best Regards,
Maksym

Hi, Anirban!

Thank you for your reply.
Our team is currently working on your case, and we will respond with more information as soon as possible.

Best Regards,
Maksym

Hello, Anirban!

Thank you for your reply. You are welcome to see our answers regarding both backend and frontend below:

Backend

1) About custom data source API:
Since the custom data source API is a protocol (a set of POST requests and responses), you have complete flexibility over how the server can be structured. There are many ways to implement the protocol depending on the use case (for instance, storing the index settings in a database or a configuration file). The only requirement is that your server responds to Flexmonster's requests in the correct format.

2) Storing the configuration in a database instead of using the configuration file:
One way to store the configuration in a database instead of a configuration file is to use a structure similar to the Data Server's settings for storing each specific data source setting in your database. For instance, you could create a table with the unique index name as an ID column and then save the other connection parameters in separate columns. In addition, you could add a user ID to the table to show only the indexes created by each specific user. Later, from the client side, you could use the requestHeaders property to pass the user's ID to the server: https://www.flexmonster.com/api/data-source-object/#requestheaders.

While the user connects to a specific index, you can retrieve the corresponding record with the connection parameters from your table. With this info (connection string, query string, etc.), it would be possible to get the needed dataset from the database, process it on the server, and return the response to Flexmonster's requests.

3) An alternative solution to custom data source API:
Also, we would like to recommend an alternative approach for your use case that wouldn't require implementing the custom data source API. The following approach is suitable for connecting Flexmonster to smaller datasets since all calculations will be done on the client side. You can implement a single server endpoint that would load the data from a database and return JSON/CSV in response. Please see the following guide for more details: https://www.flexmonster.com/doc/connecting-to-other-data-sources/#server-side-script

This solution is simpler than the custom API implementation. However, with large datasets (100MB or more), Flexmonster's performance on a client will be slow since the browser possesses limited capacities for calculating the data.

Frontend
If we understand correctly, you are looking for a way so that your users can select the desired dataset to connect Flexmonster to during runtime. If so, you could use the connectTo() or setReport() API call.
Here is a code snippet:

// Connect Flexmonster to another index (for example,"sample-index-2"):
pivot.connectTo({
  type: 'api',
  url: 'https://olap.flexmonster.com:9500',
  index: 'sample-index-2'
});

In addition, could you please tell us which view type (pivot or flat) you will use?

We are looking forward to hearing from you.

Best Regards,
Maksym

Hello, Anirban!

Hope you are doing well.
We are wondering if you had time to read our previous message.
Our team would be happy to hear your feedback.

Best Regards,
Maksym

Hello, Anirban!

Our team is wondering if you had time to look through the proposed solutions.
Please let us know which of the approaches for exposing SQL tables is better for you.
Also, you are welcome to write us in case any other questions arise.

Best Regards,
Maksym