Conditional SQL in Cognos by using prompt values to control line commenting

Here's a technique that allows the omission of certain sections of SQL in response to prompt values in Cognos Report Studio:

1 #join('|',substitute(prompt('Prompt','token','Excl'),'',grep(prompt('Prompt','token','Excl'),array('Incl', 'Excl /* '))))#

2 And column_name is not null 
3 #join('|',substitute(prompt('Prompt','token','Excl'),'',grep(prompt('Prompt','token','Excl'),array('Incl', 'Excl */ '))))# 

The prompt named Prompt has two values, Incl and Excl.

When the prompt value is 'Excl' the grep macro in line 1 returns the array element 'Excl /* '. The substitute macro replaces the array element with an array element containing ' /* '. The join macro converts the array element to a  string. /* is used as the start of a comment block in Cognos.

Line 3 resolves to */ when the prompt value is 'Excl' thus commenting out line 2 entirely.

Functions available with Text File ODBC driver

I can't find documentation anywhere.  The functions available seem to be mainly VB Scipt functions. This is my work in progress list and some comments thereon.  I refer to VB Script documentation for questions on syntax.

http://msdn.microsoft.com/en-us/library/3ca8tfek(VS.85).aspx


Use [ ] or `` with field names.  (` is the character on the key to the left of the number 1 at the top of the keyboard)

isnull() returns true or false. It is a good idea to use iif(isnull(<field>),0,<field>) to set a value for null entries if you might ever want to perform aggregation on the field.

mid()
left()
right()
strcomp()

Concatenate using + e.g. 'Dropped'+' '+'Pennies'

Use single quotes (not double quotes) for text.

year('2009-12-31') = 2009
month('2009-12-31') = 12
day('2009-12-31') = 31
dateadd('yyyy',1,`Date`)
dateserial()
datevalue()
now()
weekday()
isdate()
isnumeric() (watch for nulls)
isempty()
len()
lcase()
ucase()
trim()
ltrim()
rtrim()
string()
chr()
asc()
instr()
rnd()
str() converts number to string (leaves space for minus)
space()
sgn()
abs()
round()
CBool()
CByte()
CCur()
CDate()
CDbl()
CInt()
CLng()
CSng()
CStr()
timer()
typename()

Creating virtual records with Text File ODBC driver

Sometimes it can be useful to be able to add a virtual record to a recordset derived from a text file without actually adding a physical record into the file.

With the text file ODBC driver the table name can be omitted such that 

SELECT 'Hello world!' as [Greeting]

results in

Greeting
Hello world!

Consider a names.txt file with first_name and surname columns. You could add John Doe onto the query output by doing something like

SELECT * FROM [names.txt]
UNION ALL
SELECT 'John' as [first_name]
, 'Doe' as [surname]

CurrencyPosFormat and CurrencyNegFormat settings in Schema.ini

There are 4 possible values for CurrencyPosFormat in a schema.ini file:

• Currency symbol prefix with no separation ($1)
• Currency symbol suffix with no separation (1$)
• Currency symbol prefix with one character separation ($ 1)
• Currency symbol suffix with one character separation (1 $)

However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 3
i.e. CurrencySymbol=£ and CurrencyPosFormat=1 will result in a setting of 1£.

There are 16 possible values for CurrencyNegFormat in a schema.ini file:

• ($1)
• –$1
• $–1
• $1–
• (1$)
• –1$
• 1–$
• 1$–
• –1 $
• –$ 1
• 1 $–
• $ 1–
• $ –1
• 1– $
• ($ 1)
• (1 $)

However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 15
i.e. CurrencySymbol=£ and CurrencyNegFormat=15 will result in a setting of (1 £).

So far as I can tell CurrencySymbol=£ and CurrencyPosFormat=1 will cope with either 1£ or just 1 in a data file.  However, writing to a file with CurrencySymbol=£ and CurrencyPosFormat=1 specified will result in 1£ format for currency output.

I hope this saves someone some heartache.

By the way, I've been unable to get the CurrencyThousandSymbol in schema.ini to work at all.  I've given up trying at the moment.  I'd love to hear if anyone else gets it working.

P.S. It's CurrencyThousandsSymbol not CurrencyThousandSymbol! The Microsoft documentation is wrong! This works for reading files but not writing them. I'll keep looking at this from time to time.  BTW the international thousands separator (a non-breaking space) doesn't seem to work.  Again I'll report back if I find otherwise.

ODBC Text File Driver with Unicode files

I use ODBC links to text files on a regular basis and often have to join files coming from different systems.  Recently I had to join a Unicode file with an ANSI encoded one.  Simple, you’d think, as the scant documentation on the text files ODBC driver does mention dealing with Unicode.  However, the Define Text Format dialog used to configure the ODBC text driver (results stored in schema.ini) has only two radio buttons: ANSI and OEM.  I couldn’t get either to work with the Unicode file and so had to convert it to ANSI format before I could use it.  A real pain.  Then a eureka moment.  Manually edit schema.ini for the Unicode file entry to include the line CharacterSet=UNICODE.  It works! 

After spending ages on this I was frustrated to find a document called "Initializing the Text Data Source Driver" in information relating to Microsoft Office Access 2003.  The discussion appears to be about the Microsoft Jet database engine but it confirms that CharacterSet can also contain UNICODE or the code page number of a specific character set (e.g. 1232)

The relevant document is at:

http://office.microsoft.com/en-gb/access-help/initializing-the-text-data-source-driver-HP001032166.aspx?CTT=5&origin=HP001032168

Passing prompt parameter displayValue in URL to Cognos

I'd been used to passing prompt parameter values for Cognos reports in a URL by just simply using &p_<parameter_name>=<value>.

However, I came across a report where both the useValue and displayValue of the parameter were involved in the final output and I discovered that using &p_<parameter_name>=<value> seems to result in <value> being used as both the useValue and displayValue.  This was no good as the report required the two different values as set up on the prompt page in the report.

After endless searching and guesswork I eventually stumbled across how to send the two values separately in a URL:

&p_<parameter_name>=<selectChoices><selectOption useValue="<useValue>" displayValue="<displayValue>"/></selectChoices>

Commenting Cognos Report Studio expressions

I'd always wanted to be able to comment some of the more complex expressions I've written to aid subsequent maintenance.  Eventually I stumbled across how to do it (I think it might have been on COGNOiSe) and I have been using it liberally ever since.

#/* Insert comment here */#

Real Asset Management (RAM) Asset4000 software journal configuration

A client was struugling with journal configuration in Asset4000 - they just couldn't seem to get the debits and credits right for journals to deal with revaluation of non-current assets. 

Revaluation journals refer to four RAM database fields to generate the journal entries.

The fields are:

• GCRC Alteration
• Acc Depn Alteration
• MHCA Charge to Reval Reserve
• MHCA Charge to Impairment Reserve

It is important to understand that the signed figures contained in these fields do not consistently reflect the accounting significance of the figures.  For example, when an asset is indexed upwards the accounting entries will be Dr Cost or Valuation and Cr Acc Depn (i.e. opposite accounting significance) but both the GCRC Alteration and Acc Depn Alteration fields will contain figures with the same signage.

The Dr and Cr radio button options on the journal configuration screen do not actually mean debit and credit.  Rather, Dr means “attribute accounting significance to the figure held in the underlying database column and use that figure unadjusted” (where a positive figure is a debit and a negative figure is a credit), whilst Cr means “flip the sign on the figure held in the underlying database column and attribute accounting significance to the adjusted figure”.

The following table shows the effect of the variable signage of the database columns and the implications for the appropriate radio button settings:

Database Column	+ve figure in the database column represents a	Revaluation up of IR asset would be recorded as	When configured with a	Accounting result
GCRC Alteration	Dr	+	“Dr”	Dr
Acc Depn Alteration	Cr	+	“Cr”	Cr
MHCA Charge to Reval Reserve	Cr	+	“Cr”	Cr
MHCA Charge to Impairment Reserve	Dr	-	“Dr”	Cr

Database Column	+ve figure in the database column represents a	Revaluation down of RR asset would be recorded as	When configured with a	Accounting result
GCRC Alteration	Dr	-	“Dr”	Cr
Acc Depn Alteration	Cr	-	“Cr”	Dr
MHCA Charge to Reval Reserve	Cr	-	“Cr”	Dr
MHCA Charge to Impairment Reserve	Dr	+	“Dr”	Dr

Consequently, for all journal configurations, it is important to determine the meaning of signed column contents before it is possible to determine whether it should be configured as “Dr” or “Cr” to achieve the correct accounting output.

Another observation concerning the journal configuration screen is that the Balance Sheet and Profit & Loss radio button options have no accounting significance and could just as easily be renamed as Matrix Set 1 and Matrix Set 2.