Does Reporting Services allow you to select a dataset dynamically?

I have a remote report ...
I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.)
Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure.
I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?

Thanks in advance!
Your query can be expression-based, e.g.; =Iif(Parameters!SomeParameterValue = 0, "EXEC sp1 1<parameter list>", EXEC sp2 1<parameter list>"). Alternatively, if the sp choice can be based on a configuration convention, you can use the Report Server web.config file to store the config value. The later case is demonstrated by the DynamicConnection report in this download. The report uses a dynamic connection string but the same approach can be applied to the query.|||That worked. Thanks!
sql

Does anyone know of an easy way (read without going into the XML) of deleting a dataset?

Please see above

In report designer, go to the data tab and in its toolbar click the icon with the red X (deletes the currently selected dataset).

-- Robert

Does an embedded XML DataSet have to be hardcoded?

I have an XML DataSource in my report which does not specify a connection
string, because I want to use an embedded XML DataSet. However I would like
the XML DataSet text to be defined using an expression (actually a function
call in my custom assembly). Even though you can define an expression for the
XML DataSet, doing so generates a design time error like this:
"The XmlDP query is invalid. Syntax error at line 1, character 3 of the
ElementPath. (Microsoft.ReportingServices.DataExtensions)"
If I define the DataSet text like this it works fine:
============================================= <Query>
<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>
<XmlData>
<Root>
<Parameter ParamName="Param1" ParamValue="Value1"></Parameter>
<Parameter ParamName="Param2" ParamValue="Value2"></Parameter>
<Parameter ParamName="Param3" ParamValue="Value3"></Parameter>
<Parameter ParamName="Param4" ParamValue="Value4"></Parameter>
<Parameter ParamName="Param5" ParamValue="Value5"></Parameter>
</Root>
</XmlData>
</Query>
=============================================
If I define the DataSet text like this I get the error message:
============================================= = "<Query>"
+ "<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>"
+ "<XmlData>"
+ "<Root>"
+ "<Parameter ParamName=""Param1"" ParamValue=""Value1""></Parameter>"
+ "<Parameter ParamName=""Param2"" ParamValue=""Value2""></Parameter>"
+ "<Parameter ParamName=""Param3"" ParamValue=""Value3""></Parameter>"
+ "<Parameter ParamName=""Param4"" ParamValue=""Value4""></Parameter>"
+ "<Parameter ParamName=""Param5"" ParamValue=""Value5""></Parameter>"
+ "</Root>"
+ "</XmlData>"
+ "</Query>"
=============================================
The above is just an expression that shows the error message. Ideally my
DataSet text will look like this:
=Code.RptLib.ReportParametersXML()
It looks like I am up against a limitation in the product. Can you confirm
that this is a limitation in trhe product (embedded XML DataSets MUST be
hardcoded), or is there a workaround or technique that I am missing?
Thanks!
-- Chris
--
Chris, SSSIHello Chirs,
I have reproduced this issue on my side.
I will do some research on this. I appreciate your patience.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Hi Wei Lu,
I found an interesting thing.
By accident I tried =Code.RptLib.ReportParametersXML instead of
=Code.RptLib.ReportParametersXML() and that worked.
Because my method does not take any parameters, I guess VB.NET does not like
the empty parenthesis and for some reason the Report Designer gives a
misleading error message about this problem.
So I solved my problem and I do not have to hard code the XML. It is coming
from my custom assembly.
However, I have no idea why the expression syntax ="..." does not work. But
since I do not need that approach it is not a problem for me right now.
Still, you might want to pass this "bug" on to the developers of SSRS.
-- Chris
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chirs,
> I have reproduced this issue on my side.
> I will do some research on this. I appreciate your patience.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>

Does a table REQUIRE a dataset?

Why is it not possible for me to create a static table in Reporting
services?
I have a table that will be making some calls to a custom assembly, but it
requires NO database queries to build itself. When i leave the "dataset"
area blank, the report will not compile.
Is it really impossible to create static tables in Reporting Services?
Any help would be appreciated.
Thanks!
BrianYou can create what you want, but you still need to have a "dataset" name.
Create a new dataset ( default microsoft sql server type ) then for your
query here's an example.
Select 1 as Col#, 'A' as Col$
Union
Select 2 as Col#, 'B' as Col$
Union
Select 3 as Col#, 'C' as Col$
Union
Select 4 as Col#, 'D' as Col$
Union
Select 5 as Col#, 'E' as Col$
or am i off track on what you want to accomplish?
"G" wrote:
> Why is it not possible for me to create a static table in Reporting
> services?
> I have a table that will be making some calls to a custom assembly, but it
> requires NO database queries to build itself. When i leave the "dataset"
> area blank, the report will not compile.
> Is it really impossible to create static tables in Reporting Services?
> Any help would be appreciated.
> Thanks!
> Brian
>
>