Articles - Joel Lipman .Com

Reminder on SSRS row separator

: Joel Lipman; SQL Server Reporting Services; 26 October 2011; Hits: 9447

My Setup

XP SP3 Workstation
Business Intelligence Development Studio 2008 (BIDS)
SQL Server Reporting Services 2008 R2 (SSRS)

What do I want?
I have a report displaying room bookings. Each row lists the day, date, room name, start/finish times, booking details and the staff contact. I want an empty row to appear between each day in the list, so I have:

: Joel Lipman; SQL Server Reporting Services; 26 October 2011; Hits: 13618

The Problem
I've set some tablix headers but when I request the same report in PDF format, the tablix headers only appear once on the first page. Every subsequent page simply displays the report header (which does not include the tablix header... obviously). I right-clicked on the header row of the tablix and checked the box "Repeat header columns on each page.

: Joel Lipman; Transact-SQL; 11 October 2011; Hits: 10779

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:

copy raw

SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

SELECT
DATENAME(dw, StartDate) AS 'Day'
FROM
Timetable
ORDER BY
StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC
-- Yields
/*
Day
----------------
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

Category: Transact-SQL :: Article: 394

: Joel Lipman; Wordpress; 03 October 2011; Hits: 7994

This is for Joomla 1.5.x sites!
Note that this article is for Joomla 1.5.x sites to be converted to Wordpress 3.2.x sites. I started with a Joomla 1.5 as the move from Joomla 1.6 or greater is a lot easier since it uses nested categories like Wordpress.

There are lots of commercial migrators out there and they all seem to have this problem. I'm really keen not to ask all my users to have to change their passwords but that is what the commercial applications are doing.

: Joel Lipman; Excel; 30 September 2011; Hits: 10981

The Situation
I have a Microsoft Excel 2007 file that connects to a SQL Server 2008 R2 database. The Excel file pulls data using lookup tables and displays the data in an Excel Spreadsheet.

The Problem
We can select all cells and set row height to be 30 for example, but everytime we refresh the data in the Excel spreadsheet, all the rows get re-adjusted to fit the data and lose that consistency.

A Workaround: New line inserted before and after
So this is where I am at the moment without VBCode and other suggestions. Instead I add a newline in front of and after the smallest data value (one that I know will never be two lines (or two words)) within the SQL query itself. We have a DEPT column that is an acronym of the departments so for example:

: Joel Lipman; AutoHotkey; 29 September 2011; Hits: 21100

Well yes you could just use the Unicode version of Autohotkey. If you're looking for some up and down arrows which is all I wanted, then you don't have to use Unicode and you can use the Chr() function:

copy raw

msgbox % Chr(24) ; upwards arrow
msgbox % Chr(25) ; downwards arrow, unstable

msgbox % Chr(24) ; upwards arrow
msgbox % Chr(25) ; downwards arrow, unstable

Category: AutoHotkey :: Article: 389

: Joel Lipman; AutoHotkey; 27 September 2011; Hits: 13890

Ok so there are more fun things to do out there. If you're the type of AutoHotkey programmer that doesn't like having to use the command prompt to write to an extra text file which your program has to read, then this is for you. DllCall will usually run contained in the program and can be hidden from the end-user.

The one to rule them all:
This is a function I pulled from the Autohotkey forums submitted by SKAN which lists all the functions for a specified Dynamic Link Library (DLL) along with an inputbox for convenience:

: Joel Lipman; AutoHotkey; 27 September 2011; Hits: 10419

These are for autohotkey and in AHK code. Taken from Autohotkey forums topic 1878:

From Hexadecimal to RGB
From RGB to Hexadecimal
Check for a valid hexadecimal value

: Joel Lipman; Personal Home Page; 26 September 2011; Hits: 6917

So I know it's quite a long title but I'm not sure what I'll be searching next time. It has taken me a lot longer than I thought it would mostly out of frustration and the inability to know exactly what I'm looking for... always difficult. After a cup of tea the solution was glaringly obvious, just do a primary school mathematics table and it all makes sense (see my 10 mod table below).

This article is a quick note (so I never spend as long again) in PHP on how to determine when looping through a loop, which entry was first and which was last. This is incredibly useful for pagination.

: Joel Lipman; MediaWiki; 22 September 2011; Hits: 5005

What do I want?
Page ID, Title, Content, Category, AccessLevel (note that AccessLevel is extra to your standard MediaWiki setup and one that was customized for my day job).

Thought I already had this somewhere on my site, so it took a while again but I've posted my finished query here:

The base query to list mediawiki articles
Page ID, Title, Content, Category

copy raw

SELECT
		p.page_id AS PageID,
		CONVERT(p.page_title USING latin1) AS PageTitle,
		CONVERT(t.old_text USING latin1) AS PageContent,
		(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory
	FROM
		wikimedia_page p
	LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
	LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
	WHERE
		p.page_namespace=0
		AND t.old_text=''
		AND p.page_is_redirect=0

SELECT
p.page_id AS PageID,
CONVERT(p.page_title USING latin1) AS PageTitle,
CONVERT(t.old_text USING latin1) AS PageContent,
(SELECT GROUP_CONCAT(CONVERT(wikimedia_categorylinks.cl_to USING latin1)) FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory
FROM
wikimedia_page p
LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id
LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id
WHERE
p.page_namespace=0
AND t.old_text=''
AND p.page_is_redirect=0

Category: MediaWiki :: Article: 385

: Joel Lipman; AutoHotkey; 20 September 2011; Hits: 13351

As you can tell these are my messed up functions that convert dates into seconds and vice-versa. They're a little disorganised but they're the ones I copy and paste to my scripts then modify.

In it's straightforward form

copy raw

FormatTime( TimeString, Format )
{
     FormatTime, FormattedTime , TimeString, %Format% 
     return Formattedtime
}

FormatTime( TimeString, Format )
{
FormatTime, FormattedTime , TimeString, %Format%
return Formattedtime
}

Category: AutoHotkey :: Article: 384

: Joel Lipman; AutoHotkey; 20 September 2011; Hits: 14044

Just a quick note as I use this function in various scripts. This adds the 1000th separator comma:

    	FormatAddCommas(val) {
    		Result:=val
    		StringLen, OutputVar, Result
    		NumLoop := (OutputVar // 3)
    		DNum = 3
    		Loop, % (NumLoop+1)
    		{
    		   StringRight,Digit,Result,%DNum%
    		   StringReplace, Result, Result, %Digit%,`,%Digit%
    		   DNum += 4
    		}
    		StringLen, OutputVar, Result
    		Loop, %OutputVar%
    		{
    			FirstChar:=Substr(Result, 1, 1)
    			IfEqual, FirstChar, `,
    			{
    				Result:=Substr(Result, 2)
    			} else {
    				break
    			}
    		}
    		Return Result
    	}

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support