I ran into a very odd problem today. I was programming some VBScript into a ShelbyQUERY report design, and I wanted to give the user a Yes/No option using a MsgBox() function. Normally I just use MsgBox() to inform the user of things or to double-check my own work during development by popping up variable values during execution. So normally the only button to click is "OK," and the program resumes after the click.
But today I wanted to know if the user clicked "Yes" or if he clicked "No," so I changed the parameters and tried to capture the value of the click. But I started to get unexpected results, and I got the same thing if I clicked "Yes" or "No," either one.
After doing some digging and double-checking the value being returned from the MsgBox() buttons, I discovered that no values were being returned. I tested this with a much simpler case that was not part of the main script, and sure enough, it verified that the MsgBox() function is returning no values to the main script.
I believe the problem is not with VBScript per se, but with the implementation of it in the Report Designer (the Shelby v.5 flavor of ActiveReports). InputBox() works just fine, but MsgBox() does not, apparently.
So, when you are writing VBScript for the ShelbyQUERY Report Designer, my advice to you is to use MsgBox() only for informational pop-up messages and not for capturing responses. InputBox() is the only way to capture a response from the user, even when you just need a "yes/no" response.
About Me
Tuesday, November 2, 2010
Monday, November 1, 2010
Using Variables in T-SQL
Although there is no way to create a "pop-up" requester using Transact-SQL on its own, there is a way to handle frequently changing parameters that makes them easier to maintain over time. Instead of leaving them buried down in the WHERE clause, which is usually near the bottom of the query and can be sandwiched between the FROM clause and the GROUP BY clause (if there is one), it is possible to move parameters to the very top of the query, even above the SELECT clause, by using variables. Using variables in a query is a three-step process:
First, define the names of the variables and what type of data they will contain.
Second, set the values of the variables.
Third, reference the variables in the query wherever you normally would use the explicit value.
The first step is to define the names and types of variable you are using. There is one naming convention you must follow. The name of the variable must begin with an @ symbol. After that, any combination of letters and numbers is allowed. The variable may have any appropriate name. The datatype may be any valid datatype supported by SQL Server. There is a list of them at http://msdn.microsoft.com/en-us/library/ms187752(SQL.90).aspx. The syntax for defining the names and types is as follows:
DECLARE @variable AS datatype [, @variable2 AS datatype ... , @variableN AS datatype];
You may declare as many variables as needed in one DECLARE statement. Note the semicolon at the end of the statement. It is usually optional, but it clarifies the end of a SQL statement.
After defining the variables, you set their values. Unlike the DECLARE satement, the SET statement may only affect one variable at a time:
SET @variable = value;
Again, the semicolon at the end is usually optional. However, if you are going to use a Common Table Expression to define a "view" before the main query, you must include the semicolon after the SET statement.
Finally, you use the variable in the query. Put the @variable reference anyplace where you would normally type in the specific value. In the future, you can update the value by changing the variable assignment in the SET statement instead of searching through the query for the explicit value.
Here is an example of using two variables, @StartDate and @EndDate to search for all the giving recorded in the CNHst table between two dates.
Even in this simple example, you can see that it is easier to update the dates at the top of the query rather than updating them in the WHERE clause. Once you are comfortable using variables, you will find them immensely helpful in making your queries easy to update over time.
First, define the names of the variables and what type of data they will contain.
Second, set the values of the variables.
Third, reference the variables in the query wherever you normally would use the explicit value.
The first step is to define the names and types of variable you are using. There is one naming convention you must follow. The name of the variable must begin with an @ symbol. After that, any combination of letters and numbers is allowed. The variable may have any appropriate name. The datatype may be any valid datatype supported by SQL Server. There is a list of them at http://msdn.microsoft.com/en-us/library/ms187752(SQL.90).aspx. The syntax for defining the names and types is as follows:
DECLARE @variable AS datatype [, @variable2 AS datatype ... , @variableN AS datatype];
You may declare as many variables as needed in one DECLARE statement. Note the semicolon at the end of the statement. It is usually optional, but it clarifies the end of a SQL statement.
After defining the variables, you set their values. Unlike the DECLARE satement, the SET statement may only affect one variable at a time:
SET @variable = value;
Again, the semicolon at the end is usually optional. However, if you are going to use a Common Table Expression to define a "view" before the main query, you must include the semicolon after the SET statement.
Finally, you use the variable in the query. Put the @variable reference anyplace where you would normally type in the specific value. In the future, you can update the value by changing the variable assignment in the SET statement instead of searching through the query for the explicit value.
Here is an example of using two variables, @StartDate and @EndDate to search for all the giving recorded in the CNHst table between two dates.
declare @StartDate as datetime, @EndDate as datetime
set @StartDate = '1/1/2010';
set @EndDate = '3/31/2020';
select
C.NameCounter, sum(C.Amount) as TotalGiving
from
Shelby.CNHst as C
where
datediff(d, @StartDate, C.CNDate) >= 0 and datediff(d, C.CNDate, @EndDate) >= 0
group by
C.NameCounter
Even in this simple example, you can see that it is easier to update the dates at the top of the query rather than updating them in the WHERE clause. Once you are comfortable using variables, you will find them immensely helpful in making your queries easy to update over time.
Monday, August 23, 2010
Simple BFS Query for YTD/Annual Values and No Projects
I was recently asked to help a customer with a Budgeted Financial Statement type query that could be pushed to the Shelby v.5 Dashboard. The customer was only looking for year-to-date actual values and annual budget values. There was no need to break out by Project code. Such a request greatly simplified the query writing process over what I have been doing and documenting in previous posts. Without needing to break out Project codes I could use the GLSummary table, which already aggregates the actual transactions for each period for each account. Without needing to do any specific period reporting, I could use the GLBudget table in its native form, without unpivoting it into periodic rows. For this project I also ignored budget revisions, which further simplifies the query.
I have also discovered recently that ShelbyQUERY supports the use of WITH to define inline views before the main query's SELECT clause, which is great because if you need the same view multiple times in the main query, you can use an alias instead of typing the whole thing each time. It also moves the bulk of the view syntax out of the FROM clause, meaning that the main query's FROM clause is much more readable. One thing to remember if you are using WITH to define an inline view is that any lines of SQL code that come before it must be terminated with a semicolon. The only thing that might come in front of that in ShelbyQUERY would be variable DECLARE and SET commands. Generally the semicolon is optional for DECLARE and SET, but if there is a WITH, the semicolon is not optional.
In the query below I used a WITH statement to define the subquery that adds the columns that make it possible to do Total Line aggregates to the basic GLAcct table. I have already explained how that piece works in previous posts. I also have five variables so that the user can define a fiscal year, a company number, a fund number range, a department number range, and whether or not the Period 13 (commonly called the audit period) will be included in the calculations.
declare @FiscalYear as smallint, @CoNu as tinyint, @FundStart as smallint, @FundEnd as smallint, @DeptStart as smallint, @DeptEnd as smallint, @UsePeriod13 as varchar(1);set @FiscalYear = 2008;set @CoNu = 1;set @FundStart = 0;set @FundEnd = 999; set @DeptStart = 0;set @DeptEnd = 999;set @UsePeriod13 = 'Y'; -- Change to 'N' to exclude Period 13
with GLAccounts as (select A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, LineType = A1.LineType, Descr = max(A1.Descr), StmtType = max(A1.StmtType), T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end from Shelby.GLAcct as A1 left join Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1 where year(A1.BeginDate) = @FiscalYear and A1.CoNu = @CoNu and A1.FundNu between @FundStart and @FundEnd and A1.DeptNu between @DeptStart and @DeptEnd and A1.StmtType in ('I', 'E') group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType)
select FiscalYear = @FiscalYear, FundNu = GLAccounts.FundNu, DeptNu = GLAccounts.DeptNu, AcctNu = GLAccounts.AcctNu, AcctDescription = GLAccounts.Descr, YTD_Actual = case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 When 'Y' then GLSummary.Amt13 else 0 end when '0' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T0) when '1' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T1) when '2' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T2) when '3' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T3) when '4' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T4) when '5' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T5) when '6' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T6) when '7' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T7) when '8' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T8) when '9' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T9) end, Annual_Budget = case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end when '0' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T0) when '1' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T1) when '2' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T2) when '3' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T3) when '4' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T4) when '5' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T5) when '6' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T6) when '7' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T7) when '8' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T8) when '9' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T9) end, Annual_Budget_Remaining = (case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end when '0' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T0) when '1' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T1) when '2' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T2) when '3' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T3) when '4' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T4) when '5' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T5) when '6' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T6) when '7' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T7) when '8' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T8) when '9' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T9) end) - (case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 When 'Y' then GLSummary.Amt13 else 0 end when '0' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T0) when '1' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T1) when '2' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T2) when '3' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T3) when '4' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T4) when '5' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T5) when '6' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T6) when '7' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T7) when '8' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T8) when '9' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T9) end)from GLAccounts left join Shelby.GLSummary as GLSummary on GLAccounts.CoNu = GLSummary.CoNu and GLAccounts.BeginDate = GLSummary.BeginDate and GLAccounts.FundNu = GLSummary.FundNu and GLAccounts.DeptNu = GLSummary.DeptNu and GLAccounts.AcctNu = GLSummary.AcctNu left join Shelby.GLBudget as GLBudget on GLAccounts.CoNu = GLBudget.CoNu and GLAccounts.BeginDate = GLBudget.BeginDate and GLAccounts.FundNu = GLBudget.FundNu and GLAccounts.DeptNu = GLBudget.DeptNu and GLAccounts.AcctNu = GLBudget.AcctNu
order by FundNu, DeptNu, AcctNuSaturday, July 31, 2010
Querying the General Ledger with Total Lines - Actuals
In the earlier post on this topic I included a query that would add ten columns to the basic GLAcct table. These extra columns would allow you to summarize by Total Line right in a query. In this post I will share a query that will allow you to get the period actuals for all the accounts, including total lines. It will also provide extra lines for Project values. If you want to remove the Project level detail, just remove the Project line from the SELECT statement and from the GROUP BY statement.
Most reports need to be able to report on one or more periods, so in order to generate either a pivot table with the ability to pull any period or create a Report Designer that can prompt the user for a period, we need to multiply the accounts in GLAcct thirteen times, one for each possible Period (don't forget about the Audit period, which is period 13). To do this, we will add in a Cartesian Join between GLAcct and an inline view that is nothing but the numbers 1 through 13:
select *from Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as PeriodsWe then substitute this in place of the reference to Shelby.GLAcct in the original Total Line query given in the previous post, and we have this (above query in italics below):
select A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType, Descr = max(A1.Descr), StmtType = max(A1.StmtType), A1.Period, T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 endfrom (select * from Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as Periods) as A1 left join Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType, A1.PeriodNow we need to take the entirety of this result and use it as an inline view, add a join to the actual transaction history table, which is GLDetail, and then construct a SELECT clause that can sum up the numbers as needed. Here is that query. (Warning: This only works on SQL Server 2005 or later. If you are running MSDE or SQL Server 2000, this will not work for you.) Also, this query will return all fiscal years of all companies. This may take a long time to run (15 mintues + for some organizations). Add a WHERE clause to limit the results to just one fiscal year and company to shorten the time. (The above query is in italics below.)
select A.BeginDate, A.CoNu, A.FundNu, A.DeptNu, A.AcctNu, Descr = max(A.Descr), A.Period, Project = (select P.Name from Shelby.SSProject as P where P.ProjectCounter = D.ProjectCounter), PeriodActual = case A.LineType when 'D' then isnull(sum(D.Amt), 0) when '0' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T0), 0) when '1' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T1), 0) when '2' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T2), 0) when '3' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T3), 0) when '4' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T4), 0) when '5' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T5), 0) when '6' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T6), 0) when '7' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T7), 0) when '8' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T8), 0) when '9' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T9), 0) endfrom (select A1.CoNu,A1.BeginDate,A1.FundNu,A1.DeptNu,A1.AcctNu,A1.LineType,Descr = max(A1.Descr),StmtType = max(A1.StmtType), A1.Period, T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end from (select * from Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as Periods) as A1 left join Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1 group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType, A1.Period) as A left join Shelby.GLDetail as D on D.BeginDate = A.BeginDate and D.CoNu = A.CoNu and D.FundNu = A.FundNu and D.DeptNu = A.DeptNu and D.AcctNu = A.AcctNu and D.Period = A.Periodgroup by A.BeginDate, A.CoNu, A.FundNu, A.DeptNu, A.AcctNu, A.LineType, A.Period, D.ProjectCounter, A.T0, A.T1, A.T2, A.T3, A.T4, A.T5, A.T6, A.T7, A.T8, A.T9order by A.BeginDate, A.CoNu, A.FundNu, A.DeptNu, A.AcctNuThis approach uses the windowing function sum() over (partition by ...) to give a sum of the sums for the total lines. It uses the T0 through T9 columns as the leverage it needs to get the sum values. Windowing functions are great ways to do aggregate values on a scalar set of results or to aggregate an aggregate, as I did here.
This only gets the actual values. Getting the budget values is actually a bit harder, especially with a Project breakout. I'll post more query solutions with budget values in them later.
If you have questions, feel free to post a comment. I'd love to know if this blog is meeting anyones needs or at least of interest to some people.
Tuesday, July 27, 2010
Another Quick Aside - On Finding Server References
A customer asked today about finding references in the Shelby v.5 system to their old server. Now that his organization had a new server, they needed to update all the old server references in the system to the new server name.
The primary (though certainly not the only) places where the old server name is stored are the output file paths of reports in Selections & Listings. To find these reports and other places that reference the server path, use the following query:
select ItemType = 'Report', Title from Shelby.SSReports where Reports like '%server_name_here%'unionselect ItemType = 'Preference', ControlKey from Shelby.SSControl where Memo like '%server_name_here%'order by ItemType
This will provide a simple list. The Report lines will tell you which reports refer to the server you specified. The Preference lines will tell you where to look for preferences that reference the old server name.
Monday, July 26, 2010
An Aside on VBA and InStrRev
I had an interesting assignment come my way today, and I want to take a moment to write about it here. I will get back to the task of describing the General Ledger budgeted financial statement queries soon.
I have a copy of VB & VBA in a Nutshell by O'Reilly Publishers. I have not had much use for it recently, because the report design tool uses only VBScript, not pure VB or VBA, as its script platform. Like most O'Reilly books of its type, it provides useful insight into the "whys" of the language, and gives "Programming Tips & Gotchas" for most of the language elements. For one function in VB6, however, the writers clearly thought it had no practical use. The function is InStrRev, and their "Programming Tip" is this:
"The usefulness of a function that looks backward through a string for the occurrence of another string isn't immediately apparent."
This seems like a courteous way of saying, "This function is useless." Ah, but I beg to differ. In fact, the task I was given today was greatly simplified (if not in fact made possible) by this function. Today I was tasked with figuring out a way to take a single column of data in Excel with City-State-ZIP (strCSZ) value and separate it into three columns, one for each part of the address.
The strCSZ value had no punctuation, so only spaces separated each element. Also, the state is always just the two-letter abbreviation. At first blush, one could think that breaking the string into pieces by finding the space character would work, but this fails because some city names are actually two words, separate by a space. Working forward from the first character of the string, one could never know if the first space divides the city from the state or if it divides the first word of the city from the second word.
However, working backward from the last character of the string, the first space would always be the breaking point between the state and the ZIP code. Once that is a known value (calculated with the InStrRev function), it is a simple matter of using Mid three times to get each piece of the address. Here is a bit of the script I wrote:
intSpace = InStrRev(strCSZ, " ") strCity = Mid(strCSZ, 1, intSpace - 5) strState = Mid(strCSZ, intSpace - 3, 2) strZIP = Mid(strCSZ, intSpace + 1, 10)I want to thank the VB6 developers for including InStrRev, even though its usefulness was not "immediately apparent." It sure came in useful to me!
Tuesday, June 29, 2010
Querying the General Ledger with Total Lines
UPDATED AGAIN (7/30/2010): Corrected WHERE clause to fix problem with TotalLine designations.
UPDATED: With a shorter version of the query and added explanation of the new approach.
Recently, however, I was pulled in a different direction by a customer request to display a fairly simple set of columns from the GL tables on the Shelby v.5 Dashboard. The customer was looking for a basic year-to-date summary of budget information, like a simplified Budgeted Financial Statement report. This request gave me an opportunity to revisit a difficulty with General Ledger queries that I have struggled for years to overcome, only to be forced to rely on VBScript in the Report Designer to work around: the problem of total lines in the chart of accounts.
Total lines are difficult to deal with for three main reasons:
- They are fundamentally different than detail lines, yet still need to display a value like detail lines do.
- They are arbitrary, at least in principle, making it impossible to predict where they occur in any given chart of accounts.
- They are hierarchical, generating a nesting effect that means overlapping group values.
These properties of total lines have defied my query abilities for years. Until I worked out the solution I'm about to share, I used VBScript to calculate the total line values on a row-by-row procedural approach. This was not only inefficient and a bit of a kluge, but it meant that the only way to include total lines was to use the Report Designer; there could be no totals in the basic set of query results.
I took the recent customer request as a fresh start, and I thought through the problem from scratch. The first observation I made was that total lines display aggregate information; therefore, some form of grouping would have to be involved. This immediately led to two conclusions: the GROUP BY clause would be used to calculate the values; and the final query would have to use subqueries for the total lines, in order to preserve the scalar detail lines while including aggregate total lines in the same set of results.
Focusing first on the GROUP BY question, I had to tackle the question: GROUP BY what? In order to be able to group a set of rows, they have to share a common value. This common value would have to be related in some way to the total line level (0 through 9). I took the simplest case to think through: a single total line near the top of the chart of accounts; i.e., no total lines above it and the top of the chart of accounts. In order to calculate the total line, all the detail rows before the total line need to share the same value, and all the rows after the total line need to be a different value. Because I would need to trigger the grouping based on the total line itself, it would also be best for the total line itself to share the same value with the rows above it.
Looking at the aggregate functions available, it seemed that the most likely candidate for giving the value I needed was COUNT(). I could count the number of total lines with an account number less than or equal to the account number of the total line itself. All the lines before the single total line would be 0 (zero) and all the lines after it would be 1 (one). The total line itself would have a COUNT() value of 1, so I would need to subtract 1 from the COUNT() on the total line, so it can share the value of the rows above it.
I wrote a query using COUNT(), but it required a separate subquery for each Total Line rank 0 - 9. This meant having ten subqueries running in the SELECT statement. I later revised my approach to use a non-equi-join between two instances of the GLAcct table. This new query uses SUM() in place of COUNT(). Inside the SUM() function is a CASE statement to add 1 only for Total Line types. This has the same effect of counting the number of total lines. Because I want the total line to share the same value as the detail lines above it, I am also adding one to the SUM() for the detail lines. This works because the non-equi-join returns all the Total Lines from instance 2 that appear at or before each fund/dept/account number in instance 1.
In the GLAcct table, which contains the complete chart of accounts for each company and fiscal year, the total lines are easily distinguished from other types of lines by the LineType column. Total lines have a numbered value, ranging from 0 to 9, representing the total line level. So the SUM() function should only add 1 for the rows with a numbered value. Because a total line of a certain level should ignore any total lines of levels less than its own (a total level 5 should ignore total levels 0 through 4, for example), each total line level must be handled separately, resulting in a separate column of values for each total line level.
At the end of the process, here is the query. It takes the GLAcct table and adds 10 columns to it, one each for total levels 0 through 9. Each column yields a common value for the detail lines that should be summed together for each total line of its level. The counting is reset for each company-and-year chart of accounts.
select A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, LineType = A1.LineType, Descr = max(A1.Descr), StmtType = max(A1.StmtType), T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end, T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 endfrom Shelby.GLAcct as A1 left join Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineTypeOnce this query has been saved as a view, it can be used in place of GLAcct to provide queries on the GL tables the ability to pull out the total line sums. This is only a building-block for a complete solution. My next post will show how to take this building block and join it to General ledger transaction history and budget information, taking a step closer to a full query solution.
Subscribe to:
Posts (Atom)