dynamic resizing of chart in IE window

I've got an ASPX page with four iFrames defined (soft of like a
dashboard).
Each one of the IFrames' source is set to another ASPX page that only
contains a ReportViewer control.
I am trying to make it so that when user dynamically sizes the main
(encompassing) window, it resizes everything contained, including the
Reporting Services Chart. I thought setting the width and height to
100% for the web stuff and to 100pc for the RS Chart would do the
trick, now the chart only displays the entire size of my monitor.
Any thoughts?
I'm not a great web developer, by the way. <grin>
Thanks in advance,
Mark FefermanHi Mark,
There is several way to have the dashboard..
1) instead of using iFrames in asp pages.. you can create a dasboard.rdl
page with four charts with drillthrough to detail reports.. you can avoid the
reportmanager and tools above the report by using a URL based report
execution that way it will display only the dashboard report in the IE. im
currently using this approach..
2)Also instead of using ReportViewer control did you tried using URL based
access to reports in all the 4 iFrames
let me know your thoughts and your progress so far..
Thanks
Bava
"mark.feferman@.gmail.com" wrote:
> I've got an ASPX page with four iFrames defined (soft of like a
> dashboard).
> Each one of the IFrames' source is set to another ASPX page that only
> contains a ReportViewer control.
> I am trying to make it so that when user dynamically sizes the main
> (encompassing) window, it resizes everything contained, including the
> Reporting Services Chart. I thought setting the width and height to
> 100% for the web stuff and to 100pc for the RS Chart would do the
> trick, now the chart only displays the entire size of my monitor.
> Any thoughts?
> I'm not a great web developer, by the way. <grin>
> Thanks in advance,
> Mark Feferman
>

Dynamic Query with Multi-valued parameter

