Doorgaan naar hoofdcontent

Crystal Reports: geavanceerde kruistabelfuncties

We kunnen op verschillende manieren nog meer gegevens aan onze kruistabellen toevoegen.Hieronder wordt een aantal geavanceerde kruistabelfuncties beschreven. Alle voorbeelden zijn gebaseerd op de Noordenwind database.

1.1  Calculated Members

We kunnen nieuwe rijen of kolommen aan de kruistabel toevoegen door een Calculated Member in te voegen. Deze rijen of kolommen kunnen aangepaste berekeningen bevatten (bijvoorbeeld het verschil in verkoop tussen twee regio's) of kunnen voor de opmaak worden gebruikt (bijvoorbeeld om de drie regels een lege rij invoegen om de leesbaarheid te vergroten).

We hebben de keuze om geheel naar wens Calculated Formula's te maken en kunnen aangeven of deze eenmaal moeten worden weergegeven of moeten worden herhaald in de kruistabel.

Verwante onderwerpen

·         Calculated Formulas
·         Header Formula's
·         Insertion Formula's
·         Column /Row Value Formula's

1.1.1 Een Calculated Member toevoegen aan de kruistabel

Voor onderstaande voorbeeld hebben we de tabellen Categorieën, Producten, Orders en Orderinformatie gebruikt.

§   Maak twee Formula Fields:

  Omzet:{Orderinformatie.Prijs per eenheid}*{Orderinformatie.Hoeveelheid}

  Jaar: year({Orders.Orderdatum})

§   Maak een kruistabel als volgt:


§   Klik op OK.
§   Klik in de kruistabel met de rechtermuisknop op de rij- of kolomkop die we willen gebruiken als eerste waarde voor het Calculated Member.


Als we bijvoorbeeld een Calculated Member willen maken voor het verschil tussen 1996 en 1997,gebruiken we de formule Verschil 1997 - 1996. In dit voorbeeld klikken we met de rechtermuisknop op de kop 1996.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Selecteer de gekozen waarde als de eerste waarde.
Klik in de kruistabel met de rechtermuisknop op 1997 als tweede waarde.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Selecteer de gewenste berekening: Difference 1997 - 1996.

In de kruistabel verschijnt een rij of kolom met daarin het resultaat van de geselecteerde berekening.

§   Klik met de rechter muisknop op één van de nieuwe velden:



§   Klik Edit Calculation Formula.

We zien dan de formula:

  //The calculation formula is used in place of the summaries in //Calculated Members.
  // This calculation formula must return a/an Currency value.
  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1997.00), CurrentSummaryIndex) -
  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1996.00), CurrentSummaryIndex)

We zien hier dan dat de functie GridValueAt het snijpunt kiest van de huidige rij (CurrentRowIndex) en de kolom waarin zich het jaar bevindt (GetColumnPathIndexOf(1996.00)) en van dat snijpunt de aanwezige Summary (CurrentSummaryIndex) kiest. Zo worden dus in deze formule voor elke regel de Summary 1997 en 1996 van elkaar afgetrokken.

1.1.2 Een lege rij of kolom toevoegen aan de kruistabel

§   Klik in de kruistabel met de rechtermuisknop op de kop vóór de plek waar we de lege kolom willen invoegen.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Klik op Kolom invoegen.

Er wordt een lege kolom in de kruistabel weergegeven.

§   Klik op één van de nieuwe belden.
§   Kies Calculated Member  Ãž Edit Calculation Formula.
§   Typ daar de Formula:

  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1998.00), CurrentSummaryIndex) -
  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1997.00), CurrentSummaryIndex)

Het resultaat moet hetzelfde zijn als in de vorige paragraaf (10.1.1).

1.1.3  De verwerkingsvolgorde van berekende leden wijzigen

§   Klik met de rechtermuisknop in de linkerbovenhoek van de kruistabel.
§   Wijs in het snelmenu de optie Geavanceerde berekeningen aan en klik op Calculated Member.

De Expert voor Calculated Members  wordt weergegeven.



Bij beide zien we nog geen naam.

§   Noem de eerste Kolom na 1996 en de tweede Kolom na 1997

Bij Edit Insertion Formula zien we:

  //The insertion formula determines where this calculated member is //inserted. It must return a Boolean value.
  GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and GridRowColumnValue("@year") = 1996.00

Bij Edit Column Value Formula staat niets. Bij Edit Header Formula treffen we de eerder gemaakte headers aan.

Via dit scherm hadden we ook een kolom kunnen toevoegen. Bijvoorbeeld na 1998. Daar hadden we dan de volgende Formula voor moeten gebruiken:

  GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and GridRowColumnValue("@year") = 1998.00

§   Gebruik de pijlen in het vak Calculated Members om de verwerkingsvolgorde van de Calculated Members te wijzigen.
§   Klik op OK om de wijzigingen op te slaan en terug te gaan naar het rapport.

De waarden van de Calculated Members worden opnieuw berekend in de kruistabel.

Opmerking:
Wijzigingen in de volgorde waarin Calculated Members worden verwerkt, hebben geen invloed op het vernieuwen van de database.

1.1.4  Calculating formula's

Calculating formula's zijn wiskundige formules die door Crystal Reports worden gebruikt om de celwaarden van Calculated Members te bepalen. We kunnen een keuze maken uit de vooraf gedefinieerde formules of zelf formules ontwerpen in de Formula workshop.

De vooraf gedefinieerde formules zijn als volgt:

·         Sum of
·         Difference of
·         Product of
·         Quotient of

Deze zien er in de Formula workshop zo uit:

  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1998.00), CurrentSummaryIndex) +
  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(1997.00), CurrentSummaryIndex)

Uiteraard moeten we dan respectievelijk  -, * of / invullen.

Crystal Reports bevat verder diverse functies die we kunnen gebruiken om zelf formules te ontwerpen.


Calculation Formula bewerken

§   Klik in het Calculated Member dat we willen bewerken met de rechtermuisknop op een willekeurige cel, maar niet op de kop.
§   Wijs in het snelmenu de optie Calculated Member  aan.
§   Klik op Edit Calculation Formula.

De Formuleworkshop wordt weergegeven.

§   Geef in de Formuleworkshop de gewenste berekeningsformule op.
§   Klik op Opslaan en sluiten om terug te keren naar het rapport.

Het resultaat van de nieuwe berekeningsformule wordt in het Calculated Member weergegeven.

1.1.5  Header Formula's

Header Formula's bepalen de zichtbare rij- of kolomkoptekst van een Calculated Member.


 Edit Header Formula

§   Klik met de rechtermuisknop op de koptekst van het Calculated Member dat we willen bewerken.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Klik op Edit Header Formula.

De Formuleworkshop wordt weergegeven.

§   Geef in de Formuleworkshop de gewenste naam op tussen aanhalingstekens.

Opmerking:
We kunnen de Header ook opgeven met een formule. Een Header Formula moet een tekenreeks retourneren.

§   Klik op Opslaan en sluiten om terug te keren naar het rapport.

In de kop van het Calculated Member wordt het resultaat van de Header Formula  weergegeven.

1.1.6 Waardeformules

Als we in een formule naar een Calculated Member willen verwijzen, moeten we een waarde aan het lid toewijzen. Om deze reden kunnen we met een waardeformule waarden toewijzen aan Calculated Members.


Edit Column / Row Value Formula

§   Klik met de rechtermuisknop op de Header van de Calculated Member die we willen bewerken.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Klik al naar gelang we een rij of kolom willen bewerken respectievelijk op Edit Row Value Formula of Edit Column Value Formula.

De Formula workshop wordt weergegeven.

§   Geef in de Formula workshop de gewenste waarde op.

Opmerking:
We kunnen de waarde ook opgeven met een formule. Een waardeformule moet van hetzelfde type zijn als het groepeerveld voor rijen of kolommen waarin de formule wordt ingevoegd.

§   Klik op Opslaan en sluiten om terug te keren naar het rapport.

In het Calculated Member wordt het resultaat van de Value Formula weergegeven.

Opmerking:
Als we de Header Formula van een Calculated Member niet bewerkt hebben, wordt het resultaat van de Value Formula in de zichtbare koptekst weergegeven. Wanneer we de Header Formula bewerkt hebben, wordt de Value Formula als de zichtbare koptekst hierdoor overschreven.

1.1.7  Insertion Formula's

Insertion Formula's  bepalen waar een Calculated Member in een kruistabel moet worden weergegeven. Meestal wordt een Calculated Member slechts eenmaal weergegeven. We kunt echter ook aangeven dat het lid op meerdere locaties of in een herhaald patroon moet worden weergegeven.


Edit Insertion Formula

§   Klik met de rechtermuisknop op de koptekst van het Calculated Member dat we willen bewerken.
§   Wijs in het snelmenu de optie Calculated Member aan.
§   Klik op Edit Insertion Formula.

De Formula Workshop wordt weergegeven.

§   Geef in de Formula Workshop de gewenste formule op.

Met de volgende formule bijvoorbeeld, wordt een Calculated Member ingevoegd na het jaar 1998:

  GridRowColumnValue("@year") = 1998.00

§   Klik op Opslaan en sluiten om terug te keren naar het rapport.

Het Calculated Member wordt weergegeven op de locaties die in de Insertion Formula zijn gedefinieerd.

1.2  Embedded Summary

We kunnen een extra berekening aan de kruistabel toevoegen door een Embedded Summary in te
voegen. In tegenstelling tot Calculated Members, worden er met Embedded Summaries geen rijen of kolommen aan de kruistabel toegevoegd. In plaats daarvan worden er extra berekeningen aan de cellen van de kruistabel toegevoegd. Zo kunnen we in een rapport met regionale verkoopcijfers bijvoorbeeld een berekening voor elke regio invoegen, waarin het percentage van de totale landelijke verkoop wordt weergegeven.

1.2.1  Een Embedded Summary toevoegen aan de kruistabel

§   Klik in de kruistabel met de rechtermuisknop op een willekeurige cel, maar niet op de kop.
§   Wijs in het snelmenu de optie Embedded Summary aan.
§   Klik op Insert Embedded Summary.



Een lege Embedded Summary wordt in de kruistabel weergegeven.

§   Klik in de Embedded Summary met de rechtermuisknop op de tekst Edit this Formula.
§   Wijs de optie Embedded Summary aan.
§   Klik op Edit Calculation Formula.

De Formula Workshop wordt weergegeven.

§   Geef in de Formula Workshop de gewenste Calculated Formula op.

  GridValueAt (CurrentRowIndex, CurrentColumnIndex ,0 ) / sum({@omzet})*100

Deze Formula berekent de verhouding tussen elke lokale Summary en het generaal totaal.

§   Klik op Opslaan en sluiten om terug te keren naar het rapport.

In de Embedded Summary wordt het resultaat van de nieuwe berekeningsformule weergegeven.

1.2.2  De verwerkingsvolgorde van Embedded Summaries wijzigen

Als de kruistabel meerdere Embedded Summaries bevat, kan de volgorde waarin deze worden
verwerkt invloed hebben op het resultaat. We kunnen de volgorde waarin Embedded Summaries worden verwerkt, wijzigen in het dialoogvenster Embedded Summaries.

§   Klik met de rechtermuisknop in de linkerbovenhoek van de kruistabel.
§   Wijs in het snelmenu de optie Geavanceerde berekeningen aan.
§   Klik op Embedded Summary.

Het dialoogvenster Embedded Summaries  wordt weergegeven.


§   Gebruik de pijlen in het vak Summaries om de verwerkingsvolgorde van de Summaries te wijzigen.
§   Klik op OK om de wijzigingen op te slaan en terug te gaan naar het rapport.

De waarden van de Embedded Summaries  worden in de kruistabel opnieuw berekend in de nieuwe verwerkingsvolgorde.

Opmerking:
Wijzigingen in de verwerkingsvolgorde van Embedded Summaries hebben niet tot gevolg dat er een vernieuwing via de database wordt uitgevoerd.

Reacties

Populaire posts van deze blog

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik: Sub WachtwoordCrack()     Dim a As Integer, b As Integer, c As Integer, d As Integer, _     e As Integer, f As Integer, g As Integer, h As Integer, _  I As Integer, j As Integer, k, m As Integer     Dim begin As Date, eind As Date     Dim duur As String     Dim objSheet As Worksheet     begin = TimeValue(Time)     On Error Resume Next     For Each objSheet In Application.Worksheets         For a = 65 To 66: For b = 65 To 66: For c = 65 To 66             For d = 65 To 66: For e = 65 To 66: For f = 65 To 66                 For g = 65 To 66: For h = 65 To 66: For I = 65 To 66                     For j = 65 To 66: For k = 65 To 66: For m = 32 To 126                         ActiveSheet.Unprotect Chr(a) & Chr(b) & _   Chr(c) & Chr(d) & Chr(e) & Chr(f) & _   Chr(g) & Chr(h) &  Chr(I) & Chr(j) & C

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

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata: Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel . Oplossing: draaitabel We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel ( INVOEGEN => DRAAITABEL ). De naam veranderen we dan even van Tabel1 in draaitabel . Vervolgens maken we de draaitabel. Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum . De kopjes zetten we even om naar Naam en Laatste datum . Klaar. Oplossing: matrixformules Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken: datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1) naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A