Datasource Change HELP!

I am working in a dev environment. My report uses 20 different datasets but
they all point to the same datasource. I am moving from Dev to a UAT
environment which points to a different DB. Is there a way to change the
datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
going in and changing all the datasources for each of the datasets one by one?
Thanks in advance
OSTry opening XML <F7> and doing a find/replace on the DB name. This is
assuming that the new datasource is already defined in the solution and the
table names are the same.
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||Hi,
Have you used as a "shared resources" for all your reports? then it should
not be a problem since you need to just change the database and user
name/password etc.. details at your datasource and you will be up and
running.
Amarnath
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||William,
This really helped me! Thanx a bunch!
OS
"William" wrote:
> Try opening XML <F7> and doing a find/replace on the DB name. This is
> assuming that the new datasource is already defined in the solution and the
> table names are the same.
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||How can we programatically change the DB name?
Or
Change them dynamically in the URL?
Prad
Amarnath wrote:
> Hi,
> Have you used as a "shared resources" for all your reports? then it should
> not be a problem since you need to just change the database and user
> name/password etc.. details at your datasource and you will be up and
> running.
> Amarnath
>
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||What you should be doing is to have a shared datasource. The first time you
deploy the datasource is deployed too. Then you go to the Report Manager and
change the datasource to point to the production data. From then on when you
deploy the default is that the datasource is not overwritten so you only
need to do this once.
So there should only be one datasource that you have to change once in this
process.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<pradeepmm@.gmail.com> wrote in message
news:1144344167.425837.308400@.u72g2000cwu.googlegroups.com...
> How can we programatically change the DB name?
> Or
> Change them dynamically in the URL?
> Prad
> Amarnath wrote:
>> Hi,
>> Have you used as a "shared resources" for all your reports? then it
>> should
>> not be a problem since you need to just change the database and user
>> name/password etc.. details at your datasource and you will be up and
>> running.
>> Amarnath
>>
>> "OriginalStealth" wrote:
>> > I am working in a dev environment. My report uses 20 different
>> > datasets but
>> > they all point to the same datasource. I am moving from Dev to a UAT
>> > environment which points to a different DB. Is there a way to change
>> > the
>> > datasource for all 20 datasets from DevDB to UATDB at once or am I
>> > stuck with
>> > going in and changing all the datasources for each of the datasets one
>> > by one?
>> >
>> > Thanks in advance
>> > OS
>|||Is there any way to change the datasource for a report on an
execution-by-exection basis?
I have a situation where there are a large numbre of databases and
users need to be able to execute a single report against any of the
databases.
Is there a way to supply the database connection string to the report
when it is executed?sql

Datasets, searching, complex queries.

Alright just starting out in ASP.NET and it's making my head spin, but I think I'm getting it.

My schema in brief : Images, Categories, and a table in the middle so we can have a many-to-many relationship between images and categories. My issue is searching. I can search by keyword, and limit it to categories. So, in pseudo sql..

SELECT ... WHERE keyword LIKE '%test%' and category_id in (1,5,20,66);

I made a variable for the keyword no problem. But how can I get this dynamic list? Or, is there another way about going this problem?

Where will you put your query in? StoredProcedure or Web? I think my two posts help you:

http://forums.asp.net/t/1162993.aspx

http://forums.asp.net/t/1158548.aspx

|||

I've been informed by the big shark guy that I recommend D-SQL a lot, but you are asking for it so here goes:

First off... beware SQL injection. Strip out ' and - from your search text. Do without.

CREATE PROCEDURE sp_search @.keyword varchar(50),
@.categorylist varchar(150) as

declare @.sql varchar(max)

set @.sql = 'SELECT ... WHERE keyword LIKE ''%' + @.keyword + '%'' and category_id in (' + @.categorylist + ')' -- BTW those are double single quotes, not double quotes

EXEC sp_executesql @.sql, N'@.keyword varchar(50), @.categorylist varchar(150)'

then call sp_search in your code, adding the keyword and category list strings as parameters. Does that help?

|||

che : i'll read those links now

Charles : Interesting. Will I be able to accomplish something like this with Oracle? That looks like SQL Server to me?

|||

Oh. Most folks here are SQL Server. I'm sure you can do dynamic SQL in oracle though - my boss who taught me D-SQL co-authored the Oracle Designer/2000 Handbook. The sp_executesql is a SQL serber built-in sp, and I don't know what the equivalent is in oracle:

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a68022/dynsql.htm

|||

This is a little disappointing. Dynamic SQL seems really overkill for such a trivial problem.

Perhaps I should look at not using strongly typed Datasets? Build one on the fly? Would that be different/better?

|||

Many of the problems were have these days tend to be ones caused by the complexity of the frameworks we are working in. For example, to write a web page, I have to know C# (or VB), ASP, HTML, SQL, and .Net, and how they all interrelate. For one page. It used to be that all you needed was a front end and a database. Oh well, we get a lot in terms of scalability and functionality out of this stuff too - the complexity is market driven.

All philosophy aside, it seems to me that most searches are done with D-Sql. If you really want to make a mountain out of a molehill, you could create a search object that safely builds your query in C# and then access it from your search front end. The advantage of that would be that you could get a lot of re-use in the future. Searching is a non-trivial issue. The volume of work that has gone into searching makes it difficult to mine for simple one-off solutions.

sql

Datasets with multiple tables..

hey guys, quick question that i hope someone can help steer me in the correct direction.

i've got a failry large report that gets its data from and XML web service.. the dataset that the webservice returns has 6 tables in it (i'm interested in data from 5 of them).. in order to get my fields to show up correctly in my dataset viewer i've created 5 datasets and use the ElementPath in my query to single out the table i want for each dataset.. this works fine about 50% of the time.. the other 50% of the time it craps out on my because some of the data manipulation that the webservice does causes primary key violations.. is the report making 5 calls to the webservice? if it is, is it sending them in multiple threads.. its the only thing i can think of that would cause my key violations..

my sproc that is dying looks similar to this:

delete from dbo.Charges where contractid = @.contract

insert into dbo.charges (columns.....) values (values)

i can see that it would violate the key if both threads were doing the insert simultaneously(sp)..

now.. is there a way to only make one trip to the webservice and use those results in the report, while still having the fields available in the dataset viewer?

thanks

mike

Yes, RS will execute each DataSet in its own thread if the DataSource is not marked to use a transaction. If your RS DataSource is set to execute in a transaction each DataSet will be executed in order in the same thread. In report designer, you can mark a DataSource to use a transaction by checking the "Use single transaction" box in the Data Source dialog.

However, even if all your DataSets are executed in the same thread, each DataSet will still make a seperate call to the web service to retrieve the data.

John

|||

thanks John.. it works as expected now.. is there anyway that i could prevent all the calls to the webservice? ie have a main dataset with all the tables, then pass that dataset to a subreport for each section that uses a specific table?

thanks again for the quick reply

mike

Datasets window display

Hi

When designing a report layout, I want to use the Datasets window, however, I could not find how to open this window. In MSDN, it says" On the View menu, click Datasets" to display the Datasets window. But I could not find the Datasets under the View menu on my local. Any advice? Thanks.

Hi,jiamei:

Would you please tell in which article from MSDN, you have found this words?

|||

Rex,

Here it is:http://msdn2.microsoft.com/en-us/library/ms365160.aspx

|||

Hi,jiamei:

Which project are you in? When you stay in the report project, you can click the 'view menu' on the top of the window and you will find the dataset option in the last position of the pop up list.

Datasets that rely on parameters

Hi,
I'm trying to create a report under the following conditions:
1. I have a central database that has stored procedures that require @.db as
the parameter so you can specify where the data is coming from.
2. The report I have only wants data from a particular record, so I created
a second dataset to use in a dropdown list.
3.That second dataset uses a stored proc that requires a parameters @.id and
@.db
The problem is that the columns returned from the stored proc has the @.id
are not recognized when I try to specify them as the "value field" and the
"label field" in the Report Parameters Dialog box.
I suspect this is because it needs to know what the @.db is.
1. How do I get the second stored proc to be recognized by the Report
Parameters Dialog box?
2. How do I pass the @.db to the second stored proc without user intervention?
thanks,
-TrishIf I understand you correctly, what you are trying to do is called cascading
parameters. Search books on line for that and see if it answers you
question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:FCF8157D-2E15-4FF7-B70D-DFB3955C9FE8@.microsoft.com...
> Hi,
> I'm trying to create a report under the following conditions:
> 1. I have a central database that has stored procedures that require @.db
> as
> the parameter so you can specify where the data is coming from.
> 2. The report I have only wants data from a particular record, so I
> created
> a second dataset to use in a dropdown list.
> 3.That second dataset uses a stored proc that requires a parameters @.id
> and
> @.db
> The problem is that the columns returned from the stored proc has the @.id
> are not recognized when I try to specify them as the "value field" and the
> "label field" in the Report Parameters Dialog box.
> I suspect this is because it needs to know what the @.db is.
> 1. How do I get the second stored proc to be recognized by the Report
> Parameters Dialog box?
> 2. How do I pass the @.db to the second stored proc without user
> intervention?
> thanks,
> -Trish

Datasets in same region ?

Is it possible to use different datasets in the same region in the report.
Let's say I have 2 sources which I need to combine, I have a field in each
source I need to add together and see that result for a list of different
accounts.
I tried to do it in an expression choosing the datasets, It works with sums
but how to make it work with the single field and not the total ?
I get this error
g:\surround\reportingservice\axapta\test flere kilder\FlereKilder.rdl The
value expression for the textbox 'textbox12' contains an error: [BC30198]
')' expected.
Here is the Expression
= (Fields!VAREBELXB.Value, "DataSet1") + (Fields!VAREBELXB.Value, "C5_300")
Thx
JackNo, you can only get aggregated values from the other dataset that is not
bound to the region. I would gravitate toward doing the merging at the data
source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Jack" <jackdSPAM@.jackd.dk> wrote in message
news:%2372mERr6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Is it possible to use different datasets in the same region in the report.
> Let's say I have 2 sources which I need to combine, I have a field in each
> source I need to add together and see that result for a list of different
> accounts.
> I tried to do it in an expression choosing the datasets, It works with
> sums but how to make it work with the single field and not the total ?
> I get this error
> g:\surround\reportingservice\axapta\test flere kilder\FlereKilder.rdl The
> value expression for the textbox 'textbox12' contains an error: [BC30198]
> ')' expected.
> Here is the Expression
> = (Fields!VAREBELXB.Value, "DataSet1") + (Fields!VAREBELXB.Value,
> "C5_300")
>
> Thx
> Jack
>|||The problem is that it is from two different sources, one informix database
and one SQL database :(|||What a good reason to use SQL Server 2005 CLR stored procedures :-) Worse
come worse, I would combine the datasets at a application level and pass the
resulting ADO.NET dataset to the report using a custom data extension.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Jack" <jackdSPAM@.jackd.dk> wrote in message
news:O1$gLS26FHA.2628@.TK2MSFTNGP11.phx.gbl...
> The problem is that it is from two different sources, one informix
> database and one SQL database :(
>|||One of my colleagues thinks it's possible to make the solution in vb code,
but I don't know how to get the right data from the second dataset. There
has to be som filtering to get the right data to add, but I have no clue how
to do it in vb code.
Jack
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> skrev i en meddelelse
news:uIM7$e36FHA.472@.TK2MSFTNGP15.phx.gbl...
> What a good reason to use SQL Server 2005 CLR stored procedures :-) Worse
> come worse, I would combine the datasets at a application level and pass
> the resulting ADO.NET dataset to the report using a custom data extension.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Jack" <jackdSPAM@.jackd.dk> wrote in message
> news:O1$gLS26FHA.2628@.TK2MSFTNGP11.phx.gbl...
>> The problem is that it is from two different sources, one informix
>> database and one SQL database :(
>>
>|||I can see the idea of making a combined dataset in a dll with c sharp or
something, but how do I get this dataset into reporting services to use on
the layout tab ?
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> skrev i en meddelelse
news:uIM7$e36FHA.472@.TK2MSFTNGP15.phx.gbl...
> What a good reason to use SQL Server 2005 CLR stored procedures :-) Worse
> come worse, I would combine the datasets at a application level and pass
> the resulting ADO.NET dataset to the report using a custom data extension.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Jack" <jackdSPAM@.jackd.dk> wrote in message
> news:O1$gLS26FHA.2628@.TK2MSFTNGP11.phx.gbl...
>> The problem is that it is from two different sources, one informix
>> database and one SQL database :(
>>
>|||Another option is to link the Informix server as a linked server to your SQL
Server to submit a heterogeneous query (OPENQUERY or OPENROWSET).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Jack" <jackdSPAM@.jackd.dk> wrote in message
news:OZHpRjD7FHA.736@.TK2MSFTNGP09.phx.gbl...
> One of my colleagues thinks it's possible to make the solution in vb code,
> but I don't know how to get the right data from the second dataset. There
> has to be som filtering to get the right data to add, but I have no clue
> how to do it in vb code.
> Jack
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> skrev i en meddelelse
> news:uIM7$e36FHA.472@.TK2MSFTNGP15.phx.gbl...
>> What a good reason to use SQL Server 2005 CLR stored procedures :-) Worse
>> come worse, I would combine the datasets at a application level and pass
>> the resulting ADO.NET dataset to the report using a custom data
>> extension.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Jack" <jackdSPAM@.jackd.dk> wrote in message
>> news:O1$gLS26FHA.2628@.TK2MSFTNGP11.phx.gbl...
>> The problem is that it is from two different sources, one informix
>> database and one SQL database :(
>>
>>
>

Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

sql

Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

Datasets in Data regions

I have encountered a problem in one of my reports.
I use multiple datasets, and inside a List I have two matrices, one for my
'income' dataset and one from my 'cost' dataset. The List uses a field from
the 'income' dataset as it's 'Details Grouping'.
My problem is that even though i explicitly set the data source for the
second matrix to my 'cost' dataset, it still access the 'income' dataset. I
looked in the RDL-file and the <DataSetName> fields is correctly set as
'cost'.
Furthermore; when using the expression editor in a field in the cost-matrix,
I can only choose fields from the 'income' dataset...
Any clues as to what causes this?Your matrices are in the List data region. All items in the same data region
must use the same dataset. That is why you are getting this problem. You have
to change how you are doing this. Perhaps you need to use subreports inside
the list data region? Create a separate report for each of the matrix regions
that needs to have a different dataset. Each report must use a parameter if
you want to filter the data returned in the matrix. Add a subreport control
to the list data region and link to the report you just created, passing the
correct parameter(s) to return the data you need.
HTH
Charles Kangai, MCT, MCDBA
"Kristian Vinther" wrote:
> I have encountered a problem in one of my reports.
> I use multiple datasets, and inside a List I have two matrices, one for my
> 'income' dataset and one from my 'cost' dataset. The List uses a field from
> the 'income' dataset as it's 'Details Grouping'.
> My problem is that even though i explicitly set the data source for the
> second matrix to my 'cost' dataset, it still access the 'income' dataset. I
> looked in the RDL-file and the <DataSetName> fields is correctly set as
> 'cost'.
> Furthermore; when using the expression editor in a field in the cost-matrix,
> I can only choose fields from the 'income' dataset...
> Any clues as to what causes this?|||I combined my datasets into one, and instead used a Filter on my
matrix-rowgroups. That did the trick.
Thanks for your reply.
"Charles Kangai" wrote:
> Your matrices are in the List data region. All items in the same data region
> must use the same dataset. That is why you are getting this problem. You have
> to change how you are doing this. Perhaps you need to use subreports inside
> the list data region? Create a separate report for each of the matrix regions
> that needs to have a different dataset. Each report must use a parameter if
> you want to filter the data returned in the matrix. Add a subreport control
> to the list data region and link to the report you just created, passing the
> correct parameter(s) to return the data you need.
> HTH
> Charles Kangai, MCT, MCDBA
> "Kristian Vinther" wrote:
> > I have encountered a problem in one of my reports.
> >
> > I use multiple datasets, and inside a List I have two matrices, one for my
> > 'income' dataset and one from my 'cost' dataset. The List uses a field from
> > the 'income' dataset as it's 'Details Grouping'.
> >
> > My problem is that even though i explicitly set the data source for the
> > second matrix to my 'cost' dataset, it still access the 'income' dataset. I
> > looked in the RDL-file and the <DataSetName> fields is correctly set as
> > 'cost'.
> > Furthermore; when using the expression editor in a field in the cost-matrix,
> > I can only choose fields from the 'income' dataset...
> >
> > Any clues as to what causes this?

Datasets in custom code

Hi guys,

is it possible to access my report's datasets from within custom code or pass them as parameters to a custom assembly? I want the dataset itself, not just fields, so i can programmatically look through it for data.

Thanks!

sluggy

This is not directly supported.

There are however some alternative approaches:

* add a list reportitem to the report and inside the list make a call to your custom code/custom assembly function and pass in the fields as parameter values. This will call your function for every row of data of the dataset bound to the list.

* or a more complex but also more powerful option is to use the new CustomReportItem feature in RS 2005 and implement a custom processing control which can examine the processed DataValues inside the CustomReportItem. A starting point with a sample is available here: http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx

-- Robert

|||Thanks Robert, i will check that solution out, although it is probably overkill for the particular scenario i was dealing with

Datasets and Text Boxes

I have a table referring to a dataset called ds1 . I have txtbx1,2,3 in
the table.
all text boxes are now referred to different ds1 columns. I created
another Dataset called DS2 (becuase of business needs). Now I wanted
one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
column although the whole table is under DS1 (under which these
txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
Please let me know the best way to accomplish this.
Also wanted to see if we can do some logical conditions in the txt
boxes.
Anyyy Help would be greatly appreciated !!!!!!!!!!
ThanksHi,
Try like this. e.g.
=(Fields!emp.Value, "DS2")
Amarnath
"Sen" wrote:
> I have a table referring to a dataset called ds1 . I have txtbx1,2,3 in
> the table.
> all text boxes are now referred to different ds1 columns. I created
> another Dataset called DS2 (becuase of business needs). Now I wanted
> one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
> column although the whole table is under DS1 (under which these
> txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
> Please let me know the best way to accomplish this.
> Also wanted to see if we can do some logical conditions in the txt
> boxes.
> Anyyy Help would be greatly appreciated !!!!!!!!!!
> Thanks
>|||When I do that the Fields!emp.Value gets repeated - just because the
coverig table dataset area is DS1.

Datasets and SQL Injection

I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?

Hey,

Welcome to the forum. The key is, do you have any dataset queries that take user input and passes it to a SQL query directly? Then you may want to beware if that is true. I like the use of SPs because SQL Server will cache them for increased performance.

In addition, you should be able to use @.Parameters within a SQL query, and pass in the values as parameters, which is safer than embedding the response directly into the SQL query.

|||

wendycarpenter@.polk-county.net:

I am wondering if there is any 'built-in' functions in the C files for sql injection prevention?

Use parameterized queries!

http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

I am using parameterized queries - I've included a sample below. SQL Injection prevention has been "in the air" a lot lately and I guess I want to make sure that I am doing what I need to do to keep my customers' data safe.

INSERT INTO [Case] ([cseYear], [cseNumber], [cseStatus], [csePartI], [csePartII], [cseDestruction])

VALUES (@.cseYear, @.cseNumber, @.cseStatus, @.csePartI, @.csePartII, @.cseDestruction);
select SCOPE_IDENTITY()

|||

I appreciate the link. Maybe I wasn't specific enough. I am using the built in dataset (.xsd), so I am not creating any sql statement within my code - I strictly call the method within the datatable of the table adapter within the dataset. Most of the work is done within the auto generated code within Visual Studio 2005.

|||

Hey,

OK, which used the parameter approach I believe. That should be OK; I've seen several projects where Adapters were used even with secure info. Note that table adapters can also use stored procedures in case you want to be sure.

sql

datasets and parameters

I am trying to put up together a report with several parameters. The first
parameter is independent, the second one, though depends of this first one.
Is there a way to link them somehow, so that when i pick my first parameter,
the second dataset I'm using for my second parameter will be populated based
on the value of my first parameter.
Simple example if it's still not clear: i have a table with 3 columns, 10
rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
report brings back all 10 rows. There are 2 distinct values in the 1st col,
and 3 distinct values in the 2nd col - one distinct value on the 1st col is
associated with 2 values in 2nd col, one with the other remaining.
I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
of the first parameter and 1 value when I pick the other one.
Thank you,
kowalskylook up Cascading Parameters in books online or searching the same in this
newsgroup.
"kowalsky" wrote:
> I am trying to put up together a report with several parameters. The first
> parameter is independent, the second one, though depends of this first one.
> Is there a way to link them somehow, so that when i pick my first parameter,
> the second dataset I'm using for my second parameter will be populated based
> on the value of my first parameter.
> Simple example if it's still not clear: i have a table with 3 columns, 10
> rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
> report brings back all 10 rows. There are 2 distinct values in the 1st col,
> and 3 distinct values in the 2nd col - one distinct value on the 1st col is
> associated with 2 values in 2nd col, one with the other remaining.
> I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
> of the first parameter and 1 value when I pick the other one.
> Thank you,
> kowalsky

Datasets and iff statements

Hello experts,

I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...

Code Snippet

=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",

iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",

iif(Left(Fields!TestName.Value,6)="Search","Search Menu",

iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",

iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",

iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))

Not sure where I put the dataset name in this.

Thanks,

C

Answered myself. Choose the data set in the matrix properties. Leave the expression alone.

Datasets access from custom code

Hi to all
Is possible to access to the report datasets or even report items from the
custom code embedded in the report?, If yes, how?,could you send some sample
code?
ThanksNo, you would need to pass in the values you are interested in as parameters
to your function.
For example:
=Code.MyFunction(Fields!CityCode.Value) or
=Code.MyFunction(ReportItems!textbox1.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> Is possible to access to the report datasets or even report items from the
> custom code embedded in the report?, If yes, how?,could you send some
sample
> code?
> Thanks
>
>|||Ok... I think this is very restrictive, I need to condition the visibility
of some components in base to some information contained in the dataset,
like if exist some value for one column and if it is the same for others
columns (if exist some) and I was trying to create a general function for
all my components , it seems that I'll have to create other datasets that
makes this kind of comparision with SQL or some SP, If you have a best idea
please let me know...
What about to pass Datasets as parameters? is possible?
Thanks
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> No, you would need to pass in the values you are interested in as
parameters
> to your function.
> For example:
> =Code.MyFunction(Fields!CityCode.Value) or
> =Code.MyFunction(ReportItems!textbox1.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > Hi to all
> > Is possible to access to the report datasets or even report items from
the
> > custom code embedded in the report?, If yes, how?,could you send some
> sample
> > code?
> > Thanks
> >
> >
> >
> >
>|||No, you cannot pass the entire data set as a parameter.
You could hand in the entire fields collection, however, if that would help
you any.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:u1qA$AcdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> Ok... I think this is very restrictive, I need to condition the visibility
> of some components in base to some information contained in the dataset,
> like if exist some value for one column and if it is the same for others
> columns (if exist some) and I was trying to create a general function for
> all my components , it seems that I'll have to create other datasets that
> makes this kind of comparision with SQL or some SP, If you have a best
idea
> please let me know...
> What about to pass Datasets as parameters? is possible?
> Thanks
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> > No, you would need to pass in the values you are interested in as
> parameters
> > to your function.
> > For example:
> >
> > =Code.MyFunction(Fields!CityCode.Value) or
> > =Code.MyFunction(ReportItems!textbox1.Value)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > > Hi to all
> > > Is possible to access to the report datasets or even report items from
> the
> > > custom code embedded in the report?, If yes, how?,could you send some
> > sample
> > > code?
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
>

Datasets

When using datasets in reports, is it possible to reuse them in other
reports? For example a generic dataset that returns all employee names to
use in parameters. Another report needs the same parameter and we need to
create another dataset. Is there some mechanism to share the datasets
across reports?
This is regarding Reporting Services on SQL 2000.
ThanksCreate a sproc with parms?
--
William Stacey [MVP]
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
| When using datasets in reports, is it possible to reuse them in other
| reports? For example a generic dataset that returns all employee names to
| use in parameters. Another report needs the same parameter and we need to
| create another dataset. Is there some mechanism to share the datasets
| across reports?
|
| This is regarding Reporting Services on SQL 2000.
|
| Thanks
|
||||Yes we do have a stored proc that has params. I was wondering about the
dataset within the report itself? Having two reports that share the same
proc (via the dataset) to get a lookup is one way (and possibly the only
way) to do it, but what about sharing the same dataset within the report
designer? I don't think MS SQL reporting services is set up to do this but
I'd like to be wrong to minimize the amount of coding I need to do for each
report.
As I'm getting up and running, I imagine one day we will be generating some
or all of our reports from code and generating the RDL on the fly. As this
question pertains to SQL Reporting 2000 we are a bit behind the times but it
would be nice to know for either version (SQL 2000 or SQL 2005) as we will
one day migrate to 2005.
Thanks
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Create a sproc with parms?
> --
> William Stacey [MVP]
> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> | When using datasets in reports, is it possible to reuse them in other
> | reports? For example a generic dataset that returns all employee names
> to
> | use in parameters. Another report needs the same parameter and we need
> to
> | create another dataset. Is there some mechanism to share the datasets
> | across reports?
> |
> | This is regarding Reporting Services on SQL 2000.
> |
> | Thanks
> |
> |
>|||From what I've found, you can't reuse datasets, unfortunately. You're not
the first one to ask...
Stored procedures are the best way to go, but unfortunately you can't have a
list reusable datasets. That would have been great...
Kaisa M. Lindahl
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:%23M28JR5IGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Yes we do have a stored proc that has params. I was wondering about the
> dataset within the report itself? Having two reports that share the same
> proc (via the dataset) to get a lookup is one way (and possibly the only
> way) to do it, but what about sharing the same dataset within the report
> designer? I don't think MS SQL reporting services is set up to do this
> but I'd like to be wrong to minimize the amount of coding I need to do for
> each report.
> As I'm getting up and running, I imagine one day we will be generating
> some or all of our reports from code and generating the RDL on the fly.
> As this question pertains to SQL Reporting 2000 we are a bit behind the
> times but it would be nice to know for either version (SQL 2000 or SQL
> 2005) as we will one day migrate to 2005.
> Thanks
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> Create a sproc with parms?
>> --
>> William Stacey [MVP]
>> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
>> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
>> | When using datasets in reports, is it possible to reuse them in other
>> | reports? For example a generic dataset that returns all employee names
>> to
>> | use in parameters. Another report needs the same parameter and we need
>> to
>> | create another dataset. Is there some mechanism to share the datasets
>> | across reports?
>> |
>> | This is regarding Reporting Services on SQL 2000.
>> |
>> | Thanks
>> |
>> |
>>
>

Datasets

Problem: I need to add filters on a Dataset. The current dataset is based on
a stored procedure that I would prefere NOT to touch.
Question: Is it possible to build a new dataset based on the first dataset?
(This would inable me to filter on the data output). Or are there other
suggestions for a solution to this problem.
Thanks.
Regards
JonasAs I am going on holidays can you please respond to
terry.bilsborough@.Alcan.com.
Thanks.
Regards
Jonas Larsen
Alcan Engineering
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Problem: I need to add filters on a Dataset. The current dataset is based
on
> a stored procedure that I would prefere NOT to touch.
> Question: Is it possible to build a new dataset based on the first
dataset?
> (This would inable me to filter on the data output). Or are there other
> suggestions for a solution to this problem.
> Thanks.
> Regards
> Jonas
>|||Yes it is possible to add a filter to a data set. The data set filter
functionality is located on the dataset dialog : Filter tab. Additionally
all data regions (lists, tables, matrix, and chart) support this
functionality.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23VWlAv%23YEHA.384@.TK2MSFTNGP10.phx.gbl...
> As I am going on holidays can you please respond to
> terry.bilsborough@.Alcan.com.
> Thanks.
> Regards
> Jonas Larsen
> Alcan Engineering
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Problem: I need to add filters on a Dataset. The current dataset is
based
> on
> > a stored procedure that I would prefere NOT to touch.
> >
> > Question: Is it possible to build a new dataset based on the first
> dataset?
> > (This would inable me to filter on the data output). Or are there other
> > suggestions for a solution to this problem.
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>sql

Dataset′s

Im trying to make a dataset from a stored procedure. My problem is that RS wont use all of the rows returned.

My SP returns 9 rows. Some of them are dublicates and some ar on of a kind. And it seeems that RS just displays one of each kind. No mater how many there are i the resultset.

Ive read that RS cant handle more than one resultset, but i think that the result returned is just one resultset.

If a run the SP in the Data tab it works just fine, it displays all rows. But when im using it in previewmode its not.

The SP is built around a cursor. It makes a temporary table with info. Then just JOINs it with a real table. Would not that create a SINGLE resultset witch RS should read?

Thanks in advanceIf it is working in preview, you are doing something in the layout of your report. Maybe you are grouping by some repeating value which will cause for the number of rows to be reduced. Try to start with a blank slate and drop the fields in the detail row of a table.|||

Thanks, it workt. I started of with a blank report. You where right, it was somehing in the layout section that flawed.

Dataset problems with Stored Procedures.

I've noticed that SP:s that are more complex than just a simple
SELECT-statement don't work correctly when creating new datasets. The dataset
can't seem to populate the Fields when the SP does some additional work
before the SELECT. I know it's possible to create all the Fields manually,
but that isn't an option for us since we have a ton of reports with lots and
lots of fields. Is there another workaround for this? Maybe in SQL Server
2005?No answers? Is there really no way to solve this? This problem could be a
real show stopper for our current project...
"Patrik Ylén" wrote:
> I've noticed that SP:s that are more complex than just a simple
> SELECT-statement don't work correctly when creating new datasets. The dataset
> can't seem to populate the Fields when the SP does some additional work
> before the SELECT. I know it's possible to create all the Fields manually,
> but that isn't an option for us since we have a ton of reports with lots and
> lots of fields. Is there another workaround for this? Maybe in SQL Server
> 2005?|||Have you tried the refresh fields button (to the right of the ...)?
I have SP that do a lot (put data into multiple temp tables etc). The very
last statement is the select. So having stuff above the select matters not
at all. Sometimes I have to click on the refresh fields.
How are you calling the SP (i.e. are you doing an exec or have you set the
command type to stored procedure).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> No answers? Is there really no way to solve this? This problem could be a
> real show stopper for our current project...
> "Patrik Ylén" wrote:
>> I've noticed that SP:s that are more complex than just a simple
>> SELECT-statement don't work correctly when creating new datasets. The
>> dataset
>> can't seem to populate the Fields when the SP does some additional work
>> before the SELECT. I know it's possible to create all the Fields
>> manually,
>> but that isn't an option for us since we have a ton of reports with lots
>> and
>> lots of fields. Is there another workaround for this? Maybe in SQL Server
>> 2005?|||Hmm, it works now. I don't know what I did when I tried it the last time, but
it must've been wrong! :-)
Thanks,
Patrik
"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
>
>|||"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
> HI!
>I have a related problem. My stored procedure returns a different dataset based on an entered parameter - it returns a particular set of data for display in the report detail (@.showdetail) and another set for display in the report footer (@.showfooter)
When I call the stored procedure from within reporting services (either by
exec spname @.parameter as a text entry, or by referencing it's name) I can
retrieve the 2 separate datasets fine (ie the results sets show up in the
grid). HOWEVER, changing the parameter to 'showfooter' instead of
'showdetail' does not update the associated field list. Which means I can't
drag the second dataset's fields onto my layout.
Any ideas?|||RS only supports one dataset being returned from the SP. If you have a
single stored procedure that returns multiple datasets then you either need
to modify the stored procedure to return the appropriate dataset based on a
parameter or you need to split it into two stored procedure.
Note that you can have many many datasets in your report. You just cannot
have a stored procedure return more than one dataset. And, no, this has not
changed with 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:28DDEFA3-2903-4E8B-B56E-15757E303832@.microsoft.com...
>
> "Bruce L-C [MVP]" wrote:
>> Have you tried the refresh fields button (to the right of the ...)?
>> I have SP that do a lot (put data into multiple temp tables etc). The
>> very
>> last statement is the select. So having stuff above the select matters
>> not
>> at all. Sometimes I have to click on the refresh fields.
>> How are you calling the SP (i.e. are you doing an exec or have you set
>> the
>> command type to stored procedure).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
>> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
>> > No answers? Is there really no way to solve this? This problem could be
>> > a
>> > real show stopper for our current project...
>> >
>> > "Patrik Ylén" wrote:
>> >
>> >> I've noticed that SP:s that are more complex than just a simple
>> >> SELECT-statement don't work correctly when creating new datasets. The
>> >> dataset
>> >> can't seem to populate the Fields when the SP does some additional
>> >> work
>> >> before the SELECT. I know it's possible to create all the Fields
>> >> manually,
>> >> but that isn't an option for us since we have a ton of reports with
>> >> lots
>> >> and
>> >> lots of fields. Is there another workaround for this? Maybe in SQL
>> >> Server
>> >> 2005?
>> HI!
>>I have a related problem. My stored procedure returns a different dataset
>>based on an entered parameter - it returns a particular set of data for
>>display in the report detail (@.showdetail) and another set for display in
>>the report footer (@.showfooter)
> When I call the stored procedure from within reporting services (either by
> exec spname @.parameter as a text entry, or by referencing it's name) I can
> retrieve the 2 separate datasets fine (ie the results sets show up in the
> grid). HOWEVER, changing the parameter to 'showfooter' instead of
> 'showdetail' does not update the associated field list. Which means I
> can't
> drag the second dataset's fields onto my layout.
> Any ideas?|||Thanks Bruce
My stored procedure is actually coded to return an appropriate dataset based
on a parameter which is supplied when calling the sp in RS. It returns the
appropriate dataset to the results grid fine in RS using the supplier
parameters. The fields returned in both datasets are completely different.
The problem is that the field list is not updated when I change the
parameter, it remains the list associated with the first parameter supplied
even if click 'refresh fields' after changing the parameter
I need my report to use the two separate datasets without having to re-write
the sp which is pages long.
Is this related to the fact that you can't have a sp return more than one
dataset in rs'|||Logically, you are returning two datasets. Depending on the parameter you
get a different field list. What you need to do is call the SP two times in
design mode with different parameters to get the different field list. Then
design your form with these and hide the one you don't want based on the
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F34F84BB-226F-4A34-A0ED-551137DCA065@.microsoft.com...
> Thanks Bruce
> My stored procedure is actually coded to return an appropriate dataset
> based
> on a parameter which is supplied when calling the sp in RS. It returns
> the
> appropriate dataset to the results grid fine in RS using the supplier
> parameters. The fields returned in both datasets are completely different.
> The problem is that the field list is not updated when I change the
> parameter, it remains the list associated with the first parameter
> supplied
> even if click 'refresh fields' after changing the parameter
> I need my report to use the two separate datasets without having to
> re-write
> the sp which is pages long.
> Is this related to the fact that you can't have a sp return more than one
> dataset in rs'sql

Dataset parameters question.

I have a report, which contains student details and the courses to
which the student is registered.
I have 2 datasets, one which retrieves the student data, and another
paramaterized report which retrieves all courses for this student.
What I need to do is set this parameter to be the student_id of the
current student.
e.g. I want the report to be displayed as:
Student Details
Student ID: S1000
Name: AN Other Address: 1 22nd Street
Courses:
Math
Chemistry
Physics
Spanish
I can select the correct dataset and field in the textbox for course
name, but the report keeps asking me to enter a student id. I want it
to use the student id of the current student.
Does anyone know how I go about this?
Appreciate any help.Donâ't you use a student id parameter to filter the student? This one should
be used to filter courses.
If youâ're not filtering the students, you may have more than one student. In
this case you can use a single dataset that joins student and courses, and
group by students.
"DJ" wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.
>|||IF USING A SUBREPORT
Your Student data is in your main report, and your Course data (including
StudentID) is in your subreport. Your subreport is placed inside the list
(or table or whatever) that iterates on the student data.
In the main report, right-click on the subreport and select Properties.
Then go to the Parameters tab and map the subreport parameters to the
appropriate fields.
IF NOT USING A SUBREPORT
You might be able to do this without a subreport. Just join the course
table to the student table in your dataset. Put everything inside a table
(or nested lists if it's a freeform report). You can put rectangles and
lists inside table cells, which is cool. Map everything to the same dataset.
Then set outer grouping levels at the student level, and inner grouping or
details at the course level.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJ" <superdj@.hotmail.com> wrote in message
news:72abb98.0501070456.d13dd9d@.posting.google.com...
>I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||I have tried this using a parameterized sub report, and set the
parameter to be the student id. The dataset that retrieves the student
data is simply 'select * from students' so this should bring back all
the students and the sub report should bring back all the courses for
these students.
But still it asks me to enter a student id!
DJ wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||In your parent report parameters, check for a student_id field. You might
have specified one earlier that needs to be deleted. The data source for
your subreport should be something like "select * from courses where
student_id = @.student_id". Then you can link the parameters in the parent
and child reports.
On the other hand, you could do the whole thing in a single report, with a
data source like "select * from students inner join courses on
students.student_id = courses.student_id". Then you put the fields in a
table with student info in the header rows, and group info in the detail
rows.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<superdj@.hotmail.com> wrote in message
news:1105349201.298466.198340@.c13g2000cwb.googlegroups.com...
>I have tried this using a parameterized sub report, and set the
> parameter to be the student id. The dataset that retrieves the student
> data is simply 'select * from students' so this should bring back all
> the students and the sub report should bring back all the courses for
> these students.
> But still it asks me to enter a student id!
>
> DJ wrote:
>> I have a report, which contains student details and the courses to
>> which the student is registered.
>> I have 2 datasets, one which retrieves the student data, and another
>> paramaterized report which retrieves all courses for this student.
>> What I need to do is set this parameter to be the student_id of the
>> current student.
>> e.g. I want the report to be displayed as:
>> Student Details
>> Student ID: S1000
>> Name: AN Other Address: 1 22nd Street
>> Courses:
>> Math
>> Chemistry
>> Physics
>> Spanish
>> I can select the correct dataset and field in the textbox for course
>> name, but the report keeps asking me to enter a student id. I want it
>> to use the student id of the current student.
>> Does anyone know how I go about this?
>> Appreciate any help.
>