Regarding Custom Code using table or list control(data set)

I have the report like this

list1

{

list2

{

table1

table header

table fields

table footer

}

}

now i want to calculate the sum of one field in table when it contains particular value using CUSTOM CODE ONLY

and i want to display it in table footer. please send me answer to this id as soon as possible.

Thanks

Sateesh Maduri

Maybe I'm missing something, but couldn't you just use a conditional aggregation expression like the following in the table footer:

=Sum(iif(Fields!A.Value = "abc", CInt(Fields!B.Value), 0))

-- Robert

|||

Hello Robert

Actually I already know that what u have given using sum().But I need Custom Code for that ,I mean if I have a dataset which fills the data in the table control How should I write Custom Code to calculate sum of one field when it contains particular value.

Thanks

sql

Refresh problem in Crystal Viewer control

Dear Guru's
I am using Crystal viewer control in my VC++ application project. Here i am facing report refresh problem. If i call viewer control Refresh method directly the application is crashing. I am calling that inside OnPaint( ) .

The problem is the report data is not displaying first time the user has to click refresh button.

Please help me how to use "Refresh " method.

Thanks
arunNot sure if this will help, but make sure 'Save Data with Report' is NOT checked. Otherwise, have you tried calling Refresh as soon as the report is displayed (instead of waiting for the user to do it)?|||I am trying to refresh the report through code only using Viewer control 'Refresh' method.
If report displayed with blank then how user came to know that he has to refresh the report by clicking refresh button.

Reflective linking

