[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!

[mahalakshme]

@ombhardwajj the above is fine, just hide the observations column, I think when compared to creating model in metabase, you can just create a view in the server codebase to use for this if needed.

cc: @petmongrels based on yesterdays' discussion thought we can start back the work.

[ombhardwajj]

Commit added @mahalakshme !

[mahalakshme]

Yayy!!

[petmongrels]

1. org.avni.server.dao.metabase.DatabaseRepository#getExistingCollectionItems, org.avni.server.dao.metabase.DatabaseRepository#getCollectionByName, should be in Collection Repository
2. We need a separate question repository. org.avni.server.dao.metabase.DatabaseRepository#createQuestionForASingleTable, org.avni.server.dao.metabase.DatabaseRepository#createAdvancedQuestion, org.avni.server.dao.metabase.DatabaseRepository#postQuestion
3. lets make the method that are access only from within the class private
4. why createAdvancedQuestion2 and createAdvancedQuestion two methods
5. I am not sure whether all of this was added in the same story but we should fix all this.
6. Since we are not creating dashboard right now, lets remove the calls for them. We can get back to it in an appropriate story down the line. Right now it would create confusion.

[mahalakshme]

@petmongrels thought if creating dashboard and filters for it are not too complex, we can do them - to assess that - added creating one dashboard and filter to this card: #837 not the current(#835) card.

My intention was to try to get things work end-end instead of going deep on one thing. Let me know if you think otherwise.

[petmongrels]

ok. will review the code when done

[ombhardwajj]

Code refactoring done , please review @petmongrels !

[petmongrels]

Testing related

• Enabling canned report for an existing organisation which has ETL enabled. This is more important as for new organisation there may not be any data to test. Better to be done with any org which is well configured and has some data.
• Lets add this to our regression suite, to be applicable from release 12