We have an old Report that is Crystal and it is based on a Union BAQ. How to do you get all the data to show through in SSRS? In Crystal he had the data from the subquery1 on one side adn the date from Subquery2 on the other side. I can not seem to get that to work in SSRS. So figured, I’d ask for some advice.
If its a BAQ Report you can do it in the BAQ. If its just an RDD, you will have to pass both tables as per usual RDD Relationships…
Then you will have to modify your SQL in the SSRS Report (.rdl) just basic UNION ALL on the 2 tables.
I haven’t used the new BAQ RDD Features of 10.2.x yet, maybe it makes it easier.
It’s a BAQ report my predecessor wrote. So, If I just open it up in SSRS and start designing I can call each data separately, correct?
If its a clean SSRS untouched… What I usually do is build my BAQ… Then I open up Report Style Maintenance, I should be able to find my BAQRpt there and I pick my style and I click on “Sync Dataset” and that usually reads all my changes from my BAQ and gives me a clean base with tables and columns synced to rdl.
You could do your UNION in the BAQ and then all you do in SSRS is read the data.
1 Like
This will be clean. Thanks! I will see what happens!
So, I did it clean and when I open the SSRS I only set one of the BAQ Results, and that is only from the top level, not the Union.
What do you get when you run your BAQ?
Also remember the BAQ Report will pass in Filters if you have any specified or parameters in BAQ Designer Form.
That’s how it’s designed. You only ever get the top level results. What were you expecting to get?
So Crystal is better in this sense because it can pull the data Subquery2 (Union) data in on top of the Top level. Because I got a crystal report that does just that. So what good is a BAQ Union if SSRS can only see the top level?
You can take 2 tables of info (like jobmtl and jobasm) and make them one table. That’s the union.
To help understand a little bit more, we have artists we we like t keep track of the Proofs they do for customers and Steps the do. So the Union is between Proofs and Steps. So my next queston would be, how woudl I get this data to show up for me all on the top level if it is Union? Or do I need to rewrite it so it is not union?
The top level has all of the data. Put another field in both that’s just a calculated character field. In the Steps one, make the value ‘steps’ and in the proof one make the value ‘proofs’.
Then when you run the query you can see which table it came from.
Put another way, how many rows do you get when you run just steps? How many when you run just proofs? Does the union number of rows equal both of them put together?
It’s like taking 2 decks of cards and then shuffling them together. If they look similar, it’s just a bigger deck.
1 Like
Put the first InnerSubQuery on the TopLevel and it will pair itself with the UnionAll SubQuery.
3 Likes
No, I did not. Not yet at least.
@hkeric.wci @Banderson Her is the BAQ I am working with. May not make much sense for your company, but figured and extra set of eyes would help me out. I really need to figure out a way to get these to pull in the same report.
ArtProductivity.baq (36.7 KB)
I have done UNIONALL for the Subquery and the data pulls into the BAQ testing. However, when going into the SSRS, it just shows the top level BAQResults.
There is only one level William. When you do a union, you take two tables/queries and combine them into one table. Does your top level have results from ‘steps’ and ‘proofs’? I see you have the calculated field in there to see which one it is.
It shows up in the BAQ when I test it, but not when it goes to SSRS. So, if IA m understanding correctly, SSRS will only pull the info on the top level, even if the BAQ will pull in from both top and bottom because of the union?
My SSRS knowledge is limited, so I don’t know for sure once I get past the BAQ. Someone else will probably have to jump in.
Did you modify your SSRS report to show that calculated field?