Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report not working after concatenation of fields. Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
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
Post #1443309
Posted Thursday, April 18, 2013 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
Any ideas?
Post #1443856
Posted Thursday, April 18, 2013 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:38 AM
Points: 370, Visits: 191
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.
Post #1443892
Posted Thursday, April 18, 2013 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
Thanks for you help. Problem solved :))
Post #1443934
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse