Articles - Joel Lipman .Com

Stop Excel Row Height Self-Adjust on Refresh

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

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: 21142

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: 13955

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: 10458

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: 6945

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: 5033

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: 13428

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: 14095

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
    	}

SSRS External Images don't display

: Joel Lipman; SQL Server Reporting Services; 19 September 2011; Hits: 53457

The Situation
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).

Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).

Our Setup

Windows XP Workstation
Business Intelligence Development Studio 2008 (BIDS)
SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
Team Foundation Server 2010 (TFS)

Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:

Get report to see images hosted on an external URL
Check if image exists to display alternative placeholding image.

: Joel Lipman; Excel; 05 September 2011; Hits: 17679

So I googled this for a while and there are a lot of solutions out there, none of which applied to what we meant and lots of people in the same boat.

The Situation
We have an Excel report which summarizes for our guys at the top, all the activities and time spent by staff. There are several filters available on the report (only a few to keep it simple silly). When you click on the filter, a dropdown appears with all available values listed.

The Problem
The values are listed in alphabetical order at first. If any new values come along then they get added to the bottom of the list... This is the problem. For example, if the year dropdown has a list of 2010, 2011, 2013; then if you add an entry which has year 2012, then the dropdown list will be in the following order: 2010, 2011, 2013, 2012.

The Solution

: Joel Lipman; SQL Server Reporting Services; 02 September 2011; Hits: 16802

I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error

copy raw

ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1 (System.Data.OracleClient)

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.

Category: SQL Server Reporting Services :: Article: 380

: Joel Lipman; Oracle PL/SQL; 30 August 2011; Hits: 8003

Just a quick note as to something that worked for me. Taken from the Oracle forums @ https://forums.oracle.com/forums/thread.jspa?messageID=6861756

Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support