Dynamic column and metric selection in pivot table

[BharatNischal]

Is your feature request related to a problem? Please describe.
We recently switched to superset for the visualisation needs at my organisation. We need to create a pivot table for visualising a number of metrics for example city vs (completed orders, delivered orders, pending orders etc) just to name a few. So for this we used superset's Pivot Table 2. We are operating in multiple businesses and after plotting all the required metrics of each business we end up in 100+ pivot table charts.

Now when I added all these charts in a dashboard it become very difficult to search for a specific chart. Also now if I need to share this dashboard with a person who is only concerned about 3-4 charts the dashboard will still make an api call to all 100+ charts resulting in slow loading.

It is also worth mentioning that most of the charts were having same row and metrics but different. columns.

Describe the solution you'd like
A good solution will be to provide a dynamic selection of columns and metrics to reduce this number of charts and thus reducing loading time on dashboard resulting in better user experience.

For my use case I had developed a similar chart on top of the Pivot Table v2 which is capable of this dynamic selection. Now I want this to be available to the superset Pivot Table v2 so that others can make use of this as well. Here is a demo of the proposed chart.

Screen.Recording.2022-03-27.at.11.45.09.AM.mp4

Note that in the demo video I haven't made metric dropdown multiselect but for generic case we should make it multiple select and the control option labels should also be renamed to more logical labels

[Xtreemrus]

It's great! Can you please describe how install this?

[ihsan0297]

Did you find any solution ?

[polyannasi]

Did you find any solution ?

Have you found anything?

[cnakamu]

Hi, @BharatNischal, i'm also having problems with pivots on my organisations, and your solution suits perfectly. How can access and install this?

[rusackas]

Hi there, and thanks for starting this conversation. I have a handful of questions:

• This doesn't sound like a bug, but rather a feature request... do you mind if I migrate this to a Github Discussion?
• It seems like you've already implemented a solution that works for you, and potentially others. Would you be able/willing to contribute your changes to the V2 Pivot table if others are interested? Then, of course, everyone wins :)
• Do the changes made in the chart (group by changes, etc) persist with the FormData when saved from Explore?
• Do the changes persist in the dashboard context in some way, or would you even want them to?

Also, this has a little bit of overlap with the Drill By functionality that's currently on the roadmap. @lauderbaugh and @kasiazjc may be interested in taking a look.

[ericcwiemer]

@BharatNischal Hello there! Thank you so much for making this, it's exactly what I need! Would you mind sharing how you achieved this, or does anyone know if there is now a way to do this natively in Superset? Any help or guidance would be greatly appreciated!

[michael-s-molina]

Converting to a GitHub discussion.

[Gal-Gadot]

Hi, @BharatNischal, i'm also having problems with pivots on my organisations, and your solution suits perfectly. How can access and install this?

[ihsan0297]

@Gal-Gadot Had you find that ??

[ihsan0297]

@BharatNischal How can I use in Apache Superset, I am working on some dashboards and I need this chart in utmost top priority.. Please share :)

[dosubot[bot]]

Hi @ihsan0297, I'm here to help you with any bugs, questions, or contributions you may have. Let's tackle this together!

To use the chart in Apache Superset for your dashboards, you need to create a Slice object with the appropriate parameters and add it to your dashboard configuration. Here is an example of how you can create a Slice for a "Line Chart" and add it to your dashboard:

1. Create the Slice:

   def create_slices(tbl: SqlaTable) -> list[Slice]:
       slice_kwargs = {
           "datasource_id": tbl.id,
           "datasource_type": DatasourceType.TABLE,
           "owners": [],
       }
   
       defaults = {
           "limit": "25",
           "time_range": "100 years ago : now",
           "granularity_sqla": "ds",
           "row_limit": "50000",
           "viz_type": "echarts_timeseries_line",
       }
   
       slices = [
           Slice(
               **slice_kwargs,
               slice_name="Line Chart",
               viz_type="echarts_timeseries_line",
               params=get_slice_json(
                   defaults,
                   viz_type="echarts_timeseries_line",
                   metrics=["sum__num"],
                   groupby=["gender"],
               ),
           ),
           # Add other slices as needed
       ]
   
       return slices

2. Add the Slice to the Dashboard:

   {
     "CHART-23": {
       "children": [],
       "parents": [
         "ROOT_ID",
         "TABS-TOP",
         "TAB-TOP-2",
         "ROW-8"
       ],
       "id": "CHART-23",
       "meta": {
         "chartId": 23,
         "height": 50,
         "sliceName": "Line Chart",
         "width": 4
       },
       "type": "CHART"
     }
   }

In this example, the Line Chart slice is created with the necessary parameters and added to the dashboard configuration under CHART-23. You can adjust the slice_name, viz_type, and other parameters as needed for your specific chart ^[1].

To continue talking to Dosu, mention @dosu.

[zanglili]

@ihsan0297 Had you find that ?

[zanglili]

Which superset version has the solution in the video? I want to use the functions to dynamically select columns and metrics . @dosu

[dosubot[bot]]

The dynamic column and metric selection feature, as demonstrated in the video by BharatNischal, is not natively available in the official Superset codebase. However, there are existing plugins or extensions that provide similar functionality through various drag-and-drop controls, such as dndGroupByControl, dndColumnsControl, and dndAdhocMetricsControl ^[1].

Regarding recent developments, there has been a commit related to dynamic column and metric selection titled "explore: Allow only saved metrics and columns" (#27539) ^[2]. This might indicate ongoing work in this area, but it doesn't confirm the exact implementation of the feature shown in the video.

If you're looking for a specific version that includes BharatNischal's solution, it seems that it hasn't been integrated into the official Superset releases yet. You might need to implement a custom solution or explore community contributions for similar functionality.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[rusackas]

Lots of enthusiasm on this thread, I love to see it! @BharatNischal did you ever get this plugin in a shippable state? If it's hosted somewhere on NPM, I'd love to add it to the Third Party Plugins list and give it some love!