Select unique values in Microsoft Excel columnhttps://www.joellipman.com/articles/microsoft/excel/select-distinct-values-in-microsoft-excel-column.html

03 July 2012 A1000 and excludes the blank/empty cells. Display all Unique Found this note on one of Microsoft Help sites: Office 2010: Select the data range to do this to. Click on the "Data" tab. Click on "Advanced" (Advanced Filter). Tick the "Unique records only"...

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

SSRS Hide results table if emptyhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-hide-results-table-if-empty.html

29 March 2011 showed the heading "Equipment". Iteration in T-SQL? Unlike good old MySQL where you can do iteration within the query (SELECT @ncount:=@ncount+1), we're stuck with T-SQL. Ask Google how to iterate in T-SQL and you're confined to functions, temporary...

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

Importing Joomla articles to WordPress postshttps://www.joellipman.com/articles/cms/wordpress/importing-joomla-articles-to-wordpress-posts.html

16 August 2011 v3.2.# ------ ----------------------------------------------------------------- INSERT INTO my_wordpress_db.wp_posts SELECT id 'ID', 1 'post_author', created 'post_date', created 'post_date_gmt', CONCAT(introtext, ' ', `fulltext`) 'post_content', title...

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

List MediaWiki Articleshttps://www.joellipman.com/articles/cms/mediawiki/list-mediawiki-articles.html

22 September 2011 again but I've posted my finished query here: The base query to list mediawiki articles Page ID, Title, Content, Category SELECT p.page_id AS PageID, CONVERT(p.page_title USING latin1) AS PageTitle, CONVERT(t.old_text USING latin1) AS PageContent,...

• Type: Article
• Author: Joel Lipman
• Category: MediaWiki
• 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 I get warning errors (with an end!) BEGIN -- initial query to get me the student ID based on the inputted parameter SELECT student_accounts.student_id INTO p_STUDENT_REF FROM student_accounts WHERE student_accounts.student_username = p_STUDENT_ADNAME;...

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

Copy a table with structure and data into a temporary tablehttps://www.joellipman.com/articles/database/t-sql/copy-a-table-with-structure-and-data-into-a-temporary-table.html

03 August 2012 the structure and data. Thinking inside of the box I think everyone suggests the following (or at least the idea of): SELECT * INTO #MyTempTable FROM @GivenTable If @GivenTable is a parameter then the above will simply return an error. Also, if it was...

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

Generate Academic Calendar using MySQLhttps://www.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 I'll use this table to create the calendar. Query #1: Works from command-line but may generate "Empty Query 1065" SELECT calendar.WeekNumber AS AcademicWeek, DATE_FORMAT(calendar.StartDate, '\%e-\%b-\%y') AS Mon, DATE_FORMAT(DATE_ADD(calendar.StartDate,...

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

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 3 tape-monkeys Now suppose we had a convoluted query which needs to join the two tables so that we get the "MapOut" value: SELECT r1.MapIn ( SELECT (COUNT(e2.EmpType)-1) AS MyCount FROM DMExtractEmployeeTable e2 LEFT JOIN DMExtractReferenceTable r2 ON...

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

Problem creating SSRS report subscriptionshttps://www.joellipman.com/articles/microsoft/ssrs/problem-creating-ssrs-report-subscriptions.html

11 November 2013 Upload the report to the ReportServer. Hover the mouse cursor over it and click on the dropdown arrow to its right Select "Manage" in the dropdown menu Select "Data Sources" in the left menu Specify a "custom data source" and test the connection. Ensure...

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

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 - although I used a network share without any issues) Add a Connection Manager Right-click in "Connection Managers" Select "New Flat File Connection..." Browse to the first file in the folder to loop through and select it. Set the connection manager...

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

Zoho CRM: Get Unique Values of a Text Fieldhttps://www.joellipman.com/articles/crm/zoho/zoho-crm-get-unique-values-of-a-field.html

08 October 2021 and even on this website so we'll skip to the COQL query. This is what I have: // // build up COQL query v_CoqlQuery = "select distinct Currency from Quotes where Subject!=''"; // // build up parameters m_Params = Map();...

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

MySQL Group_concat equivalent in T-SQL and Oraclehttps://www.joellipman.com/articles/database/mysql-groupconcat-equivalent-in-t-sql-and-oracle.html

28 November 2011 because I find myself needing to group concatenate more often from another table then using data from the same table. MySQL SELECT ( SELECT GROUP_CONCAT(column_to_return_as_string) FROM a_pretend_table WHERE a_condition=true ) AS my_field_name FROM...

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

Blank columns issue when exporting to Excel (Data Only) from Crystal Reportshttps://www.joellipman.com/articles/microsoft/excel/blank-columns-issue-when-exporting-to-excel-data-only-from-crystal-reports.html

15 June 2010 in turn we export to Excel: Note that as this is from within a web application, there are no extra options or dialogs to select. The export had to work from this point on. I can't take any credit for the below as it was copied from...

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

Excel PivotTable Filter List Orderinghttps://www.joellipman.com/articles/microsoft/excel/excel-pivottable-filter-list-ordering.html

05 September 2011 button Re-Tick the filter to add it back to the report Move this added field to the Report Filter section (click on it and select "Move to Report Filter") Advanced Sorting So the above works which is great but what if you have a list of month names?...

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

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

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

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• 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 Yes you could just run a SELECT DISTINCT query along with collation to 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...

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

Compare two databases using T-SQLhttps://www.joellipman.com/articles/database/t-sql/compare-two-databases-using-t-sql.html

24 September 2013 #1 listed here and then just built on this. Method #1 I got this first query from the awesome site that is StackOverflow: SELECT * FROM INFORMATION_SCHEMA.COLUMNS Run this command against both databases and you have their full schemas. Use a comparison...

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

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

15 July 2021 your Zoho Creator: Log-in to your ZohoCRM Create a Connection Go to Setup > Developer Space > Connections > Add Connection Select the Zoho Creator connection, give it a Connection Name, I'm going with joels_creator Give it a lowercase Connection Link...

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

Zoho Deluge: Generate List of TimeZoneshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-generate-list-of-timezones.html

19 October 2021 A quick article to take a list of options from a HTML select element and convert to a Zoho Deluge list that will eventually be used to populate a dropdown. Why? Because I find myself doing this quite a bit and wanted a quick way of extracting a SELECT...

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

Zoho Inventory & eBay Picture Services: UploadSiteHostedPictureshttps://www.joellipman.com/articles/crm/zoho/zoho-inventory-ebay-picture-services-uploadsitehostedpictures.html

31 March 2022 Login to Zoho One Go to Settings (cog in top right) Click on Applications i the sidebar Click on the Add Application button Select Creator by clicking the Add button below it Link it if applicable to your super admin account Create an app, a form and...

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