looping through report items of a rendered report

Hi all,
Situation: I have a WinForm application that uses the SQL 2005 reportviewer
component. That reportviewer uses a local report. The report definition
contains a report item (textbox) called "txtEmail" in the table header.
Would it be possible to walk (in runtime!) throught all the report items of
a rendered report so I can get the value of the report item "txtEmail"? I've
tried this code to walk through all the items in a rendered report:
ListControls(reportViewer.Controls);
private static void ListControls(Control.ControlCollection control)
{
foreach (Control e in control)
{
Console.WriteLine(" * " + e.ToString());
ListControls(e.Controls);
}
}
but it didn't list the actual report items. I expected to see some output
like " * txtEmail" but that control isn't listed.
Purpose: I have a report that lists a single email address in the table
header. I have overridden the
default export behaviour of the reportviewer so I can render the report to
PDF and mail it to a emailaddress that is available in the rendered report.
Any suggestions of how to accomplish this'
I am using VS2005 with local report attached to a WinForm reportviewer
component.
Kind regards,
PeterIn the past I passed a reference of ReportItems to my custom assembly, then
would walk through and grab the values I needed. For my purposes, turned
out there was a more efficient method with hidden textboxes. Also I had
some pages with static text and no rows. Inspecting the ReportItems
collection on these pages would throw an internal error and the report would
fail. Anyway, here's the code I used:
static string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
{
_SchoolName =rptItemSchoolNameHidden.Value.ToString();
}
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
Steve MunLeeuw
"Peter Bons" <joepie@.blakjsd.bl> wrote in message
news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Situation: I have a WinForm application that uses the SQL 2005
> reportviewer
> component. That reportviewer uses a local report. The report definition
> contains a report item (textbox) called "txtEmail" in the table header.
> Would it be possible to walk (in runtime!) throught all the report items
> of a rendered report so I can get the value of the report item "txtEmail"?
> I've tried this code to walk through all the items in a rendered report:
> ListControls(reportViewer.Controls);
> private static void ListControls(Control.ControlCollection control)
> {
> foreach (Control e in control)
> {
> Console.WriteLine(" * " + e.ToString());
> ListControls(e.Controls);
> }
> }
> but it didn't list the actual report items. I expected to see some output
> like " * txtEmail" but that control isn't listed.
> Purpose: I have a report that lists a single email address in the table
> header. I have overridden the
> default export behaviour of the reportviewer so I can render the report to
> PDF and mail it to a emailaddress that is available in the rendered
> report.
> Any suggestions of how to accomplish this'
> I am using VS2005 with local report attached to a WinForm reportviewer
> component.
> Kind regards,
> Peter
>
>|||Hi!
Just checking if you managed to solve the problem. I actually wanted to
ask if I could have an embedded code function instead of an external
assembly where in I could access the ReportItems collection for given
"textbox" and loop through all the values.
Thanks in advance for any suggestions.
Steve MunLeeuw wrote:
> In the past I passed a reference of ReportItems to my custom assembly, then
> would walk through and grab the values I needed. For my purposes, turned
> out there was a more efficient method with hidden textboxes. Also I had
> some pages with static text and no rows. Inspecting the ReportItems
> collection on these pages would throw an internal error and the report would
> fail. Anyway, here's the code I used:
> static string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
> {
> _SchoolName => rptItemSchoolNameHidden.Value.ToString();
> }
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
> Steve MunLeeuw
>
> "Peter Bons" <joepie@.blakjsd.bl> wrote in message
> news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> > Hi all,
> >
> > Situation: I have a WinForm application that uses the SQL 2005
> > reportviewer
> > component. That reportviewer uses a local report. The report definition
> > contains a report item (textbox) called "txtEmail" in the table header.
> > Would it be possible to walk (in runtime!) throught all the report items
> > of a rendered report so I can get the value of the report item "txtEmail"?
> > I've tried this code to walk through all the items in a rendered report:
> >
> > ListControls(reportViewer.Controls);
> >
> > private static void ListControls(Control.ControlCollection control)
> >
> > {
> >
> > foreach (Control e in control)
> >
> > {
> >
> > Console.WriteLine(" * " + e.ToString());
> >
> > ListControls(e.Controls);
> >
> > }
> >
> > }
> >
> > but it didn't list the actual report items. I expected to see some output
> > like " * txtEmail" but that control isn't listed.
> >
> > Purpose: I have a report that lists a single email address in the table
> > header. I have overridden the
> > default export behaviour of the reportviewer so I can render the report to
> > PDF and mail it to a emailaddress that is available in the rendered
> > report.
> >
> > Any suggestions of how to accomplish this'
> > I am using VS2005 with local report attached to a WinForm reportviewer
> > component.
> >
> > Kind regards,
> > Peter
> >
> >
> >

loop in stored procedure?

I have a situation where there is one table with parent and child nodes
i.e.
catID, ParentID
i would like to make a query to find out if an entry resides in a certain
category.
for instance, say i have
Root
--Category 1
--Child 1A
--Child 1B
--Child 1C
--Child 1Ca
if i know the CatID for the Child 1Ca node, how can i write a query that is
able to tell me that it's ultimtely a child of the root nde (Category 1)?
Keep in mind that Category 1 is not always the root node in the tree.Rob,
thanks for your help. this is exactly what i needed.
best wishes.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>

Lookup table

I have a situation where I need to use the following logic. If an item is non-stocking at LOC1, the system checks LOC4 first to see if it is stocking at that location, LOC2 second, LOC3 third, etc. If no stocking LOC is found, the records remain unmodified.

Stocking LOC (in order of precedence)
Non-Stocking LOC 1 2 3 4 5

LOC1 LOC4 LOC2 LOC3 LOC5
LOC2 LOC4 LOC1 LOC2 LOC5
LOC3 LOC5 LOC4 LOC1 LOC2
LOC4 LOC1 LOC3 LOC2 LOC5
LOC5 LOC3 LOC4 LOC1 LOC2
LOC6 LOC4 LOC2 LOC1 LOC5 LOC3
Right now we have a whole series of IF statements to work through this logic. When we add a location, a lot of code needs to be changed. Is there a way to build this into a table and be able to do the lookup more effectively, and that would make it easier to add a location?

Thanks in advance.I'd propose a more normalized table with 3 fields:
Non_stocking_loc, stocking_loc, precedence

Given a non-stocking loc, it's easy to retrieve all stocking locs for it, ordered by precedence.

Looking for table / view that will tell me if I need to reinitialize subscription

I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:

1. The article is dropped from the publication

2. the object is then changed

3. The article is added back to the publication

My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.

Thanks,

Bill

Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

Peng

|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||

Correct, create a non-necessary table that means absolutely nothing. i.e.

create table dbo.t1 (col1 int primary key, col2 int)

Add this as an article to your publication, then your DDL statements should work.

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.

Looking for real-world situation examples or tutorials on replication

Marcel,
You might want to look at:
http://www.microsoft.com/technet/pro.../sql/2000/main
tain/mergperf.mspx
http://www.microsoft.com/technet/pro.../sql/2000/main
tain/tranrepl.mspx
Hilary Cotter has a book that is coming out sometime this
month (http://www.nwsu.com/0974973602p.html)
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Hi Paul,
Thanks for the info, that book looks very promising.
regards,
Marcel
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> schreef in bericht
news:1b0d01c4c269$d7197160$a501280a@.phx.gbl...
> Marcel,
> You might want to look at:
> http://www.microsoft.com/technet/pro.../sql/2000/main
> tain/mergperf.mspx
> http://www.microsoft.com/technet/pro.../sql/2000/main
> tain/tranrepl.mspx
> Hilary Cotter has a book that is coming out sometime this
> month (http://www.nwsu.com/0974973602p.html)
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>