maandag 26 mei 2014

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien.


Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig.

Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster:


Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel. Deze optie moeten we aanvinken, voor we op OK klikken.

We krijgen dan een iets ander beeld dan normaal:


Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan.

Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden.

Het resultaat is identiek aan het bovenste plaatje. Niets veranderd dus. Echter, als we nu bij Telling van Artikelomschrijving kiezen voor Waardeveldinstellingen zien we iets nieuws: Uniek aantal!


Om het verschil helemaal duidelijk te maken, heb ik het veld Artikelomschrijving een tweede keer toegevoegd. Voor de tweede kolom heb ik dan Uniek aantal gekozen. Dan krijgen we uiteindelijk:


Het verschil is overduidelijk. Zowel per groep als voor het generaal totaal zien we nu de unieke aantallen! We zien trouwens ook het woord telling staan in plaats van aantal zoals in het eerste plaatje.

Om het een en ander na te spelen, kunnen we het bestand verkopers.xlsx downloaden via

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

Voor verder Excel tips klik hier.

donderdag 22 mei 2014

Word: Button To Add a Row at the End of the Current Table; using XML and VBA

Word has a lot of buttons to add elements to a table. But there is no button to add a row at the end of the table your cursor is in. I know very well that adding a row can be done positioning the cursus in the last cell of the last row and column and then pressing the tab key. But I wanted to create a button doing this.

First of all we need a Custom UI editor for Microsoft Office to add a piece of XML to the document. My XML script looks like this:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="CV" label="CV" keytip="CV" insertBeforeMso="TabHome">
         <group id="CVactions" label="CV actions">
           <button id="AddRow" label="Add row at end of table" imageMso="FileNew" size="large" onAction="ctlAddRule" />
         </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

I added this script to a file calles AddRow.docm. In this same file a added a VBA module called Callback. In this module I put the VBA code:

Sub ctlAddRule(control As IRibbonControl)
    Dim intCol As Integer, intRow As Integer
    If Selection.Information(wdWithInTable) = True Then
        'test to determine whether your cursor is in a table
        'Selection.Tables(1) picks the current table your cursor is in
        intCol = Selection.Tables(1).Columns.Count
        intRow = Selection.Tables(1).Rows.Count
        Selection.Tables(1).Cell(intRow, intCol).Range.Select
        'selects the lowest last cell in the table
        Selection.MoveRight Unit:=wdCell
        'adds a row to the current table
    Else
        MsgBox "You cursor is not in a table"
    End If
End Sub

And you button works perfectly!

dinsdag 20 mei 2014

Friesland: verslag elfsteden wandeltocht 2014

Vooraf

Komende dinsdag 27 mei begint de 68ste wandelelfstedentocht. En ik ga een poging doen deze te lopen. Officieel nog 209 kilometer te gaan ...


Voorlopig lijkt het weer er goed uit te zien, niet te warm en weinig regen.

Hieronder zal ik verslag doen van de vijf wandeldagen.

Dinsdag 27 mei: 51,3 kilometer van Leeuwarden naar Sloten

's Ochtends in alle vroegte naar Leeuwarden gereden. Onderweg kom ik nog in een enorme bui terecht met twee regenbogen. Rond zes uur sta ik al geparkeerd bij het station. Dan is het inmiddels weer droog. Vandaar blijkt het 800 meter lopen naar het Harmoniegebouw. Daar tref ik oude bekenden van vorig jaar. Met Linda ga ik vandaag de etappe lopen.

Op de foto: onderweg naar Weidum

    Foto Nanne Nicolai

Bij de start van zeven uur is het weer dringen van jewelste. Kennelijk moeten iedereen als eerste starten. Wij wachten rustig af en staan vijf minuten later ook buiten. We zetten gelijk de sokken er in en weten eigenlijk de hele etappe een gemiddelde van 6.3 kilometer per uur aan te houden. De afstand: 51,3 kilometer.

Op de foto: stempelen vlak voor Sneek (de 'geheime' stempelpost)

    Foto Jannie Bos

Woensdag 28 mei: 46,7 kilometer van Sloten naar Workum

Dag twee zit er op. Regen van start tot finish; een barre tocht. Een aantal mensen moest stoppen vanwege onderkoeling. Ik putte inspiratie uit de tekst op de grote steen van het Rode Klif: "Leaver dea as slaaf". Even na half drie zijn we binnen. Volgens mijn Garmin hebben we 46,7 kilometer gelopen. Bijna twee kilometer meer dan de officiële opgave. Doorweekt tot op mijn huid duik ik gelijk mijn auto in om weer warm te worden.

Op de foto: bij de start.

Donderdag 29 mei: 43,2 kilometer van Workum naar Franeker

De organisatie meldt dat er de dag ervoor twaalf mensen hebben opgegeven. De eerste dag waren dat er achttien. De derde dag begint opnieuw met motregen en het is akelig guur. Een dikke trui en een plastic hoes helpen me door de eerste uren. Bij Bolsward begint het eindelijk op te klaren. Daarna gaat de hoes uit en staat de wind in de rug. Het gaat veel vlotter dan gisteren. Tien voor twee zijn we al in Franeker, na 43,2 kilometer. Deze keer maar achthonderd meter meer dan opgegeven.

Op de foto: direct na de stempelpost in de Broerkerk van Bolsward

    Foto Nanne Nicolai

Op de foto: nog net is mijn snelle tred waarneembaar; bij de stempelpost voor Harlingen

    Foto Jannie Bos

Vrijdag 30 mei: 42,9 kilometer van Franeker naar Dokkum

's Ochtends mijn rechter knie getapet. Het lijkt voor mij een zware dag te worden. Ik start ook maar wat achterin om rustig op gang te komen. Linda gaat vandaag even haar eigen gang. Maar na een half uur blijk ik warmgedraaid. Plotseling lonkt Dokkum - mijn geboortestad - en ik zet de sokken er in. Ik zie Linda een heel eind voor me lopen. Vreemd genoeg kom ik haar niet meer tegen. Die heeft kennelijk ergens binnen gezeten op het moment dat ik haar passeer.

Halverwege, even voorbij Stiens, blijk ik me plotseling in de voorhoede te bevinden. Na Bartlehiem kom ik helemaal op toeren. Ik zie nauwelijks meer iemand voor me. Bij Birdaard haal ik een man in die even met zijn voeten in het water moest. Hij haalt mij vervolgens langzaam maar zeker weer in. Van hem hoor ik dat we op plaats zestien en zeventien zouden liggen.

Vlak voor Dokkum halen we nog twee mensen in die merkbaar de pijp leeg hebben. Bij de eindpost na 42,9 kilometer staan mijn vriendin en Peter - de vaste fotograaf en de partner van Linda - mij op te wachten. We blijken nummer vijftien en zestien en zijn om 13:27 binnen.


Vrij snel daarna komt ook Linda binnen.


Op de foto: Linda en Peter, de vaste fotograaf

    Foto Jannie Bos

Op de foto: moegestreden (?) helden: Linda en ik.


Zaterdag 31 mei: 28,5 kilometer van Dokkum naar Leeuwarden

De laatste dag en de kortste afstand. Het is prachtig weer. Zonovergoten en praktisch windstil. Toch zie je nu dat bij veel mensen de kilometers beginnen te tellen. Zo loopt de man die gisteren als derde binnen kwam nu erg moeizaam. Ik moet ook wel een uur warmlopen. Verder gaat het uitstekend.

Na anderhalf uur pauzeren we even in Rinsumageest en genieten daarna van de nieuwe route naar Oenkerk. Een goede keuze van de organisatie. Na Oenkerk passeren we het beroemde en betegelde elfstedenbruggetje. Voor veel mensen de gelegenheid even te poseren. Ongetwijfeld zijn er ook deelnemers die zich daar als schaatser hebben laten vereeuwigen.

Op de foto: het Elfstedenbruggetje voorbij Giekerk

    Foto: Jannie Bos

Ook het laatste stuk via Lekkum en dan langs de Dokkumer Ee is weer prachtig. Precies half twaalf komen we dan aan bij de Harmonie in Leeuwarden. Mijn vriendin, Peter en mijn schoonouders wachten onze binnenkomst op. Met bloemen uiteraard. Volbracht, wat mij betreft. Volgend jaar de stepelfstedentocht!

Op de foto: vlak na de start in Dokkum

    Foto Nanne Nicolai

Op de foto: onze binnenkomst in Leeuwarden


En waar het allemaal om begonnen was, het derde kruisje!


Op naar de volgende.

Mensen die deelgenomen hebben aan één of meer elfstedentochten kunnen zich registreren in mijn elfstedenregister: www.walmar.nl/elfsteden.asp

donderdag 15 mei 2014

Excel: Bubble or Pie Chart or Both?

Bubble or pie chart or both? With the help of Excel VBA everything is possible!


Here are the data:

Bubble X Y Size
A 1 3 3
B 3 3 3
C 3 1 4
D 1 1 2
Pie 1 Pie 2 Pie 3 Pie 4
8 5 3 8
5 1 7 2
6 2 4 3
4 4 3 3
5 5 3 2

You can download the file ExcelGrafiekBel.xlsm through

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

maandag 12 mei 2014

Excel: Chart With Thumbs Up And Thumbs Down

This chart shows thumbs up when the values are above average and thumbs down when the values are below:


Here are the data:

