Tuesday, December 19, 2017

Dual Axis not Working in Tableau

When creating chart with Dual Axis, Synchronize Axis is disabled and cannot be clicked.

Tableau identified this as a bug, though it is supposed to have been fixed on later version, but it somehow still happens.

1. To enable Synchronize Axis, make sure that the data type of both the measures are the same.

2. If you are using Integer data type, problem persists even if the data types are the same. You should convert the measures to float.
Notice that now the Synchronize Axis is enabled after we convert the data type to float.

Format it with 0 decimal points if you want it to appear as integer.

And this is how the end result looks like.

PS: I know the graph is ugly, but I hope you get what you need :)

Thursday, November 23, 2017

Tableau Server Unable to Start due to Blocked Ports

Sometimes you might need to use different (from the default port) port on Tableau. By default, SSL is using port 443. For my case, I use SSL and port 8443 for Tableau Server. After you changed the port, and tried to start Tableau service, you get this error:
*** Tableau Server Gateway requires port 443,
***    in use by process 4.
*** Tableau Server unable to start due to blocked ports.

There are 2 files you need to change:
1. C:\Tableau\Tableau Server\data\tabsvc\config\httpd.conf
2. C:\Program Files\Tableau\Tableau Server\10.3\apache\conf\original\extra\httpd-ssl.conf

Change the port to listen to 8443 (replace 443 to 8443) on these 2 files.

Wednesday, November 8, 2017

Frame cannot Load - Content Security Policy Issue

You are using iframe to load a site from within a website, and the frame does not load.
In my case, I am using https for both the website and the iframe.

If you press F12 on the browser, you will see error similar to:
Refused to frame 'https://iframe_site' because it violates the following Content Security Policy directive: default-src 'self' 'unsafe-inline' 'unsafe-eval'...... (this can be found in web.config). Note that 'script-src' was not explicitly set, so 'default-src' is used as a fallback.

Add the iframe_site to content-security-policy on the web.config.
  <add name="Content-Security-Policy" value="default-src 'self' 'unsafe-inline' 'unsafe-eval' iframe_site;" />

PS: If you are using port for the iframe site, you would need to include the port when adding into web.config.

Monday, October 24, 2016

Cisco VPN not Working on Windows 10

Cisco VPN is not working (cannot login and connect to VPN connection).

1. Right-click on the VPN client, and Run as Administrator.
2. Open regedit and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA and rename:
  • x86 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter" to "Cisco Systems VPN Adapter"
  • x64 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows" to "Cisco Systems VPN Adapter for 64-bit Windows"

Source partially from: IT that should just work

Monday, June 20, 2016

Find Maximum Value from Multiple Columns

A colleague asked about how to get maximum value from multiple columns. After searching it online, I found a pretty good way to get it, and thought it would be good to share here.

For example, I have a table with MemberID and 3 date fields. I want to get the last date from the 3 date fields for each MemberID.

The expected end result look like:

Creating the sample data:

create table #member ( MemberID nvarchar(100), modifiedon datetime, deletedon datetime, addedon datetime )
insert into #member values ('M1', GETDATE(), GETDATE(), GETDATE())
insert into #member values ('M2', GETDATE()-1, GETDATE()-2, GETDATE()-3)
insert into #member values ('M3', GETDATE()-3, GETDATE()-2, GETDATE()-1)

Query to get the max for multiple columns:

 LastUpdateDate =
 ( SELECT MAX(LastUpdateDate)
  FROM (VALUES (modifiedon),(deletedon),(addedon)) AS UpdateDate(LastUpdateDate)
FROM #member


Friday, January 22, 2016

Calculating Customer's Latency

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.

To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:

--Create temp table
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:

--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!


Thursday, August 6, 2015

A Severe Error Occurred on The Current Command

SQL Error:
A severe error occurred on the current command. The results, if any, should be discarded.

Disconnect and connect to the SQL database again.

Source: Stackoverflow

Tuesday, January 27, 2015

XML Data Type is Not Supported in Distributed Queries

Error: Xml data type is not supported in distributed queries. Remote object '<ServerIP>\<ServerInstance>.<DatabaseName>.<Owner>.<TableName>' has xml column(s).

This error occurs when trying to select data from table on different server (using linked server) containing XML data type (even though the column with XML data type is not selected).

Create a view on the remote server, and then select the view instead of directly selecting from the table containing XML data type.

<ServerInstance> =
<DatabaseName> = DB1
<Owner> = dbo
<TableName> = Table1

Instead of using:
SELECT * FROM [].dbo.Table1

Create a view on the remote server:
SELECT * FROM dbo.Table1

And call the view:
SELECT * FROM [].dbo.vGetTable1

Thursday, November 27, 2014

Add Trusted Hosts to Tableau Server

Problem: After deploying Tableau dashboards, you are not able to display the dashboard using trusted ticket method. Error returned: "Could not locate unexpired trusted ticket <ticket_number>".

Solution: Although there could be various reason for this error, you might want to check if the IP address in which the Tableau dashboard is going to be displayed, is already added to the trusted hosts in the Tableau server. If it is not yet added, add it to the trusted hosts.
Note: Make sure not to overwrite the existing trusted hosts already added previously.

Assume the host (where the Tableau dashboard is going to be displayed) is:

1. Go to the Tableau server, and look for dataserver.properties, located in C:\ProgramData\Tableau\Tableau Server\data\tabsvc\config\ (location might vary).
Search for trusted_hosts, and take note of the existing trusted hosts already added previously.
e.g. trusted_hosts=, We will include these IPs again later.

2. Add the IP address(es) where Tableau dashboard is going to be displayed. Open Command Prompt, then go to C:\Program Files (x86)\Tableau\Tableau Server\7.0\bin\ (location might vary), and use the command
tabadmin set wgserver.trusted_hosts "<Trusted IP Addresses>" 
to add the trusted hosts. Don't forget to also include the existing trusted IP addresses previously added, separated by comma if there is more than 1 IP address.
tabadmin set wgserver.trusted_hosts ",,"

3. Save the configuration by using the command:
tabadmin config

4. Check the file dataserver.properties on step 1 and look for trusted_hosts. Now, the content of the trusted hosts should look like trusted_hosts=,,

5. Restart tableau server by using the command:
tabadmin restart
or run services.msc, then look for Tableau Server (tabsvc) service, and then restart the service.

6. Verify if the Tableau dashboard can be displayed properly (in this case, from

7. That's all, folks!

Wednesday, October 29, 2014

Report cannot be Displayed

- Event not fired.
- Microsoft.Reporting.WebForms.HttpHandlerInputException: Missing URL parameter: IterationId
(This is only part of the long error message).
- Report loads forever (never stops loading).

On the aspx page, add attribute: EnableEventValidation="false"

For my particular case, I was trying to display a report (SSRS), and it never stopped loading. However, for my case, it was because of the version of the Internet Explorer (yes, the report is only compatible with IE for mine). After changing the version of the IE through developer tool (F12), I managed to display the report properly. You can also try to display the report from another machine using the intended IE version to see if it displays properly.

Tuesday, September 23, 2014

Combine Rows into a Column

Happened to find a way to combine rows into a column. I find it quite useful, and it comes in handy.

For illustration, suppose we have UserGroup & Users. We want to list the users belonging to each group.



If we do normal joining of the 2 tables, it will look like:

If we combine the values on the rows into a column, it will look like:

Here is the query to do so:

-- Create temporary table to store UserGroup
SELECT GroupID = 1, UserGroup = 'Group 1' INTO #UserGroup
INSERT INTO #UserGroup VALUES ( 2, 'Group 2' )
INSERT INTO #UserGroup VALUES ( 3, 'Group 3' )

-- Create temporary table to store Users
SELECT UserID = 'Superman', GroupID = 1 INTO #Users
INSERT INTO #Users VALUES ( 'Luck', 2 )
INSERT INTO #Users VALUES ( 'Himura', 1 )
INSERT INTO #Users VALUES ( 'Selvi', 1 )
INSERT INTO #Users VALUES ( 'Smarty', 2 )
INSERT INTO #Users VALUES ( 'Lucky', 3 )

-- Normal Join
SELECT a.UserGroup, b.UserID
FROM #UserGroup a
LEFT JOIN #Users b
 ON b.GroupID = a.GroupID
ORDER BY a.UserGroup

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH('') )
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

-- This is the key query to combine rows into column
SELECT UserID + ', ' FROM #Users
WHERE GroupID = 1

Update: Looks like I posted something similar quite some time ago, but using different method: Selecting several rows into one row in SQL


Related Posts

Dual Axis not Working in Tableau

Problem : When creating chart with Dual Axis, Synchronize Axis is disabled and cannot be clicked. Cause : Tableau identified this as ...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts



Follow by Email