December 6, 2015 at 9:41 am
Hi,
I have a really strange situation in SSRS 2014 whereby a single column group is repeating across multiple columns.
So, instead of having a single column in the matrix to show each distinct value, I can the same values repeated across multiple columns.
The SQL query and matrix themselves are extremely simple.
SQL
----
select
Continent
,ShipType
,CalendarYear
,sum(Value) [Spend]
from
table
group by
Continent
,ShipType
,CalendarYear
Example output
------------------
Asia Chemical Tankers 2015 24536789
Asia Container Vessels 2015 3465789
Asia Dry Cargo Vessels 2015 13897625
Asia Gas Carrier 2015 5893760
Now, in SSRS, I have a very simple matrix which uses:
Detail Rows = Spend
Row Group = ShipType
Column Group = Region
The parameter specifies @Year and so I get just 2015 results
However, when it runs it shows this inside matrix:
Region Asia Asia
---------------------------------------------------------
Chemical Tankers 24536789
Container Vessels 3465789
Dry Cargo Vessels 13897625
Gas Carrier 5893760
So you see SSRS displays 2 separate columns for same group, strangely.
It does this for SOME of the Continent groups but NOT ALL.
Western Europe, for example, displays correctly in one column.
This is utterly bizarre.
I have checked everything - the stored procedure, the dataset, the shared datasource
It should display this:
Region Asia
-------------------------------------
Chemical Tankers 24536789
Container Vessels 3465789
Dry Cargo Vessels 13897625
Gas Carrier 5893760
I even completely simplified the originating SQL, as above, to pre-aggregate into a separate table, and even this doesn't work.
SSRS just decides to split out the column group into separate columns.
Can someone please help with this conundrum?
Thanks.
December 6, 2015 at 9:00 pm
Perhaps an unprintable character - group by len().
December 7, 2015 at 1:53 am
First thing I checked.
Even did a ltrim(rtrim()) but to no avail.
Original data came via flat file and imported via SSIS.
Data Types were nvarchar but I changed these and loaded to new table so all similar fields in different tables have the same data type.
Also checked collation.
It's very strange
December 7, 2015 at 5:32 am
Can you post a copy of the rdl? Or even just a screen shot of the setup?
December 7, 2015 at 5:35 am
DuncEduardo (12/7/2015)
First thing I checked.Even did a ltrim(rtrim()) but to no avail.
Original data came via flat file and imported via SSIS.
Data Types were nvarchar but I changed these and loaded to new table so all similar fields in different tables have the same data type.
Also checked collation.
It's very strange
Also, ltrim and rtrim don't remove all non visual characters, only spaces (and maybe one or two others). If you do a quick group by with len and continent that might show the issue
December 7, 2015 at 11:20 am
Hi, yes the group by len() was one of the first things I checked. It displays the expected (normal) result with a single length per continent.
I'll try to upload sthg tomorrow to better visualise the problem
December 8, 2015 at 8:01 am
equally strange solution - one of the reference tables from which the "continent" update is derived appears to have had some invisible extra characters even though these were manually typed in sql update statement.
December 8, 2015 at 1:30 pm
... and just to confirm updating the incorrect column values in table using ltrim(rtrim()) DID actually work and removed the "invisible" characters
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply