Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SLM09
Starting Member
31 Posts |
 Posted - 2010-01-28 : 13:55:13
|
| Hi all,I apologize for such a simple question, but I am new to SSRS and I have had little luck finding an answer online.I have created a dataset that is working fine. If I drag elements into the layout tab, the report registers them. However, the default is to select first(), sum(), etc.I want to return a range of options. For testing purposes I will be happy to return every hit from the table for the column I choose. IE- if I have 20 rows, then I want all 20 results one after another for the columns I added.If I simply remove the first() and the ,"source"), I get an error that the report doesn't recognize the field which makes sense since it has no way to tell where its coming from.Can anyone help?Thanks |
|
|
SLM09
Starting Member
31 Posts |
Posted - 2010-01-28 : 14:43:13
|
| Little update. I did find how to add a table. By connecting the table to a dataset in properties, I was able to (sort of) get what I was shooting for.Also- to clarify the above post since I explain things badly, quick example:=First(Fields!date.Value, "Credit_Acct_Work_Approved") Works for me=Fields!date.Value does not workTrying to list everything out in a given field like in access. |
 |
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2010-02-01 : 16:15:34
|
| Hi SLM09,Not sure if this is what you're after or not.....but here goes:-)Each data region on your layout needs to be linked with a dataset.....i.e. each table, or tablix, or text box etc (which are called data regions) need to know where to get there data from.One of the properties for each data region is its 'DataSetName'. This should be populated with the name of the dataset that you are using. Only one dataset per dataregion is allowed (but you can write whatever SQL you want in your dataset and hence can involve as few or as many database tables as you want in a dataset).Personally, I try and write everything in the SQL statement of the dataset so that I have little or no functions / calcs in the report expressions (i.e. create aggregate tables and multiple joins / groups etc.. to fit your needs).When you write an expression like =First(Fields!date.Value, "Credit_Acct_Work_Approved") you are explicitly including the dataset. That is, the string parameter (after the comma) is stating the dataset name. Hence, SSRS knows where to get the data from.When you write an expression like =Fields!date.Value SSRS doesn't know where to get the data from unless you have the data set name stored in the 'DataSetName' property of the dataregion of the expression you are typing.The keyword First is an SSRS function...there are loads of these like SUM, MAX, MIN etc... These are essentially SQL aggregate functions like those that you use when applying the GROUP clause. So, when you type MAX(Fields!Date.Value) into a field of a dataregion that has a DataSetName property of Table1, you end up rendering on the report the Maximum value in the Date column of the data set named Table1.Apologies if this was not what you were after.... :-)Kind regards,JAG7777777 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-02 : 05:30:48
|
quote:
Originally posted by SLM09
Hi all,I apologize for such a simple question, but I am new to SSRS and I have had little luck finding an answer online.I have created a dataset that is working fine. If I drag elements into the layout tab, the report registers them. However, the default is to select first(), sum(), etc.I want to return a range of options. For testing purposes I will be happy to return every hit from the table for the column I choose. IE- if I have 20 rows, then I want all 20 results one after another for the columns I added.If I simply remove the first() and the ,"source"), I get an error that the report doesn't recognize the field which makes sense since it has no way to tell where its coming from.Can anyone help?Thanks
you need to first drag a container like table and then drag and drop columns into detail row and it will show all the 20 values |
|
|
|
SLM09
Starting Member
31 Posts |
Posted - 2010-02-04 : 10:48:49
|
| Thanks for all the info.Jag- Sounds like I was dead-on with what the problem is, and although your explanation makes sense, I can't seem to get it to work. When I add a text field to my layout =Fields!something.value, it should be coming from dataset1 lets say. But when I right click the text box and try to manually tell it, "use dataset1", it only has a drop down for Data Region... and that drop down is empty. Logically, I would think that's where I would select the dataset, but I think this is whats messing me up. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 10:54:26
|
quote:
Originally posted by SLM09
Thanks for all the info.Jag- Sounds like I was dead-on with what the problem is, and although your explanation makes sense, I can't seem to get it to work. When I add a text field to my layout =Fields!something.value, it should be coming from dataset1 lets say. But when I right click the text box and try to manually tell it, "use dataset1", it only has a drop down for Data Region... and that drop down is empty. Logically, I would think that's where I would select the dataset, but I think this is whats messing me up. Thanks
Nope once you add the dataset and click refresh in data tab, you will get dataset name listed in dropdown within container where you need to select dataset. dragging and dropping fields onto container from dataset will automatically associate that container to dataset. |
|
|
|
|
|
|
|
|
SSRS simple dataset Q