Mega Table SubTotals in Brick

tigers12

I am lost in how subtotals are integrated into a mega table in a brick. I started with the ten data source DDX brick, and I was comfortable with configuring the options and the two data queries; however, the subtotal has me stumped. Is the subtotal a third query or somehow handled with the data from the original query? I have attempted it both ways. Currently, subtotal rows do not appear for each Broker_Co. and the button to collapse is visible but does not do anything. Any pointers would be appreciated.

ArborRose

This is a bit out of my area of reach, but I'll give it a go.

Based on what I read and see in your image, it looks like you are working with a brick that involves making an API call to a Domo dataset. Then performing calculations, and possibly aggregating data with subtotals.

To calculate subtotals and a grand total using API calls within a Domo brick, I'd try something like this:

// Define dataset IDs and fields

const datasetId = datasets[config.datasetIndex];

const fields = "Broker_Co., Sales";



// Fetch data from the dataset

const fetchData = async (query) => {

    const response = await fetch(query);

    return await response.json();

};



// Query to get subtotals

const subtotalQuery = `data/v1/${datasetId}?fields=${fields}&groupby=Broker_Co.&aggregate=sum(Sales)`;



// Query to get grand total

const totalQuery = `data/v1/${datasetId}?fields=${fields}&aggregate=sum(Sales)`;



// Fetch the subtotal data

const fetchSubtotalData = async () => {

    const data = await fetchData(subtotalQuery);

    return data;

};



// Fetch the grand total data

const fetchTotalData = async () => {

    const data = await fetchData(totalQuery);

    return data;

};



// Main function to get and process the data

const processData = async () => {

    try {

        const subtotalData = await fetchSubtotalData();

        const totalData = await fetchTotalData();


        // Process subtotal data

        const subtotalRows = subtotalData.map(row => ({

            Broker_Co: row.Broker_Co,

            Total_Sales: row.Sales_sum

        }));


        // Add grand total row

        const grandTotal = totalData[0]?.Sales_sum || 0;

        subtotalRows.push({

            Broker_Co: 'Grand Total',

            Total_Sales: grandTotal

        });


        return subtotalRows;

    } catch (error) {

        console.error('Error fetching data:', error);

        return [];

    }

};



// Execute the data processing

processData().then(result => {

    console.log(result);

    // You would typically return the result here to Domo

});

ArborRose

This is a bit out of my area of reach, but I'll give it a go.

Based on what I read and see in your image, it looks like you are working with a brick that involves making an API call to a Domo dataset. Then performing calculations, and possibly aggregating data with subtotals.

To calculate subtotals and a grand total using API calls within a Domo brick, I'd try something like this:

// Define dataset IDs and fields

const datasetId = datasets[config.datasetIndex];

const fields = "Broker_Co., Sales";



// Fetch data from the dataset

const fetchData = async (query) => {

    const response = await fetch(query);

    return await response.json();

};



// Query to get subtotals

const subtotalQuery = `data/v1/${datasetId}?fields=${fields}&groupby=Broker_Co.&aggregate=sum(Sales)`;



// Query to get grand total

const totalQuery = `data/v1/${datasetId}?fields=${fields}&aggregate=sum(Sales)`;



// Fetch the subtotal data

const fetchSubtotalData = async () => {

    const data = await fetchData(subtotalQuery);

    return data;

};



// Fetch the grand total data

const fetchTotalData = async () => {

    const data = await fetchData(totalQuery);

    return data;

};



// Main function to get and process the data

const processData = async () => {

    try {

        const subtotalData = await fetchSubtotalData();

        const totalData = await fetchTotalData();


        // Process subtotal data

        const subtotalRows = subtotalData.map(row => ({

            Broker_Co: row.Broker_Co,

            Total_Sales: row.Sales_sum

        }));


        // Add grand total row

        const grandTotal = totalData[0]?.Sales_sum || 0;

        subtotalRows.push({

            Broker_Co: 'Grand Total',

            Total_Sales: grandTotal

        });


        return subtotalRows;

    } catch (error) {

        console.error('Error fetching data:', error);

        return [];

    }

};



// Execute the data processing

processData().then(result => {

    console.log(result);

    // You would typically return the result here to Domo

});