Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

581 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