crystal reports row count

bebe lush posted at 20-Sep-06 05:58
How can i add a column in the report to count the rows, and to auto increment.
Like:
1. | line 1
2. | line 2
3. |
to show me the current number.Friend,

Do one thing create one Formula Field named @.SlNo, and insert into detail section. Formula is given below:

//------------------

numberVar numSlno = 0;

IF PREVIOUSISNULL(<Name of Any Field>) THEN
numSlno := 1
ELSE
numSlno := numSlno + 1

//------------------|||why use formula
just include record number itself :wave:|||why use formula
just include record number itself :wave:
Thats correct way|||I tried this one but I got error at the part (<Name of Any Field>). I am a newbie, could you please elaborate on it? Thanks a lot!

Friend,

Do one thing create one Formula Field named @.SlNo, and insert into detail section. Formula is given below:

//------------------

numberVar numSlno = 0;

IF PREVIOUSISNULL(<Name of Any Field>) THEN
numSlno := 1
ELSE
numSlno := numSlno + 1

//------------------|||Well, that code has a bug anyway.
As said before, in the Field Explorer, just use the special field Record Number.|||as one of the repliers said,
the best way to me is to add a special field recordNumber.
OR you can also use a running total field.
In fields Explorer, Create new Running Total Field, Choose any of the fields (Preferably numeric) Summerize.
and select Count .
Allow it to execute for Each Record
One of these things should solve the problem.
The Last Solution i would suggest will be to use shared variables.|||as one of the repliers said,
the best way to me is to add a special field recordNumber.
OR you can also use a running total field.
In fields Explorer, Create new Running Total Field, Choose any of the fields (Preferably numeric) Summerize.
and select Count .
Allow it to execute for Each Record
One of these things should solve the problem.
The Last Solution i would suggest will be to use shared variables.

Why to make things complicated?

Just use Record Number field.

crystal processing LARGE number of rows

i have a crystal report, oracle is pumping it out a million rows of data. regularly i would expect this report to have 50k-5mil rows of data sent to it from oracle. the 1 million rows oracle starts returning in sql*plus within half an hour from my stored procedure. i wonder if turning the stored proc into a pipelined one would help; answer i'm getting now is not really.

we have an online view of the report and an export to excel view. the online report is through the web with the crystal viewer. last run this report took 12 hours to complete. not sure how large it is (MB) - there are basically 3 types of pages created off the data; i believe these are called subreports. i am not a crystal developer, i did developer the proc that returns the million rows though.

the way the report works, is you see a list of top level entities (repeated multiple times in the data) - clicking on one of them takes you to a 2nd level entity (repeated) and clicking on one of these takes you to a 3rd page where all the (unique) products reside in that entity.

current dataset:
A z 1234
A z 2222
A y 3333
B m 5555

cr page 1:

A
B

user clicks A; page 2:

z
y

user clicks z; page 3:

1234
2222

of the 12 hours (only ran online/crystal viewer version) i expect the data to be returned in... 1 hour? not sure. majority of the time appears to be on crystal's part for processing though. does crystal start processing as soon as it tastes data, or does it need to wait for the whole result set? can i break up the pages into different procs (3) and still maintain links between them? please help, interesting in any and all suggestions. thanks!Cant you use three seperate reports by filtering the relevent data so that each report will have partial data?

Cross tab stored procedure

Hi
I have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ie
DateTime Variable Value.
T1 V1 xxxx
T1 V2 xxxx
T1 V3 xxxx
T2 V1 yyyy
T2 V2 yyyy
T2 V3 yyyy
In a SQLserver stored procedure I would like to insert this data into a temp table like the following. I don't need summaries just need a table or query where I can select on a variables value. ie "select * from temptable where V3 = yyyy"
Datetime V1 V2 V3
T1 xxxx xxxx xxxx
T2 yyyy yyyy yyyy
any suggestions for an SQL statement.
thanks in advance.
I don't exactly understand your business requirements. Are V1, V2, and V3 the only possible values for the Variable column? If so, something like this might help to get you started:

SELECT
DateTime,
MIN(V1),
MIN(V2),
MIN(V3)
FROM
(
SELECT
DateTime,
CASE
WHEN Variable = 'V1' THEN Value ELSE NULL
END AS V1,
CASE
WHEN Variable = 'V2' THEN Value ELSE NULL
END AS V2,
CASE
WHEN Variable = 'V3' THEN Value ELSE NULL
END AS V3
FROM
yourTable
) AS subQuery
GROUP BY
DateTime


|||

Thanks Terri

The base table contains many variables. The data in this table is produced by a periodic time base event which inserts a row for each of the variables along with their current value. What I am trying to achieve is a record set where I can compare the values of selected variables in a single row based on the timestamp. ie
DateTime Var1 Var2 Var3
1/1/2005 10:00 Product1 10 20
1/1/2005 10:05 Product2 9 15
1/1/2005 10:10 Product2 12 30
1/1/2005 10:20 Product1 15 16
Once I have this record set I can then do selections based on the value of any variable ie
"select * from recorset where Var1 = 'Product1'"
I tried a statement similar to the one you have posted but my case statement sintax wasn't quite right.
I'm not in the office for a few days so may not get a chance try your code.
regards
Noel

cross tab question

I am trying to create a cross tab query to to combine the rows for two dates
on one line:
DATA:
( query to get this data:
select top 6
stkhstCsiSym,
stkhstDATE,
stkhstXO
from stkhst
where stkhstdate >=20050225
order by stkhstcsisym, stkhstDATE)
1006 20050225 O
1006 20050228 X
1008 20050225 O
1008 20050228 O
1012 20050225 O
1012 20050228 X
I wish the result to be
1006 O X
1008 O O
1012 O X
I've tried the following query that does not work:
select top 6
stkhstCsiSym,
CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
from stkhst
where stkhstdate >=20050225
group by stkhstCsiSym, stkhstDATE, stkhstXO
order by stkhstcsisym, stkhstDATE:
this gives me :
1006 O NULL
1006 NULL X
1008 O NULL
1008 NULL O
1012 X NULL
1012 NULL O
Other than the fact that it does not work does anyone have anyideas how to
do this correctly?
thanks
kesShould i be using a join instead?
thanks
"Kurt Schroeder" wrote:

> I am trying to create a cross tab query to to combine the rows for two dat
es
> on one line:
> DATA:
> ( query to get this data:
> select top 6
> stkhstCsiSym,
> stkhstDATE,
> stkhstXO
> from stkhst
> where stkhstdate >=20050225
> order by stkhstcsisym, stkhstDATE)
> 1006 20050225 O
> 1006 20050228 X
> 1008 20050225 O
> 1008 20050228 O
> 1012 20050225 O
> 1012 20050228 X
> I wish the result to be
> 1006 O X
> 1008 O O
> 1012 O X
> I've tried the following query that does not work:
> select top 6
> stkhstCsiSym,
> CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
> CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
> from stkhst
> where stkhstdate >=20050225
> group by stkhstCsiSym, stkhstDATE, stkhstXO
> order by stkhstcsisym, stkhstDATE:
> this gives me :
> 1006 O NULL
> 1006 NULL X
> 1008 O NULL
> 1008 NULL O
> 1012 X NULL
> 1012 NULL O
> Other than the fact that it does not work does anyone have anyideas how to
> do this correctly?
> thanks
> kes|||As a general approach, one could use:
SELECT col1,
MAX( CASE col2 WHEN '20050225'
THEN col3
END ) AS "somecol1",
MAX( CASE col2 WHEN '20050228'
THEN col3
END ) AS "somecol2"
FROM tbl
GROUP BY col1 ;
Anith|||hey! that works!! thanks. I'm using something else a self join, but i'd have
used this if i saw it first.
thank you
kes
"Anith Sen" wrote:

> As a general approach, one could use:
> SELECT col1,
> MAX( CASE col2 WHEN '20050225'
> THEN col3
> END ) AS "somecol1",
> MAX( CASE col2 WHEN '20050228'
> THEN col3
> END ) AS "somecol2"
> FROM tbl
> GROUP BY col1 ;
> --
> Anith
>
>