A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Friday, September 25, 2009

OnFormat vs. OnBeforePrint in Active Reports

Recently I was asked to assist with an internal report for the Sales and Marketing Department. It might interest you to know that we use Shelby v.5 for our own data management purposes, just like our customers do. The report was put together in ShelbyQUERY and the Report Designer. The report was mostly working fine before I was looped into the project except that there were a few calculated values that were not working as expected. Specifically, the report needed some calculated variances of bound controls that had been aggregated using the SummaryType property of ddSMSubTotal.

In order to calculate the variances, I wrote a VBScript to pull the values for the aggregated Bound Controls in the GroupFooter1 section and to do the necessary math, returning the results of the calculation as the caption property of a label. I put the script to trigger during the OnFormat event, which is the only event I had ever used for Active Report scripts in the body of the document. The process seemed straightforward enough, except that the calculated values that were returned by the script were based on the last scalar value of the field, not the aggregated value.

I almost started down the path of creating script for the Detail section that would sum up the values by leveraging the caption of an invisible label as a place to hold a running sum. But before I did that, I took a step back and looked at other options. One of the options I researched was the event choice for when the script activates. I discovered that the OnBeforePrint event occurs right before the object values are committed to the canvas, but after the object values have been calculated.

That was the "Aha!" moment for me. I changed the script from occurring in the OnFormat event to the OnBeforePrint event. Voila'. The calculations came out based on the aggregate values, just as I wanted to happen. So I learned the following rules:
  • Use OnFormat to work with bound controls before their values are aggregated.
  • Use OnBeforePrint to work with bound controls after their values are aggregated.
If I am not working with aggregated values, I will still prefer the OnFormat event, if for no other reason than it is the default event option.

Incidentally, I also discovered in this process that the way to explicitly reference the value of a bound control is to use its DataValue property. In the past I have used the implicit reference by merely using the bound control's name to stand in for its value. The following two lines of code are functionally identical:

objValue = rpt.Sections("Detail").Controls.Item("Field1")
objValue = rpt.Sections("Detail").Controls.Item("Field1").DataValue

However, because using explicit references is generally better practice than using implicit ones, from this point forward I will give preference to including the DataValue property as part of any reference to the value of a bound control.

Friday, September 18, 2009

Enumerating Options using VBScript

ActiveReports is limited in the ways that a user can input parameters at run-time. At least it is limited in the way it has been implemented for Shelby v.5's ShelbyQUERY application. In fact the only way to capture a parameter at run-time is to use VBScript's InputBox() function. This is serviceable for many contexts, but it can be a problem when the parameter is a long text string, such as the name of an Event from the Registrations application or a company name from the General Ledger. In these cases the user must remember the exact wording of the text and then type it in without any mistakes in order to match the value in the query results exactly.

Error checking can alert the user that the value has been typed incorrectly, but it would be best to help the user along by prompting him or her with what the acceptable values are. In fact it would be ideal to give the user an enumerated list of options, so the user only has to choose the number of the desired value and enter one number instead of typing a long text string. The script below will do just that.

This script should be associated with the ActiveReports Document object and the OnReportStart event. That way it will pop up at run-time and provide a filter for the contents of the report.

After you paste the script into your VBScript editor window, be sure to update the value of the strColumnName variable so that it corresponds to the desired column in your actual query results. Also change the strPromptMessage and strPromptTitle variables so that the InputBox() prompt is worded appropriately for your report.

Sub OnReportStart

Set oDict = CreateObject("Scripting.Dictionary")
Set rsQuery = rpt.DataSource_Shelby_.RecordSet

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

intCounter = 1
strCurrItem = ""
strTestItem = ""
strColumnName = "QueryColumnName"
strPromptMessage = "Enter the prompt message here."
strPromptTitle = "Enter the Prompt Title here"

strCurrItem = rsQuery(strColumnName)
If strCurrItem <> strTestItem Then
oDict.Add intCounter, strCurrItem
strList = strList & vbCR & intCounter & " - " & strCurrItem
strTestItem = strCurrItem
intCounter = intCounter + 1
End If
Loop Until rsQuery.EOF

intChoice = InputBox(strPromptMessage & vbCr & strList, strPromptTitle)
If intChoice <> "" and IsNumeric(intChoice) Then
strItemChoice = oDict.Item(CInt(intChoice))
rsQuery.Filter = strColumnName & " = '" & strItemChoice & "'"
If rsQuery.RecordCount > 0 Then Exit Do Else rsQuery.Filter = ""
End If
If intChoice = "" Then Exit Do



MsgBox("There are no results to use for this report. Please check the query and try again after you have a set of results.")

End If

End Sub

With this code in your ActiveReports document, your end-user will have a much easier time filtering the report without have to double-check the spelling of a long text value and will avoid frustration of locating a tiny typo in the string.

Thursday, September 10, 2009

Padding Numbers with Leading Zeroes

