reporting tips
DESCRIPTION
TRANSCRIPT
Web Intelligence&
Crystal Reports----------------
Processing and Context
Dan DonarskiKolb+Co. Technology Advisers
Crystal Reporting Model
BO
Suppose we want to create the following report
Need to generate a running total for monthly sales that:◦ Are greater than 25% of regions total monthly sales
Need to also display :◦ Pct Sum of all customer sales > 25% of groups totals
Crystal Reporting Model
BO
Report Layout –- Grouped by Region / Date / Customer- Summary of customer sales
- By region
- Formula to display value- if sales > 25% of monthly total
- Running total of @Analysis
Crystal Reporting Model
BO
…but my RT Expert won’t show fields I need- @Analysis
Crystal Reporting Model
BOBecause a “PrintTime” formula is “processed”
after Running Totals….
Crystal Reporting Model
//@Analysis (GF3 – Customer)//Set formula to be evaluated at PrintState passWhilePrintingRecords ;//Check to see if criteria met and if so perform calcif {@Pct of Sales} > 25 then Sum({Orders.Order Amount}, {Customer.Customer})Name})
)Create a formula as “PrintState”
//@Pct of Sales (GF3 – Customer)//Set formula to be evaluated at PrintState passWhilePrintingRecords ;//Perform calculationSum({Orders.Order Amount}, {Customer.Customer Name}) % Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly")
Crystal Reporting Model
BO
//@Reset (Hidden in GH2 – Monthly)//Set formula to be evaluated at PrintState passWhilePrintingRecords ;//Reset the variable we'll use to hold our subtotalGlobal CurrencyVar vTotal := 0 ;
//@Accumulate (Hidden in GF3 – Customer)//Set formula to be evaluated at PrintState passWhilePrintingRecords ;//Declare that we want to make reference to a variableGlobal CurrencyVar vTotal ;//Add to the existing value of the variablevTotal := vTotal + {@Analysis}
//@Display (GF2 – Monthly)//Set formula to be evaluated at PrintState passWhilePrintingRecords ;//Display the current valueGlobal CurrencyVar vTotal ;
Crystal Reporting Model
BO
//@MonthPct (GF2 – Monthly)//Declare that we want to make reference to a variableWhilePrintingRecords ;//Perform calculationif {@Display} = 0 then 0 else
{@Display} % Sum ({Orders.Order Amount}, {Orders.Order Date}, "monthly")
…Final calculation now able to be performed
Web Intelligence Context
BO
Aggregation◦ Calculation of measures
Three componentsCalculation Function
Ex: Sum, Avg, Min, MaxInput Context
What goes into calculationEx: Dimensions in block
Output ContextWhere the aggregation is outputEx: Block Footer, Section, Body
Web Intelligence Context
BO
Default Aggregation
Calculation(Input Considerations)
(Output location)
Web Intelligence Context
BO=Min([Sales revenue] ForAll ([Quarter] ; [Year])) In ([Year])
Input Output
Web Intelligence Context
=Min([Sales revenue] ForEach ([Quarter] ; [Year])) In ([Year])
BO
Input Output
Web Intelligence Context
=Min([Sales revenue]) In ([Year])
=Min([Sales revenue]) In ([Quarter])
=Sum([Sales revenue]) In ([Year]) Where ([Year] = "2001")=Sum([Sales revenue]) In ([Year]) Where ([Year] = "2002")=Sum([Sales revenue]) In ([Year]) Where ([Year] = "2003")
=Sum([Sales revenue]) In ([Year]) Where ([Year] InList("2001" ; "2002" ; "2003") )
Output
Web Intelligence Context
• Max Sales Revenue By Year=Max([Sales revenue] In ([State] ; [Year]))
• Year of Max Sales Revenue=Max(If([Sales revenue] = Max([Sales revenue]
ForEach ([Year])) In ([State]) ; ([Year]) ; 0) In ([State] ; [Year]))