Greetings.
I want to create a tree control which will list all the employees in an
employee table in the correct hierarchy like
<company>
managers
sub managers
employees
For that, how can I setup reflective linking (linking ot the same table) in
SQL server and how can I populate the tree control from it ?
Thanks!
You may have a look here:
(Quelle:
http://msdn.microsoft.com/library/de...qd_14_5yk3.asp)
But the main part to populate the tree should be implemented in your
frontend apllication. The best thing would be to set up some recursive
function which alway try to first retrieve the "manager" and the search for
the next level, and so on.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"news.microsoft.com" <v-hshah@.microsoft.com> schrieb im Newsbeitrag
news:%23uukSelZFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Greetings.
> I want to create a tree control which will list all the employees in an
> employee table in the correct hierarchy like
> <company>
> managers
> sub managers
> employees
>
> For that, how can I setup reflective linking (linking ot the same table)
> in SQL server and how can I populate the tree control from it ?
> Thanks!
>
|||Get a copy of TREES & HIERARCHIES IN SQL for details, but look up the
"nested set model" on Google. There is no need for self-referencing
tables, recursion or procedural code.
sql

Reflective linking

Greetings.
I want to create a tree control which will list all the employees in an
employee table in the correct hierarchy like
<company>
managers
sub managers
employees
For that, how can I setup reflective linking (linking ot the same table) in
SQL server and how can I populate the tree control from it ?
Thanks!You may have a look here:
(Quelle:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp)
But the main part to populate the tree should be implemented in your
frontend apllication. The best thing would be to set up some recursive
function which alway try to first retrieve the "manager" and the search for
the next level, and so on.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"news.microsoft.com" <v-hshah@.microsoft.com> schrieb im Newsbeitrag
news:%23uukSelZFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Greetings.
> I want to create a tree control which will list all the employees in an
> employee table in the correct hierarchy like
> <company>
> managers
> sub managers
> employees
>
> For that, how can I setup reflective linking (linking ot the same table)
> in SQL server and how can I populate the tree control from it ?
> Thanks!
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but look up the
"nested set model" on Google. There is no need for self-referencing
tables, recursion or procedural code.

Reflective linking

Greetings.
I want to create a tree control which will list all the employees in an
employee table in the correct hierarchy like
<company>
managers
sub managers
employees
For that, how can I setup reflective linking (linking ot the same table) in
SQL server and how can I populate the tree control from it ?
Thanks!You may have a look here:
(Quelle:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp)
But the main part to populate the tree should be implemented in your
frontend apllication. The best thing would be to set up some recursive
function which alway try to first retrieve the "manager" and the search for
the next level, and so on.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"news.microsoft.com" <v-hshah@.microsoft.com> schrieb im Newsbeitrag
news:%23uukSelZFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Greetings.
> I want to create a tree control which will list all the employees in an
> employee table in the correct hierarchy like
> <company>
> managers
> sub managers
> employees
>
> For that, how can I setup reflective linking (linking ot the same table)
> in SQL server and how can I populate the tree control from it ?
> Thanks!
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but look up the
"nested set model" on Google. There is no need for self-referencing
tables, recursion or procedural code.

Reflective linking

Greetings.
I want to create a tree control which will list all the employees in an
employee table in the correct hierarchy like
<company>
managers
sub managers
employees
For that, how can I setup reflective linking (linking ot the same table) in
SQL server and how can I populate the tree control from it ?
Thanks!You may have a look here:
(Quelle:
_5yk3.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_5yk3.asp)
But the main part to populate the tree should be implemented in your
frontend apllication. The best thing would be to set up some recursive
function which alway try to first retrieve the "manager" and the search for
the next level, and so on.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"news.microsoft.com" <v-hshah@.microsoft.com> schrieb im Newsbeitrag
news:%23uukSelZFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Greetings.
> I want to create a tree control which will list all the employees in an
> employee table in the correct hierarchy like
> <company>
> managers
> sub managers
> employees
>
> For that, how can I setup reflective linking (linking ot the same table)
> in SQL server and how can I populate the tree control from it ?
> Thanks!
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but look up the
"nested set model" on Google. There is no need for self-referencing
tables, recursion or procedural code.

Referencing the temporary Dataset in Lookup control SQL

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
ChestonI'm not clear on what you are trying to do.

You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.

Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?

So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null

Then continue on with the dataset with more lookups/transformations.

Does this help?

Cheston|||Use a derived column transformation to set that column to 0 if it is null.

Referencing a Control's Value Property

I'm trying to reference a image control value property. I want to show
the contents of the value property in a textbox control. I thought I
saw this before but cannot seem to find it again.
For example, I have an image control called image1. Which has a value
of: C:\images\xxxx.jpg
In a textbox I want to show "C:\images\xxxx.jpg"
I'm trying something like: textbox.value=image1.value
So when I preview the report I get an image, and a textbox that shows
the value used for the text box.
Any ideas?
Thanks,
rwiethornCurrently, the only Value property exposed to expressions is Textbox.Value
(in fact, the only report items visible to expressions at all are
textboxes).
But... You could achieve the desired result by making the value of the image
control an expression that refers to the contents of the textbox:
=ReportItems!Textbox1.Value
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"rwiethorn" <rwiethorn2002@.yahoo.com> wrote in message
news:553a0349.0408230522.2226ad4d@.posting.google.com...
> I'm trying to reference a image control value property. I want to show
> the contents of the value property in a textbox control. I thought I
> saw this before but cannot seem to find it again.
> For example, I have an image control called image1. Which has a value
> of: C:\images\xxxx.jpg
> In a textbox I want to show "C:\images\xxxx.jpg"
> I'm trying something like: textbox.value=image1.value
> So when I preview the report I get an image, and a textbox that shows
> the value used for the text box.
> Any ideas?
> Thanks,
> rwiethorn

reduce the table height when NORows in table having a footer control

hi,
i am using two header ,three grouping level and in a table and there is
a one Report Footer in report.
when there is no data present then the report hide all groups and
Detail rows but the Report Footer still remains at same position. i
want that the Footer should also aligned to top when there is no data
,The space between the Header and Footer should zero in this case.
which property i need to set to do this. or what i shld do'
thx.Rajesh,
How far down is the footer?
I see there are 2 solutions in this case:
1) In the properties of a cell there is CanGrow and CanShrink
properties.(This is under Layout). Dont forget, your going to change
this for the DETAIL and grouping rows... not the footer one.
2) Also you can set all the middle rows to become Hidden. This can be
be done using an expressions that should basically check if data is
present ( check on some other row), and then the Visibiliy --> hidden
property should be set to true for all those detail/grouping rows.
I hope this helps.
regards,
Stas K.|||hi Scoredon,
I tried ur solution but its not working ,still my probelm is not
resolved.
my report format is like
TableHeader
TableHeader
Grouping I Fund
Grouping II Issuer
Grouping III Security
<Display Of Information>
Security/Facility Wise Total
Issuer Wise Total
Fund Wise Total
Grand Total
Page Footer
when there is no data then i get the structure like
TableHeader
TableHeader
Grand Total
<space>
<space>
<space>
<space>
<space>
<space>
<space>
Page Footer
I want the structure like this
TableHeader
TableHeader
Grand Total
Page Footer
I hope now you have understood my problem completely.plz try to resolve
this.
Thx
Rajesh
Sorcerdon wrote:
> Rajesh,
> How far down is the footer?
> I see there are 2 solutions in this case:
> 1) In the properties of a cell there is CanGrow and CanShrink
> properties.(This is under Layout). Dont forget, your going to change
> this for the DETAIL and grouping rows... not the footer one.
> 2) Also you can set all the middle rows to become Hidden. This can be
> be done using an expressions that should basically check if data is
> present ( check on some other row), and then the Visibiliy --> hidden
> property should be set to true for all those detail/grouping rows.
> I hope this helps.
> regards,
> Stas K.|||Rajesh,
It looks to me like you want the Page Footer to actually be a Table
Footer. Can you create an additional Table Footer row and insert the
values you have in your Page Footer?
The only problem with this is the footer will always appear immediately
below your Grand Total row instead of at the bottom of the page.
To offset this scenario, you could set the visibility properties for
the Table Footer to hidden and the Page Footer to Visible when there is
data and vice-a-versa when there is no data.
Good luck
Rajesh.jain25@.gmail.com wrote:
> hi Scoredon,
> I tried ur solution but its not working ,still my probelm is not
> resolved.
> my report format is like
> TableHeader
> TableHeader
> Grouping I Fund
> Grouping II Issuer
> Grouping III Security
> <Display Of Information>
> Security/Facility Wise Total
> Issuer Wise Total
> Fund Wise Total
> Grand Total
> Page Footer
> when there is no data then i get the structure like
> TableHeader
> TableHeader
> Grand Total
> <space>
> <space>
> <space>
> <space>
> <space>
> <space>
> <space>
> Page Footer
> I want the structure like this
> TableHeader
> TableHeader
> Grand Total
> Page Footer
> I hope now you have understood my problem completely.plz try to resolve
> this.
> Thx
> Rajesh
> Sorcerdon wrote:
> > Rajesh,
> >
> > How far down is the footer?
> >
> > I see there are 2 solutions in this case:
> > 1) In the properties of a cell there is CanGrow and CanShrink
> > properties.(This is under Layout). Dont forget, your going to change
> > this for the DETAIL and grouping rows... not the footer one.
> > 2) Also you can set all the middle rows to become Hidden. This can be
> > be done using an expressions that should basically check if data is
> > present ( check on some other row), and then the Visibiliy --> hidden
> > property should be set to true for all those detail/grouping rows.
> >
> > I hope this helps.
> > regards,
> >
> > Stas K.

