[Canned Reports] Create visualisation on question

[mahalakshme]

AC:

• Users should be able to see count of individuals registered for each subject type in the form of pie-chart Do not include the voided individuals and voided subject types.
• Piechart colors should be of lighter shades mentioned here - lighter shades
• Create via GUI options using API, so that we can make use of the drill-through functionality.
• On drilling on the pie-chart, w.r to observation, concept names should be shown in separate columns, and values which are coded, should also have concept answer names. Instead of address id, each location type should show up in separate columns so that it can be filtered later.
• If needed, apply the above question on a modal, to get the expected results

Technical suggestions:

Metabase api link

Approximate payload that might work - Not tested:

POST /api/card
Request Body Example:
json
Copy code
{
  "name": "Count by Category",
  "dataset_query": {
    "type": "query",
    "query": {
      "source-table": "your_table_id",
      "aggregation": [["count"]],
      "breakout": [["field-id", 123]]  // Replace 123 with the field ID of your category column
    }
  },
  "display": "table"  // Can be "bar", "pie", etc., depending on the visualization
}

Thoughts:

• Just showing numbers like in Cini seems to be more work for user to be able to comprehend - piechart seems to be more compact.
• Observations, with uuids replaced with their names , and uuids of concept answers replaced with their names - should be done as part of ETL itself or model can be created(seems to take more time), or exclude observations from union tables(because useful only in Data)
• Are registrations across different subject types even comparables?
• user trigger?
• refresh - new metadata - recreation
• within Avni -
• drill-down -> model without observations
• complexity

Old: Ignore

---

POST /api/card
Authorization: Bearer <your_api_token>

{
  "name": "Sales Data with Date Filters",
  "dataset_query": {
    "type": "native",
    "native": {
      "query": "SELECT date, SUM(sales) AS total_sales FROM sales_table WHERE date BETWEEN {{start_date}} AND {{end_date}} GROUP BY date ORDER BY date",
      "template-tags": {
        "start_date": {
          "id": "c53df913",
          "name": "start_date",
          "display_name": "Start Date",
          "type": "date",
          "default": "2023-01-01"  // Optional default value
        },
        "end_date": {
          "id": "c82df913",
          "name": "end_date",
          "display_name": "End Date",
          "type": "date",
          "default": "2023-12-31"  // Optional default value
        }
      }
    },
    "database": 1  // Replace with your database ID
  },
  "display": "bar",  // Visualization type
  "visualization_settings": {
    "x_axis.title": "Date",
    "y_axis.title": "Total Sales",
    "graph.line_style": "solid"
  },
  "collection_id": null  // Add to a collection if needed
}

Inputs-Ignore-for analysis purpose and later reference:

• paste the chart here
• line chart over bar chart - intelligently - every line will be subject type
• metabase, dataogram - ODK and metabase -> absolute, and then trend, display data below that

[ombhardwajj]

Please assign it to me @mahalakshme !

[ombhardwajj]

Hey @mahalakshme

1. Since we would need to specify color for each subject type individually
   (see the expected payload here https://gist.github.com/ombhardwajj/bec45e2fb53be8910847455b62356bc9) I don’t think it would be a good idea to make those of lighter colors to match our color scheme unless metabase colors look too bad which isn’t the case. Anyways lmk if we need to proceed with this

2. Couldn't find anything related to GUI options. I’ve used the APIs and we can still use the drill through functionality.

3. I don’t think we can get concept names with drill through . It only shows the option to see those individuals
   (see screenshot).

p.s. code is almost ready for this!