Thursday, July 28, 2011

Solving the "Cannot display this help file" error with Windows Help (.hlp) Files Under Vista/Windows 7/and later

This blog post provides simple step-by-step solutions to getting Windows Help files to display under Windows Vista/ Windows 7.

NOTE: Microsoft Help Viewer is not installed as standard in any of the newer operating systems (Microsoft is actively discouraging it's use). In order to add this feature to you system consult this Mircosoft Knowledgebase Article;

This gives you the information you need to install the Help viewer on Vista/7/2008/etc. Without it you will not be able to view help files *at all*.

The message which this knol is attempting to solve when viewing a Help file is;

The error message reads;

 "Cannot display this help file. Try opening the help file again, and if you still get this message, copy the help file to a different drive, and try again".

First of all it's just "information" (not an error) and whilst copying it to a drive with different permissions could solve the problem the fact that you're seeing this error in the first place indicates that it might not.

The problem is caused by the file not being from a "known" source and being in a location (on your hard drive/ network share/ USB stick/ etc) where items with a unknown source cannot be run. Windows in effect treats Help Files in the same was as it does with executables. The message is quite correct in that moving it to a location where this restriction isn't enforced will solve the problem, it's just pretty unlikely there is one!

Anyway to solve the problem right-click the file and select "Properties" (usually at the bottom).

I've highlighted the "Security" section. Click "Unblock". Click "OK".

You should now be able to open the file.

Wednesday, July 27, 2011

Noetix: Including the GL Cost in the GMF_Item_Costs Template

This blog post covers how to add in a field called GL_Cost_Item_Period_Total which displays the GL Cost from the GMF.GL_ITEM_CST table for both Release 11i and Release 12 of Oracle E-Business Suite.

The field uses a sub-query as a comparison between adding in the table and doing a sub-query revealed that adding the table causes rows to be excluded from the result set (and therefore is a big Noetix No-No).

The SQL required for Release 11 (in the column definition) is;

  WHERE 1=1
) GL_Cost_Item_Period_Total

By using the MAX value we ensure that only a single record is returned. In our configuration this is correct but you should do some testing to ensure the value is correct in all circumstances (a lot easier than it sounds - just change the SQL above to be MAX(GIC.ACCTG_COST) - MIN(GIC.ACCTG_COST), do a rebuild and look for non-zero valued columns).

The SQL required for Release 12 is;

  WHERE 1=1
) GL_Cost_Item_Period_Total

The change is around the join condition and reflect the changes Oracle has made at Release 12.

The code to test the new view is;

  Item$Item, -- You'll need to change this to your item-identifying flex field
FROM GMFG0_Item_Costs

The complete code for the file called "gmf_item_costs_gl_cost_item_period_total_xu2.sql" which needs to be saved in your Installs directory and linked to from wnoetxu2.sql is below;

-- Template for adding a simple column to the Noetix template table
-- This template can be used for adding columns of type 'COL' and 'EXPR'
-- It MUST NOT be used for column types 'LOOK', 'ATTR' or 'AUTOJOIN'
-- ****************************************************************************
-- File Name:    gmf_item_costs_gl_cost_item_period_total_xu2.sql
-- Date Created: 26-JUL-2011
-- Purpose:
--    To add in a column that shows the GL Cost Total for the Item and Period
-- ****************************************************************************

-- output to .lst file
@utlspon gmf_item_costs_gl_cost_item_period_total_xu2

COLUMN max_from NEW_VALUE max_from_position

SELECT MAX(from_clause_position) max_from
FROM   n_view_table_templates
WHERE  view_label = 'GMF_Item_Costs';

COLUMN max_col NEW_VALUE max_col_position

SELECT MAX(column_position) max_col
FROM   n_view_column_templates
WHERE  view_label = 'GMF_Item_Costs';

-- -----------
INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, gen_search_by_col_flag, profile_option, product_version, created_by, creation_date, last_updated_by, last_update_date)
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GL_Cost_Item_Period_Total' -- column_label
  ,NULL -- table_alias
   '  WHERE 1=1 ' || 
   '    AND GIC.Organization_Id = XMAP.ORGANIZATION_ID ' || 
   ')' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'EXPR' -- column_type
  ,'The total GL Cost associated with the Item and Period. NOTE Do not try and SUM this column as the value will be incorrect' -- description
  ,'N' -- group_by_flag
  ,'N' -- gen_search_by_col_flag
  ,null -- profile_option
  ,'12+' -- product_version
  ,'A Pellew' -- created_by
  ,TO_DATE('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,TO_DATE('26-JUL-2011')) -- last_update_date

INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, gen_search_by_col_flag, profile_option, product_version, created_by, creation_date, last_updated_by, last_update_date)
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GL_Cost_Item_Period_Total' -- column_label
  ,NULL -- table_alias
   '  WHERE 1=1 ' || 
   '    AND GIC.Orgn_Code = XMAP.PROCESS_ORGN_CODE ' || 
   '    AND GIC.ITEM_ID = COSTS.Item_Id ' || 
   ')' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'EXPR' -- column_type
  ,'The total GL Cost associated with the Item and Period. NOTE Do not try and SUM this column as the value will be incorrect' -- description
  ,'N' -- group_by_flag
  ,'N' -- gen_search_by_col_flag
  ,null -- profile_option
  ,'11-11.999' -- product_version
  ,'A Pellew' -- created_by
  ,TO_DATE('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,TO_DATE('26-JUL-2011')) -- last_update_date

INSERT INTO n_view_table_templates
  (view_label, query_position, table_alias, from_clause_position, application_label, table_name, product_version, base_table_flag, subquery_flag, gen_search_by_col_flag, created_by, creation_date, last_updated_by, last_update_date)
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GIC' -- table_alias
  ,(&max_from_position + 1) -- from_clause_position
  ,'GMF' -- application_label
  ,'GL_ITEM_CST' -- table_name
  ,'%' -- product_version
  ,'N' -- base_table_flag
  ,'Y' -- subquery_flag
  ,'N' -- gen_search_by_col_flag
  ,'A Pellew' -- created_by
  ,to_date('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,to_date('26-JUL-2011')) -- last_update_date



NOTE: Updated on the 27th July 2011 removing the "chr(13)" codes, this (ironically) seems to cause the column to not be added to the view. Not quite sure why (and neither are Noetix - as the column does appear in the help) but removing the additional code solves the problem.

Friday, July 22, 2011

SSRS: Deployment Problems With Large Data Models

This blog post describes how to solve the "Maximum request length exceeded" error when deploying large data models; the "maximum" size of the XML file the Report Server is expecting.

The Problem
The problem is easily sumarised by the actual error message;

The text of the message reads;

"There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded."

What this actually means is that you've tried to pass an XML file (Report Model?) to the server which is larger than the maximum size allowed.

The Background/ Duplicating The Problem
The test file that I used to run into this problem has a Data Source View (DSV) that contains about 35 objects. It's actually based on views in an Oracle e-Business Suite schema. The DSV diagram is;

 Apologies for this being a little small but the point is to show you the complexity, not to go into details of exactly what I'm doing. If I look into the project directory I can see that that the two files (the DSV and the Report Model) are 1MB and 3MB respectively;

When I attempt to deploy the project onto the Report Server (Windows 2008R2 running SQL Server 2008R2) I get the error message displayed above.

Fixing the Problem
This isn't actually that hard it just involves a change in the web.config file on the Report Server.

Open explorer and go to;

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Of course if you're installed your software into some strange local directory you'll need to change the above path so that it works for your setup. But then you'll be used to doing that.

Open the file web.config in a text editor (i.e. Notepad);

The bit I've highlighted reads;
httpRuntime executionTimeout="9000"

Change this bit to read;
httpRuntime executionTimeout="9000" maxRequestLength="10240"

This will give you a 10MB size limit. If you need a different value then you can just change the 10240 to something else. 10MB seems a good size but if you have a highly controlled environment you might want to set a value only slighly larger than your largest model.

Once you've mode this change re-boot the server and you will be able to deploy the large model without any issues.

Thursday, July 14, 2011

SSRS: Dynamically Linking Reports Together

This blog post provides a step-by-step guide with a worked example of how to link together two reports without hard-coding report paths (allowing you to migrate reports from Development > Testing > Live without having to worry about changing code to make sure, for example, your Live report is not linking to a Development report).

By far the easiest way to work through this is to look at a specific example.

Let us assume we have two Reports "Journal Lines" (General Ledger) and "Invoice Details" (Accounts Payable). The user has defined a link between the "Journal Lines" report that, if the Journal is the result of an invoice, the user can click on the Invoice Number in the Journal Lines report and be taken to all Invoices that match that Invoice Number in the Invoice Details report.

Our Reporting Services server is structured so that at the Root there is a series of "Live" folders as well as hidden DEV (Development) and TST (Testing) folders which contain reports being worked on with the same folder structure as the Live folders;

What we need to be able to do is code the link between the two reports in the DEV folder in such a way so that when the reports are moved to the TST folder the links automatically re-point themselves.

Linking The Reports
Open the main report (in this case Journal Lines) and select the cell you wish to make clickable to perform the link. Right click it and select "Text Box Properties ...";

Select the "Action" item on the left panel (bottom entry), and then select "Go to URL"

Click on the function button next to the "Select URL" entry box and enter the formula;

=Globals!ReportServerUrl + "?" + Replace(Replace(Globals!ReportFolder, "General Ledger", "Accounts Payable") + "/Invoices Between Two Dates&rs:Command=Render", "/", "%2f")

Click "OK" back through the dialogs to save the change.

This formula uses the global variables ReportServerUrl and ReportFolder to build a URL. The inner replace function swaps the "General Ledger" directory (see first image) with "Accounts Payable" while the outer one replaces all the "/" characters with their HTML equivalent.

Run the report, the link this creates will now map directly to the new report.

Councillors Right of Access to Information and Confidentiality

This blog post summarises the statutory right of access to information available to Councillors across the political spectrum in the UK. It is by no means exhaustive. I am by no means a legal expert! All Councillors should be aware of who is fulfilling the "Monitoring Officer" role at their Council and this is the best person to ask regarding access to information and confidentiality.

Hopefully this will be useful to Councillors (primarily opposition Councillors - of which I am one) across England.

(Of course if you are a member of the public and you want to know something then "asking a Councillor" might actually be useful!)

Executive Summary
Councillors have the right to access any information held by the Council to which they are a member. Some information may be labelled "Confidential" but if that information would be accessible under the Freedom of Information Act then the Councillor is freely able to publish the information.

A Councillors right of access to information held by the Council to which they belong is derived from statute and case-law as follows;

Applying specifically to Councillors;

  • S.100F LGA 1972 and the Local Authorities (Executive Arrangements) (Access to Information) (England) Regulations 2000
  • The common law ‘Need to Know’

Applying to all UK citizens;

  • The Freedom of Information Act 2000 and EI Regulations
  • S.17 Local Government and Finance Act 1982

COMMENT/NOTES: The right of access is NOT the same as the right to publish or otherwise make public. It is perfectly possible for a Councillor to be aware of something that they are unable to make public.

It is vitally important that when Councillors agree to be bound by Confidentiality Clauses in order to gain access to information they also make clear under what conditions the Confidentiality Clause comes to an end (typically the public disclosure of the information by the County Council or a third party where the County Council has publicly responded or is preparing to publicly respond to such a disclosure). The last thing Councillors need is to be effectively gagged by a confidentiality agreement when the information is already public!

Detailed Report
The Right of a Councillor to access information is enshrined in the Local Government Act 1972, specifically (S.100(F) LGA 1972). This provides that any document held by the council containing material relating to any business to be transacted at any meeting of the council, committee or sub-committee must be available for inspection by any member of the council unless it is deemed by the proper officer to fall within certain categories of ‘exempt’ information (under Sched 12A), with the exception of para 3 (except to the extent that the information relates to proposed terms of a contract), and para 6.

Additionally there is a feature of Common Law called the ‘Need to Know’. Under common law principles members of authorities have the right to access information held by the authority where it is reasonably necessary to enable the member to properly perform their duties as a councillor (Ex p Hook 1980). The House of Lords coined the phrase ‘need to know’ in a case involving a councillor who asked for access to a social services adoption file in order to assist in making an informed decision on a housing matter. The HL found that
even though the councillor wasn’t a member of the social service committee they had established a bone fide need to know the information due to their role as a councillor.

In common with all citizens Councillors can also use the Freedom of information Act 2000 (FOI) and EI Regulations to access information. The General presumption is that information should be disclosed unless one of the exceptions contained in the act applies. The various exceptions include things such as legal privileged or commercially sensitive information.

Councillors have a statutory right to inspect any books, deeds, contracts, bills, vouchers and receipts prior to the annual audit (Section 17, Local Government Finance Act, 1982). They also have a right to inspect accounts and to take copies throughout the year (Section 228(3), Local Government Act, 1972).

Often adminstrations (of all political hues) will stamp the word "Confidential" on a document that for one reason or another they do not want to enter the public domain. Just because a document has the word "Confidential" on it it does not *always* mean that a Councillor in possession of the document is not able to make it public (of course some documents marked "Confidential" should remain confidential but it is based on the full content of the document, not just the use of the word Confidential!).

If the information is accessed using the FOI legislation, the information can be regarded as public i.e. the Councillor may share the information with others. In fact many Councils automatically disclose this information on their own websites.

However, if the Councillor has accessed information under the Common law 'Need to Know' power or under S.100F LGA 1972, in some cases the information will be 'confidential' in which case the Councillor will be bound by confidentiality.

In respect of information accessed under S.17 Loc Gov & Finance Act 1982, the position was considered recently by the Courts and the result is unclear and will depend upon the nature of the information.

If the Councillor accesses the information by any means other than FOI, they can check with the Monitoring Officer whether it is accessible by FOI. If the Monitoring Officer confirms that it is, then this supersedes any “confidential” markings on the papers, and the information can be used in public.

Monday, July 11, 2011

Installing Active Directory Tools Under Windows Vista (SP2)

This blog post is a step-by-step guide to installing the Active Directory Tools (i.e. Active Directory Users and Computers) on a Windows Vista machine. It has been tested on Windows Vista Enterprise (32-bit, Service Pack 2) but will probably work with all non-home versions.

First of all you need to download the software from Microsoft. If you type "windows vista remote server administration tools" into Google one of the results it returns will be titled "Microsoft Remote Server Administration Tools for Windows Vista", click on this link. Alternatively a direct link is available here;

You will need to have validated your version of window in order to download/ install it.

NOTE: If you are running the 64-bit version of Windows Vista then this is not the right link for you. You can either go to the directly link;

Or type "Microsoft Remote Server Administration Tools for Windows Vista for x64-based Systems" into Google for the correct link.

If you're unsure of which version you're running then go to the start button, right-click "Computer" and then select "Properties". You'll see something like this;
Look at the "System type:" value and you'll see what version of Windows you're running.

Once you've got the file install it (it's a standard KB update file, you will need Administrator rights on the machine you are using).
After it's been successfully installed go to the Start Menu > Control Panel and select "Programs and Features", then select the "Turn Windowsfeatures on and off" under Tasks on the left;

The "Windows Features" dialog box will be displayed, scroll down to "Role Administration Tools" (under "Remote Server Administration Tools") and select the the following items;

Click "OK" to make the changes.
In order to make finding these under the Start Menu a little easier right-click the Start Button and select "Properties";

Select "Customize ..." and then scroll down the list until you see "System administrative tools" and choose where you want the tools to display;

Under the Start Menu you will now see an "Administrative Tools" option, under this you'll see the new AD Tools you've just installed.
NOTE: Sometimes a reboot is required to pick up these changes!

Thursday, July 7, 2011

SSRS: Working With Oracle Database Parameters In Report Builder 3

This Knol gives a simple example which shows some of the tricks/ ways of working you might like to use then writing SSRS reports against a back-end Oracle system.

The first thing to do is to open Report Builder, create a new dataset, connect to any Oracle database (the SQL we'll be using will run against any system), and then paste in the SQL;

  :Where_Test as TEST_PARAMETER
WHERE 'TEST' = :Where_Test

As you can see this is pretty simple; it is just returning today's date and the parameter that's being used but only if the user passes in 'TEST'.

Give this a run (pass in TEST) and you'll see something like;

Now if you change the parameter you're passing in to 1234 and re-run;

The error is the standard "ORA-01722: invalid number" error and is due to Oracle trying to convert 'TEST' (in the SQL) to a number which of course fails.

You might assume that setting your "Data Type" to Text for the parameter would work;

You'd be wrong. It makes absolutely no difference.

The only way to resolve this problem is to update the original SQL to;

  :Where_Test as TEST_PARAMETER
WHERE 'TEST' = TO_CHAR(:Where_Test)

By adding the TO_CHAR you are converting whatever value is passed into automatically to a character. Running this SQL (with 1234 as the parameter) will give you;

There is no error (there is no result because '1234' is not 'TEST').

You get exactly the same problems with dates.

Monday, July 4, 2011

Noetix: Adding a new Z$ Column Reference

Sometimes you need to add an additional Z$ column to link between two view. This Google Knol tells you what you need to know to make a simple customisation.

NOTE: It's worth pointing out that there are a narrow range of customisations supported by Noetix and this isn't one of them. If you have purchased NCM (Noetix Customisation Maintenance) then it's likely that you can get them to support this enhancement but it's at the discretion of whoever handles your NCM files.

We will be making this change to the template for a view - what this means is if multiple views are generated from the template all will contain the additional column. This is by far the simplest way to add a new column (we shy away from customising individual views).

For the purposes of this example I'll be adding in a link between the PO_Receipts and the PO_PO_Distributions templates, I'm working against Oracle e-Business Suite version 12.1.3 and as things stand at the moment (Noetix views 6.0.1) there is no existing link.

Looking at the SQL of the built view I can see that the table alias POD maps to PO.PO_DISTRIBUTIONS_ALL which is the oracle base table that I want to publish the ROWID from in order to link to PO_PO_Distributions (if you open the target view, from the PO_PO_Distributions template, you can see the existing Z$ column Z$NPLPO_PO_Distributions is based on the PDSTR alias which in turn maps to PO.PO_DISTRIBUTIONS_ALL which is correct).

If you look into look into the N_VIEW_TABLE_TEMPLATES table (which is a Noetix object) you can see that the active query position at R12 for the view PO_Receipts is 2. This can be tricky to spot but you were provided with a script called get_data_tmpl.sql during the Noetix training which provides the answer.

Finally the SQL;

  KEY_VIEW_LABEL = 'PO_PO_Distributions' -- the target template
AND VIEW_LABEL = 'PO_Receipts' -- template name
AND QUERY_POSITION = 2 -- From get_data_tmpl.sql
AND TABLE_ALIAS = 'POD' -- table alias of PO.PO_DISTRIBUTIONS_ALL from PO_Receipts

You'll need to save this in a script can call it from wnoetxu2.sql in the normal way.

C# Programming Test #1

Interview test
Ever needed a simple test to get that interviewee talking? The point of this Knol is to provide you with a small code sample to discuss with an applicant.

Given the size of the world and the number of companies in it it's pretty unlikely that either a) whoever you give this test to (as a company) will have read this, or b) if you memorise this (as an interviewee) that this will be the test the company uses.

It's just a bit of fun that comes after I realised that I'd written some technically correct but logically incorrect code. Hope it will be useful to someone someday!

Here is the code snippet;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace SimpleFileRenamer
  class Program
    static void Main(string[] args)
      string[] filePaths = Directory.GetFiles(Directory.GetCurrentDirectory());
      if (filePaths.Count() > 0)
        StreamReader file = null;
        string line;
          file = new StreamReader(Directory.GetCurrentDirectory() + @"\Settings.txt");
          while ((line = file.ReadLine()) != null)
            foreach (string OldName in filePaths)
              string[] sRename = line.Split('|');
              string NewName = Path.GetFileName(OldName).Replace(sRename[0], sRename[1]);
              if (Path.GetFileName(OldName) != NewName)
                  Path.GetDirectoryName(OldName) + @"\" + NewName);
          if (file != null)

The first thing to say is that this code compiles and runs. This test isn't about asking people how much they know of the C# syntax and how easily they could write applications using Notepad to compile later. If you like people like that then you're probably after a much longer series of tests!

Question One: What does it do?

A fairly good answer would be: Renaming files based on a series of rules read in from a file.

That's an excellent high-level description that you could give to anyone who would instantly know what was going on. Of course Software Engineers tend to answer in a more complicated way so you can expect some mention of "in the same directory", "using old/new values in the file separate by | (pipe)", "renaming only files where the rules in the settings file require it", etc.

As a quick example of how it works if our Settings.txt file contained two rows:

And you had the files; Execution_Test.txt and OldData.txt in your directory then after running the application these would be changed to ExecutionText.txt and Data.txt.

Simple eh?

Now here are a (non-exhaustive) list of things that could be raised during a discussion;

  • Lack of comments. Always a good one for the interviewee to raise!
  • There is no error-checking so errors drop right out with the ugly "... has stopped working" windows error message. This can happen in multiple places; Reading from Settings.txt (does it even exist for a start!), renaming a locked file, invalid text in Settings.txt, etc.
  • Variable naming. Very inconsistent; file, NewName, sRename, etc.
  • BUG: The application will try and rename itself and the settings.txt file - ideally both of these should be ignored.
  • BUG: If you have two entries in Settings.txt (say Old|New, and Yes|No) then if you have a file which combines the two entries (OldYes.txt, NewYes.txt, etc) then the application will crash as when it tries to do the second rename the file will no longer exists - the application should read settings and loop files rather than reading files and looping settings.
  • BUG (Obscure): If you are simply changing case (Old > old) then Directory. Move will raise an error, you need to use a temporary file in this special case.

I'm sure there are more (I should really have written these down as I thought of them rather than trying to remember a few days later), let me know all the ones I've missed by commenting below.