Post reply

Report not working after concatenation of fields.

  • April 17, 2013 at 8:41 am

    #271279

    Hello everyone,

    I am having problems running a report after making a change to concatenate two fields.

    I have a report with parameters. @StartDate, @EndDate, @ShipperNo, @ServiceType.

    I have a dataset called Distinct Account. The query is as follows:

    SELECT DISTINCT Deliveries.ShipperNo + ' ' + ShipperName.AccountName AS ShipperNo

    FROM Deliveries LEFT OUTER JOIN

    ShipperName ON Deliveries.ShipperNo = ShipperName.ShipperNo

    ORDER BY ShipperNo

    Previously my query was: Select Distinct Deliveries.ShipperNo FROM Deliveries

    This query picked up all the shipper nos which enabled my report to run.

    I have now joined to the ShipperName table to get the Account Name and to able to concatentate two fields: ShipperNo and ShipperName.

    On my parameter ShipperNo i have selected Allow Multiple Values.

    On my Parameter ShipperNo Under Available values I have selected Get Value from a query.

    Dataset:DistinctAccount

    Value Field: ShipperNo

    Label Field: ShipperNo

    When i run my report on my multiple dropdown list for Shipper No, it looks as follows:

    336152 Granger

    373733 Hartley

    Previously my report ran correctly when not using concatentation. I am now finding that my report is not returning any data at all.

    Am i doing something wrong?

    Thanks

  • April 18, 2013 at 7:35 am

    #1607747

    Any ideas?

  • April 18, 2013 at 8:13 am

    #1607781

    Firstly, does running the join query in SMS result in having lots of records, or just the 2 you listed.

    At first glance it appears as if it were working as an INNER join, not an OUTER, however I believe that you may be confusing SSRS with that way you setup the values/label for the dropdown and in your query. Here is how I would do it more clearly:

    1. The dataset shoulld contain two fields

    SELECT DISTINCT Deliveries.ShipperNo, Deliveries.ShipperNo + ' ' + ShipperName.AccountName AS ShipperLabel

    FROM Deliveries LEFT OUTER JOIN

    ShipperName ON Deliveries.ShipperNo = ShipperName.ShipperNo

    ORDER BY ShipperNo

    2. Then for the parameter field, set the Value Field: ShipperNo and the Label Field: ShipperLabel

    Hopefully that will help, good luck.

  • April 18, 2013 at 9:16 am

    #1607819

    Thanks for you help. Problem solved :))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply