woensdag 25 juni 2014

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
    group:=groupnumber
ELSE
    group:=0;
group;

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 download the file ChartSideBySide.rpt through

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

maandag 23 juni 2014

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 http://www.rwlnetwork.org/news/balance-%E2%80%93-an-example-for-using-frames-in-real-world-learning.aspx


Here are the data:

Inner Outer
Benevolence 1 Intrinsic Values 3
Tradition 1 Conservation 2
Security 1 Extrinsic Values 2
Power 1 Openness to Change 2
Achievement 1
Hedonism 1
Simulation 1
Self-Direction 1
Universalism 1

You can download the file ExcelCircumplex.xlsx through

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

donderdag 19 juni 2014

Crystal Reports: On SQL Expression Fields

This is based on an article from the SAP site:

http://search.sap.com/notes?id=0001217871&boj=/sap/bc/bsp/spn/scn_bosap/notes.do?access=69765F6D6F64653D3939382669765F7361706E6F7465735F6E756D6265723D30303031323137383731

I tried to add some more examples to it.

SQL Expression Field definition

SQL 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 Expression field versus a Crystal Reports formula field

Often an SQL Expression field and a Crystal Reports formula field can accomplish the same goal. For example, a report design requirement is to display the first 3 letters of the first name of an employee.

A formula field or an SQL Expression field can fulfill this requirement. The advantage of the SQL Expression field is that the processing of the request will be done on the database server instead of in the Crystal Reports Designer. This results in a faster processing time.

SQL Expression for SQL Server:

{fn LEFT("Employee"."First Name",3)}

Crystal Reports formula:

left({Employee.First Name},3)

SQL Expression Fields and SELECT Statements

Using a SELECT statement in a SQL Expression field is not supported. Generally, an SQL Expression field cannot contain a SELECT statement because Crystal Reports can only process one SELECT statement per main report. If a SELECT statement is included in an SQL Expression field, an error message, similar to the following, may appear:


 The exact wording of the message depends upon the database in use. In this case, I used SQL Server.
 
Although SQL Expressions with SELECT statements are not supported, they can work if they only return a single value.  For example, Maximum, Minimum and Count are functions that return a single value.

It appears to be neceassary to enclose the SELECT statement with parentheses.  For example, a Microsoft SQL Server requires the SELECT statement to be enclosed in parentheses and so does Microsoft Access.

Example for the SQL Server error message when you leave the parenthesis out:

Example with the parenthesis:
 
(
SELECT DISTINCT 'Yes'
FROM orders
WHERE CustomerId = "Customers"."CustomerID"
)

Same example for Microsoft Access:

(
SELECT DISTINCT 'Yes'
FROM orders
WHERE CustomerId = `Customers`.`CustomerID`
)

In both cases, the Show SQL Query box will look something like this:


Database Functions Not Pre-Defined in Crystal Reports

Database functions that are not pre-defined in Crystal Reports can be used in SQL Expression fields.
For example, in case of the SQL Server:

LOWER("table"."field")

LOWER is not a pre-defined function in Crystal Reports and, therefore, will not turn blue in the SQL Expression Editor. However, the syntax will be accepted and you can now insert this field into your report.
Another example for the SQL Server:
 
DATEPART("WW", "Orders"."OrderDate")
 
Another example for Microsoft Access:
 
DATEPART("WW", `Orders`.`OrderDate`,2,2)

In both cases the Show SQL Query box will look something like this:

Parameters

You can not use parameters in an SQL Expression field. In case you want to use them, you have to switch to Commands.

Excel: Milestones Chart

Downloaden a milestones template from the Microsoft Office website:

http://office.microsoft.com/en-001/templates/timeline-with-milestones-TC102930035.aspx

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:

DATE MILESTONE POSITION BASELINE BELOW BASELINE ABOVE
23-jan Project Start 25 0 #N/B
14-feb Milestone 1 10 0 #N/B
24-feb Milestone 2 -10 #N/B 0
1-mrt Milestone 3 15 0 #N/B
15-mrt Milestone 4 -15 #N/B 0
15-mei Milestone 5 15 0 #N/B
15-jun Milestone 6 -15 #N/B 0
30-jun Milestone 7 15 0 #N/B
15-jul Milestone 8 -20 #N/B 0
30-jul Milestone 9 20 0 #N/B
23-okt Milestone 10 -15 #N/B 0
31-dec Project End 15 0 #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

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

dinsdag 17 juni 2014

Excel: Bar Chart With Wingdings and Symbols

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


My data:

Patients Time Since Treatment Response Time to Response → Ongoing response Y
10 17 23 11
9 19 7 13 4
8 38 25 15 4
7 48 0 36
6 52 12 27 4
5 59 10 20 4
4 60 23 37
3 89 5 25 4
2 90 16 69 4
1 98 25 24

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

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

vrijdag 13 juni 2014

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

Afgelopen 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 familie gerelateerd aan de gegevens uit mijn database.


dinsdag 10 juni 2014

Friesland: PLEIDOOI VOOR EEN BREDERE INVULLING VAN HET ELFSTEDENBREVET

DE ELFSTEDENTOCHTEN EN HET ELFSTEDENBREVET




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
Ja (elk jaar)
Ja
Zeilen
Ja (elk jaar)
Nee
Zwemmen
Nee
Nee

AANTAL VOLTOOIERS VAN EEN ELFSTEDENTOCHT
Naar mijn ruwe schatting hebben zeker honderdduizend mensen minimaal één keer een elfstedentocht afgelegd.

VERSCHILLENDE ELFSTEDENTOCHTEN
De schaatselfstedentocht werd voor het eerste georganiseerd in 1909, de fietselfstedentocht in 1912. Voor de tweede wereldoorlog is deze tocht alleen in de jaren 1912, 1913, 1914, 1928, 1929, 1931, 1934, 1935, 1936 en 1937 gehouden. Vanaf 1947 wordt er elk jaar gefietst. Alle andere tochten zijn eerst veel later gestart.

Al in 1909 fietste en schaatste Gerlof Dirks van de Leij de tocht. De fietstocht was toen nog niet officieel. In 1912 fietsten en schaatsten Theodorus Adriani Hoen en Haye Ypma de elfstedentocht. Anderen herhaalden deze prestatie later. Mogelijk hebben zo'n drieduizend personen twee verschillende elfstedentochten gedaan.

De atleet Jan Zeegers schaatste de elfstedentocht zes keer. In 1943 wandelde hij de tocht in anderhalve dag. In 1947 werd voor het eerst de kano-elfstedentocht georganiseerd. Jan Zeegers en zijn vrouw waren er bij. Mogelijk was hij de eerste die drie verschillende tochten deed.

Door het zogenaamde elfstedenbrevet weten we dat er minimaal 1.508 personen drie verschillende tochten hebben gedaan. Mogelijk hebben totaal een kleine tweeduizend personen drie verschillende elfstedentochten gedaan. 

Niet goed is hoeveel mensen meer dan drie elfstedentochten gedaan hebben. Ik ga dan even uit van de individueel agelegde tochten en niet van de estafettevorm. In ieder geval geldt dat voor de bekende Jeen van den Berg (4x) en zanger Syp van der Ploeg (5x). Ook Stephan Rekker, voorzitter van de fietselfstedentocht, heeft vijf verschillende tochten voltooid. Mijn elfstedenregister telt nu 40 mannen die deze prestatie geleverd hebben. Het is me van geen enkele vrouw bekend.

Voor een volledige lijst van mannen met vier of meer afgelegde individuele elfstedentochten, zie: elfstedentocht 4x of meer

Tijdens de wandelelfstedentocht van 2014 trof ik Jetze Buma: hij heeft aan vier verschillende elfstedentochten meegedaan en de tocht totaal 62 keer afgelegd. Het brevet heeft Jetze niet omdat hij de tocht niet officieel heeft kunnen schaatsen. Tijdens de kano-elfstedentocht van 2014 kwam ik Jan Kokmeijer tegen. Hij heeft de elfstedentocht op zeven verschillende manieren gedaan. Een record dat hij bij mijn weten samen met voormalig marathon schaatser Bennie van der Weide en Frans Wiersema houdt. Gerben van Houten, Hendrik Bakker en ikzelf hebben er zes gedaan. Daarnaast hebben Kokmeijer en Van Houten ook nog aan de hardloop estafette meegedaan.

Historie elfsteden brevet
Op initiatief van de Koninklijke Vereniging De Friesche Elf Steden, Stichting de Friese Elfsteden Rijwieltocht en Stichting Friese 11-steden Wandeltochten wordt aan sporters, die de elfstedentocht onder auspiciën van deze organisaties hebben geschaatst, gefietst en gelopen een brevet uitgereikt. Het brevet is een indrukwekkend document, dat voor het eerst werd uitgereikt in 1987. Sindsdien zijn er - tot en met het voorjaar van 2016 - 1.508 sporters die een brevet verdiend hebben.

NAAR EEN BREDER BREVET
Groot probleem in dit geval is natuurlijk dat de schaatselfstedentocht heel weinig gehouden wordt. De laatste keer is al weer twintig jaar geleden. Als de klimaatverwachtingen uitkomen, worden de kansen op een nieuwe schaatstocht ook nog eens steeds kleiner. Het wordt schier onmogelijk het brevet in zijn huidige vorm te verwerven.

Daarom wil ik pleiten voor een bredere opzet van het elfstedenbrevet. Vanaf drie of meer individuele elfstedentochten zou al naar gelang het aantal voltooide tochten een ander brevet verstrekt kunnen worden.

Deze aanpassing zou ook mensen die de tocht nooit hebben kunnen schaatsen de kans geven een dergelijk brevet te verwerven. Mensen die de tocht bijvoorbeeld gestept, gewandeld en gekanood hebben. Speciale brevetten voor deelname aan vier of meer verschillende elfstedentocht zou voor velen een mooie uitdaging zijn en een geweldige reclame voor Friesland en de elfstedentocht.

MIJN ELFSTEDENREGISTER
Via www.walmar.nl/elfsteden.asp kan iedereen registreren aan welke elfstedentochten hij/zij heeft meegedaan en hoe vaak

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(0, 3).Value = Now()
            Sheets("history").Cells(intRows, 1).Offset(0, 4).Value = Sh.Name
            Sheets("history").Cells(intRows, 1).Offset(0, 5).Value = Application.UserName
        Else
            For Each rngCell In Target.Cells
                intRows = Sheets("history").UsedRange.Rows.Count + 1

                Sheets("history").Cells(intRows, 1).Offset(0, 0).Value = rngCell.Address
                Sheets("history").Cells(intRows, 1).Offset(0, 1).Value = oldValue
                Sheets("history").Cells(intRows, 1).Offset(0, 2).Value = rngCell.Value
                Sheets("history").Cells(intRows, 1).Offset(0, 3).Value = Now()
                Sheets("history").Cells(intRows, 1).Offset(0, 4).Value = Sh.Name
                Sheets("history").Cells(intRows, 1).Offset(0, 5).Value = Application.UserName
            Next
        End If
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    oldValue = Target.Value
End Sub

You need an additional sheet called history to store the changes. In the North American Excel version, history is a reserved name. So you have to think of a different one. You can hide this sheet, of course. The sheet will look like this:


+Brian Canes tipped me to shorten the Workbook_SheetChange code to:

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).Resize(1, 6) = _
            Array(Target.Address, oldValue, Target.Value, Now(), Sh.Name, Application.UserName)
        Else
            For Each rngCell In Target.Cells
                intRows = Sheets("history").UsedRange.Rows.Count + 1
                Sheets("History").Cells(intRows, 1).Resize(1, 6) = _
                Array(Target.Address, oldValue, Target.Value, Now(), Sh.Name, Application.UserName)
            Next
        End If
    End If
End Sub