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

15 June 2010 a further issue with our web-report is that even an export to Excel (Data Only) from Crystal Reports 9, created extra columns. This issue could not be replicated on the client machine (ie. from my workstation). I would have to upload the report to the...

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

MySQL Commands to display all columnshttps://joellipman.com/articles/database/mysql/mysql-commands-to-display-all-columns.html

28 October 2010 scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across the tables of the local database which had valid content to extract keywords from. The following is a MYSQL query that displays the...

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

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

24 September 2013 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 tool to compare the outputs. Method...

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

Reorder Columns in a Tablehttps://joellipman.com/articles/database/reorder-columns-in-a-table.html

22 July 2013 Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this. How? Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns...

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

Modifying columns in a tablehttps://joellipman.com/articles/database/modifying-columns-in-a-table.html

24 July 2013 What? This is an article to remind me how to modify a column in a database table the old fashioned way (as in stop making me use GUI interfaces so poorly programmed when even I've made better DBMS tools). All SQL -- Add a column to an existing table...

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

ZohoDesk & ZohoAnalytics: Display Ticket Attachments in Analytics Reporthttps://joellipman.com/articles/crm/zoho/zohodesk-zohoanalytics-display-ticket-attachments-in-analytics-report.html

05 December 2023 info r_Analytics; // // add to analytics m_Header = Map(); m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID); m_Columns = Map(); m_Columns.put("columns", m_Data); m_Params = Map(); m_Params.put("CONFIG", m_Columns.toString()); // // in analytics, browse...

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

SQL Calendar in Business Intelligence Development Studiohttps://joellipman.com/articles/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 on. [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]), -- date used in all calculations for date d.[Date], --generates matrix columns [WeekDay] = DATEPART(WEEKDAY, d.[Date]), --used to display day of month on calendar [Day] = DATEPART(DAY,d.[Date]),...

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

SSIS Convert a string into a datehttps://joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 (DT_WSTR) and another as a integer (DT_R8). Why? It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the...

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

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCasehttps://joellipman.com/articles/microsoft/ssis/ssis-script-convert-uppercase-to-mixed-case-using-titlecase.html

04 October 2013 " and similar should remain lowercase. How? The SSIS Script component task has to be generic, it should only apply this to columns we specifically tick in a package and we should be able to use the same script in any package... eek! Either of the two...

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

Merge the content of two similar tables in MySQLhttps://joellipman.com/articles/database/mysql/merge-the-content-of-two-similar-tables-in-mysql.html

11 January 2011 two above tables are very similar with different column names. I also don't want the data to produce double the number of columns. My Solution SELECT title, intro FROM ( SELECT `title`, `introtext` intro FROM `jos_content` a WHERE a.state=1 UNION ALL...

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

Room Availability Calendar in Business Intelligence Development Studiohttps://joellipman.com/articles/microsoft/ssrs/room-availability-calendar-in-business-intelligence-development-studio.html

23 March 2011 I tried and I spent a few hours searching the net for a solution which displays the hours in the top columns, and undefined number of rooms in the rows. No all I was looking for was a calendar showing 1 day with the hours on the top. Working hours only...

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

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

03 August 2012 ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION FOR XML PATH ('') ),1,1,'' ) + ';'; EXEC(@TableDeclaration); --...

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

Search a database for a string (MySQL, T-SQL)https://joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @mySearchString +''')' + CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext') AND TABLE_SCHEMA='dbo' ORDER BY...

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

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

23 April 2013 The conditional operation failed. Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "DC_MyDate" (7349)"...

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

Zoho Analytics: Set Up a Widget Displaying Sales Personhttps://joellipman.com/articles/crm/zoho/zoho-analytics-set-up-widget-displaying-sales-person.html

24 October 2022 defined values based on the filters I select on a dashboard. First you will need a table or query that has a bunch of columns and rows with one of the columns containing the sale person name. Something like the following: With these kind of columns...

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

ZohoAnalytics: Pivot Campaigns vs Contactshttps://joellipman.com/articles/crm/zoho/zohoanalytics-pivot-campaigns-vs-contacts.html

20 March 2023 campaign results vs contacts. Why? Simply the client wanted a report on customers on each row and then the campaigns as columns. What we want: Contact Name Lead Source Campaign 1 Campaign 2 Campaign 3 ---------------- --------------- --------------...

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

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

07 January 2011 = 'Lansdowne Campus' SET @specifiedDate = (SELECT @GivenDate) SET @specifiedCampus = (SELECT @GivenCampus) -- Get columns SetId and WeekNumber from the weekstructure table based on the given date SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE...

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

CSS keeping the footer at the bottomhttps://joellipman.com/articles/web-development/css/css-keeping-the-footer-at-the-bottom.html

25 April 2011 Stick to the Bottom of the Page Aim: Header Left Right Footer Situation The header displayed fine. The left and right columns finally got them side by side. But the footer that has a background image was under the left and right columns... The main...

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

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

08 August 2011 I want to convert a start and end date to a single time value (hours minutes seconds mseconds) I want to total 3 of the columns (which measure in milliseconds) and convert to a time value with milliseconds showing. Now why don't these match? Using in an...

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

MySQL parameters in Excel 2007 PivotTableshttps://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