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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy