SELECT DISTINCT ds_generationdate AS As_on_DATE, Fund_Code Trust_Account_NO, Fund_Name Trust_Account_Name,
Security_symbol SECURITY, Issuer_sector Sector, Qty NO_of_shares, WACQTY Acquisition_Cost,
mtm_price Market_Value_Share, mtm_value Market_Value, mtm_value - bookvalue Gain_Loss --ASSET_CLASS_NAME
FROM RPT_CUST_PORT_DAILY_POS rcpdp
WHERE ASSET_CLASS_NAME = 'EQUITY'
[[AND To_Char(rcpdp.Ds_generationdate,'MM-dd-yyyy') = To_Char({{AS_ON_DATE}},'MM-dd-yyyy')]]
[[AND rcpdp.Fund_Code = {{TRUST_ACC_NO}}]]
GROUP BY Issuer_sector, DS_GENERATIONDATE, FUND_CODE, FUND_NAME, SECURITY_SYMBOL,
QTY, WAC QTY, MTM_PRICE, MTM_VALUE, MTM_VALUE - BOOKVALUE --ASSET_CLASS_NAME
ORDER BY ISSUER_SECTOR
ABOVE IS THE QUERY FOR THE PIVOT TABLE. PEASE HELP.
flamber
September 8, 2021, 11:08am
#2
Hi @RohitRaina
Post "Diagnostic Info" from Admin > Troubleshooting.
How many rows would your query return (without any filters)?
Pivot Table makes a lot more queries to calculate the subtotals, so it is possible that the database is not indexed fully, so those queries will then be very slow.
Is there a reason why you cannot create the query directly via the GUI instead of through SQL?
There are 131,450 Rows in total.
We create query through SQL because its our client requirement.
flamber
September 8, 2021, 11:42am
#4
@RohitRaina Quite sure you are hitting this issue:
https://github.com/metabase/metabase/issues/13572 - upvote by clicking on the first post
You will have to make the question in GUI or create a database view.