12 November 2011

SSRS - Aggregate of an Aggregate

So here is how to subtotal a column that is itself a sum function. SSRS 2005 wont allow you to aggregate an aggregate function. For example, the total of a column showing a Running Total, useful in daily stock balance calculations.
Add the following code to the report  Report > Properties

Dim public totalBalance As Decimal 
Public Function AddTotal(ByVal balance As Decimal) AS Decimal totalBalance = totalBalance + balance return balance 
End Function 
Public Function GetTotal() return totalBalance 
End Function

This code adds two variables: totalbalance and cnt as decimal numbers. And two functions AddTotal and GetTotal.
AddTotal allows items in rows to be added up, use as follows in a value cell, where you had;


in the total cell, where it you were unable to simply use

use instead

Simply add more variables and public functions if you need to sum the sum of more that one field.
Inspired by this post on averages of averages link

07 September 2011

Thunderbird Re-Index to fix Global Search

Using Thunderbird on more than one machine can lead to messages missing from the index and reducing the usefulness of the global search.

Two easy options to re-index your IMAP folders and fix the problem

1. Right click and open properties for the folder you want to fix, and click repair folder. This will also download the mail again from the server.

2.  Find and delete all of the files with the .msf file extension in your profile folder, as described here

MS-SQL / Excel Data Connectors

Data Connectors are a very handy way to give office staff access to the data they need from an MS-SQL database without any knowledge of SQL.

Where central data is held in MS-SQL databases but the application level is unable to provide all the various summary and analysis functions that fast moving businesses need.

Either create views and link the connectors to these or simply link the connectors to SQL statements with a read-only access to the database.
Once you have your new data connector saved it's easy to directly edit the .odc files to change the initial catalog, the view that is being accessed and user names etc.

The .odc file can also be changed between accessing tables or views or SQL statements. Via 'Connection Properties' dialog found in the Data tab

The main advantage of data-connector driven excel models is that the data is refreshed from the primary source either automatically or at the press of a button.

Provides as-good-as online access for business analysis or performance monitoring projects with virtually nil development overhead.

08 May 2011

Grub customiser / Ubuntu

Beats trying to find the right file to edit..

sudo add-apt-repository ppa:danielrichter2007/grub-customizer
sudo apt-get update
sudo apt-get install grub-customizer

Dead easy to use also

04 February 2011

Microsoft® SQL Server® 2008 Management Studio Express

A very handy bit of free software from MS for connecting SQL servers and executing SQL commands. For those just learning SQL it has a built in graphical SQL query builder that helps you get started. Also a very easy to use db diagram section which is always handy for reference when dealing with a new database.


Use Ubuntu to Bypass or Crack Windows XP Passwords

If you forget your password it can be annoying. Here is how to get past those dark days.

Download and burn the latest Ubuntu distribution to CD, this can be found here www.ubuntu.com.

The Bypass Method

Use the CD as a boot disk and load up Ubuntu without installing it onto the hard disk, it can run from the CD. Once loaded fire up the Terminal and run

sudo synaptic

In the window that opens, go to 'Settings > Repositories' in the popup make sure its all ticked except 'Source code'. Then close the popup and any warnings and hit 'Reload' a button on the main toolbar. Once that is complete close the window and head back to the terminal.

sudo apt-get install chntpw

Using this application you can reset of any user IF syskey is not installed. First mount the windows disk, go to places and click on the disk that holds your windows installation. Then its back to the terminal for more command line action.

First navigate to the directory where windows stores the passwords.

cd /media/"something"/windows/system32/config

the 'something' being the name of your HDD where Windows is installed. Run the application to see where you stand.

Here is an example of what you might see

andy@andy-ubuntu:/media/Win7/Windows/System32/config$ chntpw SAM

chntpw version 0.99.5 070923 (decade), (c) Petter N Hagen
Hive name (from header): <\SystemRoot\System32\Config\SAM>
ROOT KEY at offset: 0x001020 * Subkey indexing type is: 666c
Page at 0x19000 is not 'hbin', assuming file contains garbage at end
File size 262144 [40000] bytes, containing 8 pages (+ 1 headerpage)
Used for data: 296/55856 blocks/bytes, unused: 11/42192 blocks/bytes.

* SAM policy limits:
Failed logins before lockout is: 0
Minimum password length : 0
Password history count : 0
| RID -|---------- Username ------------| Admin? |- Lock? --|
| 01f4 | Administrator                  | ADMIN  | dis/lock |
| 03e9 | Andy                           | ADMIN  |          |
| 01f5 | Guest                          |        | *BLANK*  |
| 03ed | HomeGroupUser$                 |        |          |
| 03eb | xbox                           |        |          |

---------------------> SYSKEY CHECK <----------------------- SYSTEM 

SecureBoot : -1 -> Not Set (not installed, good!)
SAM Account\F : 0 -> off
SECURITY PolSecretEncryptionKey: -1 -> Not Set (OK if this is NT4)
Syskey not installed!

RID : 0500 [01f4]
Username: Administrator
comment : Built-in account for administering the computer/domain
homedir :

User is member of 1 groups:
00000220 = Administrators (which has 2 members)

Account bits: 0x0211 =
[X] Disabled | [ ] Homedir req. | [ ] Passwd not req. |
[ ] Temp. duplicate | [X] Normal account | [ ] NMS account |
[ ] Domain trust ac | [ ] Wks trust act. | [ ] Srv trust act |
[X] Pwd don't expir | [ ] Auto lockout | [ ] (unknown 0x08) |
[ ] (unknown 0x10) | [ ] (unknown 0x20) | [ ] (unknown 0x40) |

Failed login count: 0, while max tries is: 0
Total login count: 1

- - - - User Edit Menu:
1 - Clear (blank) user password
2 - Edit (set new) user password (careful with this on XP or Vista)
3 - Promote user (make user an administrator)
4 - Unlock and enable user account [probably locked now]
q - Quit editing user, back to user select
Select: [q] >

So from this you can see the different users and the state of their accounts. To access the Administrator account first we have to 'Unlock' it with option 3 and then reset the password with option 1. Its apparently not a good idea to type a new password, just do that once you logged into Windows.

So all is good, go back to Windows log in as the 'Administrator' and reset the other users passwords.

However if the line above "Syskey not installed" in fact reads something like "Syskey is installed" you have to try a different approach.

The Crack Method

Fire up the Terminal

sudo apt-get install ophcrack && sudo ophcrack

this will install and run the application, you will also need to load firefox, it comes pre-installed and there should be an icon on the bar at the top. Head to ophcrack.sourceforge.net and download the the first XP table, they get bigger as you go down, so just start on with the first. Save this file to the windows disk and then install the table from Ophcrack by clicking the 'Tables' button.

Load the Encrypted SAM file by clicking the 'Load' button and navigating to the .../Windows/System32/config... folder and clicking choose.

Again you will see a list of users, you only want to focus on the Admin so remove the others by clikcing on them and pressing delete. (This only removes they from the Cracking process, it wont delete the actual user)

Add the tables you need, if hashes are in the NT column use VISTA tables if the LM hash column has values use the XP tables.

Select the appropriate table and hit 'Crack' and then sit back and cross your fingers.

15 January 2011

Route lookup?

Problem: A large amount of locations, having the postcode of point and A point B, how do I lookup en mass the best route between the points?

I'm pretty sure google maps could be wrangled to provide the data just need to find some site that will allow the data to input and then results downloaded...

whatgas.com & fuel accelerators for bulk haulage

Checkout this site to find the cheapest fuel price near you. Will from whatgas.com was kind enough to share the whole dataset on derv prices going back about 5 years.

What the best way to modify haulage rates with fuel costs on the rise, how much increase in fuel can the haulier just soak up before the rates have to change? How often would it be practical to change rates given bulk distribution contracts are based on delivered product? How should the change in rate be related to the change in fuel.

It's a tough one and needs a clear and transparent process if haulage companies are going to accept the changing rates. Currently thinking that by separating the fuel cost of each ton delivered for every journey is going to be the way to go. This way the net rate per tonne can be fixed and the overall rate get adjusted when derv prices increase or decrease past a pre set trigger point.