SQL Queries for Statisticshttps://www.joellipman.com/articles/database/mysql/sql-queries-for-statistics.html

13 December 2010 Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize. One of my systems is a MediaWiki CMS which is used for the official Bournemouth University...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Accessing a MySQL Database with Business Intelligence Development Studiohttps://www.joellipman.com/articles/database/mysql/accessing-a-mysql-database-with-business-intelligence-development-studio.html

05 January 2011 That's all folks! Hopefully this will save you time. I spent a whole morning following someone else's complicated connection using SQL Server Management Studio and would not recommend anyone to go through the same rubbish. I just took a step back and...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Returned a data type that is not validhttps://www.joellipman.com/articles/database/t-sql/returned-a-data-type-that-is-not-valid.html

06 January 2011 ' ', YEAR(t1.Date)) AS Month Solved by changing SQL statement to t1.Date AS Month This will return the SQL date but using your report builder or business intelligence development studio, you can specify in the report designer how to format the returned...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Parameters not being used in report processinghttps://www.joellipman.com/articles/microsoft/ssrs/parameters-not-being-used-in-report-processing.html

07 January 2011 I've spent a whole morning going in the wrong direction with a convincing SQL script. This article is just a note on when using Business Intelligence Development Studio (BIDS) or Report Builder 2.0 and when previewing the report, the parameters are not...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Difference between two dates - the midnight hourhttps://www.joellipman.com/articles/web-development/difference-between-two-dates-the-midnight-hour.html

06 April 2011 $sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2); // Using the examples above this is doing the following: 2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00 2011-03-08 00:30:00 - 2011-03-08 23:30:00 =...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Creating a Profile Plugin for Joomla 1.6https://www.joellipman.com/articles/cms/joomla/creating-a-profile-plugin-for-joomla-16.html

27 April 2011 all this for me... Neat huh? Folder I'm going to shove all these files into a folder which I will then compress and install using the Joomla! CMS Extension Manager. I'm going to call the folder "profile5" (without the double-quotes) and create a...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Could not instantiate mail functionhttps://www.joellipman.com/articles/cms/joomla/could-not-instantiate-mail-function.html

09 May 2011 domain.com and you want to recieve mail on user@domain.com, you must *not* set your hostname to domain.com. We recommend using server.domain.com instead. You must make sure that you add the A record for server.domain.com so that it resolves. 2) The...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: *

Sort order a dropdown list in MS InfoPath without programminghttps://www.joellipman.com/articles/microsoft/infopath/sort-order-a-dropdown-list-in-ms-infopath-without-programming.html

21 July 2011 I entered them in the datasheet. Googling this led me to browse MSDN for an hour before I realised all those experts were using programming solutions that seemed a bit over the top for something that should be so simple. Hey presto, I found a...

• Type: Article
• Author: Joel Lipman
• Category: Infopath
• Language: *

SSRS / MDX Date Querieshttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-mdx-date-queries.html

03 August 2011 I find myself using date ranges endlessly as I've been working in SSRS. The below is derived from a collection of various sources across the web as well as some of my own. These are what worked in my environment: WinXP, BIDS (vs2008), TFS (vs2010), SSRS...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

The ReportServer Databasehttps://www.joellipman.com/articles/database/the-reportserver-database.html

08 August 2011 columns (which measure in milliseconds) and convert to a time value with milliseconds showing. Now why don't these match? Using in an aggregate, how do I get the milliseconds between start and end times instead of summing the remaining columns?...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

MySQL parameters in Excel 2007 PivotTableshttps://www.joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 What's the problem? Enter this as a query in Excel without the parameters and it will work. Set MySQL parameters (using @ - same as T-SQL) and Microsoft Excel 2007 gets confused. Putting it all together Create a SQL query that will return: staffname,...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

SSRS Parameters in Oracle Stored Procedurehttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-parameters-in-oracle-stored-procedure.html

02 September 2011 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...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Stop Excel Row Height Self-Adjust on Refreshhttps://www.joellipman.com/articles/microsoft/excel/stop-excel-row-height-self-adjust-on-refresh.html

30 September 2011 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...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

SSRS Retrieving Oracle Stored Procedure Success or ErrorLevelhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-retrieving-oracle-stored-procedure-success-or-errorlevel.html

04 November 2011 the one to execute the stored procedure, we use an account specifically created to run the stored procedure. This requires using 2 data sources in SSRS. A user will specify the student reference in the parameters of the first report (the one with all...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

SSRS AlphaNumeric Parameter Validationhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-alphanumeric-validation.html

07 November 2011 is alphanumeric, 0 if not alpha numeric. SELECT @ParameterToCheck REGEXP '^[A-Za-z0-9]+$' AS AlphaNumeric FROM... Usage -- Using the ORACLE example SELECT Column1, Column2, (LENGTH( TRIM( TRANSLATE( :ParameterToCheck,...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

SSRS Zero Paddinghttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-zero-padding.html

10 November 2011 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...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

MySQL: Display Users and Duration in Matrix Timesheethttps://www.joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 ...do some more here ???... } } Yields -- Note this data matches the brief as we've converted the seconds to time. -- Using MySQL to convert rather than PHP so as not to have to deal with hour changes and the magical 23:00 hour. -- 2nd Note: We use...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Print Directory Contents to a Filehttps://www.joellipman.com/articles/microsoft/windows-os/print-directory-contents-to-a-file.html

19 July 2012 we add the "Command prompt from here" option (now built-in to Windows 7). I added this option for him in Windows 7 Ultimate using the system registry (see "How: for Windows 7" below). -- yield a_subfolder_in_this_folder a_file_in_this_folder.txt...

• Type: Article
• Author: Joel Lipman
• Category: Windows OS
• Language: *

Connect to Joomla database in standalone scripthttps://www.joellipman.com/articles/cms/joomla/connect-to-joomla-database-in-standalone-script.html

17 August 2012 details and extract data from the database. How? Based on a script I found on StackOverflow. Not sure what version they were using so I modified it to make this work with my environment (Joomla 2.5.6). This is a cut down version of my download.php...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Basic Joomla 2.5 Mootools Formhttps://www.joellipman.com/articles/cms/joomla/basic-joomla-25-mootools-form.html

20 August 2012 who just want to see an example of an AJAX form within Joomla 1.6.x - 2.5.x in it's most basic state. At time of print, I am using this with Joomla 2.5.6. Why? The examples of the official site (mootools.net) did not work in my Joomla environment nor...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB