Calling GetReportParameters within a report Code block?

I want to write a Code block routine that returns the parameter Prompts
and ValidValue that was selected from within the report. Can I access
the ReportParameters collection (not the parameters collection, but the
actual parameter definitions collection) from within a Code block?
Thanks,
--jasonDo you know that you can get both of the things you want without doing
anything special? Parameters!parametername.Value and
Parameters!parametername.Label Lots of people don't realize you can use
the .Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<burt4684@.gmail.com> wrote in message
news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> I want to write a Code block routine that returns the parameter Prompts
> and ValidValue that was selected from within the report. Can I access
> the ReportParameters collection (not the parameters collection, but the
> actual parameter definitions collection) from within a Code block?
> Thanks,
> --jason
>|||Thanks, Bruce. I didn't know about the .Label property. That's good to
know. My only issue with .Value is some of my parameters are primary keys
and I need a good way to display the lookup values instead of the actual key
values. I was thinking I could iterate through the parameter.ValidValues
collection and display the ValidValue.Name that corresponds to the
parameter.Value that was selected. Do you know if this is possible?
Thanks,
--jason
"Bruce L-C [MVP]" wrote:
> Do you know that you can get both of the things you want without doing
> anything special? Parameters!parametername.Value and
> Parameters!parametername.Label Lots of people don't realize you can use
> the .Label
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <burt4684@.gmail.com> wrote in message
> news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > I want to write a Code block routine that returns the parameter Prompts
> > and ValidValue that was selected from within the report. Can I access
> > the ReportParameters collection (not the parameters collection, but the
> > actual parameter definitions collection) from within a Code block?
> > Thanks,
> >
> > --jason
> >
>
>|||Create another dataset that accepts the parameter as it's queryparameter.
You will end up with a single record (i.e. it has done your lookup), then
anywhere you need to use this value use this expression: =First(Fields!fieldname.value,"DatasetName")
Note that when you create a queryparameter RS adds a report parameter. If
you have the queryparameter with the same name as the reportparameter you
already have (must match case exactly since it is case sensitive) then your
query parameter and report parameter will already be mapped correctly.
This should do exactly what you need to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> Thanks, Bruce. I didn't know about the .Label property. That's good to
> know. My only issue with .Value is some of my parameters are primary keys
> and I need a good way to display the lookup values instead of the actual
key
> values. I was thinking I could iterate through the parameter.ValidValues
> collection and display the ValidValue.Name that corresponds to the
> parameter.Value that was selected. Do you know if this is possible?
> Thanks,
> --jason
> "Bruce L-C [MVP]" wrote:
> > Do you know that you can get both of the things you want without doing
> > anything special? Parameters!parametername.Value and
> > Parameters!parametername.Label Lots of people don't realize you can
use
> > the .Label
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > <burt4684@.gmail.com> wrote in message
> > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > I want to write a Code block routine that returns the parameter
Prompts
> > > and ValidValue that was selected from within the report. Can I access
> > > the ReportParameters collection (not the parameters collection, but
the
> > > actual parameter definitions collection) from within a Code block?
> > > Thanks,
> > >
> > > --jason
> > >
> >
> >
> >|||As always, there's more than one way to skin a cat. Great idea. Thanks for
the help!
--jason
"Bruce L-C [MVP]" wrote:
> Create another dataset that accepts the parameter as it's queryparameter.
> You will end up with a single record (i.e. it has done your lookup), then
> anywhere you need to use this value use this expression: => First(Fields!fieldname.value,"DatasetName")
> Note that when you create a queryparameter RS adds a report parameter. If
> you have the queryparameter with the same name as the reportparameter you
> already have (must match case exactly since it is case sensitive) then your
> query parameter and report parameter will already be mapped correctly.
> This should do exactly what you need to do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
> news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> > Thanks, Bruce. I didn't know about the .Label property. That's good to
> > know. My only issue with .Value is some of my parameters are primary keys
> > and I need a good way to display the lookup values instead of the actual
> key
> > values. I was thinking I could iterate through the parameter.ValidValues
> > collection and display the ValidValue.Name that corresponds to the
> > parameter.Value that was selected. Do you know if this is possible?
> >
> > Thanks,
> >
> > --jason
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Do you know that you can get both of the things you want without doing
> > > anything special? Parameters!parametername.Value and
> > > Parameters!parametername.Label Lots of people don't realize you can
> use
> > > the .Label
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > <burt4684@.gmail.com> wrote in message
> > > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > > I want to write a Code block routine that returns the parameter
> Prompts
> > > > and ValidValue that was selected from within the report. Can I access
> > > > the ReportParameters collection (not the parameters collection, but
> the
> > > > actual parameter definitions collection) from within a Code block?
> > > > Thanks,
> > > >
> > > > --jason
> > > >
> > >
> > >
> > >
>
>

