Doorgaan naar hoofdcontent


Posts uit juni, 2014 weergeven

Crystal Reports: Group Chart Positioned Side By Side

Got a question from somebody. Charts showing products and revenues in a report grouped by category, can the different charts be shown two by two instead of one below the other?

I tried to find a solution on the internet but could not find an answer to this question. I finally succeeded to create this:

The example is based on the Northwind database, using the tables, Categories, Products, Orders and Oder_Details.

To get the charts side by side instead of one below the other, I used one sub report. Main report and sub report are based on the same data and contain the same charts.

I passed a shared variable from the main to the subreport:

shared numbervar group;
if groupnumber mod 2 = 1 then

In the main report I used a formula to suppress the groups alternately:

groupnumber mod 2 = 0

In the sub reports I used the formula  to suppress the groups alternately:

groupnumber <> {@group}+1 OR {@group}=0

I linked the sub report like this:

You can do…

Excel: Circumplex Chart, Model Of Relations (Schwartz)

I could not make it as nice as the original but it looks OK to me.

Circumplex - model of relations among different value groups (following Schwartz 1992)

And the original from

Here are the data:
InnerOuterBenevolence1Intrinsic Values3Tradition1Conservation2Security1Extrinsic Values2Power1Openness to Change2Achievement1Hedonism1Simulation1Self-Direction1Universalism1
You can download the file ExcelCircumplex.xlsx through

Crystal Reports: On SQL Expression Fields

This is based on an article from the SAP site:

I tried to add some more examples to it.
SQL Expression Field definitionSQL Expression fields are similar to formula fields, but they are written in Structured Query Language (SQL). They are useful in optimizing report performance because the tasks they execute are performed on the database server.
You can use SQL Expression fields to perform pre-defined functions on database fields. The list of available functions depends on the type of database in use. This list is available in the Function Tree of the SQL Expression Editor dialog box.
NOTE: If Crystal Reports recognizes the function name it will turn blue in the SQL Expression Editor dialog box. Additional database functions may be available to you, depending on your database, but they will not turn blue.
SQL Expre…

Excel: Milestones Chart

Downloaden a milestones template from the Microsoft Office website:

To my opinion it needed some improvement. This is what I created:

Compared to their version:

I used the same data as they did for their example, with the last column changed and one added:

DATEMILESTONEPOSITIONBASELINE BELOWBASELINE ABOVE23-janProject Start250#N/B14-febMilestone 1100#N/B24-febMilestone 2-10#N/B01-mrtMilestone 3150#N/B15-mrtMilestone 4-15#N/B015-meiMilestone 5150#N/B15-junMilestone 6-15#N/B030-junMilestone 7150#N/B15-julMilestone 8-20#N/B030-julMilestone 9200#N/B23-oktMilestone 10-15#N/B031-decProject End150#N/B
Instead of their error bars I simply used columns. All the rest is not too hard to find out.

You can download the file ExcelMilesonesChart.xlsx through

Excel: Bar Chart With Wingdings and Symbols

On the occasion of a request from +Shane Devenshire I created this Excel chart:

My data:

PatientsTime Since TreatmentResponse● Time to Response→ Ongoing responseY10172311●9197134●83825154●748036●65212274●55910204●4602337●3895254●29016694●1982524●
The arrow is created using a label based op the column Ongoing Response, using the font Wingdings. The black circle is created using a label based on the Y column

What the orginal version looked like:

You can download the file ExcelShane.xlsx through

Friesland: geslacht Bangma, oude grafzerken, geboortelepels en zilveren ijssouvenirs

De Bangma'sAfgelopen week is bij een kijkdag voor geboortelepels van het Fries Museum opnieuw een geboortelepel van de Bangma's opgedoken. In mijn database had ik al behoorlijk wat gegevens over deze familie. Oorspronkelijk lijken deze Bangma's te komen uit het verdwenen dorpje Engwier bij Makkum. Al lijkt het me ook goed mogelijk dat de achternaam komt van de Bangama State die volgens Waling Dijkstra bij Oosthem lag.

Van daar uit verspreidt de familie zich over een groot deel van Friesland. In de kerken van Tjerkwerd, Wolsum en Oosthem vinden we oude grafzerken die herinneren aan de voorzaten van deze familie. Verder zijn ons ook de teksten van diverse geboortelepels en zilveren ijssouvenirs bekend.

Het wapen op de lepel van Tetje Oenes Bangma (1585-1646).

Nagenoeg hetzelfde wapen treffen we aan op een aantal latere geboortelepels, maar dan met klavers en eikels andersom. De lepel van Swopkje Eelkes Bangma (1766-1846) met wapen:

In het onderstaande schema heb ik de famili…



DE VERSCHILLENDE VARIANTEN VAN DE ELFSTEDENTOCHT De elfstedentocht is of wordt behalve op de schaats op nog ten minste elf andere manieren uitgevoerd. Ik beperk me hier tot die manieren waarbij een flinke fysieke inspanning vereist is. Een totaal overzicht:
Wijze Actief Onderdeel brevet Fietsen Ja (elk jaar) Ja Hardlopen (estafette en individueel) Ja (weer vanaf 2015) Nee Kano├źn Ja (elke drie jaar en nonstop elk jaar) Nee Mountainbiken Ja (elk jaar) Nee Roeien (estafette en individueel) Ja (elk jaar) Nee Schaatsen Ja (onbekend) Ja Skateboarden Nee Nee Skeeleren Ja (elke twee jaar) Nee Stand up paddling Ja (elk jaar) Nee Steppen Ja (elk jaar) Nee Surfen Nee (1983-1989) Nee Wandelen

Excel: Keeping Track Of Changes in Your Data Using VBA

Today I created an Excel workbook that keeps track of changes in your data, on a hidden worksheet called history.

This is the VBA script I used, the code must be placed in ThisWorkbook,

Option Explicit
Public oldValue As Variant

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim intRows As Integer
    Dim rngCell As Range
    intRows = Sheets("history").UsedRange.Rows.Count + 1

    'MsgBox Target.Count
    'to prevent changes on the history sheet are recorder as well
    If Sh.Name <> "history" Then
        intRows = Sheets("history").UsedRange.Rows.Count + 1

        If Target.Count = 1 Then
            Sheets("history").Cells(intRows, 1).Offset(0, 0).Value = Target.Address
            Sheets("history").Cells(intRows, 1).Offset(0, 1).Value = oldValue
            Sheets("history").Cells(intRows, 1).Offset(0, 2).Value = Target.Value
            Sheets("history").Cells(intRows, 1).Offset…