SSRS 2005 Data Sets & Field List

In SSRS 2005, I have a report defined with 2 data sets and 2  tables as follows:
table  tblYtd uses dataset   ytd_ia_rep
table  tblFytd uses dataset   fytd_ia_rep

Each of these data sets returns the same field list, i just run them with different parameters.

When I go into the Expressions window on the table tblFytd  to try to add a field to one of the tables, the only fields listed are those from the first table.    So the windows displays Fields (ytd_ia_rep),  but not Fields (fytd_ia_rep).

How do I get the fields to show up from the dataset this table uses?   fytd_ia_rep


InfoaccessAsked:
Who is Participating?
 
InfoaccessConnect With a Mentor Author Commented:
I have since deleted it and started over with a brand new report.    It worked the way it should, so I don't know what the problem ever was.   I should close this out!   Thanks.
0
 
shorakCommented:
Are you 100% sure that the table is referring to the correct dataset as you have mentioned that both datasets return the same data fields but for different periods. A table in a report can only be associated with one dataset so it sounds like you might be looking at the wrong table or the table is referring to the wrong dataset.

I take it that you inserted a new column into the table and then used the expression box to refer to the fied in the dataset that you want to display? You could also drag the field into the new column by opening the Datasets toolbox (by going to the View Menu and selecting 'Datasets') and explanding the desired dataset and then drag the relevant field to your table.

Its worth checking to make sure that your table is referring to the correct dataset first before you continue.

Rob
0
 
planoczCommented:
I would change to one dataset with two parameters of @StartDate and @EndDate.
You will be able to check Days,Months and Years. I f that  is what you are searching for in your datasets.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
InfoaccessAuthor Commented:
Shorak, I've check the dataset name on the table a few times, but I will go back and check again.   I didn't know you could drag the fields to the table cells directly...so I will try that also.     I can't believe I've been using SSRS for 2 years and didn't know that!  

Anyhow..thanks.
0
 
InfoaccessAuthor Commented:
I checked, and the dataset name was correct.  I originally created that table by copying the first one, so I deleted it and created a new table from scratch.   It's setup to use the fytd_ia_rep dataset.   So I dragges some fields from that dataset into the details row of the table, and it creates an expression instead of allowing me to use the field.    Sum(Fields!ty_dollar_sales.Value, "fytd_ia_rep")

If i change the cell to use just the fieldname,  =Fields!ty_dollar_sales.Value, when I run the report, I get the values from teh ytd_ia_rep dataset.    

I looked in the expressions window, and still the only fields listed are for the ytd_ia_rep dataset.    

I'm attaching a document that has a screen shot of the table properties and of the Expressions Window, hoping that helps.

Screen-Shots.docx
0
 
shorakCommented:
I'm a little confused as to why the expression is Sum(Fields!ty_dollar_sales.Value, "fytd_ia_rep") when dragging into the detail row as this should only happen when your dragging into the table footer.

Looking at your screenshots..it seems you have setup the table property correctly but for some reason, the expression is only showing the field for the other dataset. You could try adding a 3rd table but giving it a different name to the 2nd one and set it up to refer to the fytd dataset and see if that displays the fields from the correct dataset in the expression. If that dosnt work then I'm confused as much as you are. Its possible that the xml behind the scenes of the report has become currupt in someway and isnt reflecting the changes you have made. Not sure what else you could try to be honest.

Sorry if I havnt helped you much

Rob
0
 
InfoaccessAuthor Commented:
I've tried a whole new project, new tables...all kinds of things.  But I just can't seem to get fields from more than one dataset.   It's kind of mind-boggling to me!  

 I tried using subreports, but then I get page breaks between each of the subreports, and the defeats the purpose of have a summary report to show 4 different periods on 1 page.    I did some investigation on the page break issue, and it appeasr that it's a design issue, and Microsoft is considering changing that in SSRS 2008.

Thanks for  your input.  At the very least, it makes feel better that I'm not just being stupid!  
I'm going to take a whole different approach and give up on the multiple datasets in one report for now.

0
 
ValentinoVBI ConsultantCommented:
This may be a silly question, but are you sure you've got the latest SP installed?  I've created reports using the 2005 version and using several datasets in one report in the past, so it should be possible for you as well...

See http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4
0
 
InfoaccessAuthor Commented:
Yes...the latest SP is installed.
0
 
joeviCommented:
Would it be possible for you to attach the RDL file?
0
 
InfoaccessAuthor Commented:
Because I never found a fix for hte problem, and I had to move on to getting a solution created.    Should have closed the out sooner!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.