Hessel de Walle's Blog

Look at the X-axis of this graph: In the example you can use scroll bars to manipulate how to compare the three series. The data from A1:F31 : Apples Pears Banana's A 2001 50 2002 100 2003 150 2004 200 2005 100 2006 300 2007 250 2008 400 2009 430 2010 250 P 2001 123 2002 100 2003 150 2004 543 2005 250 2006 300 2007 54 2008 251 2009 234 2010 98 B 2001 87 2002 100 2003 234 2004 543 2005 345 2006 456 2007

Saw a graph like this on the internet. In it self, nothing special. But in my opinion it would be more clear to group the subjects like this: But both might be good alternatives. So why not give the viewer a choice. So I added a pick list with two choices: apart or together . So now you have both graphs in one. Here are the data as I grouped them from A3:D33: Town Haircut Half Gallon T-bone steak Manhattan 22,21 Brooklyn Honolulu 15,1 San Francisco 16,79 San Jose CA 14,13 Queens 17,79 Stamford CT 20,03 Washington 16,29 Orange County 15,57 Boston 15,29 Manhattan 2,34 Brooklyn 2,12 Honolulu 3,28 San Francisco 2,28 San Jose CA 2,05 Queens 2,58 Stamford CT 2,51 Washington 2,71

Just a simple one: A stacked bar chart based on the product table from the database NorthWind. I used a formula field Top to get the upper part of the column: maximum({Products.UnitPrice})/5 The data: The upper column I then changed into a Beveled Box : For the lower column I switched the data labels on and chose Outside maximum . The file you can download here: www.walmar.nl/zips/graphmatch.zip You can also download the file through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

In Crystal Reports it is very easy to create a Gantt Chart . You just need a table with a starting date and an ending data. And there you go. In the given example I used the data from the Orders table from the database Northwind . I just took the data from July 1996. The regular chart looks like this: I just chose Color by series to get the same color for all the bars. Now I created a Gantt Chart with different colors before and after a given date (July 15 1996, in this case). Here Orange stands for Done and Green for To Do . How did I create this one? First I created a parameter GivenDate with 15-7-1996 as Default value. Next I created a Formula Field BeforeGivenDate : if {Orders.OrderDate}<{?GivenDate} and {Orders.ShippedDate} < {?GivenDate} then     {Orders.ShippedDate} - {Orders.OrderDate} else     (if {Orders.OrderDate}< {?GivenDate} and {Orders.ShippedDate}>= {?GivenDate} then         {?GivenDate}- {Orders.OrderDate}     else         0

In this example I split the Y-axis into 5 parts. The main principles? I used a 2-D bar combined with data labels to create the Y-axis. I combined this 2-D bar chart in one graph with a stacked column chart. Integrated with this stacked column chart I used line charts to create the X-axis labels. I already managed to split the Y-axis, but in this example we can do this unlimited times. Here are the the basic data: Amount 1 Amount 2 Amount 3 Amount 4 Amount 5 2001 220 200 180 189 258 2002 350 320 290 279 325 2003 250 290 300 312 267 2004 430 400 370 435 345 2005 350 369 167 234 189 2006 456 367 423 367 421 You can also download the file splityaxis4times.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Normally when you use the OFFSET function to create dynamic names, it will use absolute cell referencing . So, you will get examples like this: =OFFSET(Sheet2!$A$1;0;0;COUNTA(Sheet2!$A:$A);1) So, what if we used relative cell referencing instead? The result would depend on the cell our cursor is in when we create our formula and the cell our cursor is in when we are looking at the name again. Fill the range A1:A4 with the figures 12 to 15. Put your cursor in A1 again. Create a name RANGE based on the formula with relative cell referencing : =OFFSET(Sheet1!A1;0;0;COUNTA(Sheet1!A:A);1) Now put your pointer in B15 . Look at the name again: As you can see, we get a different formula now based on the position of our cursor. So for what purpose could we use relative cell referencing ? On a sheet with the name relativeoffset ,I created a table in A1:J18 like this: Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9

Simple but nice, the match stick graph: Here are the data from A1:B18 : Year Apples 2000 20 2001 71 2002 91 2003 71 2004 71 2005 20 2006 4 2007 40 2008 10 2009 16 2010 47 2011 77 2012 12 2013 68 2014 76 2015 62 2016 7 You can also download the file lucifergrafiek.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

On the internet you can find many examples of Excel Gantt charts, either created as a graph or using conditional formatting and how to build them. My example has some additions to the graph type of the Gantt chart: A date line (the black vertical line). Different colors for the task or part of a task before and after a specific date (in this case July 31). Here are the data, from A1:C27 : Task Start Date Duration in days Task 1 3-6-2013 1 Task 2 4-6-2013 1 Task 3 4-6-2013 5 Task 4 11-6-2013 1 Task 5 11-6-2013 12 Task 6 25-6-2013 3 Task 7 28-6-2013 1 Task 8 28-6-2013 4 Task 9 4-7-2013 3 Task 10 9-7-2013 1 Task 11 10-7-2013 3 Task 12 15-7-2013 3 Task 13 18-7-2013 1 Task 14 21-7-2013 5 Task 15 24-7-2013 7 Task 16 27-7-2013 4 Task 17 30-7-2013 3 Ta

Evidently, this is not my idea. But I made some adjustments to it and that is what I want to present here. The data A1:G8: Boston Consulting Group Matrix Products Market growth Market Size Share Market Share Market Share Largest Competitor Relative Market Share Product1 3%  €  10.000.000 9% 14% 32%        0,44 Product2 5%  €  20.000.000 19% 22% 13%        1,69 Product3 14%  € 15.000.000 14% 51% 25%        2,04 Product4 17%  €  40.000.000 37% 12% 26%        0,46 Product5 7%  €  12.000.000 11% 15% 28%        0,54 Product6 4%  €   10.000.000 9% 24% 34%        0,71 You can also download the file bcgm.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

In de loop van de afgelopen jaren zijn verschillende kerken in Friesland gerestaureerd. De Grêfskriftekommisje van de Fryske Akademy heeft in zulke gevallen een nauwkeurige inventarisatie gemaakt van de bekende en onbekende grafschriften. Veel van deze verslagen, wisselend in het Fries en Nederlands, zijn nooit gepubliceerd. Ik wil ze nu via deze Blog beschikbaar stellen in de vorm van PDF's. Arum : www.walmar.nl/zips/arum.pdf Cornwerd: www.walmar.nl/zips/cornwerd.pdf Drachten : www.walmar.nl/zips/drachten.pdf Goutum : www.walmar.nl/zips/goutum.pdf Haskerhorne : www.walmar.nl/zips/haskerhorne.pdf Heerenveen : www.walmar.nl/zips/heerenveen.pdf Leeuwarden: www.walmar.nl/zips/leeuwarden.pdf Oosterlittens : www.walmar.nl/zips/oosterlittens.pdf Sint Nicolaasga: www.walmar.nl/zips/stnicolaasga.pdf St. Jacobiparochie: www.walmar.nl/zips/stjacobiparochie.pdf Stavoren : www.walmar.nl/zips/stavoren.pdf Winsum : www.walmar.nl/zips/winsum.pdf

Just an idea. A simple idea. Here are the data from A1:C10: 0-10% 125 500 10-20% -24 -500 20-30% 245 500 30-40% 432 500 40-50% -123 -500 50-60% 235 500 60-70% 53 500 70-80% 67 500 80-90% -87 -500 90-100% 98 500 C1:C10 contains a formula: =IF(B1>0;500;-500) All three series are in the column graph. Series 3 is connected to the secondary axis. Series 3 Overlapped 100% and No Gap 0% and Transparancy 50%. Both Y-axis between -500 and 500. Both series: Invert if negative . Right Y-axis removed. Data labels for the second series switched on. You can also download the file excelbackground.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Crystal Reports In Crystal Reports, this is very simple. When you put a number of fields in the detail section of your reports, it will automatically put the field names in the page header. The page header will be repeated on every page and the problem is solved. In case of a group, you can simply check the option Repeat Group Header On Each Page : Report Builder / Reporting Services In Report Builder / Reporting Services it is all a bit complicated. In a table, for example, you do not have column groups, so Report Builder does not recognize which textboxes are the column headers and checking Repeat header columns on each page doesn't work . A known bug for which we need a work around. Instead, we need to open Advanced Mode in the Groupings pane : We'll get: Clicking on a Static group highlights the corresponding textbox in the tablix. For the column headers that you want to repeat, select the Static group that highlights the leftmost column

I based my example on the database Northwind using the tables  Orders and Order_details . I created a formula field Revenue : {Order_Details.Quantity}*{Order_Details.UnitPrice} and another one Monthname : monthname(month({Orders.OrderDate}), true) Based on those two formula fields I created the graph which then looks like this: The problem is, it is then sorted on mont name in an alphabetical order (the month names are in Dutch by the way). The way I found to solve this problem is this. Go tot the Chart Expert: Click Monthname . Click Order . Choose In specified order . Now, fill in all the names of the months like this. Then click Others . Click Discard all others . Click OK twice. Your graph will then end up like this, with the month names in the right order:

My idea for an alternative to the broken axis on a column graph looks like this: This image of a broken axis graph I found on Andy Pope's website ( http://www.andypope.info/charts/brokencolumn.htm ): You can also download the file restrictmaximum.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

A graph in Crystal Reports with different colors when the revenue values are above the norm or when they are below the norm. The graph is based on the tables Orders and Order_Details from the database Northwind . Formula Field Revenue : {Order_Details.Quantity}*{Order_Details.UnitPrice} Group on Orders.OrderDate per Month . Summary on Formula Field Revenue for the created group. Formula Field RevenueAbove : if Sum ({@revenue}, {Orders.OrderDate}, "monthly")> {?prmNorm} then     Sum ({@revenue}, {Orders.OrderDate}, "monthly") else     0 Formula Field RevenueBelow : if Sum ({@revenue}, {Orders.OrderDate}, "monthly")<= {?prmNorm} then     Sum ({@revenue}, {Orders.OrderDate}, "monthly") else     0 Parameter Norm , type Numeric . Formula Field Norm : {?prmNorm} Created a Line Chart with the data: Changed the lines for revenueabove and revenuebelow into Risers . Picked nice colors.

Based on the tables Categories , Order_Details , Orders and Products from the Northwind database I created this example of a Pareto Analysis, the best I could get so far: To get the percentage I first created a Running Total {#PercentageCum}on the field  Order_Details.ProductID.  Then I created a Formula Field Percentage : {#PercentageCum}/Count ({Order_Details.ProductID})*100 This field I used in the report. The initial column graph is based on change of Categories.CategorieName. Values : Count of Order_Details.ProductId #PercentageCum I linked the second series to the second Y-axis. For this series I chose line graph instead of column and gave it the color red. I turned the secondary Y-axis off (you can not use the field Percentage in the graph; so you can not show the real percentage on the Y-axis: Crystal Reports doesn't allow charts to use formulas that refer to running totals). You can download the file here: www.walmar.nl/zips/GraphPareto.zip You

How hard is it to create special graphs in Crystal Reports. In my opinion a tool like Crystal Reports lacks the flexibility of Excel, but this does not mean one can not create nice graphs. In this case I created a graph with different colors for positive and negative values. Crystal Reports version 2011 does not do this automatically. You need to help it. I started with creating a straightforward vertical bar graph. and changed it in to this: Apart from some minor changes like removing the legend and adding data labels, I only had to do one major change: adding conditions to the Color Highlight tab. In the Chart Expert I picked the tab Color Highlight : I created two conditions for values below and above the zero line, as you can see. In this example I experimented with the Access Northwind database. You can download the file here: www.walmar.nl/zips/StackedBarAboveBelow.zip You can also download the file through this link: https://drive.google.com/folde

Outlook 2010 tips & trucs Je kunt het bestand  outlook2010tips.pdf  hier downloaden: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing   I N H O U D S O P G A V E 1             Algemeen          1.1          Werkbalk snelle toegang             1.2          Snelle stappen 2             E-mail   2.1          Regels  2.2          CC mail onderscheiden 2.3          Verwijderde items automatisch wissen               2.4          Een pauze voor het verzenden                2.5          Afzender toevoegen aan Contactpersonen       2.6          E-mailadressen verslepen          2.7          Verzonden berichten terughalen            3             Vergaderverzoeken      3.1          Automatisch accepteren             4             Reminder Afwezigheidsassistent / Automatische antwoorden                5             Handtekening maken   6             Postbus toevoegen       7             PST bestanden 7.1         

How do you display for each company which one had a total positive profit (revenue minus costs) and which one a negative. My idea: Here are the data from A1:D11: Revenue Costs Profit Company 1  €      500  € 400  €  100 Company 2  €   1.000  € 760  €  240 Company 3  €      890  € 900  €   -10 Company 4  €      640  € 700  €   -60 Company 5  €      570  € 600  €   -30 Company 6  €      875  € 800  €    75 Company 7  €      690  € 600  €    90 Company 8  €      500  € 600  € -100 Company 9  €   1.050  € 700  €  350 Company 10  €      900  € 700  €  200 From F1:H11 I created formulas: Revenue - Costs Plus Revenue - Costs Plus Revenue - Costs Minus =IF(D2:D11>0;B2:B11;NA()) =IF(D2:D11<=0;B2:B11;NA()) =IF(D2:D11<=0;C2:C11 *-1 ;NA()) =IF(D3:D12>0;B3: