A brush with a Lumia, a less than stunning version of office for the mac, a need to do some stuff with Access for the rugby club I'm secretary for, and the intrigue of pen input has lead me to wanting to try a Microsoft Surface. I've ordered a Surface 3, 4gig ram and 128 gig … Continue reading Surface Curious
Reporting on activity by week
This is a rewrite of this post as i completely failed to understand what i’d written. The original text is left after the break for completeness. A week is a unit of time that has to be derived, and therefore has multiple meanings. Some business will run Monday to Friday, others will run Sunday to … Continue reading Reporting on activity by week
Date tricks in Excel – Latest Month, and preceeding 12
Assuming you have your data formatted as a table (if not, why not) then you can use the following technique to see if it is the most current month for that set of date or if it is in the most recent n number of months. For this example we assume that you have a … Continue reading Date tricks in Excel – Latest Month, and preceeding 12
Minimum date in Cognos Report Studio
I have a list containing [Customer ID] [Transaction ID] [Transaction Date] [Sales Team Name] [Purchase Type] I want to have a list that gives me the date of earliest transaction for each customer. Filtering the report on: [Transaction Date] = minimum ([Transaction Date] for [Customer ID]) will give me the earliest date the customer had … Continue reading Minimum date in Cognos Report Studio
Converting YYYY-MM-DD to YYYYMMDD in Cognos Report Studio
How to convert standard SQL date format to YYYYMMDD right(year([Gym].[Member Details].[DOB]),4)+ right(month([Gym].[Member Details].[DOB])+100,2)+ right(day([Gym].[Member Details].[DOB])+100,2)
Resizing value prompt boxes in Cognos Report Studio
Thanks to the following site http://cognospaul.com/2012/01/10/quickie-dynamic-select-and-search-size/ for the follwoing. To resize a value prompt insert a snippet of html before it with the following <divid="selectSearch"> Then another html snippet afterwards with the code below </div> var e=document.getElementById('selectSearch'); var myselect = e.getElementsByTagName('select')[0]; if(myselect.childNodes.length>0){myselect.style.width=''} This will then resize the element automatically within the page (just don't ask me how)
Financial Year in Excel
This formula will give the financial year a date occurred in. This assumes that the date is in A1. =IF(MONTH(A1)<4,YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"-"&RIGHT(YEAR(A1)+1,2)) This can be altered for acadamic year by replacing the 4 with a 9 (academic months start in September, not April)
Comments within Cognos Report Studio
You can add comments In expressions by using the following ;#/*your comments here*/#; As with any programming the better documented your report and code is, the easier it is to maintain.
Automatically Reporting on Previous Calendar Month
This little snippet is used in a filter and automatically looks at the previous calendar month. cast([Sales Team].[Client Contacts].[Start Date],date) between _first_of_Month(_add_months(CURRENT_DATE,-1)) and _last_of_month(_add_months(CURRENT_DATE,-1)) If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked … Continue reading Automatically Reporting on Previous Calendar Month
Formatting an ID Number
This snippet takes an ID number and displays it in the format of 3-3-4 (e.g. 123 456 7890) substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),1,3)+' '+ substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),4,3)+' '+ substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),7,4)+' ' Its a simple substring operation, withthe account number being cast as a 10 character string
IT or Clinical ? 