Post reply

bizarre behaviour SSRS

  • December 6, 2015 at 9:41 am

    #302780

    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

    #1844273

    Perhaps an unprintable character - group by len().

  • December 7, 2015 at 1:53 am

    #1844298

    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

    #1844338

    Can you post a copy of the rdl? Or even just a screen shot of the setup?

  • December 7, 2015 at 5:35 am

    #1844340

    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

    #1844463

    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

    #1844661

    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

    #1844768

    ... 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