Project Zebra: What we see there is nothing but savagery, injustice ►

◄ Project Zebra: Next he'll be wanting his own Thundertank

As this is being uploaded as a placeholder for info to support a presentation, I'll skip context. For more details on Galileo see https://magiquegalileo.com/audit-management/

Dashboard procedures are essentially just a SELECT statement with standard fields, which can then be referred to in dashboard configuration. The following query, to illustrate the fields, gets audits for a specified user for the current and previous years. id and UrlForm have a special meaning as dashboard items normally only contain one link per line, but your output fields can contain HTML so this isn't in any way a hard limitation.

DECLARE @intUid int; SET @intUid=[your user ID]; -- override @intUid whilst testing
DECLARE @UsName1 nvarchar(128); SELECT @UsName1 = UsName1 FROM People WHERE UsUid = @intUid;

DECLARE @AuYear VARCHAR(16); SELECT @AuYear = SeValue FROM Setting 
WHERE SeSection IN ('00Global') AND SeKey IN ('optCurrentYear');

SELECT
id = tAu.AuUID,
deleted = tAu.Deleted,
statusid = tAu.StatusId,
key1 = 0,
key2 = 0,
ref=tAu.AuRef,
name=tAu.AuName1,
field1 = '',
field2 = '',
field3 = '',
image1 = 0,
field4 = '',
field5 = '',
field6 = '',
image2 = 0,
field7 = '',
field8 = '',
field9 = '',
image3 = 0,
Type = '',
UrlForm = '../AP/AuForm.aspx'
FROM [Audit] tAu
WHERE ( tAu.AuYear=@AuYear OR tAu.AuYear=CAST(CAST(@AuYear AS INT)-1 AS VARCHAR(16)) ) AND
( tAu.AuLeadAuditor=@UsName1 );

A note of caution: whilst an admin can run SQL queries against a Galileo database via the browser using the "Db Update" tab of /SysAdmin/SaTask01.aspx I wouldn't necessarily recommend this for anyone just starting out. If your instance is hosted by MGS talk to them, and if your instance is on-premises it's preferable to work with standard tools (SQL Server Management Studio). However you work, adopt defensive development processes and take care to fork any procedures you modify rather than alter existing ones, keeping in mind that these could alter in the course of normal system upgrades.

It also isn't reasonable to expect supplier support for your own code, or if you break things through carelessness. That's normal. Understand your backup arrangements, use test systems where available/relevant, etc.

It would be reasonably practical to work without direct access to infrastructure. The procedure names are listed under "Dashboard Items" and you can use something like the following to see the SQL behind them:

select definition from sys.sql_modules where object_id = object_id('BLL_DashBoard_getMyReviewPoints');

So you could potentially use the "Db Update" tab to create new procedures or amend them once created, "Table Stats" to show the schema of individual tables and things like "select top 10 * from audit" and export results as zipped XML (which is basically the equivalent of print statements for debugging) to get a feel for the structure, but it’s not really what the interface is designed for. (And it's less forgiving of syntax than Management Studio. For example, don't forget to append GO after a BEGIN [...] END block). That said, if you've ever found yourself altering SQL in Business Objects reports or supporting dynamically built queries you'll feel right at home.

Update: based on further discussion with other users, committing commands with GO does seem to be mandatory if entering SQL via "Db Update" -- and the parser also seems to dislike the long and descriptive /*--- narrative here ---*/ style comments seen in some built-in Galileo procedures, so it's probably best to document your procedures separately from the queries.

With a view to providing meaningful examples, below's a snapshot of the procedures for dashboard items we use and also includes a 'blank' placeholder item similar to the code example above. Eight were written to meet specific needs, four tweak existing procedures to add links, sorts or additional fields for context (for example sorting bookings by title alphabetically). They aren't especially clean or commented, but most are quite simple.

The date-driven items in particular aren't likely to translate directly to your organisation, but do illustrate the use of Transact-SQL case statements to output relevant information depending on what stage an activity is at and target periods -- for example, reminders of when a draft or final report is due, depending on whether a field work start date or draft date has been entered, and then reminders to chase questionnaires and other things.

dashboards.zip (updated November)

-- 'ASD01 create new key dates dashboard item.sql'
-- 'ASD02 create new follow ups dashboard item.sql'
-- 'ASD03 add link to draft working papers dashboard item.sql'
-- 'ASD04 add fields to review points dashboard item.sql'
-- 'ASD05 add sort to bookings dashboard item.sql'
-- 'ASD07 add context to staff timesheets view.sql'
-- 'ASD08 create new audit reports due soon dashboard item.sql'
-- 'ASD09 create new finals issued list attachments.sql'
-- 'ASD10 create new to be reviewed dashboard item.sql'
-- 'ASD11 create new other management monitoring of audits dashboard item.sql'
-- 'ASD12 create new placeholder dashboard item.sql'

Our auditor dashboard layout is:

My review points	Key dates for my audits
Attached current year audit bookings	Follow up of audits I'm lead for
My open timesheets	My open working papers

And managers/reviewers also get a separate default layout of:

Review points I've created awaiting response/clearing	Report milestones this/prev year, due within a week or overdue
Working papers with items marked for review, or final issued but not all reviewed	Date monitoring for this/prev year audits
Attachments on finalised audits	Staff timesheets with status
	My open timesheets

Feel free to get in touch. It's a niche sort of thing and the built-in procedures are likely to cover a lot of usage scenarios, but I'm a strong believer in organisations being able to find their way around their own data.

In closing, the following snippet is a specific hack we use with Galileo 4.0.8.7 -- setting the default state of selected dashboard items to 'open' by inserting some javascript into the login screen template (probably shown on the "Customer Information" tab of /SysAdmin/SaTask01.aspx) that populates relevant cookies.

<script type="text/javascript"> document.cookie = "ctl00_MainContent_PnlBar1={'SelectedItems':['0'],'ExpandedItems':['3','2','1','0']}; expires=Thu, 31 Dec 2099 12:00:00 UTC; path=/"; document.cookie = "ctl00_MainContent_PnlBar2={'SelectedItems':['0'],'ExpandedItems':['3','2','1','0']}; expires=Thu, 31 Dec 2099 12:00:00 UTC; path=/"; </script>

I'm told that this is a configurable option in later versions (and 4.3 changes things) so it's really just a curiosity.

BLOG  MORE  ABOUT  MAIL