Hessel de Walle's Blog

The report I created here contains no data from an external source. The numbers we see, all come from calculations based on the figures given via the input parameters. The expressions look like this: Max Deviation: =Code.BerekeningNorm(Parameters!parNormAfwijking.Value,Parameters!parProfielLengteNorm.Value) (based on the upper two paramaters) Requirement : =Code.Berekening(Parameters!parMaxAfwijkingNorm.Value,  Parameters!parProfielLengteNorm.Value)  (based on lower parameter and the right upper one) The function code behind the report: Function Berekening(MaxAfwijkingNorm as Integer,ProfielLengteNorm as Integer) as Double Berekening = MaxAfwijkingNorm ^2 Berekening = Berekening + (ProfielLengteNorm / 2)^2 Berekening = Berekening / (2 * MaxAfwijkingNorm) Berekening = Berekening - ((Berekening^2) -  250000)^(1/2) End Function Function BerekeningNorm(NormAfwijking as Double,ProfielLengteNorm as Integer) as Double BerekeningNorm=NormAfwijk...

Thee Excel pivot table may be the best tool in the market for creating ad hoc report. We can very rapidly compare data from various angles. As soon as we are dealing with standard reports, reports with a longer time span and standardized results, I personally think the pivot table is less suitable as a reporting instrument. In that case I prefer Excel functions like: SUMIFS AVERAGESIFS COUNTIFS To build reports like this is definitely more complex and time consuming. In return you get longer lasting reports. In the following example I created three examples to compare SUMIFS ,  AVERAGEIFS  and COUNTIFS  to the similar pivot tables. The examples are based on a dataset. Examples and dataset you can download. In the dowload it is easy to see how I used the different functions. Even in these relatively simple examples you can see an important diffenence. If you select a specific country the left examples still show all rows and columns. Some rows and...