Hidden Subreport Executing Queries

I'm currently developing a report in which the customer has requested a
drill-down type of interface that starts at a regional level to a
district level to a territory level and finally to the individual
customers within the territory. The drill-down interface was easy to
create by using Groups in conjunction with the ToggleItem and
Visibility.Hidden properties at the different Grouping levels.
The problem I have run into is that there are around 1 Million
customers across the country and the report was attempting to retrieve
all of the records at all levels at once. This turns out to be
unworkable from a performance standpoint.
To try to work around this I proposed using drill-through at the
territory level to open a new report with that territories customer
list. The customer was unwilling to accept that approach since they
wanted to keep the flexibility of navigating through the tree without
the disruption of opening a separate report.
Next I tried to work around the problem by implementing a subreport at
the customer level thinking that the subreport would only execute once
the user drilled down into the territory level. Following is a general
overview of this solution:
+ North East
- South East
- Georgia
- Atlanta
- subreport displays here with territory customer list
+ Savannah
+ Athens
+ Florida
+ Kentucky
- Midwest
+ Kansas
+ Iowa
+ Minnesota
+ West
By using SQL Profiler I found that the report was actually executing
the query for each individual territory's corresponding subreport
even though those levels were hidden. This was essentially the same
scenario that I had with my initial attempt.
To try to work around this I created a parameter within the subreport
called "show_subreport" and if that parameter is set to 1 the dataset
will return the customer list and if it is set to 0 the dataset will
not return any rows. By manually setting the parameter to 0 through
the parameter mapping in the parent report this worked well. However,
I have not been able to figure out how to dynamically change that
parameter by using an expression. I have been trying to set the
parameter expression to something like:
= IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
However, I have not been able to get to that property or any other
property which would be relevant to the "show_subreport" parameter.
This seems like it would be a very common type of report request so I
assume there is a way to do this. Any help would be appreciated.I've got the same problem.
--
/Mattias
"fred_mumble@.yahoo.com" wrote:
> I'm currently developing a report in which the customer has requested a
> drill-down type of interface that starts at a regional level to a
> district level to a territory level and finally to the individual
> customers within the territory. The drill-down interface was easy to
> create by using Groups in conjunction with the ToggleItem and
> Visibility.Hidden properties at the different Grouping levels.
> The problem I have run into is that there are around 1 Million
> customers across the country and the report was attempting to retrieve
> all of the records at all levels at once. This turns out to be
> unworkable from a performance standpoint.
> To try to work around this I proposed using drill-through at the
> territory level to open a new report with that territories customer
> list. The customer was unwilling to accept that approach since they
> wanted to keep the flexibility of navigating through the tree without
> the disruption of opening a separate report.
> Next I tried to work around the problem by implementing a subreport at
> the customer level thinking that the subreport would only execute once
> the user drilled down into the territory level. Following is a general
> overview of this solution:
> + North East
> - South East
> - Georgia
> - Atlanta
> - subreport displays here with territory customer list
> + Savannah
> + Athens
> + Florida
> + Kentucky
> - Midwest
> + Kansas
> + Iowa
> + Minnesota
> + West
> By using SQL Profiler I found that the report was actually executing
> the query for each individual territory's corresponding subreport
> even though those levels were hidden. This was essentially the same
> scenario that I had with my initial attempt.
> To try to work around this I created a parameter within the subreport
> called "show_subreport" and if that parameter is set to 1 the dataset
> will return the customer list and if it is set to 0 the dataset will
> not return any rows. By manually setting the parameter to 0 through
> the parameter mapping in the parent report this worked well. However,
> I have not been able to figure out how to dynamically change that
> parameter by using an expression. I have been trying to set the
> parameter expression to something like:
> = IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
> However, I have not been able to get to that property or any other
> property which would be relevant to the "show_subreport" parameter.
> This seems like it would be a very common type of report request so I
> assume there is a way to do this. Any help would be appreciated.
>sql

Help_Creating Archive Table

I have a table called customers that store information about the particular customer. I would like to have a table called Archive so that when I delete a Customer that have not been active for a specific time then the deleted information will be automatically be inserted into the Archive table. Do I need to create the archive table with the same numbers of columns as exactly as the customer table?

I need some basic idea about how this should be implemented.

Any input will be appreciated.
Thanks in advanceDo you use foreign keys? If so - you have to move customer activity history to archive table too. Structure of table could be the same, unless you want to know when data were moved to archive or any other additional information. And do not use removed ids again....|||Thank you snail for the reply
I have just created the Archive table. The primary key of the Customer Table is a foreign key in the archive table. I have added three more columns in the Archive Table namely Comments,ArchiveDate and EmployeeName, these three columns are not in the customer Table.Can this be possible? as this colums are not in the Customer Table. These columns are important because when a customer is deleted I want to be able to enter the reason why a specific customer was deleted. What did u mean by ids, are you talking about Identity Seed or?
Again I have trouble formulating the query that will insert the deleted columns into the Archive Table. Need additional Info
Thanks for the help so far|||well, if you got two identical tables you could write a trigger that fires on a deleted record from the customers table you can use
INSETED OF DELETE trigger

or if you got two different tables you can write a store procedure for deleting so it can insert the required record into the archive table with
the supplied fields first you have to insert to the achive table
using
insert into achive(field1,field2)
select field1,field2 from customers where customerid = @.custID

then update the record in the achive using
update achive set field6 = @.employeeName ...
where customerid = @.custID

to it then delete the record from the customers table.|||Thank u elamor for the reply. I am going to use Store Procedure, the idea is clear, I think with these info I know where to start, definately I might be posting my query here for deburg if ?

Help: ADO with Unix and Oracle

We have done a complex software with VB using SQL Server through ADO. Now
our customer is planning to switch from SQL Server to Unix/Aix Oracle 8 DB.

I'm a newbie both in Oracle and Unix and wonder how this will affect our
software. Would changing the connection string be enough (I quess not).

Any help would be appreciated !

Jyrki Hamalainen
Laukaa, Finland
jyrki . hamalainen @. ids . fiOn Thu, 04 Sep 2003 08:23:14 GMT in comp.databases.ms-sqlserver,
"Jyrki H" <jyrki@.nowhere.com> wrote:

>We have done a complex software with VB using SQL Server through ADO. Now
>our customer is planning to switch from SQL Server to Unix/Aix Oracle 8 DB.
>I'm a newbie both in Oracle and Unix and wonder how this will affect our
>software.

It will probably affect your income more, think of all the dosh you
can charge for that conversion :-)

>Would changing the connection string be enough (I quess not).

I'm no oracle expert but at a guess and from experience of people in
CDMA connecting Access to Oracle the ODBC/OLEDB driver you get in
Windows won't do you any favors, IIRC Oracle has something called
SQL.NET and that's probably your best bet for the connection.

The rest will depend on how you wrote your app, if a lot of stored
procedures and triggers, etc then you're going to have fun making them
all work, I'm not sure if there's any conversion tools for converting
a database over, standard stuff should be able to be scripted (tables
and columns) but when you get to the nitty gritty, UDFs, etc. then you
may find things that are not supported or that Oracle will implement
some functionality you need in a different way.

Again I stress that I don't know Oracle and that a lot more may be
instantly convertable than I indicated... or a lot less. You have to
investigate this and come up with some time frame about how long it
will take and how much it will cost before your customer commits as
although there's a smiley on my first paragraph, it may be closer to
to the mark than you think.

Sorry I can't give you any specifics on Oracle but it's something to
start thinking about on the planning side.

I'd suggest if you haven't already done so, post a similar question in
an Oracle news group, normally when talking about conversions or
interactions between products I would recommend cross-posting to both
groups but in this case I wouldn't, IME it would likely start a flame
war, some of those Oracle guys are little, how shall I put it...
esoteric. :-)

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)