The attached Excel file contains the below information ...
Columns B and C below use TimeStamps and other code to calculate ...
As an example, for Column B, 'CurFY'
Case
When (extract(month,?p_EndDate?)=10 and [Q_TPRR].[LTMonths] = 0) then [Q_TPRR].[TotalCases]
... etc
TotalCases is a Measure for each month and there are Measures of 'TotalCases' each month for
the three Business Groups of 'AAA', 'BBB', and 'CCC'
When appropriate Columns in the List are Cut, the 'TotalCases' for each Business Group
roll up for Total 'TotalCases" for each Business Group.
We have designed a Table to resemble an Excel sheet, and would like to correctly drag in
Singletons for MANY different Queries and place in appropraite "Excel cells" in the Table.
As an example ...
'CurFY'
Business Group FY 2018 Cases FY 2017 Cases
AAA 10 6
BBB 5 8
CCC 44 43
ALL 59 57
Dragging in a Singleton and assigning it 'CurFY' correctly displays 59
which is the 'TotalCases' Total for 'AAA', 'BBB', and 'CCC'
Is there a way to "dissect" CurFY into the three Business Groups of 'AAA', 'BBB', and 'CCC'?
IF so, we will utilize Singletons for the three Business Groups and apply that same technique
to many other Queries.
TIA, Bob
Looks Like S O L U T I O N ...
New Singletons have to be written for the three Business Groups
of 'AAA', 'BBB', and 'CCC'. Example ...
Case
When (extract(month,?p_EndDate?)=10 and ([Q_TPRR].[LTMonths] = 0) and ([Q_TPRR].[BusinessGroup] = 'AAA')) then [Q_TPRR].[TotalCases]
.... etc.
This will enable us to present an Excel Report (from many different Queries). Will be fun modifying all the Queries
for Singleton values.
Looks a little difficult to maintain because each cell is actually a singleton.
Any chance you can use repeaters for AAA, BBB, CCC?
All of the Singletons are dynamic calculations based on an "Ending Date" selection
on a Prompt Page which also has options of selecting 'AAA', 'BBB', and 'CCC'.
Should continue to work fine w/ no maintenance required ... except when
Mgmt asks for a Change ... "the only thing constant is Change".