Month Values Qualification Average Above Average Below Average
jan 130 C 79 130 0
feb 80 C 79 80 0
mar 56 D 79 0 56
apr 91 C 79 91 0
may 98 C 79 98 0
jun 59 D 79 0 59
jul 70 D 79 0 70
aug 86 C 79 86 0
sep 90 C 79 90 0
oct 38 D 79 0 38
nov 81 C 79 81 0
dec 69 D 79 0 69

You can download the file ExcelThumbsUpDown.xlsx through

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

vrijdag 9 mei 2014

Crystal Reports: Displaying Values from a Column in a Single Text Field

The other day I discussed the solution for this problem in Report Builder / Reporting Services. A couple of days later I got the same question for Crystal Reports. Somebody wanted his report to look like this:


In the example I am showing Customers and Order numbers from the Noordenwind database, the Dutch equivalent to the Northwind database.

How can we get this result? I created a report grouped on Klanten.Bedrijf. In the group header I put the formula field orderreset:

shared stringvar ordercollect:=""; 

In the detail section I put the formula field ordercollect:

shared stringvar ordercollect;
ordercollect:=ordercollect & totext({Orders.Order-id},"00000") & ", ";

In the group footer I put first of all the group name field Klanten.Bedrijf and next to it the formula field orderprint:

shared stringvar ordercollect;
left(ordercollect,instrrev(ordercollect,",")-1)

Finally, I suppressed the details and the group header section ending up with the result on the picture.

Of course we could improve the formula fields in order to remove duplicates for instance. Then, we need three more complex formulas like

productreset:
shared stringvar array productcollect;
shared numbervar i:=0;
redim productcollect[1];
""
productcollect:
shared stringvar array productcollect;
shared numbervar i;
if not({Producten.Productnaam} in productcollect) then
(
i := i + 1;
redim preserve productcollect[i];
productcollect[i] := {Producten.Productnaam};
);
productcollect[i]

and productprint:
shared stringvar array productcollect;
join(productcollect,", ")

The result:



You can download the files klantenorders.rpt and klantenproducten.rpt through

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

donderdag 8 mei 2014

Crystal Reports: Line Chart with Banded Background indicating Good, Average and Bad

Pretty easy to create: Crystal Reports line chart with banded background, indicating good, average and bad:


For this example I used the noordenwind database, the Dutch equivalent of the Northwind database. For the bands I used three formula fields:

Good: 50000
Bad: 30000
Average: 40000

I created a stacked bar chart based on the three formula fields.

The line chart presents the total sales volume per customer, limited to the country Germany.

You can download the file Graphbands.rpt through

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

Report Builder / Reporting Services: Multi Value Column Printed in a Single Textbox

I got a request to print a column with multiple values in a single textbox. The first result looked like the upper part of the picture; the second one like the one below.

In my example I use the Noordenwind database with this SQL query, called DataSet2:



SELECT     dbo.Orders.[Order-id], dbo.Orders.Klantnummer, 
dbo.Producten.Productnaam
FROM         dbo.Orders INNER JOIN
             dbo.Orderinformatie ON dbo.Orders.[Order-id] = dbo.Orderinformatie.[Order-id]
INNER JOIN Producten ON Producten.Productnummer = dbo.Orderinformatie.Productnummer
Where Klantnummer ='VINET'



Data:



10248 VINET Queso Cabrales
10248 VINET Singaporean Hokkien Fried Mee
10248 VINET Mozzarella di Giovanni
10274 VINET Fløtemysost
10274 VINET Mozzarella di Giovanni
10295 VINET Gnocchi di nonna Alice
10737 VINET Konbu
10737 VINET Jack's New England Clam Chowder
10739 VINET Inlagd Sill
10739 VINET Filo Mix



In the first example I used a textbox with this expression:

=join(LookupSet(First(Fields!Klantnummer.Value, "DataSet2"), Fields!Klantnummer.Value, Fields!Productnaam.Value, "DataSet2"), ", ")

As you can see, we get duplicate values and the values are not sorted. To get this done I needed a function:



Function RemoveDuplicates(objItems As Object()) As Object()
System.Array.Sort(objItems) 

Dim intK As Integer = 0

For intI As Integer = 0 To objItems.Length - 1
If intI > 0 AndAlso objItems(intI).Equals(objItems(intI - 1)) Then
Continue For
End If
objItems(intK) = objItems(intI)
intK += 1
Next

Dim objSortedUnique As [Object]() = New [Object](intK - 1) {}

System.Array.Copy(objItems, 0, objSortedUnique , 0, intK)

Return objSortedUnique 
End Function



This function you have to add to the report property code:





This function we have to add to the expression in the textbox, like this:



=join(Code.RemoveDuplicates(LookupSet(First(Fields!Klantnummer.Value, "DataSet2"), Fields!Klantnummer.Value, Fields!Productnaam.Value, "DataSet2")), ", ")



Like we can see in the second example, the values are sorted and unique.