Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

working with multiple dataset withing a table in SSRS Expand / Collapse
Author
Message
Posted Tuesday, January 13, 2009 8:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 16, 2009 7:19 PM
Points: 3, 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.
Post #635973
Posted Tuesday, January 13, 2009 8:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #635978
Posted Wednesday, January 14, 2009 8:34 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 684, Visits: 518
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.
Post #636286
Posted Friday, January 16, 2009 3:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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} :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #638568
Posted Friday, January 16, 2009 3:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 20,744, Visits: 32,557
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} :P


You're bad.....:P



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)
Post #638573
Posted Thursday, April 22, 2010 12:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 4, 2010 7:37 AM
Points: 17, 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
Post #908928
Posted Thursday, April 22, 2010 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 10,295, Visits: 13,278
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

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
Post #908941
Posted Wednesday, October 13, 2010 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 9:57 AM
Points: 1, Visits: 61
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
Post #1003606
Posted Wednesday, October 13, 2010 8:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:09 PM
Points: 509, Visits: 14,597
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.
Post #1003660
Posted Wednesday, October 13, 2010 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:22 AM
Points: 213, Visits: 779
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
Post #1003712
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse