How to create report using WebService as DataSource.

Hi,

We created a simple web serivce having a Web Method which accepts a string parameter and returns "Hello World" concatenated with parameter value. We tested this web service by browsing it through IE and it was working fine by accepting a parameter and displaying the "Hello World " concated with the value given through the parameter when clicked on "Invoke" button.

We now created a Reporting Services 2005 project using BI having a report for which the data source will be the web service which is mentioned above. We defined a parameter for the report which will pass the value to the webservice and the report will display the returned value from the web service.

When we have done the report and executed by giving a value to the parameter and clicking on view report button it was displaying the "Hello World" but the parameter which we passed to the web service is not getting concatenated. When we tried to trace the application we could see that the value passed to the web service web method was comming as null. So kindly help me where I went wrong in developing such a kind of report which is not passing a parmeter to webservice.

Kindly provide us the solution in order to make this issue to be resolved.

Thanks in Advance.I can concur with VDeevi, I was able to recreate the exact same issue. In my case I'm passing parameters to a web service that is returning a dataset. When I set a default value in the webmethod it returns a dataset successfully. My problem is also that the parameters are not getting passed to the web service.

What I think is strange is that I've done the BOL sample with the SSRS web service Listchildren method and it works successfully. Does that mean that the problem is not in SSRS but in my implementation of the web service?

This works:

<WebMethod()> _
Public Function GetTestDataset() As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(1)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

This doesn't work:

<WebMethod()> _
Public Function GetTestDataset(ByVal ID As Integer) As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(ID)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

|||

It's probably not your implementation, but in the parsing specifics of the SoapAction and Namespace.

For example, if your SoapAction is http://tempuri.org/HelloMyNameIs, we split it into http://tempuri.org and HelloMyNameIs throwing away the separating / character.

But if you look at the way webservice is defined, the namespace is actually http://tempuri.org/ - with a trailing /.

So in this case, when method name/namespace can not be correctly derived from SoapAction and when SoapAction can not be correctly derived from method name (in the above case we would get http://tempuri.org//HelloMyNameIs) you should use both <Method …/> and <SoapAction …/>, e.g.

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloMyNameIs"/>
<SoapAction>http://tempuri.org/HelloMyNameIs</SoapAction>
</Query>

We are working on updates to the documentation.

|||Thanks Brain, it really helped us to solve this issue.

Thanks once again,|||Worked for me as well. Thanks a lot!

How to create full text index by SQL Server Express?

Hi all..

I tried too much to create FULL TEXT INDEX by using SQL Server 2005 Management Studio Express, it returns this ERR MSG:

Informational: No full-text supported languages found.

Informational: No full-text supported languages found.

Msg 7680, Level 16, State 1, Line 1

Default full-text index language is not a language supported by full-text search

This problem dos not come when I use Microsoft SQL Server 2005 Management Studio, to create FULL TEXT INDEX!

My DB collation is: "Arabic_CI_AS"

But I don’t need this, I can use English Language.

Please, what can I do?

Full Text Indexing is not available in the 'basic' SQL Server 2005 Express.

You must download and install SQL Server 2005 Express Edition with Advanced Services.

Overview

Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express) is a new, free, easy-to-use version of SQL Server Express that includes a new graphical management tool and powerful features for reporting and advanced text-based searches. SQL Server Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for embedded application clients, light Web applications, and local data stores.

SQL Server Express with Advanced Services has all of the features in SQL Server 2005 Express Edition, plus you can:

Easily manage and administer SQL Server Express with a new easy-to-use graphical management tool -- SQL Server 2005 Management Studio Express (SSMSE).

Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Run SQL Server Reporting Services reports on local relational data.|||

Of course I'm using sql server with advanced edition, and this problem appears only with it, you may see this URL:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1109200&SiteID=1

In that URL there is no correct answer, so he changed his mind, and as I think, decided to use SQL- Server 2005 Trial instead of (SQL SERVER EXPRESS ADV).

However; as we (me & my friend Ibrahim) understood this ERR message:

Informational: No full-text supported languages found.

Informational: No full-text supported languages found.

Msg 7680, Level 16, State 1, Line 1

Default full-text index language is not a language supported by full-text search

My friend thought that the correct question is about: how to add languages to support full-text, so he wrote the other question as a correction to this one, not as a repetition.

Thank you very much.

|||

See the follwing links for clarification:

default full-text language Option
"The default value of this option is the language of the server."
http://msdn2.microsoft.com/en-us/library/ms180854.aspx

Setting Server Configuration Options
http://msdn2.microsoft.com/en-us/library/ms189631.aspx

sys.fulltext_languages (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176076.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi…

When I execute this query:

select * from sys.fulltext_languages

It returns 0 rows, nothing…

Also when I tried to update sys.fulltext_languages or sys.configurations by using UPDATE or sp_configure it returns this msg:

Ad hoc update to system catalogs is not supported

Also when I tried to enable 'allow updates' it returns same error msg.

Any way, thank you so much…

I will buy SQL Server 2005 Developer edition.

Thank you.

sql

How to create a table (structure) based on a result of a stored procedure

Dear all,
There is a stored procedure that returns a result set consisting of some 30
columns of different types.
How can I automatically (i.e. not by means of CREATE TABLE statement) create
a table with a structure that correspons to the returned result set?
I mean something like:
SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
(The above unfortunately doesn't work).
To be specific: the stored procedure in question is:
sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
Any help would be greatly appreciated!
Thank you in advance.
Best regards,
AndrewAndrew
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||Dear Uri,
As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
> No , you have to know how many "columns" stored procedures will be
> returned
> insert into tablename exec sp
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
> sp_storedprocedure)
> instead of select you can use INSERT INTO will work
> eg:
> create table #t(i int)
> insert into #t exec myproc
>
> create proc myproc
> as
> select 1
>
> vinu
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Thank you for the hint. If I understand you correctly, you suggest that I
should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
That's OK, but how can I get (programmatically) the list of "columns" that
are returned by the stored procedure?
My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
So, the situation is as follows: I have got a stored procedure and want to
put its result set into a new table.
Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
but instead of ExistingTableOrView there is a stored procedure.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Andrew
> Well, in that case take a look into dynamic sql
>
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>
>|||Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
>> sp_storedprocedure)
>> instead of select you can use INSERT INTO will work
>> eg:
>> create table #t(i int)
>> insert into #t exec myproc
>>
>> create proc myproc
>> as
>> select 1
>>
>> vinu
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Great! It works!
Thank you very much for your help!
Best regards,
Andrew
P.S. Isn't it a bit strange that we have to refer to our own SQL server via
'openrowset'?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
> Andrew
> --This procedure returns three or two columns from Orders Table depends
> on parameter
> create proc usp_test
> @.orderid int =10248
> as
> if @.orderid=10248
> select orderid,orderdate from orders where orderid=@.orderid
> else
> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
> --I use OPENROWSET command to run this SP and get the data out to
> temporary table
> select * into #t
> from
> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
> 'set fmtonly off; exec usp_test 10249') as p
> select * from #t
> drop table #t
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>>
>>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
:-)
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
OPENQUERY() is another option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||Tibor
I did not want to create a linked server that OPENQUERY required unlike
OPENROWSET when you can reference to the server without having a linked
server
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfQkCm0AHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
> OPENQUERY() is another option...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> Great! It works!
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table
>> depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest
>> that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT
>> to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>>> Andrew
>>>
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> No , you have to know how many "columns" stored procedures will be
>>> returned
>>>
>>> insert into tablename exec sp
>>>
>>>
>>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>

How to create a table (structure) based on a result of a stored procedure

