Cascading Parameter couse requery of other parameters

Cascading Parameter cousing fatal DB overhead and performance problem.
I have 2 parameters:
- Parameter A) Customers
- Parameter B) Contact Persons
Both have a dataset as list source.
In the dataset of B) is a reference to A).
If I choose a selectio in A) then B) becomes refreshed and the selection in
A) is used as parameter. That is how it should be.
BUT: A) is requiried too!!! :-O
In other reports I have more parameters and dependencies. The list sources
are allways datasets with many returning rows. And this behaviour results in
a big performance problem.
Even in the sample report described here it is a performance problem.
I can't believe it, that this is the ripeness of reporting services! Please
tell me somebody that this is not true and how I can solve the problem.I don't understand your problem. Is it that you don't want to require a
selection for customer? You can always add an All (do a union query).
Not sure why you have a performance problem, you shouldn't. Assuming that
you design it that each cascading parameter has no more that a few hundred
responses. I use cascading parameters all the time without a problem.
What do you consider many rows?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> Cascading Parameter cousing fatal DB overhead and performance problem.
> I have 2 parameters:
> - Parameter A) Customers
> - Parameter B) Contact Persons
> Both have a dataset as list source.
> In the dataset of B) is a reference to A).
> If I choose a selectio in A) then B) becomes refreshed and the selection
in
> A) is used as parameter. That is how it should be.
> BUT: A) is requiried too!!! :-O
> In other reports I have more parameters and dependencies. The list sources
> are allways datasets with many returning rows. And this behaviour results
in
> a big performance problem.
> Even in the sample report described here it is a performance problem.
> I can't believe it, that this is the ripeness of reporting services!
Please
> tell me somebody that this is not true and how I can solve the problem.|||Thank you for the fast answer!
My dropdownlist in the parameter "customers" must be queried only one time.
The list in parameter "Contact Person" must be filled each time a customer is
selected. Becouse this list shows all contact persons from the selected
customer.
In the parameters are several 1000 records displayed e.g. "Customers" or
"Projects".
You wrote "You can always add an All (do a union query)." What does this
means exactly. I use a dataset for the parameter "customer". The dataset
contains:
"Select CustID, CustName from Customers Order by Custname".
In the "Report Parameter" dialog I choose
Available Valus:
- From query
There is no other option to specify that this query should be executed only
one time.
I have to create reports with more then 5 cascading parameters. Some
parameters have several 1000 rows. If each selection will couse the requery
from all parameters it takes realy to long. Much more then 6 seconds (after
somebody choose a parameter) which is the "success criteria".
"Bruce L-C [MVP]" wrote:
> I don't understand your problem. Is it that you don't want to require a
> selection for customer? You can always add an All (do a union query).
> Not sure why you have a performance problem, you shouldn't. Assuming that
> you design it that each cascading parameter has no more that a few hundred
> responses. I use cascading parameters all the time without a problem.
> What do you consider many rows?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > Cascading Parameter cousing fatal DB overhead and performance problem.
> >
> > I have 2 parameters:
> > - Parameter A) Customers
> > - Parameter B) Contact Persons
> > Both have a dataset as list source.
> >
> > In the dataset of B) is a reference to A).
> >
> > If I choose a selectio in A) then B) becomes refreshed and the selection
> in
> > A) is used as parameter. That is how it should be.
> > BUT: A) is requiried too!!! :-O
> >
> > In other reports I have more parameters and dependencies. The list sources
> > are allways datasets with many returning rows. And this behaviour results
> in
> > a big performance problem.
> >
> > Even in the sample report described here it is a performance problem.
> >
> > I can't believe it, that this is the ripeness of reporting services!
> Please
> > tell me somebody that this is not true and how I can solve the problem.
>
>|||I don't think that the queries should be executed more than once, are you
sure they are? The customers query should execute only once as far as I know
(could be wrong, could be that my lists are short enough that I don't see
the delay).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> Thank you for the fast answer!
> My dropdownlist in the parameter "customers" must be queried only one
time.
> The list in parameter "Contact Person" must be filled each time a customer
is
> selected. Becouse this list shows all contact persons from the selected
> customer.
> In the parameters are several 1000 records displayed e.g. "Customers" or
> "Projects".
> You wrote "You can always add an All (do a union query)." What does this
> means exactly. I use a dataset for the parameter "customer". The dataset
> contains:
> "Select CustID, CustName from Customers Order by Custname".
> In the "Report Parameter" dialog I choose
> Available Valus:
> - From query
> There is no other option to specify that this query should be executed
only
> one time.
> I have to create reports with more then 5 cascading parameters. Some
> parameters have several 1000 rows. If each selection will couse the
requery
> from all parameters it takes realy to long. Much more then 6 seconds
(after
> somebody choose a parameter) which is the "success criteria".
>
>
> "Bruce L-C [MVP]" wrote:
> > I don't understand your problem. Is it that you don't want to require a
> > selection for customer? You can always add an All (do a union query).
> >
> > Not sure why you have a performance problem, you shouldn't. Assuming
that
> > you design it that each cascading parameter has no more that a few
hundred
> > responses. I use cascading parameters all the time without a problem.
> >
> > What do you consider many rows?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > > Cascading Parameter cousing fatal DB overhead and performance problem.
> > >
> > > I have 2 parameters:
> > > - Parameter A) Customers
> > > - Parameter B) Contact Persons
> > > Both have a dataset as list source.
> > >
> > > In the dataset of B) is a reference to A).
> > >
> > > If I choose a selectio in A) then B) becomes refreshed and the
selection
> > in
> > > A) is used as parameter. That is how it should be.
> > > BUT: A) is requiried too!!! :-O
> > >
> > > In other reports I have more parameters and dependencies. The list
sources
> > > are allways datasets with many returning rows. And this behaviour
results
> > in
> > > a big performance problem.
> > >
> > > Even in the sample report described here it is a performance problem.
> > >
> > > I can't believe it, that this is the ripeness of reporting services!
> > Please
> > > tell me somebody that this is not true and how I can solve the
problem.
> >
> >
> >|||100% for shure!
I replaced the SQL Statment with a call of a stored procedure. And the
stored procedure write a log entry in a table each time the SP is called.
Each time I select a entry in the 'Customer' Parameter the query for the
'customer' Parameter get executed. And (strictly correct) also the query for
the cascading parameter 'Contact Person'.
Just for the sake of completness: The database is on a oracle server. But
this could not be related to this behaviour, becouse Reporting Services fires
the refill of the parameter drop down list.
"Bruce L-C [MVP]" wrote:
> I don't think that the queries should be executed more than once, are you
> sure they are? The customers query should execute only once as far as I know
> (could be wrong, could be that my lists are short enough that I don't see
> the delay).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > Thank you for the fast answer!
> >
> > My dropdownlist in the parameter "customers" must be queried only one
> time.
> > The list in parameter "Contact Person" must be filled each time a customer
> is
> > selected. Becouse this list shows all contact persons from the selected
> > customer.
> >
> > In the parameters are several 1000 records displayed e.g. "Customers" or
> > "Projects".
> >
> > You wrote "You can always add an All (do a union query)." What does this
> > means exactly. I use a dataset for the parameter "customer". The dataset
> > contains:
> > "Select CustID, CustName from Customers Order by Custname".
> > In the "Report Parameter" dialog I choose
> > Available Valus:
> > - From query
> > There is no other option to specify that this query should be executed
> only
> > one time.
> >
> > I have to create reports with more then 5 cascading parameters. Some
> > parameters have several 1000 rows. If each selection will couse the
> requery
> > from all parameters it takes realy to long. Much more then 6 seconds
> (after
> > somebody choose a parameter) which is the "success criteria".
> >
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't understand your problem. Is it that you don't want to require a
> > > selection for customer? You can always add an All (do a union query).
> > >
> > > Not sure why you have a performance problem, you shouldn't. Assuming
> that
> > > you design it that each cascading parameter has no more that a few
> hundred
> > > responses. I use cascading parameters all the time without a problem.
> > >
> > > What do you consider many rows?
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > > > Cascading Parameter cousing fatal DB overhead and performance problem.
> > > >
> > > > I have 2 parameters:
> > > > - Parameter A) Customers
> > > > - Parameter B) Contact Persons
> > > > Both have a dataset as list source.
> > > >
> > > > In the dataset of B) is a reference to A).
> > > >
> > > > If I choose a selectio in A) then B) becomes refreshed and the
> selection
> > > in
> > > > A) is used as parameter. That is how it should be.
> > > > BUT: A) is requiried too!!! :-O
> > > >
> > > > In other reports I have more parameters and dependencies. The list
> sources
> > > > are allways datasets with many returning rows. And this behaviour
> results
> > > in
> > > > a big performance problem.
> > > >
> > > > Even in the sample report described here it is a performance problem.
> > > >
> > > > I can't believe it, that this is the ripeness of reporting services!
> > > Please
> > > > tell me somebody that this is not true and how I can solve the
> problem.
> > >
> > >
> > >
>
>|||Just to make sure I understand. You pick a customer and the customer sp is
executed. It only gets executed when you select a customer. It does not get
executed when you select a cascading parameter, correct?
If this is true, is it a real problem? Does the user keep the report open
and keep selecting different customers?
One possible solution is to have the customers be cascaded parameter itself.
Having several thousand records as the source for a listbox is not a good
idea. I have seen this be a problem with other products besides RS. My
suggestion is to somehow end up with a most a couple of hundred items in the
list.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> 100% for shure!
> I replaced the SQL Statment with a call of a stored procedure. And the
> stored procedure write a log entry in a table each time the SP is called.
> Each time I select a entry in the 'Customer' Parameter the query for the
> 'customer' Parameter get executed. And (strictly correct) also the query
> for
> the cascading parameter 'Contact Person'.
> Just for the sake of completness: The database is on a oracle server. But
> this could not be related to this behaviour, becouse Reporting Services
> fires
> the refill of the parameter drop down list.
> "Bruce L-C [MVP]" wrote:
>> I don't think that the queries should be executed more than once, are you
>> sure they are? The customers query should execute only once as far as I
>> know
>> (could be wrong, could be that my lists are short enough that I don't see
>> the delay).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
>> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
>> > Thank you for the fast answer!
>> >
>> > My dropdownlist in the parameter "customers" must be queried only one
>> time.
>> > The list in parameter "Contact Person" must be filled each time a
>> > customer
>> is
>> > selected. Becouse this list shows all contact persons from the selected
>> > customer.
>> >
>> > In the parameters are several 1000 records displayed e.g. "Customers"
>> > or
>> > "Projects".
>> >
>> > You wrote "You can always add an All (do a union query)." What does
>> > this
>> > means exactly. I use a dataset for the parameter "customer". The
>> > dataset
>> > contains:
>> > "Select CustID, CustName from Customers Order by Custname".
>> > In the "Report Parameter" dialog I choose
>> > Available Valus:
>> > - From query
>> > There is no other option to specify that this query should be executed
>> only
>> > one time.
>> >
>> > I have to create reports with more then 5 cascading parameters. Some
>> > parameters have several 1000 rows. If each selection will couse the
>> requery
>> > from all parameters it takes realy to long. Much more then 6 seconds
>> (after
>> > somebody choose a parameter) which is the "success criteria".
>> >
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't understand your problem. Is it that you don't want to require
>> > > a
>> > > selection for customer? You can always add an All (do a union query).
>> > >
>> > > Not sure why you have a performance problem, you shouldn't. Assuming
>> that
>> > > you design it that each cascading parameter has no more that a few
>> hundred
>> > > responses. I use cascading parameters all the time without a problem.
>> > >
>> > > What do you consider many rows?
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
>> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
>> > > > Cascading Parameter cousing fatal DB overhead and performance
>> > > > problem.
>> > > >
>> > > > I have 2 parameters:
>> > > > - Parameter A) Customers
>> > > > - Parameter B) Contact Persons
>> > > > Both have a dataset as list source.
>> > > >
>> > > > In the dataset of B) is a reference to A).
>> > > >
>> > > > If I choose a selectio in A) then B) becomes refreshed and the
>> selection
>> > > in
>> > > > A) is used as parameter. That is how it should be.
>> > > > BUT: A) is requiried too!!! :-O
>> > > >
>> > > > In other reports I have more parameters and dependencies. The list
>> sources
>> > > > are allways datasets with many returning rows. And this behaviour
>> results
>> > > in
>> > > > a big performance problem.
>> > > >
>> > > > Even in the sample report described here it is a performance
>> > > > problem.
>> > > >
>> > > > I can't believe it, that this is the ripeness of reporting
>> > > > services!
>> > > Please
>> > > > tell me somebody that this is not true and how I can solve the
>> problem.
>> > >
>> > >
>> > >
>>|||You understand it right!
I choose a customer, customer and contact person get requiried. If I choose
a contact person nothing get requiried.
Possibly your hint on making all parameters to cascading parameters help me
out of the mud. I could place a preselected parameter for language and refer
on it from all other parameter datasets. I'll test it.
The requirements are focused on filtering the data. It is a CRM Database.
I can't imagine how to keep the parameters less then several thousends.
Customer beginning with 'A' are more then 7000 for example. Projects in one
country are several thousends, Sales Persons are more then 1000. It is just a
middle sized company. And I'm in trouble at the beginning of the project.
Thank you very much for all your help on this!!!
"Bruce L-C [MVP]" wrote:
> Just to make sure I understand. You pick a customer and the customer sp is
> executed. It only gets executed when you select a customer. It does not get
> executed when you select a cascading parameter, correct?
> If this is true, is it a real problem? Does the user keep the report open
> and keep selecting different customers?
> One possible solution is to have the customers be cascaded parameter itself.
> Having several thousand records as the source for a listbox is not a good
> idea. I have seen this be a problem with other products besides RS. My
> suggestion is to somehow end up with a most a couple of hundred items in the
> list.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > 100% for shure!
> >
> > I replaced the SQL Statment with a call of a stored procedure. And the
> > stored procedure write a log entry in a table each time the SP is called.
> >
> > Each time I select a entry in the 'Customer' Parameter the query for the
> > 'customer' Parameter get executed. And (strictly correct) also the query
> > for
> > the cascading parameter 'Contact Person'.
> >
> > Just for the sake of completness: The database is on a oracle server. But
> > this could not be related to this behaviour, becouse Reporting Services
> > fires
> > the refill of the parameter drop down list.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I don't think that the queries should be executed more than once, are you
> >> sure they are? The customers query should execute only once as far as I
> >> know
> >> (could be wrong, could be that my lists are short enough that I don't see
> >> the delay).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> >> > Thank you for the fast answer!
> >> >
> >> > My dropdownlist in the parameter "customers" must be queried only one
> >> time.
> >> > The list in parameter "Contact Person" must be filled each time a
> >> > customer
> >> is
> >> > selected. Becouse this list shows all contact persons from the selected
> >> > customer.
> >> >
> >> > In the parameters are several 1000 records displayed e.g. "Customers"
> >> > or
> >> > "Projects".
> >> >
> >> > You wrote "You can always add an All (do a union query)." What does
> >> > this
> >> > means exactly. I use a dataset for the parameter "customer". The
> >> > dataset
> >> > contains:
> >> > "Select CustID, CustName from Customers Order by Custname".
> >> > In the "Report Parameter" dialog I choose
> >> > Available Valus:
> >> > - From query
> >> > There is no other option to specify that this query should be executed
> >> only
> >> > one time.
> >> >
> >> > I have to create reports with more then 5 cascading parameters. Some
> >> > parameters have several 1000 rows. If each selection will couse the
> >> requery
> >> > from all parameters it takes realy to long. Much more then 6 seconds
> >> (after
> >> > somebody choose a parameter) which is the "success criteria".
> >> >
> >> >
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > I don't understand your problem. Is it that you don't want to require
> >> > > a
> >> > > selection for customer? You can always add an All (do a union query).
> >> > >
> >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> >> that
> >> > > you design it that each cascading parameter has no more that a few
> >> hundred
> >> > > responses. I use cascading parameters all the time without a problem.
> >> > >
> >> > > What do you consider many rows?
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> >> > > > Cascading Parameter cousing fatal DB overhead and performance
> >> > > > problem.
> >> > > >
> >> > > > I have 2 parameters:
> >> > > > - Parameter A) Customers
> >> > > > - Parameter B) Contact Persons
> >> > > > Both have a dataset as list source.
> >> > > >
> >> > > > In the dataset of B) is a reference to A).
> >> > > >
> >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> >> selection
> >> > > in
> >> > > > A) is used as parameter. That is how it should be.
> >> > > > BUT: A) is requiried too!!! :-O
> >> > > >
> >> > > > In other reports I have more parameters and dependencies. The list
> >> sources
> >> > > > are allways datasets with many returning rows. And this behaviour
> >> results
> >> > > in
> >> > > > a big performance problem.
> >> > > >
> >> > > > Even in the sample report described here it is a performance
> >> > > > problem.
> >> > > >
> >> > > > I can't believe it, that this is the ripeness of reporting
> >> > > > services!
> >> > > Please
> >> > > > tell me somebody that this is not true and how I can solve the
> >> problem.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Dosn't work!
Now I made the customer parameter a cascading parameter from parameter
language. But the customer parameter get still requeried.
"Bruce L-C [MVP]" wrote:
> Just to make sure I understand. You pick a customer and the customer sp is
> executed. It only gets executed when you select a customer. It does not get
> executed when you select a cascading parameter, correct?
> If this is true, is it a real problem? Does the user keep the report open
> and keep selecting different customers?
> One possible solution is to have the customers be cascaded parameter itself.
> Having several thousand records as the source for a listbox is not a good
> idea. I have seen this be a problem with other products besides RS. My
> suggestion is to somehow end up with a most a couple of hundred items in the
> list.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > 100% for shure!
> >
> > I replaced the SQL Statment with a call of a stored procedure. And the
> > stored procedure write a log entry in a table each time the SP is called.
> >
> > Each time I select a entry in the 'Customer' Parameter the query for the
> > 'customer' Parameter get executed. And (strictly correct) also the query
> > for
> > the cascading parameter 'Contact Person'.
> >
> > Just for the sake of completness: The database is on a oracle server. But
> > this could not be related to this behaviour, becouse Reporting Services
> > fires
> > the refill of the parameter drop down list.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I don't think that the queries should be executed more than once, are you
> >> sure they are? The customers query should execute only once as far as I
> >> know
> >> (could be wrong, could be that my lists are short enough that I don't see
> >> the delay).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> >> > Thank you for the fast answer!
> >> >
> >> > My dropdownlist in the parameter "customers" must be queried only one
> >> time.
> >> > The list in parameter "Contact Person" must be filled each time a
> >> > customer
> >> is
> >> > selected. Becouse this list shows all contact persons from the selected
> >> > customer.
> >> >
> >> > In the parameters are several 1000 records displayed e.g. "Customers"
> >> > or
> >> > "Projects".
> >> >
> >> > You wrote "You can always add an All (do a union query)." What does
> >> > this
> >> > means exactly. I use a dataset for the parameter "customer". The
> >> > dataset
> >> > contains:
> >> > "Select CustID, CustName from Customers Order by Custname".
> >> > In the "Report Parameter" dialog I choose
> >> > Available Valus:
> >> > - From query
> >> > There is no other option to specify that this query should be executed
> >> only
> >> > one time.
> >> >
> >> > I have to create reports with more then 5 cascading parameters. Some
> >> > parameters have several 1000 rows. If each selection will couse the
> >> requery
> >> > from all parameters it takes realy to long. Much more then 6 seconds
> >> (after
> >> > somebody choose a parameter) which is the "success criteria".
> >> >
> >> >
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > I don't understand your problem. Is it that you don't want to require
> >> > > a
> >> > > selection for customer? You can always add an All (do a union query).
> >> > >
> >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> >> that
> >> > > you design it that each cascading parameter has no more that a few
> >> hundred
> >> > > responses. I use cascading parameters all the time without a problem.
> >> > >
> >> > > What do you consider many rows?
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> >> > > > Cascading Parameter cousing fatal DB overhead and performance
> >> > > > problem.
> >> > > >
> >> > > > I have 2 parameters:
> >> > > > - Parameter A) Customers
> >> > > > - Parameter B) Contact Persons
> >> > > > Both have a dataset as list source.
> >> > > >
> >> > > > In the dataset of B) is a reference to A).
> >> > > >
> >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> >> selection
> >> > > in
> >> > > > A) is used as parameter. That is how it should be.
> >> > > > BUT: A) is requiried too!!! :-O
> >> > > >
> >> > > > In other reports I have more parameters and dependencies. The list
> >> sources
> >> > > > are allways datasets with many returning rows. And this behaviour
> >> results
> >> > > in
> >> > > > a big performance problem.
> >> > > >
> >> > > > Even in the sample report described here it is a performance
> >> > > > problem.
> >> > > >
> >> > > > I can't believe it, that this is the ripeness of reporting
> >> > > > services!
> >> > > Please
> >> > > > tell me somebody that this is not true and how I can solve the
> >> problem.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Something more what I recognized right now!
If I execute the report the parameters get requeried again!
After I selected the parameters I clicked on the button to create the report
and after that new requests from the parameter datasources are traced on the
server.
"Dev Main" wrote:
> Dosn't work!
> Now I made the customer parameter a cascading parameter from parameter
> language. But the customer parameter get still requeried.
>
> "Bruce L-C [MVP]" wrote:
> > Just to make sure I understand. You pick a customer and the customer sp is
> > executed. It only gets executed when you select a customer. It does not get
> > executed when you select a cascading parameter, correct?
> >
> > If this is true, is it a real problem? Does the user keep the report open
> > and keep selecting different customers?
> >
> > One possible solution is to have the customers be cascaded parameter itself.
> > Having several thousand records as the source for a listbox is not a good
> > idea. I have seen this be a problem with other products besides RS. My
> > suggestion is to somehow end up with a most a couple of hundred items in the
> > list.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > > 100% for shure!
> > >
> > > I replaced the SQL Statment with a call of a stored procedure. And the
> > > stored procedure write a log entry in a table each time the SP is called.
> > >
> > > Each time I select a entry in the 'Customer' Parameter the query for the
> > > 'customer' Parameter get executed. And (strictly correct) also the query
> > > for
> > > the cascading parameter 'Contact Person'.
> > >
> > > Just for the sake of completness: The database is on a oracle server. But
> > > this could not be related to this behaviour, becouse Reporting Services
> > > fires
> > > the refill of the parameter drop down list.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I don't think that the queries should be executed more than once, are you
> > >> sure they are? The customers query should execute only once as far as I
> > >> know
> > >> (could be wrong, could be that my lists are short enough that I don't see
> > >> the delay).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > >> > Thank you for the fast answer!
> > >> >
> > >> > My dropdownlist in the parameter "customers" must be queried only one
> > >> time.
> > >> > The list in parameter "Contact Person" must be filled each time a
> > >> > customer
> > >> is
> > >> > selected. Becouse this list shows all contact persons from the selected
> > >> > customer.
> > >> >
> > >> > In the parameters are several 1000 records displayed e.g. "Customers"
> > >> > or
> > >> > "Projects".
> > >> >
> > >> > You wrote "You can always add an All (do a union query)." What does
> > >> > this
> > >> > means exactly. I use a dataset for the parameter "customer". The
> > >> > dataset
> > >> > contains:
> > >> > "Select CustID, CustName from Customers Order by Custname".
> > >> > In the "Report Parameter" dialog I choose
> > >> > Available Valus:
> > >> > - From query
> > >> > There is no other option to specify that this query should be executed
> > >> only
> > >> > one time.
> > >> >
> > >> > I have to create reports with more then 5 cascading parameters. Some
> > >> > parameters have several 1000 rows. If each selection will couse the
> > >> requery
> > >> > from all parameters it takes realy to long. Much more then 6 seconds
> > >> (after
> > >> > somebody choose a parameter) which is the "success criteria".
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > I don't understand your problem. Is it that you don't want to require
> > >> > > a
> > >> > > selection for customer? You can always add an All (do a union query).
> > >> > >
> > >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> > >> that
> > >> > > you design it that each cascading parameter has no more that a few
> > >> hundred
> > >> > > responses. I use cascading parameters all the time without a problem.
> > >> > >
> > >> > > What do you consider many rows?
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > >> > > > Cascading Parameter cousing fatal DB overhead and performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I have 2 parameters:
> > >> > > > - Parameter A) Customers
> > >> > > > - Parameter B) Contact Persons
> > >> > > > Both have a dataset as list source.
> > >> > > >
> > >> > > > In the dataset of B) is a reference to A).
> > >> > > >
> > >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> > >> selection
> > >> > > in
> > >> > > > A) is used as parameter. That is how it should be.
> > >> > > > BUT: A) is requiried too!!! :-O
> > >> > > >
> > >> > > > In other reports I have more parameters and dependencies. The list
> > >> sources
> > >> > > > are allways datasets with many returning rows. And this behaviour
> > >> results
> > >> > > in
> > >> > > > a big performance problem.
> > >> > > >
> > >> > > > Even in the sample report described here it is a performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I can't believe it, that this is the ripeness of reporting
> > >> > > > services!
> > >> > > Please
> > >> > > > tell me somebody that this is not true and how I can solve the
> > >> problem.
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >|||You need to reconsider how you are doing this. I had thought letting them
pick a letter as you suggest here but if you have 7000 customers with the
letter A then that doesn't work. It doesn't matter what product you have,
you do not want 7000 or more in a list, it is just a bad design and the
performance will be awful. One thing you could do is have the user put in a
search criteria prior to customer, for instance parts of the customer name
and then the customer list uses like
select customer, customerid from customer where customer like '%' +
@.CustSearchCriteria + '%'
A user can not select well from a list of 7,000 (that is 100 pages of
customers).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:9B6FA691-39BC-4F99-BC5D-ABB1FD662F50@.microsoft.com...
> You understand it right!
> I choose a customer, customer and contact person get requiried. If I
choose
> a contact person nothing get requiried.
> Possibly your hint on making all parameters to cascading parameters help
me
> out of the mud. I could place a preselected parameter for language and
refer
> on it from all other parameter datasets. I'll test it.
> The requirements are focused on filtering the data. It is a CRM Database.
> I can't imagine how to keep the parameters less then several thousends.
> Customer beginning with 'A' are more then 7000 for example. Projects in
one
> country are several thousends, Sales Persons are more then 1000. It is
just a
> middle sized company. And I'm in trouble at the beginning of the project.
> Thank you very much for all your help on this!!!
> "Bruce L-C [MVP]" wrote:
> > Just to make sure I understand. You pick a customer and the customer sp
is
> > executed. It only gets executed when you select a customer. It does not
get
> > executed when you select a cascading parameter, correct?
> >
> > If this is true, is it a real problem? Does the user keep the report
open
> > and keep selecting different customers?
> >
> > One possible solution is to have the customers be cascaded parameter
itself.
> > Having several thousand records as the source for a listbox is not a
good
> > idea. I have seen this be a problem with other products besides RS. My
> > suggestion is to somehow end up with a most a couple of hundred items in
the
> > list.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > > 100% for shure!
> > >
> > > I replaced the SQL Statment with a call of a stored procedure. And the
> > > stored procedure write a log entry in a table each time the SP is
called.
> > >
> > > Each time I select a entry in the 'Customer' Parameter the query for
the
> > > 'customer' Parameter get executed. And (strictly correct) also the
query
> > > for
> > > the cascading parameter 'Contact Person'.
> > >
> > > Just for the sake of completness: The database is on a oracle server.
But
> > > this could not be related to this behaviour, becouse Reporting
Services
> > > fires
> > > the refill of the parameter drop down list.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I don't think that the queries should be executed more than once, are
you
> > >> sure they are? The customers query should execute only once as far as
I
> > >> know
> > >> (could be wrong, could be that my lists are short enough that I don't
see
> > >> the delay).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > >> > Thank you for the fast answer!
> > >> >
> > >> > My dropdownlist in the parameter "customers" must be queried only
one
> > >> time.
> > >> > The list in parameter "Contact Person" must be filled each time a
> > >> > customer
> > >> is
> > >> > selected. Becouse this list shows all contact persons from the
selected
> > >> > customer.
> > >> >
> > >> > In the parameters are several 1000 records displayed e.g.
"Customers"
> > >> > or
> > >> > "Projects".
> > >> >
> > >> > You wrote "You can always add an All (do a union query)." What does
> > >> > this
> > >> > means exactly. I use a dataset for the parameter "customer". The
> > >> > dataset
> > >> > contains:
> > >> > "Select CustID, CustName from Customers Order by Custname".
> > >> > In the "Report Parameter" dialog I choose
> > >> > Available Valus:
> > >> > - From query
> > >> > There is no other option to specify that this query should be
executed
> > >> only
> > >> > one time.
> > >> >
> > >> > I have to create reports with more then 5 cascading parameters.
Some
> > >> > parameters have several 1000 rows. If each selection will couse the
> > >> requery
> > >> > from all parameters it takes realy to long. Much more then 6
seconds
> > >> (after
> > >> > somebody choose a parameter) which is the "success criteria".
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > I don't understand your problem. Is it that you don't want to
require
> > >> > > a
> > >> > > selection for customer? You can always add an All (do a union
query).
> > >> > >
> > >> > > Not sure why you have a performance problem, you shouldn't.
Assuming
> > >> that
> > >> > > you design it that each cascading parameter has no more that a
few
> > >> hundred
> > >> > > responses. I use cascading parameters all the time without a
problem.
> > >> > >
> > >> > > What do you consider many rows?
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > >> > > > Cascading Parameter cousing fatal DB overhead and performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I have 2 parameters:
> > >> > > > - Parameter A) Customers
> > >> > > > - Parameter B) Contact Persons
> > >> > > > Both have a dataset as list source.
> > >> > > >
> > >> > > > In the dataset of B) is a reference to A).
> > >> > > >
> > >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> > >> selection
> > >> > > in
> > >> > > > A) is used as parameter. That is how it should be.
> > >> > > > BUT: A) is requiried too!!! :-O
> > >> > > >
> > >> > > > In other reports I have more parameters and dependencies. The
list
> > >> sources
> > >> > > > are allways datasets with many returning rows. And this
behaviour
> > >> results
> > >> > > in
> > >> > > > a big performance problem.
> > >> > > >
> > >> > > > Even in the sample report described here it is a performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I can't believe it, that this is the ripeness of reporting
> > >> > > > services!
> > >> > > Please
> > >> > > > tell me somebody that this is not true and how I can solve the
> > >> problem.
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >