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
});