reduce databse size

Hi, my database size has grown out of control and I need help with the
following issues. (I am very new to databases)

I am storing financial tick data in one of the tables and after two months
the database has grown to 30GB. I do not need a permanent record of this
tick data after it has been processed and tried to remove all rows from this
table (delete from Tickdata), however sql does not take kindly to removing
millions of rows and the operation seams to time out. The only solution I
could come up with was to delete the table.

Secondly, after managing to clear out these tables I have noticed that the
database size is still 30GB, despite 29GB being available. Is there any way
to reduce the size of the database from 30GB. I tried the shrink database
option but it does not do anything. Any ideas?

Thanks.Have you tried a TRUNCATE Tickdata?
Look at dbcc shrinkfile in the Books online in order to make the db smaller.

"Fred" <Fred@.hotmail.com> wrote in message
news:4178493c$1@.duster.adelaide.on.net...
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from
this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any
way
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
> Thanks.|||"Fred" <Fred@.hotmail.com> wrote in message news:<4178493c$1@.duster.adelaide.on.net>...
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any way
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
> Thanks.

Here's what I use:

backup log SQLData with TRUNCATE_ONLY
dbcc shrinkfile (2,20,TRUNCATEONLY)

The first paramater in shrinkfile is the file number. To get a list
of filenumbers, issue the following query:

select * from sysfiles

The 2nd parameter is the number of pages. If you specify a number
less than the database uses, it will use the number of pages that it
requires.

"SQLData" is the name of the database. Replace that with your
database.

I do these to help manage log size. You may need to toy around with
these to get exactly what you're looking for.|||On Fri, 22 Oct 2004, Fred wrote:

> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.

The reason it times out is most likely the logging of each delete. Most
operations in SQL Server are logged (I'm not qualified to give a complete
explanation of logging, but in short as it pertains to this problem it has
to do with being able to to restore the database to a previous point and
also it is much slower than a logged operation, plus log size can grow
very large depending on your setup) The truncate table command (see BOL)
is much like a delete from without any where criteria, except that in most
cases this is a non logged operation and will thus complete much much
faster (this non logged behavior however depends on some database options
- the recovery mode in SQL 2000, some other option I can't remember in SQL
7.0. See BOL). Depending on your backup solution non logged operations may
or may not be appropriate.

Alternately, you can only delete a smaller subset of rows at once and
iterate until everything you want deleted has been deleted, which is
probably less efficient overall but may help with any timeout / GUI
responsivness issues you are having. I took this approach once for a quick
and dirty solution to a similar problem.

Dave|||Metal Dave (metal@.spam.spam) writes:
> The truncate table command (see BOL) is much like a delete from without
> any where criteria, except that in most cases this is a non logged
> operation and will thus complete much much faster

TRUNCATE TABLE is logged, however minimally. When you delete rows, each
row is logged. With TRUNCATE TABLE, only the page deallocation is logged.

Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
keys.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Oct 2004, Erland Sommarskog wrote:

> Metal Dave (metal@.spam.spam) writes:
> > The truncate table command (see BOL) is much like a delete from without
> > any where criteria, except that in most cases this is a non logged
> > operation and will thus complete much much faster
> TRUNCATE TABLE is logged, however minimally. When you delete rows, each
> row is logged. With TRUNCATE TABLE, only the page deallocation is logged.
> Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
> keys.

Oops, sorry for oversimplification. I was just trying to stick with "ask a
question, answer a question" much like the "take a penny, leave a penny"
at your local 7-11. Thanks for clarifying. The OP may find it useful
anyway though, as it's still much faster and advanced restore didn't not
sound like a priority to him/her.

Dave