Calling a web service from Custom Code Block

Is it possible to make a web service call from a report's custom code
section? Or is it neccesary to wrtie a custom assembly to do it?
Thanks.It will be necessary to write a custom assembly to do it - unless you
unsecure the permissions of the Report Expressions - which is not a good
idea.
You'll need to learn about Code Access Security to do this properly and our
book's Chapter 9 will give you a great deal of information to get you up and
running with writing Custom Assemblies and Securing them for use in
Reporting Services.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"billd" <billd@.discussions.microsoft.com> wrote in message
news:C7AC6A11-97BC-4648-9130-8A6AA196C488@.microsoft.com...
> Is it possible to make a web service call from a report's custom code
> section? Or is it neccesary to wrtie a custom assembly to do it?
> Thanks.|||Peter,
Doing something similar to Chapter 9 - but getting stuck on what I need to
put into the policy file - I have read Yogesh's link - but still having
problems.
Any help appreciated.
"Peter Blackburn (www.sqlreportingservice" wrote:
> It will be necessary to write a custom assembly to do it - unless you
> unsecure the permissions of the Report Expressions - which is not a good
> idea.
> You'll need to learn about Code Access Security to do this properly and our
> book's Chapter 9 will give you a great deal of information to get you up and
> running with writing Custom Assemblies and Securing them for use in
> Reporting Services.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
> "billd" <billd@.discussions.microsoft.com> wrote in message
> news:C7AC6A11-97BC-4648-9130-8A6AA196C488@.microsoft.com...
> > Is it possible to make a web service call from a report's custom code
> > section? Or is it neccesary to wrtie a custom assembly to do it?
> >
> > Thanks.
>
>

Call SQL Stored Procedure

Greetings,

Even though this may be not right place with this issue I would like to try!
I facing with the problem Object Variable or With Block variable not set while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.

Many thanks in Advancehowzabout

Dim oConn, oComm, oRS
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
Set oRS = CreateObject("ADODB.RecordSet")

' Open connection to database
oConn.ConnectionString = Application("ConnectionString")
oconn.Open

' Use the stored proc
oComm.CommandType = 4 ' adStoredProc
oComm.ActiveConnection = oConn
oComm.CommandText = "spProcName"
oComm.Parameters.Refresh
oComm.Parameters("@.ParameterName") = sParameterName

oRS.CursorLocation = 3 ' adUseClient
oRS.Open oComm, , 3 ' adOpenStatic

Dunno if that will help with the access form or not. This is from a VBScript snippet that I use.

Regards,

hmscott

Call a storedproc in select from block

Hi everyone,

I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....

Select *

, (Exec MyStoredProc MyParam) as Field1

From Table1

It's complicated. You need to set up a (possible looped back) linked server and invoke it through the OPENROWSET() function. It is is explained here

http://www.sqlmag.com/Article/ArticleID/19842/sql_server_19842.html

You may need to enable the 'Ad Hoc Distributed Queries' sp_configure options in SQL 2005 (not sure ... I didn't try)

From a TSQL perspective, one of the main reasons we don't let you invoke procedures from inside queries is that we cannot determine the shape of the result set at the time we compile the query plan. Moreover, if the procedure has side effects, the semantics become unclear (e.g. resuls are different if you invoke once per row or once per query).

By using the "trick" above, the full distributed query machinery kicks into action which involves a lot of extra overhead not found in normal queries. Frankly, I'm not sure what the semantic is if you try something like

SELECT * FROM mytable JOIN OPENROWSET(..., 'EXEC myproc') ...

Perhaps you can try rewriting your code to use a table-valued function if it doesn't have side effects. This will give you better performance and predictable semantics.