Getting a list of the queried parameters for a report (via soap)

What I'd liek to be able to do is display dropdowns (like the dropdowns in
the standard reportviewer component with queried parameters (eg, values come
from a datasource).
For example Employee Sales Summary in SampleReports includes a field for
employee name/id... which is automatically populated.
When i run GetReportParameters, I end up with nothing other dthan a type.
Thanks
Weston WeemsWeston,
Your code to call GetReportParameters should look similar to this:
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(report, historyID, forRendering, values,
credentials);
Then you just have to loop through the ValidValues of parameters.
foreach (ReportParameter parameter in parameters)
{
/// insert code to evaluate which parameter type/name, if dropdown
foreach (ValidValue vv in parameter.ValidValues)
{
LI = new System.Web.UI.WebControls.ListItem(vv.Label, vv.Value);
///see if this value is the same with the default value
///in which case we make the current list item selected
if (vv.Value == parameter.DefaultValues[0] && parameter.State ==ParameterStateEnum.HasValidValue)
{
LI.Selected = true;
}
this.ProcessorID.Items.Add(LI);
}
This code works for the one parameter that I have setup as a query based
parameter in the report.
Hope that helps,
Steve
"Weston Weems" wrote:
> What I'd liek to be able to do is display dropdowns (like the dropdowns in
> the standard reportviewer component with queried parameters (eg, values come
> from a datasource).
> For example Employee Sales Summary in SampleReports includes a field for
> employee name/id... which is automatically populated.
> When i run GetReportParameters, I end up with nothing other dthan a type.
> Thanks
> Weston Weems
>
>sql

getresourcecontents

Could someone give me an example of displaying reporting services resources via a treeview using the getresourcecontents method?

Sorted this now

Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.ms-excel")
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=Myxls.xls")
Response.BinaryWrite(myByteArray)

Get/SetExecutionOptions question

Can Get/SetExecutionOptions really get/set all the Execution properties that
are available via Report Manager? As far as I can see I can set a report to
Live or Snapshot and set a schedule if Snapshot. How do I retrieve the
setting " Cache a temporary copy of the report. Expire copy of report after
a number of minutes" for example ? This comes back as
ExecutionSettingEnum.Live but I can't see how to get the number of minutes?
Same if the expiry is based on a schedule - it does not get populated when
Live. I need to script these settings but can't see how to do this?
--
Cheers,
JasIf execution is set to Live, use GetCacheOptions method to get expiration
definition:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_3cq8.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23FuGijFFFHA.2572@.tk2msftngp13.phx.gbl...
> Can Get/SetExecutionOptions really get/set all the Execution properties
> that are available via Report Manager? As far as I can see I can set a
> report to Live or Snapshot and set a schedule if Snapshot. How do I
> retrieve the setting " Cache a temporary copy of the report. Expire copy
> of report after a number of minutes" for example ? This comes back as
> ExecutionSettingEnum.Live but I can't see how to get the number of
> minutes? Same if the expiry is based on a schedule - it does not get
> populated when Live. I need to script these settings but can't see how to
> do this?
> --
> Cheers,
> Jas
>
>|||Perfect, I completely missed that method :-)
--
Cheers,
Jas
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OdPCASLFFHA.3972@.TK2MSFTNGP15.phx.gbl...
> If execution is set to Live, use GetCacheOptions method to get expiration
> definition:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_3cq8.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23FuGijFFFHA.2572@.tk2msftngp13.phx.gbl...
>> Can Get/SetExecutionOptions really get/set all the Execution properties
>> that are available via Report Manager? As far as I can see I can set a
>> report to Live or Snapshot and set a schedule if Snapshot. How do I
>> retrieve the setting " Cache a temporary copy of the report. Expire copy
>> of report after a number of minutes" for example ? This comes back as
>> ExecutionSettingEnum.Live but I can't see how to get the number of
>> minutes? Same if the expiry is based on a schedule - it does not get
>> populated when Live. I need to script these settings but can't see how to
>> do this?
>> --
>> Cheers,
>> Jas
>>
>

get SQL connection for SQL config from XML

Hi

In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML.
This way, I have no chance to set connection via XML.
Any ideas on how to aproach this ?
(I want to have as much configuration on SQL server so I can modify it easily from future GUI application.

best regards

For this scenario what you need is an indirect configuration, where the connection string to the SQL Server database where your "real" configurations are stored is in turn stored in an environment variable:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

I do not know of any way to perform this indirection through an intermediary XML config file - only environment variables are supported.

|||

One more method:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

|||You should be able to set your initial SQL Server connection from an XML configuration file. In the Configurations dialog, make sure that the XML configuration is at the top of the list, so that it is executed first.

get some rows into one column

Hello,

can I do this via SQL:

example: tbltest has 5 rows:

col1
===
A
C
F
M
R

What I want is this:

result:
===============
A, C, F, M, R

Do I really have to go through the rows per SP? I could do this:

SELECT UDF(col1)
FROM tbltest

Ain't there a more simple way, maybe theres a T-SQL-command ?

thx
candide_shWell, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.

thx for your answers
candide_sh

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

It helps you in situaions whenever you wish to create commm seperated
values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew

But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew

That is how it works. Try it...I beleive it will help you a lot

create procedure sp_return_students
as
set nocount off

/* Declare variable which will store all student name */
Declare @.StudentName varchar(8000)

/* Query that will return student names and at the same time
concatenate values. /*
select @.StudentName = coalesce(@.StudentName + ', ', '') + stu_name
from tbl_students

/* At last, you just have to define column name that will store values
*/
Select @.StudentName As Student_Name

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

|||candide_sh,

There is one major problem with this solution: it might not work. It
might not work the next time you run it, after you have run a service
pack, after you have upgraded your server, after you have added another
join to your query, etc. And that is because there is no defined
behavior for this concatenation if multiple rows are involved.

So I would seriously advise you NOT to do it this way.

--
Gert-Jan

candide_sh@.yahoo.de wrote:

Quote:

Originally Posted by

>
Well, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.
>
thx for your answers
candide_sh
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

It helps you in situaions whenever you wish to create commm seperated
values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew
>
But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew
>
That is how it works. Try it...I beleive it will help you a lot
>
create procedure sp_return_students
as
set nocount off
>
/* Declare variable which will store all student name */
Declare @.StudentName varchar(8000)
>
/* Query that will return student names and at the same time
concatenate values. /*
select @.StudentName = coalesce(@.StudentName + ', ', '') + stu_name
from tbl_students
>
/* At last, you just have to define column name that will store values
*/
Select @.StudentName As Student_Name

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>>

|||There are a lot of good reasons so many experienced SQL programmers
are warning you about this.

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
The kludges that have been posted are unpredictable, unsupported and
some are highly proprietary.|||Crawl back under your rock Denial Again.

You've a cheek talking about people not being 'well mannered'.

I'm offensive to one person only - celko (well two if we are counting spats
where you spout dis information and rubbish about SQL Server) because he's
offensive to most people.

You are just offensive 'period' and people don't need to take my word for
it - you have a reputation that preceeds you...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||Nah, I think text() + FOR XML certainly has the flavour of a kludge over

Quote:

Originally Posted by

it. The syntax is anything about intuitive, and if the data include
ampersands or angle brackets, there are a few surprising waiting for us.

I disagree, true - it's unintuitive to relational folk but isn't most XML.

This the XML side of the SQL Server data engine and until you start learning
how it works, syntax etc... then I guess anything to do with XML is a
kludge.

The ampersand and angle brackets are not suprises, books online tells us
what the behaviour is, ampersand and square brackets both have meaning
within the XML which is what text() is giving us.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||On Thu, 1 Nov 2007 05:40:36 -0000, Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Nah, I think text() + FOR XML certainly has the flavour of a kludge over
>it. The syntax is anything about intuitive, and if the data include
>ampersands or angle brackets, there are a few surprising waiting for us.

>
>I disagree, true - it's unintuitive to relational folk but isn't most XML.
>
>This the XML side of the SQL Server data engine and until you start learning
>how it works, syntax etc... then I guess anything to do with XML is a
>kludge.
>
>The ampersand and angle brackets are not suprises, books online tells us
>what the behaviour is, ampersand and square brackets both have meaning
>within the XML which is what text() is giving us.

Hi Tony,

Whether intuitive or not, it still is a kludge in my eyes. The phrase
FOR XML does somewhat imply that it is intended to produce, well, XML,
don't you think? And if you use that to produce straight-text non-XML
output, then I have no other word for it than "kludge".

Though I also agree with Erland that it's better than any of the
alternatives :)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis