Download - Boug 042208 Suppressing Duplicate Values
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
1/13
Techniques to Replace or SupplementBreaks
Deborah L. Lennington
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
2/13
Functions to be shown:
Previous() returns the value from the same field,but from the previous row.
Running Count() returns the number within aseries that a value represents. (Note: on cross tabs this counts across a row then down through the nextrow).
ColumnNumber() returns the number of thephysical column that the formula is entered into.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
3/13
Project
Grant Fin Ops Cntct Name
Project Grant
Start Dt
Fiscal
Year Acctg Pd Journal Date Sum of Dollar AmountF005567 JOHNSON,ELAINE C 08/1/2001 2005 11 05/01/2005 217.41
2005 05/03/2005 197.64
2005 05/11/2005 (19,795.51)
2005 05/13/2005 103.10
2005 05/27/2005 846.59
2005 05/31/2005 17,917.11
Total: AP11 (513.66)
2005 12 06/01/2005 116.00
2005 06/10/2005 855.46
2005 06/15/2005 (19,281.85)
2005 06/24/2005 897.76
2005 06/30/2005 22,852.18
Total: AP12 5,439.55
Total: FY 2005 4,925.89
2006 1 07/01/2005 885.96
2006 07/08/2005 878.87
2006 07/14/2005 (24,721.40)
2006 07/22/2005 850.76
2006 07/29/2005 7,599.52
2006 07/31/2005 5,388.38
Total: AP1 (9,117.91)
The report model:
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
4/13
Possibilities: Use Previous() function
If([Variable]Previous([Variable]);[Variable]) formulaused for PG, FINOPS Contact, and PG Start Datecolumns.
Previous() returns the value from the same field, in theprevious row.
If value on line is different from value from previous line,
value will show.
If value on line is same as value from previous line, valuedoes not show.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
5/13
Possibilities: Alerter Hides Values
Alerter conditions compare either the cells value, or anobjects value to a specified value (either a specificvalue, or another object).
Using Alerters if a condition is met, the specifiedcell and its contents are formatted as defined. So -define the condition, and format the cell to white
font/white fill if met. Tip: Evaluating the comparison in terms of a user-
created variable, allows greater flexibility.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
6/13
Screens for Establishing Alerters
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
7/13
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
8/13
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
9/13
Creating my new variable:
ROW COUNT: FY and AP =RunningCount([Sumof Dollar Amount];IncludeEmpty;([Rev/ExpJnl's].[Fiscal Year];[Rev/Exp Jnl's].[AccountingPeriod]) which will be used for Fiscal Year column.
Formula counts the lines in the section, based on the[Sum of Dollar Amount] measure.
Includes empty rows in count (though, not absolutelynecessary to stipulate here).
Resets the RunningCount whenever one the other orboth of the two break variables ([FiscalYear]/[Accounting Period]) change.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
10/13
Possibilities: Column Counting in
Horizontal Tables =If(ColumnNumber()=2;[MERGED: Journal ID])
ColumnNumber() counts from the first column on.
If the column number is the one desired, then showsthe value if not, it doesnt.
Tip: In a horizontal table with one column of tableheadings information otherwise repeating would beseen in second table column only.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
11/13
Journal Information on: BE00032539Dated: 8-Apr-05
Journal Source:
BE
Journal Reference:Journal Description:
TO RECHARGE LONG
DISTANCE PHON
Journal Line Number: 1 2 3Journal Line Description: 11/04-2/05 PHONE RECHARGE 1/05-3/05 PHONE RECHARGE 2/05 PHONE RECHARGEJournal Line Reference: LUDWIG SAPER RAMDepartment:Project Grant:Fund: 10000 - General 10000 - General 10000 - GeneralProgram: RESIN - Research Incentives RESIN - Research Incentives RESIN - Research IncentivesClass: 21000 - Departmental Research 21000 - Departmental Research
21000 - Departmental
Research
Account:611600 - Long Distance Calls
ITCom611600 - Long Distance Calls
ITCom611600 - Long Distance Calls
ITCom
Amount:
2.40
3.76
0.44
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
12/13
Been there done thatbad idea Question IfColumnNumber()works so easily, why
couldnt RowIndex() (after all, it does look fairlysimilar) be used instead of going through the wholecreated variable hassle?
Answer RowIndex () provides the unique row
number of thedata return line that a value wasoriginally associated with; not the row that the datavalue appears on within any given table.
-
8/2/2019 Boug 042208 Suppressing Duplicate Values
13/13