reporting tips

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]))

Dan Donarskiddonarski@kolbco.com

Web Intelligence & Crystal Reports