Cross Tab Report of Quarterly Percentages
I need to calculate the % of Items closed (= # items closed / total # of items) per quarter and create a cross tab report.
The first idea that came to my mind was to use the setup of a cross tab report. Something like the following:
Quarterly Closed Items Report
% of Closed Items per Quarter------40%----55%----80%----100%-----68.8%
% Closed Items per Quarter = 100*(total number of items closed in a quarter) / (total number of items in a quarter)
But the typical cross tab setup using filtered portals based on a self join relationship doesn't work because the use of summary fields in the Item table doesn't discriminate between different quarters. In a word, it gets the cumulative % of items closed per year, and not the % items closed per quarter for each year.
My current setup is the following:
Item::__kp_Item X AllItem::__kp_Item
The item status (Closed or Open) of each Item record is entered in the Item::ItemStatus field.
The "number of items" is calculated using the following field: Item::ItemCount (Summary; Count of __kp_Item)
The "number of items closed" is calculated using the following fields:
Item::ItemClosed (Calculation; Case (ItemStatus = "Closed"; 1; 0)
Item::CountItemsClosed (Summary; Total of ItemClosed)
Q_%Item_Closed layout based on the Item table
This layout has the following two sub summary parts:
The Sub-summary by DateItemYear (leading) Part
This part contains:
the field z_DateItemYear (calculation; Year (ItemDate); caclulation result is number)
the text headings Q1, Q2, Q3, Q4, <<z_DateItemYear>>Total.
The Sub-summary by DateItemYear (trailing) Part
This part contains:
Every portal refers to the AllItem TO.
Each portal is filtered to the adequate quarter. So the filter in the portal for the Q1 is:
AllItem::z_DateItemYear = Item::z_DateItemYear and
AllItem::z_Quarter_number = 1
Each portal has just one field referenced to the AllItem TO.
Obviously any field from the Item table that calculates the % of items closed gives the same value which is the cumulative % of closed items.
I am sure that getting percentages per quarter and putting on a cross tab report is a mundane task in FM and this has been addressed many times before. But since I haven't been able to track the right post I am asking for help about how to set it up.