I'm having problems getting a dynamic query with a multi-valued parameter. I
have a report parameter, defined as multi-value, called deployment_id. I
want to have a dynamic query built with it (because I am eventually going to
also send in a parameter for the WHERE clause).
This works:
SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name
FROM o_dpl_communication C INNER JOIN
o_dpl_recipient R ON C.recipient_id = R.recipient_id
WHERE C.deployment_id IN (@.deployment_id)
This does not:
="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name " &
"FROM o_dpl_communication C INNER JOIN " &
"o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
"WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
Anyone know how I can fix this dynamic query? Thanks!
StephanieRead the thread Dynamic Conditional report parameters. I show how to do this
... kindof. Because you need single quotes this is not easy (unless your
parameter list is integer, in which case it is quite easy). Anyway, read the
thread and see if it helps you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||try to use:
Join(Parameters!deployment_id.Value, ", ")
the .value return an array, the Join keyword convert the array into a string
with a comma as a separator
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||First of all, are you imbedding code directly in the report? that is not
good, since you have to redeploy the whole report should the code need to be
changed. Make a stored procedure instead and pass the list in as a
varchar(255).
Once inside the sproc, all you have to do is parse through the list in a
while loop and create output into a temp table that you can then filter or
output any way you want.
this is way more efficent on the sql server, and will make life easier in
the long run!
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||That worked perfectly, thanks very much!
Stephanie
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||Carl,
Thanks for the reply. I knew I could use a stored proc but I needed this to
work on multiple databases. I'm sending in a report parameter of the
database name that I want to report on and I don't want the stored proc to be
in each of the databases.
Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
Stephanie
"Carl Henthorn" wrote:
> First of all, are you imbedding code directly in the report? that is not
> good, since you have to redeploy the whole report should the code need to be
> changed. Make a stored procedure instead and pass the list in as a
> varchar(255).
> Once inside the sproc, all you have to do is parse through the list in a
> while loop and create output into a temp table that you can then filter or
> output any way you want.
> this is way more efficent on the sql server, and will make life easier in
> the long run!
> "Jeje" wrote:
> > try to use:
> > Join(Parameters!deployment_id.Value, ", ")
> >
> > the .value return an array, the Join keyword convert the array into a string
> > with a comma as a separator
> >
> >
> > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > I
> > > have a report parameter, defined as multi-value, called deployment_id. I
> > > want to have a dynamic query built with it (because I am eventually going
> > > to
> > > also send in a parameter for the WHERE clause).
> > >
> > > This works:
> > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name
> > > FROM o_dpl_communication C INNER JOIN
> > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > WHERE C.deployment_id IN (@.deployment_id)
> > >
> > > This does not:
> > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name " &
> > > "FROM o_dpl_communication C INNER JOIN " &
> > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > >
> > > Anyone know how I can fix this dynamic query? Thanks!
> > >
> > > Stephanie
> >
> >|||Thanks, Bruce. That's just what I was looking for. And I appreciated seeing
the additional information on the parameters, ie. Value(0), Count, etc.
Stephanie
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||thats cool. In that particular case, i would place the dynamic sql inside the
loop and passed in the value. I am glad you have a working solution! :-)
"Stephanie" wrote:
> Carl,
> Thanks for the reply. I knew I could use a stored proc but I needed this to
> work on multiple databases. I'm sending in a report parameter of the
> database name that I want to report on and I don't want the stored proc to be
> in each of the databases.
> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
> Stephanie
>
> "Carl Henthorn" wrote:
> > First of all, are you imbedding code directly in the report? that is not
> > good, since you have to redeploy the whole report should the code need to be
> > changed. Make a stored procedure instead and pass the list in as a
> > varchar(255).
> > Once inside the sproc, all you have to do is parse through the list in a
> > while loop and create output into a temp table that you can then filter or
> > output any way you want.
> > this is way more efficent on the sql server, and will make life easier in
> > the long run!
> >
> > "Jeje" wrote:
> >
> > > try to use:
> > > Join(Parameters!deployment_id.Value, ", ")
> > >
> > > the .value return an array, the Join keyword convert the array into a string
> > > with a comma as a separator
> > >
> > >
> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > > I
> > > > have a report parameter, defined as multi-value, called deployment_id. I
> > > > want to have a dynamic query built with it (because I am eventually going
> > > > to
> > > > also send in a parameter for the WHERE clause).
> > > >
> > > > This works:
> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name
> > > > FROM o_dpl_communication C INNER JOIN
> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > > WHERE C.deployment_id IN (@.deployment_id)
> > > >
> > > > This does not:
> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name " &
> > > > "FROM o_dpl_communication C INNER JOIN " &
> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > > >
> > > > Anyone know how I can fix this dynamic query? Thanks!
> > > >
> > > > Stephanie
> > >
> > >|||I'm pretty sure it only works because the data type of the field is integer.
If it had been string it would not have worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
> thats cool. In that particular case, i would place the dynamic sql inside
> the
> loop and passed in the value. I am glad you have a working solution! :-)
> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed this
>> to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then filter
>> > or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into a
>> > > string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am eventually
>> > > > going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >|||sure
in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:
'toto', 'tata', 'tutu'
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
> I'm pretty sure it only works because the data type of the field is
> integer. If it had been string it would not have worked.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql inside
>> the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>|||Good point.
Bruce
"Jeje" <willgart@.hotmail.com> wrote in message
news:4ED05EA4-A55B-480A-BFBB-6B863080B026@.microsoft.com...
> sure
> in case of an array of string the code is different
> something like:
> "'" & Join(Parameters!deployment_id.Value, "', '") & "'"
> single quote ' added in the join clause + single quote ' added before and
> after the Join clause produce:
> 'toto', 'tata', 'tutu'
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
>> I'm pretty sure it only works because the data type of the field is
>> integer. If it had been string it would not have worked.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql
>> inside the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code
>> > need to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list
>> > in a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life
>> > easier in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>>
>|||Bruce, cannot locate the thread, believe that info will be very helpful to me.
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||Go to Google, click on groups from the down arrow so you search groups then
search on this:
Dynamic Conditional report parameters
You will see it then.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Z-Will" <Z-Will@.discussions.microsoft.com> wrote in message
news:3EFFC67E-1254-4B64-90C7-264C3C43C36B@.microsoft.com...
> Bruce, cannot locate the thread, believe that info will be very helpful to
> me.
> "Bruce L-C [MVP]" wrote:
>> Read the thread Dynamic Conditional report parameters. I show how to do
>> this
>> ... kindof. Because you need single quotes this is not easy (unless your
>> parameter list is integer, in which case it is quite easy). Anyway, read
>> the
>> thread and see if it helps you.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > I'm having problems getting a dynamic query with a multi-valued
>> > parameter.
>> > I
>> > have a report parameter, defined as multi-value, called deployment_id.
>> > I
>> > want to have a dynamic query built with it (because I am eventually
>> > going
>> > to
>> > also send in a parameter for the WHERE clause).
>> >
>> > This works:
>> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name
>> > FROM o_dpl_communication C INNER JOIN
>> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > WHERE C.deployment_id IN (@.deployment_id)
>> >
>> > This does not:
>> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name " &
>> > "FROM o_dpl_communication C INNER JOIN " &
>> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> >
>> > Anyone know how I can fix this dynamic query? Thanks!
>> >
>> > Stephanie
>>

Dynamic Granting of Insert Permissions?

I need to restrict insert permissions on a single table to a pre defined time window each day.

One way would be to craete a new user in NT and restrict login to the time window, and then only grant insert permissions on the table to this user.

better, though, would be to achieve this at DB level. Is this possible?Why would you ever need to do this at the DB level?

You might be able to set up a trigger to remove the inserted row after it is added if the time is in a range.

Better would be to have your application logic restrict access to the insert statement between certain times.

dynamic dimension security - user and secretary

Hi

I have defined a dimension with a role with dynamic security like filter({set},CurrentMember.Name=UserName)

This works well while I have only the users.

Does someone have an Idea how this could be handled if the user has a secretary or an assistent who should see the same figures.

Thanks

Hannes

One thing that's not clear is which dimension hierarchy "CurrentMember.Name" refers to - is it like an Employee dimension? If so, maybe this same dimension could be added in a different "DelegateEmployee" role, which has a many-many relation to the original measure group. Each row of the intermediate measure group fact table (DelegateFact) would relate a Delegate to an Employee. This way, each employee could have multiple (0-N) delegates, and these delegates could be determined via the intermediate measure group, like:

Filter(({set, Generate(Exists([DelegateEmployee].[Employee].[Employee], set, "DelegateFact")},

{LinkMember([DelegateEmployee].[Employee].CurrentMember, [Employee].[Employee])}),

[Employee].[Employee].CurrentMember.Name = UserName)

|||

Thats a good idea - thanks.

Dynamic CLR Table-Valued Function

Is it possible to create a CLR Table-Valued Function in SQL 2005 in which th
e
fields can be defined at runtime? In other words, table-valued function must
define a table definition in the [SqlFunction] attribute, which makes the
definition of the columns you are returning from the function static. How ca
n
I overcome this limitation and at runtime define the columns to return?
What I want to achieve is something similar to the in-line Table-Value
Function available in SQL 2000 and SQL 2005, but now using the CLR."examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
[snip]

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
Not as far as I know.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels,
Thanks for the answer. By the way, congratulations on your work. I have your
book since it was released and it has been a great reference for me.
Fernando
"Niels Berglund" wrote:

> "examnotes" <Fernando@.discussions.microsoft.com> wrote in
> news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:
>
> [snip]
>
> Not as far as I know.
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||Hello Fernando,

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
Yes and I don't think so.
I don't think so: The typical TVF pattern isn't going to accomodate that
-- Understand that SQL Server really expects the return value of any CLR
function to come form System.Data.SqlTypes. At least that's what I've figure
d
out so far. There's not SqlTable type in that namespace, so I don't see how
you're doing to do this any conventional way.
My work-around would be have the TVF return a single column of XML.

> In other words,
> table-valued function must define a table definition in the
> [SqlFunction] attribute, which makes the definition of the columns you
> are returning from the function static.
This is true if and only if you are using Visual Studio to deploy the functi
on
because Visual Studio consumes that attribute to write the CREATE FUNCTION
statement. Fire up profiler and what the deployment process, you'll see it.

> How can I overcome this
> limitation and at runtime define the columns to return?
Write a code generator that generates the TVF, because -- short of that --
you're not going to get around the fact that a T-SQL TVF requires a static
table definition. Or return XML.

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
I don't see any easy ways to do that, at least today.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for the great ideas to workaround this issue. The code generator
probably will work better for my case as I intend to join results from
multiple Table-Valued Functions.
Thanks!,
Fernando
"Kent Tegels" wrote:

> Hello Fernando,
>
> Yes and I don't think so.
> I don't think so: The typical TVF pattern isn't going to accomodate that
> -- Understand that SQL Server really expects the return value of any CLR
> function to come form System.Data.SqlTypes. At least that's what I've figu
red
> out so far. There's not SqlTable type in that namespace, so I don't see ho
w
> you're doing to do this any conventional way.
> My work-around would be have the TVF return a single column of XML.
>
> This is true if and only if you are using Visual Studio to deploy the func
tion
> because Visual Studio consumes that attribute to write the CREATE FUNCTION
> statement. Fire up profiler and what the deployment process, you'll see it
.
>
> Write a code generator that generates the TVF, because -- short of that --
> you're not going to get around the fact that a T-SQL TVF requires a static
> table definition. Or return XML.
>
> I don't see any easy ways to do that, at least today.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Kent Tegels <ktegels@.develop.com> wrote in
news:b87ad74f0cb8c7ba687dccef80@.news.microsoft.com:

> Hello Fernando,
> This is true if and only if you are using Visual Studio to deploy the
> function because Visual Studio consumes that attribute to write the
> CREATE FUNCTION statement. Fire up profiler and what the deployment
> process, you'll see it.
[snip]

> Write a code generator that generates the TVF, because -- short of
> that -- you're not going to get around the fact that a T-SQL TVF
> requires a static table definition. Or return XML.
The issue is not so much the table definition in itself, but the
FillRowMethod method, which has to have the same output param definition as
the table definition.
Niels|||"examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:B7466A77-02A2-42DC-8583-8AC96C49D670@.microsoft.com:

> Niels,
> Thanks for the answer. By the way, congratulations on your work. I
> have your book since it was released and it has been a great reference
> for me.
>
Thanks so much!
Niels|||Fernando (Fernando@.discussions.microsoft.com) writes:
> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime? In other words, table-valued
> function must define a table definition in the [SqlFunction] attribute,
> which makes the definition of the columns you are returning from the
> function static. How can I overcome this limitation and at runtime
> define the columns to return?
You really should not be able to. A table-valued function is something
that can be used in a query:
SELECT a.col1, a.col2, b.col3, ...
FROM tbl t
JOIN tblfunction (@.par1, @.par2) f ON t.keycol = f.somecol
A query is static, and all parts in it must be known when the query
is compiled. SQL Server has deferred name resolution, so you might be
able to create the procedure if an object is missing. But if the query
does not compile, when it's time to execute it you get an error.
After all, with your dynamic TVF, what is going to happen if it does
not return somecol in its result set?

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
You cannot do inline-functions with the CLR. CLR TVF are akin to
multi-statement functions in T-SQL. An inline function is in fact
not a function at all; it is a macro which is expanded into the query,
and the optimizer builds the query plan for the expanded query. It
goes without saying that is not going to happen with a CLR function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx