working with multiple dataset withing a table in SSRS

Question

working with multiple dataset withing a table in SSRS

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

bboufford Old Hand Points: 397 More actions · Answer 1

So - after reading all of this and being totally confused. Is there an answer. Can I link (therefor group) mutliple datasets to return one record per group

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

bboufford (4/22/2010)
So - after reading all of this and being totally confused. Is there an answer. Can I link (therefor group) mutliple datasets to return one record per group

Not that I am aware of. Remember for each dataset you have to make a call to the database. If the data is coming form the same source you can usually get it all in one call, which will normally be faster.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

David Laplante Grasshopper Points: 15 More actions · Answer 3

Hoping this is not too late, here is one solution. Not an ideal one but still one that can help in easy situations

WARNING: This is not ideal in all situations. Use common sense 🙂

Using the report's code behind feature.

- Declare an array to store the whole of the second dataset

- Create function to initialize (redim) the array to the number of rows in your second dataset

- Create a function to store rows of your second dataset on a row by row basis

- Create a function to retrieve the desired row by passing in one or more parameters

This will mimic SSRS R2's lookup function. If you expect the number or rows retrived to be more than one, concatenate them with VbCrLf.

Let me know if you need actual code and i'll whip something up

Doug Lane SSCrazy Points: 2118 More actions · Answer 4

Jack isn't saying multiple datasets are bad; he's saying trying to blend multiple datasets in the same table control is bad. RS just wasn't designed to take more than one dataset per control. That said, whether we can all get along or not, gjyothi still would like help.

If I understand the problem correctly, you want to show something like this:

Invoice1

___MeterInfo1 ... ... ... ...

___MeterInfo2 ... ... ... ...

___MeterInfo3 ... ... ... ...

Invoice2

___MeterInfo1 ... ... ... ...

___...

If that's the case, I think Jack is right in that it's easier to merge two sets of data on the back end than it is to try to mash them together in RS. If someone has a more efficient hack for that, I'd love to hear it.

Edit:I just noticed there was a whole second page of posts after I responded, so, um, yeah...sorry about that.

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 5

Hi,

I think the best way to do this is to get all the data in a single dataset and then group by the invoice number, and then group by the invoice details. You will just need to join using a left outer to the details table. This will ensure that any invoices with no details will still be shown.

The script below is from the Adventureworks database.

select h.*,d.* from sales.SalesOrderHeader H

left join sales.SalesOrderDetail D on D.SalesOrderID=h.SalesOrderID

where SalesOrderNumber = 'SO43659'

Using the above as an example you would need to create two groups the 1st one would be

SalesOrderId and then the 2nd would be SalesOrderDetailId.

Let me know how you get on.

Thanks

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 6

I concur with SSC-Enthusiastic.

You'll only need to cross data sets if the two things you want to mesh are from different servers. Otherwise you can combine the two tables (even across databases) in a single T-SQL statement, and then use simple groups on the table to display it how you want.

2008R2 lets you do limited lookups, and another poster mentioned code-behind, but I consider such trickery to be beyond most mere mortals and usually more trouble than they're worth 😛

PS: I don't usually resort to violence but the world would be a better place with one less scab in particular from this thread leaving alive.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

Doug Lane (10/13/2010)
Jack isn't saying multiple datasets are bad; he's saying trying to blend multiple datasets in the same table control is bad. RS just wasn't designed to take more than one dataset per control. That said, whether we can all get along or not, gjyothi still would like help.
If I understand the problem correctly, you want to show something like this:
Invoice1
___MeterInfo1 ... ... ... ...
___MeterInfo2 ... ... ... ...
___MeterInfo3 ... ... ... ...
Invoice2
___MeterInfo1 ... ... ... ...
___...
If that's the case, I think Jack is right in that it's easier to merge two sets of data on the back end than it is to try to mash them together in RS. If someone has a more efficient hack for that, I'd love to hear it.
Edit:I just noticed there was a whole second page of posts after I responded, so, um, yeah...sorry about that.

This is an old post and I suspect the OP is long gone... however... a simple two level hierarchical result set would certainly do the job here. A hierarchyID column (or the equivalent) to sort on would do the job nicely.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

websolprov SSC Enthusiast Points: 137 More actions · Answer 8

I came across the following post which may be of use to the OP:

http://www.sqlservercentral.com/Forums/Topic429975-150-1.aspx?Update=1 also do you think that perhaps using a list as a "back bone" off which you can then group and hook the other datasets too using parameters?

www.host-junction.net

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please note that this thread is 2 years old.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

Heh... makes no moxnix... some revived conversations are just as fun. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

websolprov SSC Enthusiast Points: 137 More actions · Answer 11

Yes I realise that, but very often the threads will be referred to over and over again as people learn from what others have done before them. I am sure that if it was irrelevant then people would have marked it for archive.

www.host-junction.net

stan.goodvibes SSC-Addicted Points: 426 More actions · Answer 12

Exactly. I've got the same issue 2 years on and here I am in this thread. I've got a pretty complicated annual statement report for about 2000 superannuation members, each one about 10 pages long and lots of detailed tables about different stuff for each member. Doing it all in on Stored Proc would be a nightmare.

So I have a main SP that gets all the basic info for each member (name address etc etc) then I need to call a load of other procs for each member. I think the subreport option is going to be the best bet. Not sure why I didn't think of that!

BTW it was worth it to see BSRLong in action. Good to see that the thread made it back on subject. Hopefully he is off spending less time on forums and more time on learning english and basic social skills...

Thanks for all your input,

Dave

Amit Raut SSCrazy Points: 2613 More actions · Answer 13

Hey BSRLong,

Where are you? And where is your solution? 😉

stan.goodvibes SSC-Addicted Points: 426 More actions · Answer 14

Amit Raut (10/9/2013)
Hey BSRLong,
Where are you? And where is your solution? 😉

hahahahaha God what a blast from the past this thread is. Can't believe that was over 2 years ago.

I somehow managed to struggle on with my solution without any further input from BSRLong, God only knows how...

🙂