Articles - Joel Lipman .Com

MySQL Group_concat equivalent in T-SQL and Oracle

: Joel Lipman; Databases; 28 November 2011; Hits: 15628

MySQL is just the best
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.

I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:

copy raw

RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I

RowID column_to_return_as_string
--------- --------------------------
1 Me
2 Myself
3 I
-- to be returned as
RowID my_field_name
--------- --------------------------
1 Me,Myself,I

Category: Databases :: Article: 403

: Joel Lipman; SQL Server Reporting Services; 17 November 2011; Hits: 35183

How to Display Report Execution Time in SQL Server Reporting Services 2008 R2
So there are other articles out there but I was looking to display in milliseconds the execution time it took for a particular report (which searches for results matching the submitted parameter) to run.

Lifted from Dattatray Sindol's blog
Other sites have this solution as well so who copied off who is not my concern as this is not the solution to our problem. This is the solution that I initially used but my end-users were asking why is it always 0 seconds. This was because we were using the following MDX statement which had seconds as its smallest denominator:

copy raw

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

// yields
// 0 hour(s), 1 minute(s), 2 second(s)

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"
// yields
// 0 hour(s), 1 minute(s), 2 second(s)

This isn't what I want... It wasn't accurate enough and always saying 0 seconds was just confusing the end-user...

Category: SQL Server Reporting Services :: Article: 401

SSRS Zero Padding

: Joel Lipman; SQL Server Reporting Services; 10 November 2011; Hits: 201539

Quick Reminder
I didn't want to do this at the database level, mainly because it meant modifying the SQL query. The zero padding would need to be applicable within an MDX query.

The Situation
We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log displaying the oracle errors.

Oracle Errors
An Oracle error usually returns in the format of -12345. If we want to look them up the error is ORA-12345. Unfortunately Oracle also returns errors of less than 10000 so ORA-00201 would actually be returned as "-201". As I wanted a link so that the user can just click on this link and it would take them to http://ora-00201.ora-code.com/.

: Joel Lipman; SQL Server Reporting Services; 07 November 2011; Hits: 10915

Intro
If you ever want to check the parameters submitted with a report for alpha numeric characters (so it doesn't contain symbols, punctuations, etc) then you should do this at the database level, and then get the report to complete the check:

The Plan

User enters value in parameters and clicks on "View Report"
Report passes parameter to dataset which gets formatted by the database
Report retrieves (select) formatted parameter as a field value to use
Report loads with changes based on returned value.

The Gist

Add database level parameter check
Add IIF in SSRS to confirm

: Joel Lipman; SQL Server Reporting Services; 04 November 2011; Hits: 11135

What?
We have a report in SQL Server Reporting Services 2008 R2 (SSRS) reading from an Oracle 10g database which works great and lists all the details on a specific student. An additional request is that there appears a link that will run a stored procedure which

Updates a timestamp in an existing table
Inserts a row into an audit table

I need the report to run the stored procedure, then based on the errorlevel, return a message.

How?

: Joel Lipman; MySQL; 31 October 2011; Hits: 9760

Why?
I recently made a joomla module that displays the lastest members to signup. It goes a little further and counts activated accounts for the past day, week, month and year (the below examples count all accounts irrespective of being activated or not). It needs to pick up trends as well and compare for example todays, with yesterdays up to the same hour.

How?
I used to use a lot more PHP but since becoming an analyst, I do more at the database level now. What follows should be usable mySQL statements to get all the numbers:

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

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

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

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

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

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

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

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support