I want to create expandable structure like this with pivot table result
I have some what created a pivot table and achieved desired result like these
i m only not able to put expandable structure and grouping total by each month
My query is a wrapper query like this , what more can i try in redash
SELECT MONTHNAME(Date) as Month,
Invoice_Type,
count(Case_ID) AS ‘Count of Case ID’,
round(sum(Unit_Price),2) AS ‘Sum of Unit Price’,
round(sum(Gross_Sale_List)) AS ‘Gross_Sale For List Price’
FROM
( SELECT cast(convert_tz(rlic.dateapproved_c, ‘UTC’, ‘US/Central’) AS date) AS ‘Date’,
concat(u.first_name,’ ‘,u.last_name) AS ‘Analyst Group::multi-filter’,
(pt.name) AS Invoice_Type,
(lc.case_id_c) AS Case_ID,
concat(l.first_name,’ ‘,l.last_name) AS ‘Client’,
(CASE
WHEN rlic.for_entity_c = ‘Individual’ THEN ‘Individual’
ELSE epib.name
END) AS ‘Entity’,
rlic.product_catalog_type_c AS ‘Invoice Type Group::multi-filter’,
(rli.likely_case) AS ‘Unit_Price’,
(rli.list_price) AS ‘list_Price’,
rli.quantity AS ‘Quantity’,
(rli.quantityrli.likely_case) AS ‘Gross Sale’,
(rli.quantityrli.list_price) AS Gross_Sale_List,
(CASE
WHEN rlic.recurring_type_c IS NULL THEN rli.likely_case
ELSE rlic.total_due_c
END) AS ‘Total Billed’,
(rlic.amount_paid_c) AS ‘Total Paid’,
(COALESCE((CASE
WHEN rlic.recurring_type_c IS NULL THEN rli.likely_case
ELSE rlic.total_due_c
END-(rlic.amount_credit_c+rlic.debit_amount_c))-rlic.amount_paid_c, 0 )) AS ‘Balance’,
rli.status AS ‘Invoice Status Group::multi-filter’,
epibc.business_service_status_c AS ‘Fpal Service Status Group::multi-filter’,
CASE
WHEN rlic.lead_from_c LIKE ‘%CTAX%’ THEN concat(coalesce(cc.ctax_status_c,’‘),’ : CTAX’)
WHEN rlic.lead_from_c = ‘Monitoring’ THEN concat(coalesce(cc.tap_status_c,‘’),’ : Monitoring’)
WHEN rlic.lead_from_c = ‘FPal’ THEN concat(coalesce(cc.fpal_status_c,‘’),’ : FPal’)
WHEN rlic.lead_from_c = ‘BAM’ THEN concat(coalesce(cc.fpal_status_c,‘’),’ : BAM’)
WHEN rlic.lead_from_c IS NULL THEN ‘UNRELATED’
WHEN rlic.lead_from_c = ‘’ THEN ‘UNRELATED’
ELSE ‘UNRELATED’
END AS ‘Client Status’,
rlic.years_list_c AS ‘Years’,
rli.description AS ‘Description’,
c.date_entered AS ‘Create Date’,
rlic.sale_type_c AS ‘Sale level’
FROM revenue_line_items rli
INNER JOIN revenue_line_items_cstm rlic ON rli.id=rlic.id_c
AND rli.deleted = ‘0’
AND rlic.lead_from_c = ‘FPal’
AND rlic.dateapproved_c IS NOT NULL
INNER JOIN product_templates pt ON pt.id=rli.product_template_id
INNER JOIN leads l ON rli.opportunity_id = l.opportunity_id
AND l.deleted = ‘0’
INNER JOIN leads_cstm lc ON lc.id_c=l.id
AND l.first_name NOT LIKE ‘%test%’
AND l.last_name NOT LIKE ‘%test%’
LEFT JOIN contacts c ON c.id = l.contact_id
INNER JOIN contacts_cstm cc ON cc.id_c = c.id
LEFT JOIN users u ON u.id = rlic.user_id2_c
AND u.deleted=0
LEFT JOIN epi_business_revenuelineitems_1_c ebr ON ebr.epi_business_revenuelineitems_1revenuelineitems_idb = rli.id
LEFT JOIN epi_business epib ON epib.id = ebr.epi_business_revenuelineitems_1epi_business_ida
LEFT JOIN epi_business_cstm epibc ON epibc.id_c = epib.id
WHERE u.id IN (‘7ec5821c-0405-11ea-bc5a-0a1db36945e2’,
‘52111c30-0406-11ea-9fa7-0a1db36945e2’,
‘4251be34-794c-11e9-be9c-0a1db36945e2’)
AND rlic.product_catalog_type_c IN (‘Accounting -Misc’,
‘Tax Preparation’,
‘Accounting’) – AND rlic.service_parent_id_c IS NOT NULL
AND cast(convert_tz(rlic.dateapproved_c, ‘UTC’, ‘US/Central’) AS date) between
‘{{ Date Range.start }}’ and ‘{{ Date Range.end }}’
) P
GROUP BY MONTHNAME(Date), Invoice_Type
ORDER BY MONTHNAME(Date),
Invoice_Type ;