Does anyone know of any brillant SSRS consultants? I’m having an issue with a report (custom) where I have two different DataSets and I want to combine them.
If I do a LEFT OUTER JOIN on the DataSet1 Query the information from DataSet2 will go on the end of the rows instead of creating new rows.
I want it to create new rows. I have placed this into a subreport which is pulling the data correctly. DataSet one is in a group. There is matching information from both datasets but I can’t seem to get it to show correctly. It will either repeat throughout the report based on the number of rows from DataSet1 rather than just show the data in the correct groups based on the matching data in DataSet2.
I hope this makes sense and hence the reason for asking for a consultant that is willing to take some freelance work.
We have used Dave for a couple years now I think. He has done a lot of report work for us since go-live and always does a great job. Would recommend Dave to anyone looking for SSRS report work.
@aarong two things: if you post contact information people can contact you directly rather than seeing sales pitches, etc. here.
Second, the community may be able to help in any event! It sounds like instead of having all of the columns for a joined dataset, you might want to do a UNION between the two datasets which should give you all rows from both datasets. In order for that to work, you’d need to select some dummy fields in the opposite dataset and line up the columns. For example:
SELECT 0 As PackNum, OrderDtl_PartNum As PartNum
FROM DataSet1
UNION
SELECT MscShipDt_PackNum AS PackNum, MscShpDt_PartNum As PartNum
FROM DataSet2
Mr. Aaron: You could do the Union in SSRS - however, I think doing it in the BAQ would be the easier.
Also, I must admit i do not know what you are doing with your Grouping. I thought you mentioned that you wanted to group by part number. With that in mind - i would think that a subreport may be easier. I would add a subreport to a PartNum Group Footer and filter the subreport by PartNum. The report will print all of the Sales Order records first and then it will print the MscShip records.
The subreport has some limitations in that it would be difficult to lets say add up ALL of the PartNum records for one part number. Also the subreport pagination in SSRS has limitations i.e. repeating headers and pagination.
However, in theory the subreport would easier that a Union - in my opinion.
Yes, you could do it in SSRS. I’ll also agree with @DaveOlender that if this happens to be a BAQ Report based on a BAQ you’d want all of the querying work to be done in the BAQ rather than SSRS for performance reasons. If you’re working with a RDD set of datasources, then rejoining in SSRS is an option.
I’ll further agree with Dave that subreports or different tablixes are usually a better option for displaying one set of data and then another, but if you have a definitive need to have the data stacked for interleaving data via grouping or for exporting, then the union remains a valid option.
I’m trying to format the address to remove blanks and have a clean address e.g 10 Downing St, London SW1A 2AA rather than 10 Downing St,,,London SW1A 2AA
How about constructing the string with concat not worrying about blanks the first time through, then removing them after with REPLACE(yourconcatenatedstring, ' ,', '');
