Hi,
Is there any way we can expose certain tables of our database to the user so the user can choose fields and tables based on his/her current requirement?
The database is following domain driven design pattern so the foreign key relationship is not there at the DB level.
For example, let's say we have the following tables -
customer,
order
location
Product
Customer contains locationId
Order contains CustomerId and ProductId
In the actual scenario, the database contains around 100 or more tables
Now I would like to some way make all the tables and fields of the table available to the end user and based on his/her current requirement, the user would be able to choose fields from the tables and can also be able to establish a relationship via the user interface.
I have been able to generate reports using a custom .NET API that is available in your github but for that I had to write a query and join all the tables so that all the fields of tables become available to the user.
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,
Thanks for replying and suggesting how I can achieve my requirement. Can you please guide me -
on the following?
.Net 6 Custom API Back End -
Instead of specifying the data sources in appsettings file of .Net custom api, how can I store the configuration in a separate table in DB? In that case, the user would be able to set certain data sources using another UIs, and then at runtime, the user can select a specific data source from the list of data sources that he/she saved earlier.
Vue 3 Front End
can we bind type, url, index so that we can dynamically pass the user selected options at runtime.
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