Multiple Datasource as Parameter

This question has been asked numerous times, but I'm still stumped. I have
done several research and some people say it's impossible, while others say
it can be done. One I heard is the dataset extension. I'm still a little
confuse how this work since I'm quite new to working with Reports, especially
in Reporting Services. Hopefully someone can help shed some lights here.
Here's the situation. We have a couple of databases with same fields,
tables, and schema. Each database belongs to a department within the
company. I don't want to have to create each report for each different
database. What I wanted to do is be able to create a report that will be
able to use a parameter for the different databases. The parameter will be a
dynamic one, where the user can just select the database that they want and
then click view.
Can someone shed some lights here?
Thanks.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
* Use a custom data processing extension. For detailed dicussions see also:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=12f4a06c-57a5-42cf-8e7b-03e0b093129c&sloc=en-us
Native support (expression-based connection strings) is planned to be
included in the next version.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
have
> done several research and some people say it's impossible, while others
say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
be a
> dynamic one, where the user can just select the database that they want
and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Hello Chang,
What you want to do is pretty easily done - without having to step into
creating custom data extensions...
Bill and I explain this in great detail in our book "Hitchhiker's Guide to
SQL Server 200 Reporting Services" p 298 - 306.
However you may be able to follow these brief instructions:
1) Execute a query in the DataSet Designer that will pull back the fields
that you want. - this should populate the Fields List.
2) Then replace the query string in the DataSet Designer with a Visual
Basic.NET expression (include the =) - for example:-
=iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
xpvs2003.pubs.dbo.sysobjects")
3) Create for yourself the Report Parameter - in the above example this is
called "Source".
In my simple example here in VB.NET expression I'm just returning a Select
String which will obtain it's content from the sysobjects tables in either
the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
dependant upon the parameter.
If the databases are on different servers then what I'd do is include the
full schema name of the target including the servername in the FROM clause -
after having linked the SQL servers (see sp_linked servers in Books On Line)
This should get you started, - but much more comprehensive details and
explanation is in the book.
Best Wishes
Peter Blackburn
www.sqlreportingservices.net
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
> have
> done several research and some people say it's impossible, while others
> say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
> especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
> be a
> dynamic one, where the user can just select the database that they want
> and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Thank you Peter and Robert. Those advice are extremely helpful.
Peter I have been following up on your website forum and I found a lot of
neat stuff that I can use for my reports. I'm in the process of getting my
boss to order me a copy of your book.
Regards,
Chang
"Peter Blackburn (www.sqlreportingservice" wrote:
> Hello Chang,
> What you want to do is pretty easily done - without having to step into
> creating custom data extensions...
> Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> SQL Server 200 Reporting Services" p 298 - 306.
> However you may be able to follow these brief instructions:
> 1) Execute a query in the DataSet Designer that will pull back the fields
> that you want. - this should populate the Fields List.
> 2) Then replace the query string in the DataSet Designer with a Visual
> Basic.NET expression (include the =) - for example:-
> =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> xpvs2003.pubs.dbo.sysobjects")
> 3) Create for yourself the Report Parameter - in the above example this is
> called "Source".
> In my simple example here in VB.NET expression I'm just returning a Select
> String which will obtain it's content from the sysobjects tables in either
> the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> dependant upon the parameter.
> If the databases are on different servers then what I'd do is include the
> full schema name of the target including the servername in the FROM clause -
> after having linked the SQL servers (see sp_linked servers in Books On Line)
> This should get you started, - but much more comprehensive details and
> explanation is in the book.
> Best Wishes
> Peter Blackburn
> www.sqlreportingservices.net
>
>
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > This question has been asked numerous times, but I'm still stumped. I
> > have
> > done several research and some people say it's impossible, while others
> > say
> > it can be done. One I heard is the dataset extension. I'm still a little
> > confuse how this work since I'm quite new to working with Reports,
> > especially
> > in Reporting Services. Hopefully someone can help shed some lights here.
> >
> > Here's the situation. We have a couple of databases with same fields,
> > tables, and schema. Each database belongs to a department within the
> > company. I don't want to have to create each report for each different
> > database. What I wanted to do is be able to create a report that will be
> > able to use a parameter for the different databases. The parameter will
> > be a
> > dynamic one, where the user can just select the database that they want
> > and
> > then click view.
> >
> > Can someone shed some lights here?
> >
> > Thanks.
>
>|||Will Peter, my boss has just ordered me your book from Amazon.com. Can't
wait to get it and join your forum community.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >|||Will Peter, my boss has just ordered your book for me from Amazon.com. Can't
wait to get the book.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >

Multiple Calls to ServerReport.SetParameters() With Varying Numbers Of Parameters

I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.

Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);

this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;

}

}

protected void btnViewRpt_Click(object sender, EventArgs e)

{

ReportParameter[] rptParams = GetReportParameters();

this.rptViewer.ServerReport.SetParameters(rptParams);

this.rptViewer.ServerReport.Refresh();

}

private ReportParameter[] GetReportParameters()

{

int paramCount = 0;

ReportParameter[] retVal;

string emptyVal = null;

if (txtName.Text != "") paramCount++;

if (txtAddress.Text != "") paramCount++;

if (txtZip.Text != "") paramCount++;

retVal = new ReportParameter[paramCount];

paramCount = 0;

if (txtName.Text != "")

retVal[paramCount++] = new ReportParameter("Name", txtName.Text);

if (txtAddress.Text != "")

retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);

if (txtZip.Text != "")

retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);

return retVal;

}

The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.

The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.

So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

Any suggestions?

>>

I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

<<

Not meaning to be argumentative... just trying to figure out your situation...

What is the situation in which sending an explicit null "can cause errors"?

IAC, if you want to "render as though for the first time" (wasn't this a Madonna song <g>?) , you should probably use ReportViewer.Reset. You may need to re-specify the URL and path *after* that, though.

>L<

|||

Thanks so much for your reply! That's exactly what I was looking for. After applying ReportViewer.Reset() in our code, I got the behavior I was looking for.

Now, to address your other question (and to perhaps move away from "just get it working" toward "do it the right way"): I have a report that accepts a DateTime parameter (called ReportDate, surprisingly enough):

The parameter allows NULL values. But it also gets its set of available values from a query written for that purpose. It's also set to get the default value from the same query that provides the available values.|||

Well, that's just cr*ppy, if true. Can you share exactly the line you are using to pass the null, just in case this is supposed to work but you need to use a slightly different syntax?

>L<

|||

The code is a bit involved because we're scraping parameter values from screen controls that are automatically generated. However, I think you're on to something. I was just looking at how our parameters collection is constructed:

Code Snippet

for(int rowCount = 1; rowCount < parameterTable.Rows.Count; rowCount++)

{

TableRow tr = parameterTable.Rows[rowCount];

Control parameter = tr.Cells[1].Controls[0];

string parameterName = ((WebControl)parameter).Attributes["ParameterName"];

string parameterValue = "";

if (parameter isParameterDatePicker)

parameterValue = ((ParameterDatePicker)parameter).Text == string.Empty ? null : ((ParameterDatePicker)parameter).Text;

elseif (parameter isParameterDropDownList)

parameterValue = ((ParameterDropDownList)parameter).Value == string.Empty ? null : ((ParameterDropDownList)parameter).Value;

elseif (parameter isParameterTextBox)

parameterValue = ((ParameterTextBox)parameter).Text == string.Empty ? null : ((ParameterTextBox)parameter).Text;

else

thrownewException("Unable to determine parameter type.");

// Create the report parameter

ReportParameter reportParameter = newReportParameter();

reportParameter.Name = parameterName;

reportParameter.Values.Add(parameterValue);

reportParameters.Add(reportParameter);

}

Notice how every parameter is created with a string-type value, regardless of which type of value it represents. I'm wondering if, when actual values are specified, the code under the covers is able to successfully convert the strings into the DateTime type the report is expecting, but in the case of NULLs, the conversion/casting fails? I wonder if casting the nulls as specific data types would work?

greg

|||

Scratch that previous thought about data types. I realize now that the value(s) assigned to a ReportParameter are in fact arrays of strings. I discovered in a separate posting by Lisa that the way to create a ReportParameter with a NULL value is to simply create the parameter with the name only and not assign a value at all, like so:

Code Snippet

ReportParameter rptParam = new ReportParameter("ParamName");

or

Code Snippet

ReportParameter rptParam = new ReportParameter();

rptParam.Name = "ParamName";

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

So what I've determined works best here when viewing a report multiple times with optional parameters that may or may not be specified from one viewing to the next, is to simply not create any ReportParameters that don't have values specified and to use the ReportViewer.Reset() method between report renderings, as Lisa suggested, and I get the result I want. Optional parameters assume their default values as assigned in the report, and the ReportViewer object is induced to "forget" any previously-specified ReportParameter values in the current rendering of the report. If there's any downside to that I haven't discovered it yet. :-)

|||

>>

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

<<

Thank you for taking the time to check this out. I really thought this would be a better thing to do than Reset, if it worked. Why?

Simply because it goes against the inner grain, in terms of efficiency, to totally re-initialize if you don't have to. There are degrees of initialization in most processes that can be re-run, and one would like to use the right one <sigh>. Of course it has to *work* to be the right one. <double sigh>. There may be no downside, as you suggest, to doing extra initialization, but in some cases there is and you don't discover it until much, much later... Oh well...

Thanks again for checking this out,

>L<