SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


working with multiple dataset withing a table in SSRS


working with multiple dataset withing a table in SSRS

Author
Message
bsrlong
bsrlong
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 46
Thank you for taking the time to make your point. I will must was showing a lack of patience and frustration myself.

As soon As I do find a solution I will not just post a reply I will attempt to have a document available.

This is an interesting subject actually. People I find who have SSRS thrust on them lean on the near by for guidance have little patience for learning the product. Well, let me explain, I work in a shop where the M.S. guru fled, leaving me to defend and attempt to promote the products. Anyway, what I see are people who do not really learn the product first, time or not. I see at work one person using stored procedures and views, why, because someone else did before her. Now a SQL contract happens by for awhile. He is not an SSRS expert, so he throws out his preferences. I am sure you have seen the same. So we must strive to direct people toward the product itself.

Granted this can be a bit difficult due to the lack of Reference books. In the COBOL/JCL/CICS world, in general I just needed good reference book for each. The same with DB2/SQL. Then again, I need not need to be the administrator, etc.

In the M.S. world there are so many books, 'how tos', but not many like those few yellow, white and black Osborne Complete References. The one's for Crystal and SharePoint where very handy for me. Of course, they still do not contain everything.

With SSRS one needs a guide that states, this is a Table, this how it works, this is a List, a Rectangle, a Matrix, etc. Instead we have how tos that display one use and do not often answer our question. So we search the the net for videos and M.S. how tos, that still come up short and do not give a Tech explanation.

So, we hack away and we goggle - The forums. I have supported Heat by Frontrange. That is a help desk software. Some from the company used to go in to the forums and actually reply about - well, if you go to the registry,etc. Well, there go. Some one giving good detail. As an admin for the product I was not about to touch the Registry, but I can not complain the person was informative.

You do mention a good point. Design. Only not for queries but the report itself. I inherited this report from someone and since the report was for one student at a time, that is how I designed the report. Some Textboxes and tables at the top showing contact info and Basic info (one dataset) A table with some enrolment info (DS two), a table with FCAT scores (DS3) a table with current classes (DS4) a table with history (DS5).

That was all fine for one at a time. The parameter at the top is student ID and school year. Of course the history table can have no rows or many (That No Rows option is cool in properties. Just type some text for no rows).

So this is a powerful ability of SSRS.

Now, oh, I want all the ninth grade. oops. I did not design the report for that, did I. So I am wondering about throwing it all in a List or Rectangle but how do I reference the other datasets in the List since a list want to be associated with a data set ( I have not tried the rectangle yet), etc.

If I do find a solution that works, I will let you know and I will attempt not to be cryptic.

OK, good hacking.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216610 Visits: 41986
bsrlong (1/13/2009)
As soon As I do find a solution I will not just post a reply I will attempt to have a document available.


I am certainly looking forward to that. When do you think you might be able to get to it? Might be good to follow up your detailed reply by submitting an article on the subject.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dave K-596875
Dave K-596875
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 535
bsrlong (1/13/2009)
Thank you for taking the time to make your point. I will must was showing a lack of patience and frustration myself.

...

OK, good hacking.

I just wanted to say that your post reminds of the random text email spambots generate in order to get around spam filters. Most of it seems random and doesn't make much sense.

You are aware that the original poster was asking about how to use multiple data sets in a table, right? Your follow-up posts seem to indicate that you think the question was how to use multiple data sets on a report. I think most people know how to do that.

I am eagerly anticipating your solution for using multiple data sets in a table in a way unlike the suggestions made in this thread.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216610 Visits: 41986
Jeff Moden (1/13/2009)
bsrlong (1/13/2009)
As soon As I do find a solution I will not just post a reply I will attempt to have a document available.


I am certainly looking forward to that. When do you think you might be able to get to it? Might be good to follow up your detailed reply by submitting an article on the subject.


{insert Jeapordy theme song here} Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95599 Visits: 38968
Jeff Moden (1/16/2009)
Jeff Moden (1/13/2009)
bsrlong (1/13/2009)
As soon As I do find a solution I will not just post a reply I will attempt to have a document available.


I am certainly looking forward to that. When do you think you might be able to get to it? Might be good to follow up your detailed reply by submitting an article on the subject.


{insert Jeapordy theme song here} Tongue


You're bad.....Tongue

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
bboufford
bboufford
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 45
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
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44983 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
David Laplante
David Laplante
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 113
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 Smile

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
Doug Lane
Right there with Babe
Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)

Group: General Forum Members
Points: 723 Visits: 14611
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
Abs-225476
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 831
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search