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

24 August 2011 start time, end time... Report Specification PivotTable Report...resources in row (along the side)......staff in columns (along the top)......persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)... With date range as...

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

Search a database with SOUNDEXhttps://www.joellipman.com/articles/database/search-a-database-with-soundex.html

11 October 2012 ** Example: ** ** EXEC [usp_GenerateSearchbySoundexQuery] 'Not Available', 'dbo'; ** ** ** ** Return Values : Table of two columns: ** ** - Value: Your search term and matching variations ** ** - Source: The table_name.column_name it found this value...

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

Stored Procedure to List Distinct Values and Countshttps://www.joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns. How? IF OBJECT_ID('usp_ListDistinctValuesAndCounts', 'P') IS NOT NULL DROP PROCEDURE [usp_ListDistinctValuesAndCounts];...

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

Search a database for a value and count matching rowshttps://www.joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 (eg. Customer_ID) ** ** - Column: the column name which will contain this value. ** ** ** ** Return Values : Table of three columns: ** ** - myValue: value specified in parameter 1 (matched in database to search) ** ** - myTable: table in which the...

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

Parse a HTML Table into a ListViewhttps://www.joellipman.com/articles/web-development/html/parse-a-html-table-into-a-listview.html

30 March 2013 %ReturnedHTMLTableRows0% { RowIndex++ ReturnedHTMLTableColString:=ReturnedHTMLTableRows%RowIndex% ; split the row into columns StringReplace,ReturnedHTMLTableColString,ReturnedHTMLTableColString, ,|,A IfInString,ReturnedHTMLTableColString,...

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

Excel: Find values in one column that are not in anotherhttps://www.joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new... Why? Consider the...

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

SSIS: How to loop through multiple flat files as data sourceshttps://www.joellipman.com/articles/microsoft/ssis/ssis-how-to-loop-through-multiple-flat-files-as-data-sources.html

13 June 2014 table with this data. Note that this only works if all the text files to be used as source data have the same number of columns and where the column widths match. How? I've adapted my real working product with an example. As my work was for a...

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

AutoHotkey: App GUI Listview to Rename Fileshttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-app-gui-listview-to-rename-files.html

08 July 2020 scan for files. The 3rd button simply reloads the app (used mostly for testing/development). Lastly, a listview that has 2 columns: the 1st lists the files in that directory, the 2nd displays the new filenames of the files. ; ; Language: English ;...

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

ZohoCRM: Import Attachmentshttps://www.joellipman.com/articles/crm/zoho/zohocrm-import-attachments.html

16 January 2023 View it to see what is expected of the reverse process, the import file. The import file I got working had the following columns: Attachment Id: This needs to the unique filename of the actual file on disk (as opposed to "File Name" which is the file...

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

Zoho Analytics: Create a Monitoring System with Imageshttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-create-a-monitoring-system-with-images.html

25 July 2024 of the last options on the "Import Your Data" page is "Enter Data Right Away": You'll be presented with a table of about 10 columns called "Untitled-1". I enter some labels in the first column. These will be keys later on to link my report to so keep...

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

Write a Calendar in Autohotkeyhttps://www.joellipman.com/articles/automation/autohotkey/write-a-calendar-in-autohotkey.html

27 January 2010 in full; apologies for the mess coding but it's a mix between HTML and autohotkey syntax (note we're making a table with 9 columns and 6 rows - there's a spacing column on either side): htmlText:="" htmlText:=htmlText " " htmlText:=htmlText ""...

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

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

13 December 2010 logged in) and of course the Timestamp (DateTimeStamp). Here is a page of some MySQL queries I can do based on just those 5 columns: MediaWiki: number of unique guests, students and staff by month. Also associates a UserID to the wikimedia_user table to...

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

Alternate row background colour in Reporting Serviceshttps://www.joellipman.com/articles/microsoft/ssrs/alternate-row-background-colour-in-reporting-services.html

04 February 2011 two for the rest of the report. [The second example is what I use most frequently]. Additional: Suppose you have lots of columns, you don't want to have to click on each cell to specify this so just do it to the first column then bring up the properties...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• 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 Datasheet View (you could probably use a "Standard View", I just used the Datasheet one) Give the view a name, select what columns you want the form to have (I included the IDs for functional purposes) Further down the "Create View" page, there should...

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

Performance Report - Background colors based on dataset valueshttps://www.joellipman.com/articles/microsoft/ssrs/green-red-background-colors-based-on-dataset-values.html

02 December 2011 a Matrix rather than a Tablix: Insert a "Matrix" into the body of your report. Drag "ReportName" into the field that says "Columns". Right-click on the grey left margin and select "Insert Row" then "Outside Group - Below". Delete the row with the left...

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

Returning Oracle Stored Procedure Resultset in SSRShttps://www.joellipman.com/articles/microsoft/ssrs/returning-oracle-stored-procedure-resultset-in-ssrs.html

19 January 2012 TO my_ssrs_user; Notes: No "SELECT... INTO..." on the second query which is the one that will return my columns. Two output fields are expected to be returned: "p_STUDENT_DETAILS" and "p_STUDENT_REF". 2. Add a datasource Nothing different to your...

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

JDatabase: using the Joomla database with exampleshttps://www.joellipman.com/articles/cms/joomla/jdatabase-using-the-joomla-database.html

27 January 2013 of db->query() $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->insert($db->quoteName('#__my_users')) ->columns(array('name', 'username')) ->values(implode(',', array($db->quote('Joe'), $db->quote('jlipman')) )); $db->setQuery($query);...

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

SSIS Skip Rows in Excel Source filehttps://www.joellipman.com/articles/microsoft/ssis/ssis-skip-rows-in-excel-source-file.html

04 April 2013 How? My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"): In the data flow, click on the Excel datasource component so that it's selected. View the Properties panel...

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

MS Excel - Sort pivottable column headings by datehttps://www.joellipman.com/articles/microsoft/excel/sort-pivottable-column-headings-by-date.html

12 April 2013 "08" so maybe I'm barking up the wrong tree. If I padded with one more zero so that "008" is before "010": Great! The columns are displayed in the correct order but who's ever seen a date written as the 008th of April? What if we didn't use parentheses?...

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

Generate a Timesheet in MySQL https://www.joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 -- 2013-12-05 Thursday -- 2013-12-06 Friday -- 2013-12-07 Saturday -- 2013-12-08 Sunday Brilliant! We have the first two columns of what we want to do. Now we need the same again but this time we want the morning slots SELECT DATE( DATE_ADD( NOW( ) ,...

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