April 17, 2013 at 8:41 am
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
Any ideas?
April 18, 2013 at 8:13 am
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
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