Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SSRS 2005 Data Sets & Field List

Posted on 2010-09-16
Medium Priority
Last Modified: 2012-06-21
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

Question by:Infoaccess
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Expert Comment

ID: 33699530
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.

LVL 27

Expert Comment

ID: 33700005
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.

Author Comment

ID: 33700321
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 I will try that also.     I can't believe I've been using SSRS for 2 years and didn't know that!  

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 33700657
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.

LVL 11

Expert Comment

ID: 33700843
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 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


Author Comment

ID: 33701245
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.

LVL 37

Expert Comment

ID: 33733047
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...


Author Comment

ID: 33744758
Yes...the latest SP is installed.

Expert Comment

ID: 33999007
Would it be possible for you to attach the RDL file?

Accepted Solution

Infoaccess earned 0 total points
ID: 34001078
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.

Author Closing Comment

ID: 34031662
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!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question