Archive

Archive for the ‘SQL’ Category

Random results after PivotTable refresh.

March 8th, 2012 admin No comments

A funny thing happend to me lately…

A user (manager) came to me with a PivotTable his predecessor created that produced random results after refreshing.
The PivotTable was based on a PivotCache connected to a query on a SQL Server.
When the query was run in Management Studio it was complaining about a conversion and returned a error.
This query didn’t produce a error in excel but just returned the records upuntil the error.
As the query wasn’t sorted the number of records returned to excel was random as so the results in the PivotTable.

If after the first refresh the data was almost complete nobody would have noticed the corrupt results

Hope I this helps anybody having the same issue….

p.s. Self Service BI is not for everybody …

Categories: Excel, Pivot table, SQL Tags: , ,

Importing varbinary fields into Excel

May 18th, 2011 admin No comments

I needed to compare two large datasets and find out if they contained the same records.
So I amended the records with a HashByte at the end so I could check if they existed in the other one.
First I imported both queries in Excel with the idea to do a lookup on them.
But a varbinary field is not imported in excel and it will return no error when ommitting a field,
except when the only field is a varbinary field then Excel willl tell you:

No columns that Microsoft Excel can use were returned from this query.

So I had to go back to the Management Studio and join the two sets which was a pain because they were on differend servers.
So much pain I kept trying to find a Excel solution.
All I had to do was convert the varbinary field by making it a parameter in a function which would return a normal varchar.

So

SELECT [field] FROM [table]

had tot change to

SELECT master.dbo.fn_varbintohexstr([field]) AS [field] FROM [table]

 

enjoy

Categories: Excel, SQL Tags: ,