Dear all,
There is a stored procedure that returns a result set consisting of some 30
columns of different types.
How can I automatically (i.e. not by means of CREATE TABLE statement) create
a table with a structure that correspons to the returned result set?
I mean something like:
SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
(The above unfortunately doesn't work).
To be specific: the stored procedure in question is:
sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
Any help would be greatly appreciated!
Thank you in advance.
Best regards,
AndrewAndrew

> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
> sp_storedprocedure)
> instead of select you can use INSERT INTO will work
> eg:
> create table #t(i int)
> insert into #t exec myproc
>
> create proc myproc
> as
> select 1
>
> vinu
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>|||Dear Uri,
As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Andrew
>
> No , you have to know how many "columns" stored procedures will be
> returned
> insert into tablename exec sp
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>|||Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>|||Dear Uri,
Thank you for the hint. If I understand you correctly, you suggest that I
should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
That's OK, but how can I get (programmatically) the list of "columns" that
are returned by the stored procedure?
My basic question is
So, the situation is as follows: I have got a stored procedure and want to
put its result set into a new table.
Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
but instead of ExistingTableOrView there is a stored procedure.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Andrew
> Well, in that case take a look into dynamic sql
>
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;D
ATABASE=Northwind;UID=appdev;PWD=np,
jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
>|||Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>|||Dear Uri,
Great! It works!
Thank you very much for your help!
Best regards,
Andrew
P.S. Isn't it a bit strange that we have to refer to our own SQL server via
'openrowset'?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
> Andrew
> --This procedure returns three or two columns from Orders Table depends
> on parameter
> create proc usp_test
> @.orderid int =10248
> as
> if @.orderid=10248
> select orderid,orderdate from orders where orderid=@.orderid
> else
> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
> --I use OPENROWSET command to run this SP and get the data out to
> temporary table
> select * into #t
> from
> openrowset('SQLOLEDB','SERVER=NT_SQL_4;D
ATABASE=Northwind;UID=appdev;PWD=n
p,jho;',
> 'set fmtonly off; exec usp_test 10249') as p
> select * from #t
> drop table #t
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>

How to create a group on selected report parameters?

Hello,
Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
states in a report that returns orders.
I successfully can return order numbers along with the associated state
where the order was placed.
However, if I select the states of NH, VT, and MA and there are no orders
from VT, I still need a group header for VT to show that there was no data
for VT. So my thought was to create a report grouping based on the states
selected in the multivalue State parameter. I can't find a way to do this.
Also, I know I could use a subreport to resolve this but it is not an option
here. I need this report to be exported to MS Excel and subreports do not
export to Excel.
Thank you,
JustinOn Oct 3, 3:48 pm, justinsaraceno
<justinsarac...@.discussions.microsoft.com> wrote:
> Hello,
> Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> states in a report that returns orders.
> I successfully can return order numbers along with the associated state
> where the order was placed.
> However, if I select the states of NH, VT, and MA and there are no orders
> from VT, I still need a group header for VT to show that there was no data
> for VT. So my thought was to create a report grouping based on the states
> selected in the multivalue State parameter. I can't find a way to do this.
> Also, I know I could use a subreport to resolve this but it is not an option
> here. I need this report to be exported to MS Excel and subreports do not
> export to Excel.
> Thank you,
> Justin
>From your description, you will most likely want to insert dummy rows
in the returned dataset from the stored procedure/query that is
sourcing the report based on the parsing out of the multi-select
parameter. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
possible solution. I wish there was a better way than having to manipulate
my dataset with dummy data. Thank you again.
"EMartinez" wrote:
> On Oct 3, 3:48 pm, justinsaraceno
> <justinsarac...@.discussions.microsoft.com> wrote:
> > Hello,
> > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > states in a report that returns orders.
> >
> > I successfully can return order numbers along with the associated state
> > where the order was placed.
> >
> > However, if I select the states of NH, VT, and MA and there are no orders
> > from VT, I still need a group header for VT to show that there was no data
> > for VT. So my thought was to create a report grouping based on the states
> > selected in the multivalue State parameter. I can't find a way to do this.
> >
> > Also, I know I could use a subreport to resolve this but it is not an option
> > here. I need this report to be exported to MS Excel and subreports do not
> > export to Excel.
> >
> > Thank you,
> > Justin
>
> >From your description, you will most likely want to insert dummy rows
> in the returned dataset from the stored procedure/query that is
> sourcing the report based on the parsing out of the multi-select
> parameter. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Hi,
I have the same problem. Did you find any solution to that?
--
Thanks,
Tabbey
"justinsaraceno" wrote:
> Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> possible solution. I wish there was a better way than having to manipulate
> my dataset with dummy data. Thank you again.
> "EMartinez" wrote:
> > On Oct 3, 3:48 pm, justinsaraceno
> > <justinsarac...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > states in a report that returns orders.
> > >
> > > I successfully can return order numbers along with the associated state
> > > where the order was placed.
> > >
> > > However, if I select the states of NH, VT, and MA and there are no orders
> > > from VT, I still need a group header for VT to show that there was no data
> > > for VT. So my thought was to create a report grouping based on the states
> > > selected in the multivalue State parameter. I can't find a way to do this.
> > >
> > > Also, I know I could use a subreport to resolve this but it is not an option
> > > here. I need this report to be exported to MS Excel and subreports do not
> > > export to Excel.
> > >
> > > Thank you,
> > > Justin
> >
> >
> > >From your description, you will most likely want to insert dummy rows
> > in the returned dataset from the stored procedure/query that is
> > sourcing the report based on the parsing out of the multi-select
> > parameter. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
> >|||I didn't find anything better than creating some dummy data via something
like a temp table. If SSRS supported export to excel for reports with
subreports, there wouldn't be such a hack to get around this.
"tabbey" wrote:
> Hi,
> I have the same problem. Did you find any solution to that?
> --
> Thanks,
> Tabbey
>
> "justinsaraceno" wrote:
> > Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> > possible solution. I wish there was a better way than having to manipulate
> > my dataset with dummy data. Thank you again.
> >
> > "EMartinez" wrote:
> >
> > > On Oct 3, 3:48 pm, justinsaraceno
> > > <justinsarac...@.discussions.microsoft.com> wrote:
> > > > Hello,
> > > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > > states in a report that returns orders.
> > > >
> > > > I successfully can return order numbers along with the associated state
> > > > where the order was placed.
> > > >
> > > > However, if I select the states of NH, VT, and MA and there are no orders
> > > > from VT, I still need a group header for VT to show that there was no data
> > > > for VT. So my thought was to create a report grouping based on the states
> > > > selected in the multivalue State parameter. I can't find a way to do this.
> > > >
> > > > Also, I know I could use a subreport to resolve this but it is not an option
> > > > here. I need this report to be exported to MS Excel and subreports do not
> > > > export to Excel.
> > > >
> > > > Thank you,
> > > > Justin
> > >
> > >
> > > >From your description, you will most likely want to insert dummy rows
> > > in the returned dataset from the stored procedure/query that is
> > > sourcing the report based on the parsing out of the multi-select
> > > parameter. Hope this helps.
> > >
> > > Regards,
> > >
> > > Enrique Martinez
> > > Sr. Software Consultant
> > >
> > >|||On Oct 4, 8:08 am, justinsaraceno
<justinsarac...@.discussions.microsoft.com> wrote:
> Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> possible solution. I wish there was a better way than having to manipulate
> my dataset with dummy data. Thank you again.
> "EMartinez" wrote:
> > On Oct 3, 3:48 pm, justinsaraceno
> > <justinsarac...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > states in a report that returns orders.
> > > I successfully can return order numbers along with the associated state
> > > where the order was placed.
> > > However, if I select the states of NH, VT, and MA and there are no orders
> > > from VT, I still need a group header for VT to show that there was no data
> > > for VT. So my thought was to create a report grouping based on the states
> > > selected in the multivalue State parameter. I can't find a way to do this.
> > > Also, I know I could use a subreport to resolve this but it is not an option
> > > here. I need this report to be exported to MS Excel and subreports do not
> > > export to Excel.
> > > Thank you,
> > > Justin
> > >From your description, you will most likely want to insert dummy rows
> > in the returned dataset from the stored procedure/query that is
> > sourcing the report based on the parsing out of the multi-select
> > parameter. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant