Crystal Reports: Exporting to Excel omits column headershttp://mail.joellipman.com/articles/microsoft/excel/crystal-reports-exporting-to-excel-omits-column-headers.html

03 June 2010 and there but none of them produced consistent results. One solution was to untick "Simplify page headers" on the Excel Format Options when you export the report. Because our report is exported via a web-based system, this extra dialog doesn't appear...

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

Joes FREE Website Thumbnailer (JWT)http://mail.joellipman.com/component/content/article/joes-free-website-thumbnailer.html?catid=40

28 June 2011 initially takes a picture and saves this as an image file called "lastscreenshot.bmp". It then converts this image into the format with the quality/size that is set in the program options. Consider this file temporary and if you want to delete/rename...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

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

30 September 2011 refreshing data and preferred the end result with this change. Note on "row height": We did not want the customer having to format the report after they've already gone through the hassle of clicking on the big fat refresh button... Keeping It Simple...

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

SSRS Zero Paddinghttp://mail.joellipman.com/articles/microsoft/ssrs/ssrs-zero-padding.html

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

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

DataJumble - Shuffling characters in a data valuehttp://mail.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 @DateVal date; DECLARE @TimeVal time; DECLARE @DateMatch int; DECLARE @TimeMatch int; DECLARE @ColumnTypeIsDate int; -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal =...

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

Licence GNU/GPLhttp://mail.joellipman.com/static-items/licence-gnugpl.html

03 April 2009 or non-consumer uses, unless such uses represent the only significant mode of use of the product. “Installation Information” for a User Product means any methods, procedures, authorization keys, or other information required to install and execute...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

DataScramble - Randomizing data rowshttp://mail.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 int; DECLARE @TimeMatch int; DECLARE @ColumnTypeIsDate int; DECLARE @IntOptions int; DECLARE @CharOptions varchar(max); -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET...

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

Cheat Sheet for mySQL vs t-SQLhttp://mail.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 MONTHNAME(now()) DATENAME(mm, getdate()) Month Number MONTH(now()) DATEPART(mm, getdate()) European Date (dd/mm/yyyy) DATE_FORMAT(now(), '%d/%m/%Y') CONVERT(varchar, getdate(), 103) Time to Seconds TIME_TO_SEC(now()) (DATEPART(hour, getdate()) * 3600) +...

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

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

30 March 2013 so until I do some benchmarking I won't know which ones best: Method #1 This method replicates the table in a listview format: ; tell the script which content to parse Haystack := CodeContainingMyHTMLTable ; determine markers (character positions)...

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

SSIS Skip Blank Rows in Flat File Sourcehttp://mail.joellipman.com/articles/microsoft/ssis/ssis-skip-blank-rows-in-flat-file-source.html

23 April 2013 occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Why? So the solution must be obvious to you by now. At least that's what Microsoft people think to...

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

MS-DOS: Copy folders without overwriting fileshttp://mail.joellipman.com/articles/automation/ms-dos/ms-dos-copy-folders-without-overwriting-files.html

23 February 2014 c:\Destpath\*.* -- since January 1st 2010 XCOPY /E /D:01-01-2010 c:\Sourcepath\*.* c:\Destpath\*.* // D: is in mm-dd-yyyy format This will copy any files modified since January 1st 2010. If the destination file does not exist, it will copy the original...

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

XML Schema Referencehttp://mail.joellipman.com/articles/web-development/xml/xml-schema-reference.html

13 August 2014 anyAttribute Enables the author to extend the XML document with attributes not specified by the schema appinfo Specifies information to be used by the application (must go inside annotation) attribute Defines an attribute attributeGroup Defines an...

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

Zoho Deluge - Get English Ordinalhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-get-english-ordinal.html

08 November 2018 = if(v_ThisDayDateLastChar 20),"st,nd,rd".toList().get(v_ThisDayDateLastChar-1),"th"); // full formal date v_ContractDateFormatted = v_NextRenewalDate.toString("EEEE, d") + v_EnglishOrdinal + " of " + v_NextRenewalDate.toString("MMMM, yyyy"); info...

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

Google Drive API v3 - OAuth2 using Service Account in PHP/JWThttp://mail.joellipman.com/articles/google/google-drive-oauth-using-service-account-in-php.html

02 February 2019 branches of variables with fewer commands than unsetting specific variables. Let's specify the output page to be in JSON format: // set content type of this page header('Content-Type: application/json'); // init $api = array(); $access_token = ''; 2a....

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

MS Excel - Split Workbook into separate files per sheethttp://mail.joellipman.com/articles/microsoft/excel/ms-excel-split-workbook-into-separate-files-per-sheet.html

11 February 2019 a single spreadsheet into multiple worksheets of 3000 rows each, and then output each sheet to a separate file that is formatted as XLS (Not *.xlsx). How? In summary, we are going to move the Excel file to a folder of its own. We're going to run two...

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

Zoho Deluge - Update Creator from CRMhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-update-creator-from-crm.html

18 February 2019 the EndPoint: Following the syntax: https://creator.zoho.com/api////form//record/update where ownername is the owner, format is the type (I prefer JSON), applicationName is the name of your app, and form name the Creator form name. If you are on the EU...

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

Convert Past Date to Time Ago in PHPhttp://mail.joellipman.com/articles/web-development/php/convert-past-date-to-time-ago-in-php.html

16 May 2019 but this one I understand and can customize to fit my needs. This PHP function accepts as parameter a SQL date (or date format that strtotime() can convert) and outputs the largest unit (for example "1 year" not "1 year 2 months 3 days, etc...")....

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Zoho Deluge - Compare time and currenttime with timezonehttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-compare-time-and-currenttime-with-timezone.html

13 January 2020 // yields // 25-Mar-2020 20:41:07 // 25-Mar-2020 21:41:07 Setting a TimeZone to a value that is in Zoho date time format - DO NOT USE v_CurrentTime = zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss", "Europe/London"); v_ExpiryTime =...

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

Zoho Deluge: Sort a Map by a specific fieldhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-sort-a-map-by-a-specific-field.html

29 January 2021 by v_SortingKey = ifnull(r_Record.get("date"),""); if(!isNull(v_SortingKey)) { // as this is a date from CRM (atomic date format), arrange so it can be sorted alphabetically in reverse order v_DatePart =...

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

Zoho Creator: Receive JSON via a Shopify Webhookhttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-receive-json-via-a-shopify-webhook.html

15 July 2021 a JSON response from a webhook created in Shopify. See my article: Zoho Deluge: Push Item to Shopify API if you want information on setting up an integration from Zoho Creator to Shopify API. Why? In this example, we want a webhook that whenever an...

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