Hessel de Walle's Blog

De aanleiding voor dit script is de behoefte om geautomatiseerd een aantal rapporten van Reporting Services om te zetten naar PDF formaat. Het onderstaande VBA script is algemeen te gebruiken om een willekeurige internet pagina om te zetten naar PDF . Het kan aangeroepen worden vanuit elk Office pakket. Zelf heb ik het gemaakt in Access . Voor het omzetten van de internet pagina naar PDF heb ik gebruik gemaakt van de PDFCreator . Dit kan gratis worden gedownload:  http://www.pdfforge.org/ De PDFCreator kan als printer worden aangeroepen met VBA . Binnen dit programma kunnen we een aantal zaken instellen zoals de map waarin we de PDF's willen opslaan. Het VBA script: 'functie nodig om de standaard printer aan te passen Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long Sub printURL()     Dim objExplorer As Object     Dim lngQuery As Long 'geef waarde QueryStatusWB...

The other day I ran in to a problem with Report Builder . I had a number of multi value parameters with a lot of values. And there was a single value parameter for selecting items too. The report rendered fine in design mode. But as soon as I published it, it was no longer rendering properly. The mutli value parameters also contained values which would not give any result at all given a certain item number. But then again, in design node no problem. The solution I finally came up with was to interrelate the multi value parameters to the single value parameter by using a SQL   subquery. Let's look at this example I created based on the NorthWind database. My main dataset: SELECT        Customers.CustomerID, Customers.CompanyName, [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,                          ...

A short while ago, I got the request to filter on a field which contained, beside regular values, also NULL values. They wanted to be able to filter on NULL values as well as regular values. Choosing multiple values should be an option as well. The direct functionality of  Report Builder did not offer me a solution. SQL was needed. My example is based on the Northwind database of  SQL Server . I created two datasets. dsRegion provides the data for the parameter parRegion . The  SQL looks like this: SELECT region FROM customers UNION SELECT ' Empty'  ORDER BY region Before ' Empty'  I have put a space to get it at the top when sorted. The parameter looks like this: Allow multiple values is checked. Available Values is just the region in the Value field . The SQL for the second dataset, dsCustomer , looks like this: SELECT * FROM   Customers WHERE ISNULL(region,' Empty')  IN (@parRegion) Mind the space before Empty . N...

The report I created here contains no data from an external source. The numbers we see, all come from calculations based on the figures given via the input parameters. The expressions look like this: Max Deviation: =Code.BerekeningNorm(Parameters!parNormAfwijking.Value,Parameters!parProfielLengteNorm.Value) (based on the upper two paramaters) Requirement : =Code.Berekening(Parameters!parMaxAfwijkingNorm.Value,  Parameters!parProfielLengteNorm.Value)  (based on lower parameter and the right upper one) The function code behind the report: Function Berekening(MaxAfwijkingNorm as Integer,ProfielLengteNorm as Integer) as Double Berekening = MaxAfwijkingNorm ^2 Berekening = Berekening + (ProfielLengteNorm / 2)^2 Berekening = Berekening / (2 * MaxAfwijkingNorm) Berekening = Berekening - ((Berekening^2) -  250000)^(1/2) End Function Function BerekeningNorm(NormAfwijking as Double,ProfielLengteNorm as Integer) as Double BerekeningNorm=NormAfwijk...

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 Fi...

Working on a Crystal Reports job and a Report Builder job at the same time lately. So time to continue my comparison of the two tools. This time the way how they deal with database fields. Which fields are used in the report? This is something indicated automatically by Crystal Reports : I simply have to look for the green checkmarks . In Report Builder I have no indication whatsoever, so I just have to look and look and look ... What is the field type? In Crystal Reports this is something you can simply switch on or of, Show Field Type : In Report Builder I have found no other way that to look it up in the database: Sorting the fields of large tables With large tables in can really speed things up when you can sort the field order. In Crystal Reports there is an option to do so: Sort Fields Alphabetically No such thing in Report Builder unfortunately. What kind of data does a field contain? In Crystal Reports you have th...

It does not look like a question easy to answer. First of all, how can you actually compare Reporting Tools? I tried to do this using three variables: Easy to use (indicated by the size of the bubble) Fit to create Standard or Ad Hoc reports (indicated by the postion on the X-axis) Precise design features (indicated by the position on the Y-axis) Of course, you could look at other features as well like looks learning curve I am just starting this by postioning the tolls I am very familiar with: Excel Access Crystal Reports Report Builder / Reporting Services And we have more: Qlikview Cognos Report Studio

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...

Crystal Reports In Crystal Reports a Boolean Field   type is simply represented by the words True or False when you include such a field in your report. Right-click the field. Choose  Format Field . Choose the tab  Boolean . Nice, but not as nice as when you would change the True and False into checked or unchecked check boxes. Right-click the field. Choose  Format Field . Choose the tab  Font . Pick the font Wingdings . Choose the tab Common . Click the X+2 after Display string . Fill in the Formula: if {Products.Discontinued}= true then     chr(254) //checked checkbox else     chr(111) //unchecked checkbox Click OK . Then we will get: Report Builder / Reporting Services Report Builder will normally also represent a Boolean Field type as either True or False .  In order to change it here, add a column to the tablix. Right click it. Choose Expression . ...

Crystal Reports In Crystal Reports you can easily create a Running Total based on a numeric field. We'll give a simple example. right click the numeric field. choose  Insert . click Running Total . We'll get: We could now simply click OK, and our Running Total is there. We can also click Use a formula under Evaluate . Click the X+2 button. Then fill in the formula: {Order Details.Quantity}>100 Now we would have a conditional Running Total , only totaling the values over 100. SS Reporting Services / Report Builder We'll now try the same example in Report Builder. To our table we have to add a column. Right-click this column. Click Expression .  We'll get: There fill in the formula: =RunningValue(Fields!Quantity.Value,SUM,"dstOrder") Clicking OK would give us a column with a Running Total . We still need to add the co...

Crystal Reports In Crystal Reports it is quite easy to define alternating row colors. You just go to the Section Expert . Click Details . Choose Color . Click the formula button x+2. There you fill in a formula like this: if recordnumber mod 2 = 0 then     crAqua else     crNoColor When you want to alternate group colors, in stead of clicking Details , you click a Group header or footer . The formula is just slightly different: if groupnumber mod 2 = 0 then     crAqua else     crNoColor SS Reporting Service / Report Builder In SSRS or Report Builder we can do the same but is just a little bit more complicated. First take a look at row coloring. Select a cell in a row. Right click. Select Text Box Properties . Select Fill.  Click fx behind Fill color . Type the Formula.   =IIf(RowNumber( Nothing) Mod 2=0, "Silver", "Tran...

Crystal Reports 2011 In Crystal Reports it is quite easy to reset the page number for every new group. We can do this by using the Section Expert: Presenting the page number and the total page number per group is now quite easy. We could do so by using for instance a formula consisting of special fields: PageNumber & " "& TotalPageCount But we'll have a problem when we want to add the overall total pagenumber . Crystal Reports does not have an overal total pagecount special field. The only way to solve this problem - to my knowledge - is using a combination of a subreport and a shared variable . Report Builder / SS Reporting Services In Report Builder we can use the Built-in Fields for solving this problem. When we want the page numbers per group, we use PageNumber and TotalPages ; when we want the overall numbers, you use OverallPageNumber and OverallTotalPages . Member Descript...

1        Basiscursus ANSI SQL In dit cursusmateriaal houd ik rekening met de verschillende varianten van de taal SQL. Ik heb deze cursus verschillende malen gegeven in Access and SQL Server Management Studio. Andere tools zou ook geen probleem zijn. Duur cursus: 2 dagen INHOUDSOPGAVE ANSI SQL 1                 Inleiding                                                                                           ...