Hi all
I've implemented our custom data processing extension for Reporting Services
and the 'Refresh Fields' button available in the Generic Query Designer is
very useful to us to make sure the fields in the RDL files are updated.
Unfortunately, I can't seem to find a way to programmatically invoke this
'Refresh Fields' command. I want to write my own utility, but I'm not an
expert in .NET nor XML. Any pointers or guidance on how to achieve my goal
is greatly appreciated!
Thanks!!Hi,
I'll see if I can find the answer. I'll update you once I have more
information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you and your
"William Wang[MSFT]" wrote:
> Hi,
> I'll see if I can find the answer. I'll update you once I have more
> information.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi,
You may want to implement the refresh logic externally by calling
IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
for more information:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
ae765089b645dc3a
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the info. It's helpful to know what exactly happens for 'Refresh
Fields' behind the scene.
Unfortunately my main goal is to update the given RDL file(s). Everytime I
clicked the 'Refresh Fields' button the corresponding RDL file gets updated,
which is what I'm looking for.
In a Reporting Project, I want to be able to programmatically refresh all
its RDL files. Maybe I should ask how to get access to an IDbCommand object
for each report?
Your help is appreciated!! Thanks!
Jenny
"William Wang[MSFT]" wrote:
> Hi,
> You may want to implement the refresh logic externally by calling
> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
> for more information:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
> ae765089b645dc3a
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||There's not a direct way so far.
<yinjennytam@.newsgroup.nospam> wrote in message
news:28AD35DA-113D-43C3-B95A-50C910D01CD8@.microsoft.com...
> Thanks for the info. It's helpful to know what exactly happens for
> 'Refresh
> Fields' behind the scene.
> Unfortunately my main goal is to update the given RDL file(s). Everytime
> I
> clicked the 'Refresh Fields' button the corresponding RDL file gets
> updated,
> which is what I'm looking for.
> In a Reporting Project, I want to be able to programmatically refresh all
> its RDL files. Maybe I should ask how to get access to an IDbCommand
> object
> for each report?
> Your help is appreciated!! Thanks!
> Jenny
>
> "William Wang[MSFT]" wrote:
>> Hi,
>> You may want to implement the refresh logic externally by calling
>> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this
>> thread
>> for more information:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
>> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
>> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
>> ae765089b645dc3a
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
Showing posts with label available. Show all posts
Showing posts with label available. Show all posts
Friday, March 30, 2012
Wednesday, March 7, 2012
intra-query parallelism
We have:
SQL7-sp4/w2k
4 Processors
use all available processors
Minimum query plan threshold for considering queries for parallel execution Set to 4
And the query next:
---
select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
from (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo in ('n10429','n10430','n10431')
group by p.id_distribuidora, p.id_cliente
)a inner join (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
group by p.id_distribuidora, p.id_cliente
) b
on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
order by b.BeautiKit desc, a.Monedas desc
---
the Minimum query plan threshold for considering queries for parallel execution we set to 1,2,3,4,5
and have the error: Intra-query parallelism caused your server command (process ID #19) to deadlock
who need set?
p.d. the same query over SQL7/NT and one processor no have problem.
thank, for your helpTry adding "OPTION (MAXDOP 1)" to the query to force the optimizer to NOT
use parallel execution for that particular query. If that works, then wait
for MS to fix it.
Or you can rewrite the query to get around the problem. It looks like the
two derived tables are the same query using different catalog values. You
should be able to create one derived table that generates two sums - you
would also need to determine if data exists for both catalog value sets in
order to get the same result set (data is returned only if matching rows
exists in both derived tables).
"FcoResendiz" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB80CFD-F586-42B3-BCCA-196FC2F2BA9A@.microsoft.com...
> We have:
> SQL7-sp4/w2k
> 4 Processors
> use all available processors
> Minimum query plan threshold for considering queries for parallel
execution Set to 4
> And the query next:
> ---
> select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
> from (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo in
('n10429','n10430','n10431')
> group by p.id_distribuidora, p.id_cliente
> )a inner join (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
> group by p.id_distribuidora, p.id_cliente
> ) b
> on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
> order by b.BeautiKit desc, a.Monedas desc
> ---
> the Minimum query plan threshold for considering queries for parallel
execution we set to 1,2,3,4,5
> and have the error: Intra-query parallelism caused your server command
(process ID #19) to deadlock
> who need set?
> p.d. the same query over SQL7/NT and one processor no have problem.
> thank, for your help
>
SQL7-sp4/w2k
4 Processors
use all available processors
Minimum query plan threshold for considering queries for parallel execution Set to 4
And the query next:
---
select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
from (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo in ('n10429','n10430','n10431')
group by p.id_distribuidora, p.id_cliente
)a inner join (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
group by p.id_distribuidora, p.id_cliente
) b
on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
order by b.BeautiKit desc, a.Monedas desc
---
the Minimum query plan threshold for considering queries for parallel execution we set to 1,2,3,4,5
and have the error: Intra-query parallelism caused your server command (process ID #19) to deadlock
who need set?
p.d. the same query over SQL7/NT and one processor no have problem.
thank, for your helpTry adding "OPTION (MAXDOP 1)" to the query to force the optimizer to NOT
use parallel execution for that particular query. If that works, then wait
for MS to fix it.
Or you can rewrite the query to get around the problem. It looks like the
two derived tables are the same query using different catalog values. You
should be able to create one derived table that generates two sums - you
would also need to determine if data exists for both catalog value sets in
order to get the same result set (data is returned only if matching rows
exists in both derived tables).
"FcoResendiz" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB80CFD-F586-42B3-BCCA-196FC2F2BA9A@.microsoft.com...
> We have:
> SQL7-sp4/w2k
> 4 Processors
> use all available processors
> Minimum query plan threshold for considering queries for parallel
execution Set to 4
> And the query next:
> ---
> select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
> from (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo in
('n10429','n10430','n10431')
> group by p.id_distribuidora, p.id_cliente
> )a inner join (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
> group by p.id_distribuidora, p.id_cliente
> ) b
> on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
> order by b.BeautiKit desc, a.Monedas desc
> ---
> the Minimum query plan threshold for considering queries for parallel
execution we set to 1,2,3,4,5
> and have the error: Intra-query parallelism caused your server command
(process ID #19) to deadlock
> who need set?
> p.d. the same query over SQL7/NT and one processor no have problem.
> thank, for your help
>
Sunday, February 19, 2012
interrelated report parameters
Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>
Subscribe to:
Posts (Atom)