Solved

SSRS 2005 Data Sets & Field List

Posted on 2010-09-16
13
1,010 Views
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


0
Comment
Question by:Infoaccess
13 Comments
 
LVL 11

Expert Comment

by:shorak
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.

Rob
0
 
LVL 27

Expert Comment

by:planocz
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.
0
 

Author Comment

by:Infoaccess
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!  

Anyhow..thanks.
0
 

Author Comment

by:Infoaccess
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.

Screen-Shots.docx
0
 
LVL 11

Expert Comment

by:shorak
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

Rob
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Infoaccess
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.

0
 
LVL 37

Expert Comment

by:ValentinoV
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
0
 

Author Comment

by:Infoaccess
ID: 33744758
Yes...the latest SP is installed.
0
 
LVL 4

Expert Comment

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

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:Infoaccess
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!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now