Cascading Parameters

I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?

Thanks,

The Rook

I'd be very interested to see how you got one parameter's available values to be dependant on another's. Every way I've tried, I've had a forward dependency error.

Do you mean all available values, or the selected values?

|||I meant the "selected values." MSDN has a walkthrough that's very helpful and it's URL is: http://msdn2.microsoft.com/en-us/library/aa337426(d=printer).aspx - good luck!|||

Thanks for the link.

As for displaying the selected values on the report, that depends. If you are using just single-valued parameters, you can just drop a textbox for each parameter on the page, and set its value to the parameter. If you have a multi-valued parameter, you can try =Join(Parameters!Foo, ", ") for a comma-separated list of selected values. If you wanted something fancier, the only way I can think of is to use that same join syntax to pass the selected parameters to a query that returns the rowset that can populate, say, a table.

Cascade delete relationship slower?

I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:

> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.

> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.

> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>

Capturing Report Parameters

I am trying to capture the selected report parameters for a generated
report prior to it being sent to the server. The intent is to use
these values to set as defaults for the saved linked report. I have
no problem retrieving the list of valid values but don't know if the
values selected in the ReportView can be captured. Any Ideas?
PaulOn Dec 18, 3:04 pm, Paul <blackwell_p...@.hotmail.com> wrote:
> I am trying to capture the selected report parameters for a generated
> report prior to it being sent to the server. The intent is to use
> these values to set as defaults for the saved linked report. I have
> no problem retrieving the list of valid values but don't know if the
> values selected in the ReportView can be captured. Any Ideas?
> Paul
It's not very likely that this is possible (in terms of capturing the
report parameter on the client-side). You could try using Javascript;
but, this is a long shot. If you are just trying to pass the parameter
value selected to another report (via Jump to Report, Jump to URL,
etc) You can just set the report to jump to (or the URL of the report)
and then select the parameter to pass to it (or in the case of using a
URL, append the Parameter's value). To access the parameter's value
you would set the Parameter to an expression similar to:
=Parameters!Param1Name.Value
If using a URL, a similar expression to this should work.
="http://ServerX/reportserver?/SomeReportsDirectory/
ReportName&rs:Command=Render&Param1=" + Parameters!Param1.Value
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant