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

SSRS 2005 Data Sets & Field List

Posted on 2010-09-16
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
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 directly...so I will try that also.     I can't believe I've been using SSRS for 2 years and didn't know that!  

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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


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

See http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

837 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