In some contexts a number needs to be padded in order to look right or to work in the desired context. For example, in the Shelby v5 software, the Company Number and Fund Number values are always displayed as three-digit or four-digit values, with padded zeroes for one- and two-digit vlaues. Thus, company "1" is displayed as either "001" or "0001."

Another example from Shelby v5 is when the NameCounter is used to create the path for an individual picture. Pictures are stored with a file name that includes seven digits for the NameCounter value, so the number is padded with however many zeroes are needed to make seven digits.

To show you how each of these situations can be handled, I will do some sample queries on the Shelby v5 table called CNHst. This table contains contribution history, but that is incidental to this exercise. I am using this table because it contains both a CoNu (company number) column and a NameCounter column.

To start with, here is a basic query to pull the values we eventually want to pad with leading zeroes. An example result set appears underneath.


The original values are numeric, and therefore they will always show up with only the digits in the number itself. We cannot "pad" numeric values, so the first step is to change the numeric values into character values, using the CAST() function.

CoNu = cast(CoNu as varchar(4)),
NameCounter = cast(NameCounter as varchar(7))

The only visible change is that the values are now aligned to the left of each cell instead of aligned to the right. However, now that the values have been changed into character values, we can add zeroes to the left by a simple concatenation of a literal text string of zeroes.

CoNu = '000' + cast(CoNu as varchar(4)),
NameCounter = '000000' + cast(NameCounter as varchar(7))

To make each row the same fixed length of four digits (for CoNu) and seven digits (for NameCounter), we need to cut off the extra zeroes. The best way to do this in T-SQL is to use the RIGHT() function to pick up the string of characters starting with the rightmost character and counting left the desired number of digits.

CoNu = right('000' + cast(CoNu as varchar(4)), 4),
NameCounter = right('000000' + cast(NameCounter as varchar(7)), 7)

Now we have uniform output with each number reflected as desired with a fixed number of digits and padded zeroes as needed.

Tuesday, September 8, 2009

Counting Rows

I was travelling last week and didn't get a chance to post, so I'm going to post a "bonus" message today to make up for it. In fact, it was last week when I got an issue of the SQLServerCentral.com newsletter with an article that caught my eye. It was entitled How to Get Table Row Counts Quickly and Painlessly by Kendal Van Dyke. The article points out that the total row counts of all the tables in a database are stored in system tables that may be queried.

I am always interested in getting useful information out of the system tables, especially when it can provide a new perspective on the database itself. If you read my earlier blog entry on finding all the tables that contain particular column names, you probably know that already. The technique in the article is a way to look at all the row counts of all the tables in a list, which could be a great guage to the overall "size" of the database, and it could give insight to sluggish queries, if a table happened to be much larger than expected.

Of course it would be possible to write the simple query to count the rows of one table at a time, like this:

select count(*) from Shelby.NANames

This approach is valid, and it is still the only way to do a count on rows that meet particular criteria, because you can add a WHERE clause to limit the rows. The system table approach always yields the total number of rows, with no such filtering possible.

Nevertheless, getting an overview of the database rows from the system tables can still yield interesting, if not essential, information. Here is the code from Kendal's article, though I encourage you to sign up with sqlservercentral.com and read it for yourself as well.

-- Shows all user tables and row counts for the current database

-- Remove is_ms_shipped = 0 check to include system objects

-- i.index_id < 2 indicates clustered index (1) or hash table (0)

SELECT o.name,


FROM sys.indexes AS i


INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id

WHERE i.index_id < 2

AND o.is_ms_shipped = 0


For Shelby v.5 users, this code can run straight from ShelbyQUERY, so there is no need to have any special querying software running to check out the database. Try it, and take a peek "behind the curtain" of the database.

Posting SQL Code to HTML Pages

This is just a quick post with a simple heads-up when you want to post query code to an HTML environment, such as a web page or a blog entry. I have learned the hard way that the one symbol commonly used in SQL statements can trip up an HTML post: the "less than" symbol. This symbol is <, of course. It seems innocent enough, but in HTML environments the "less than" symbol is ALSO the "open HTML tag" symbol. An HTML page will expect some kind of tag information after that symbol, and when there is not an expected piece of HTML code after it, the results will be undesirable.

Worse, a blog editor such as blogger may lose the unrecognized text that comes after the symbol, causing the need to redo much of the work of creating the query statement. For example, I just had to redo a post I put up a couple of weeks ago because of a "less than" comparison in the query statement. After I posted it, parts of my code disappeared, and when someone tried to use it, she just got an error message. I only heard from one person, but anyone who tried to use it would have gotten nowhere with it. It's fixed now, so don't worry about having to figure out which one it was.

If you want to publish a SQL statement in an HTML environment, you will need to edit the HTML before you publish it. All you have to do is replace the "less than" symbol with the HTML-friendly code of & l t ; (without the spaces; I had to add spaces so the code wouldn't change into the symbol in this post) instead. You might want to change the "greater than" symbol to & g t ; (again, don't put in the spaces) while you're at it.