Group Wise Page Numbers

Hi,
Iam printing a report having grouping, a group will spread over many pages and I want No. of pages for that group and also page number. for ex.

A report has 50 pages,

group 1 of 20 pages
group 2 of 10 pages
group 3 of 20 pages

I want No. of Pages 20 and current page as 1 of 20, 2 of 20. and for the second group again No. of Pages 10 and current page as 1 of 10, 2 of 10 and so on.

How do I do this.? can anyone help me on this.

thanks for ur suggestions in advance.Hi,

put Special Field "Page N of M"

now, Open Section Expert and navigate on first group level. there is one event "Reset Page Number After". Write below code on that

eg.
Previous({S_EVT_ACT.OWNER_LOGIN}) <> GroupName ({S_EVT_ACT.OWNER_LOGIN})

Using this code u get group wise Page N of M value.

-Yags|||Thanks ! it worked really.
I have one more question, which book is good for developing reports under vb 6.0 using crystal reports. suggest me one.

thanks again.|||Hi,

I have not prefered any book for Crystal Reports b'caz there is no book which
suitable for real crystal report development.

Sorry and most welcome for any issue regarding crystal reports

-Yags

Group Wise No. of Pages.

Hi iam using count of pages for each group,
but it gives me a wrong no. of pages. i.e. for a group of 4 pages, for the first page total pages displayed is TOTAL PAGES:1 and for the other 3 pages it displays as TOTAL PAGES:3

Page 1

Group Name : xxx Page 1 of 1

------
Page 2

Group Name : xxx Page 1 of 3

------
Page 3

Group Name : xxx Page 2 of 3 so on...

any suggestions are appreciated.
thanks in advance.This is for CR 8.5 using RDC...

Make sure ResetPageNumberAfter is set to True for only the last Group Footer Section.|||Yes, Iam using CR 8.5,

Iam printing TOTAL PAGES in PAGE FOOTER, I have changed "Reset Page Number after" option. but still Iam getting the same result. anything else to do, please help me out.
thanks again.

GROUP BY with multiple columns

Hi all,
Iam havin a rather complex query and need to add another column in the
resultset. That new column is a COUNT aggregation and I need to use the GROU
P
BY clause. Below is the query that I tried. However, there is a problem with
text, ntext or image columns being in the GROUP BY clause. Is there another
way?
QUERY:
--
SELECT
p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
p.match_id, p.shop_id,
p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
big_state, m.category_name,
m.subcategory_id, COUNT(v.filter_value_id) FROM
cds_products p
JOIN
cds_matched_categories m
ON
p.match_id = m.id
JOIN
cds_small_images s
ON
p.small_image_id = s.id
JOIN
cds_big_images b
ON
p.big_image_id = b.id
JOIN
cds_product_2_filter_values v
ON
p.id = v.product_id
WHERE p.shop_id = 66
GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
p.match_id, p.shop_id,
p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
m.subcategory_id
SCHEMA:
--
CREATE TABLE [dbo].[cds_big_images] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[shop_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_matched_categories] (
[id] [int] NOT NULL ,
[shop_id] [int] NOT NULL ,
[category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[subcategory_id] [int] NULL ,
[state] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_product_2_filter_values] (
[product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[filter_value_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_products] (
[id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[infile] [int] NOT NULL ,
[description] [text] COLLATE Latin1_General_CI_AS NULL ,
[price] [money] NOT NULL ,
[link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
[match_id] [int] NOT NULL ,
[shop_id] [int] NOT NULL ,
[small_image_id] [int] NOT NULL ,
[big_image_id] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_small_images] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[shop_id] [int] NOT NULL
) ON [PRIMARY]
GOYou cannot group by large objects.
May be you do something like this..
Instead of grouping by the all the columns
you can join with a subquery.
instead of joining with cds_product_2_filter_values
join it with
(select product_id, count(filter_value_id) as tot_count from
cds_product_2_filter_values) as v
and then directly select tot_count.
I don't know the busniess or the type of relationship with the table.
May be you can think in these lines and try to find the count without
grouping by the text col.
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Reik" wrote:

> Hi all,
> Iam havin a rather complex query and need to add another column in the
> resultset. That new column is a COUNT aggregation and I need to use the GR
OUP
> BY clause. Below is the query that I tried. However, there is a problem wi
th
> text, ntext or image columns being in the GROUP BY clause. Is there anothe
r
> way?
> QUERY:
> --
> SELECT
> p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
> p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
> big_state, m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) FROM
> cds_products p
> JOIN
> cds_matched_categories m
> ON
> p.match_id = m.id
> JOIN
> cds_small_images s
> ON
> p.small_image_id = s.id
> JOIN
> cds_big_images b
> ON
> p.big_image_id = b.id
> JOIN
> cds_product_2_filter_values v
> ON
> p.id = v.product_id
> WHERE p.shop_id = 66
> GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
> p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
> m.subcategory_id
>
>
> SCHEMA:
> --
> CREATE TABLE [dbo].[cds_big_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_matched_categories] (
> [id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subcategory_id] [int] NULL ,
> [state] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_product_2_filter_values] (
> [product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [filter_value_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_products] (
> [id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [infile] [int] NOT NULL ,
> [description] [text] COLLATE Latin1_General_CI_AS NULL ,
> [price] [money] NOT NULL ,
> [link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
> [match_id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [small_image_id] [int] NOT NULL ,
> [big_image_id] [int] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_small_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>|||If you didn't mind truncating a little data in the resultset you could
CAST() the 2 text columns (description & link) to VARCHAR(8000) in the
select list and the group by clause. Do those columns both really need
to be more than 8000 characters? I'm assuming link is a URL or some
kind of reference to another product.
Has dbo.cds_products got a primary key? I assume the id column is the
primary key. If so then you could pull all the stuff from
dbo.cds_products out into a main query and join in the GROUP BY stuff in
a derived table like this (untested):
SELECT
prod.id, prod.title, prod.state, prod.infile, prod.description,
prod.price, prod.link, prod.match_id, prod.shop_id,
prod.small_image_id, prod.big_image_id,
d.small_state, d.big_state, d.category_name, d.subcategory_id,
d.filter_count
FROM dbo.cds_products as prod
INNER JOIN
(
SELECT
p.id, s.state AS small_state, b.state AS big_state,
m.category_name,
m.subcategory_id, COUNT(v.filter_value_id) as filter_count
FROM cds_products as p
INNER JOIN cds_matched_categories AS m ON p.match_id = m.id
INNER JOIN cds_small_images AS s ON p.small_image_id = s.id
INNER JOIN cds_big_images AS b ON p.big_image_id = b.id
INNER JOIN cds_product_2_filter_values as v ON p.id =
v.product_id
WHERE p.shop_id = 66
GROUP BY p.id, s.state, b.state, m.category_name,
m.subcategory_id
) AS d ON d.id = prod.id
That way you don't need to GROUP BY the text columns as the
cds_products.id column is enough to do the grouping from that table.
*mike hodgson*
http://sqlnerd.blogspot.com
Reik wrote:

>Hi all,
>Iam havin a rather complex query and need to add another column in the
>resultset. That new column is a COUNT aggregation and I need to use the GRO
UP
>BY clause. Below is the query that I tried. However, there is a problem wit
h
>text, ntext or image columns being in the GROUP BY clause. Is there another
>way?
>QUERY:
>--
>SELECT
> p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
>p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
>big_state, m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) FROM
> cds_products p
>JOIN
> cds_matched_categories m
>ON
> p.match_id = m.id
>JOIN
> cds_small_images s
>ON
> p.small_image_id = s.id
>JOIN
> cds_big_images b
>ON
> p.big_image_id = b.id
>JOIN
> cds_product_2_filter_values v
>ON
> p.id = v.product_id
>WHERE p.shop_id = 66
>GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
>p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
> m.subcategory_id
>
>
>SCHEMA:
>--
>CREATE TABLE [dbo].[cds_big_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_matched_categories] (
> [id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subcategory_id] [int] NULL ,
> [state] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_product_2_filter_values] (
> [product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [filter_value_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_products] (
> [id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [infile] [int] NOT NULL ,
> [description] [text] COLLATE Latin1_General_CI_AS NULL ,
> [price] [money] NOT NULL ,
> [link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
> [match_id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [small_image_id] [int] NOT NULL ,
> [big_image_id] [int] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_small_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>
>|||Worked excellent thanks. One more question. You were right about these two
text columns. Somehow I didnt realize a varchar column could be more than 25
5
chars. Is there a rule of thumb whever you wanna use a large varchar column
or a text column? In my example, the descriptions will likely be less than
2000 chars and the URL's in the link column might be up to 300 chars. Should
I stick with a varchar column or is there any performance/storage drawback
with that?
"Mike Hodgson" wrote:

> If you didn't mind truncating a little data in the resultset you could
> CAST() the 2 text columns (description & link) to VARCHAR(8000) in the
> select list and the group by clause. Do those columns both really need
> to be more than 8000 characters? I'm assuming link is a URL or some
> kind of reference to another product.
> Has dbo.cds_products got a primary key? I assume the id column is the
> primary key. If so then you could pull all the stuff from
> dbo.cds_products out into a main query and join in the GROUP BY stuff in
> a derived table like this (untested):
> SELECT
> prod.id, prod.title, prod.state, prod.infile, prod.description,
> prod.price, prod.link, prod.match_id, prod.shop_id,
> prod.small_image_id, prod.big_image_id,
> d.small_state, d.big_state, d.category_name, d.subcategory_id,
> d.filter_count
> FROM dbo.cds_products as prod
> INNER JOIN
> (
> SELECT
> p.id, s.state AS small_state, b.state AS big_state,
> m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) as filter_count
> FROM cds_products as p
> INNER JOIN cds_matched_categories AS m ON p.match_id = m.i
d
> INNER JOIN cds_small_images AS s ON p.small_image_id = s.i
d
> INNER JOIN cds_big_images AS b ON p.big_image_id = b.id
> INNER JOIN cds_product_2_filter_values as v ON p.id =
> v.product_id
> WHERE p.shop_id = 66
> GROUP BY p.id, s.state, b.state, m.category_name,
> m.subcategory_id
> ) AS d ON d.id = prod.id
> That way you don't need to GROUP BY the text columns as the
> cds_products.id column is enough to do the grouping from that table.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Reik wrote:
>
>|||varchar column is better than text column anytime.. and you can store to a
max of 8000 characters. But your table page size is 8 k. So, you rowsize
cannot go beyond 8k bytes. And varchar is better than text performance wise,
manageability and you can apply more functions on it :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Nice. Then I will change the datatype of these two columns to varchar. Thank
s!
"Omnibuzz" wrote:

> varchar column is better than text column anytime.. and you can store to a
> max of 8000 characters. But your table page size is 8 k. So, you rowsize
> cannot go beyond 8k bytes. And varchar is better than text performance wis
e,
> manageability and you can apply more functions on it :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),
col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt sho
w anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched
based on no of records..
I not able to really see why the first one is not returning any records if t
he condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...

quote:

---

> Hi,
> Iam using a count along with a group by condition. (Eg., Select

---

count(col1),col1 from table1 where col1 = <value> group by col1)

quote:

---

> If I run the query and if no matching records if found the result doesnt

---

show anything. Why is this so?. But if I give

quote:

---

> count(column) without the group by condition then a single record is

---

fetched based on no of records..

quote:

---

> I not able to really see why the first one is not returning any records if

---

the condition does not match.

quote:

---

>
> Thanx in advance
> regards
> Mahesh
>

---

|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
David Portas
--
Please reply only to the newsgroup
--

Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt show anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched based on no of records..
I not able to really see why the first one is not returning any records if the condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...
> Hi,
> Iam using a count along with a group by condition. (Eg., Select
count(col1),col1 from table1 where col1 = <value> group by col1)
> If I run the query and if no matching records if found the result doesnt
show anything. Why is this so?. But if I give
> count(column) without the group by condition then a single record is
fetched based on no of records..
> I not able to really see why the first one is not returning any records if
the condition does not match.
>
> Thanx in advance
> regards
> Mahesh
>|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
--
David Portas
--
Please reply only to the newsgroup
--

gridview

iam taking first value of label and comparing with rest of values if it is same then i want to change the color of that row.
but what is happening is entire gridview backcolor is changing to red.

i want to change only those rows which matches.

void GridView1_DataBound(object sender, EventArgs e)
{
foreach (GridViewRow gv in GridView1.Rows)
{

Label lbl = (Label)gv.Cells[2].Controls[0].FindControl("Label1");
string t = lbl.Text;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label lbl4 = (Label)GridView1.Rows[i].Cells[2].Controls[0].FindControl("Label1");
if (t == lbl4.Text)
{
GridView1.Rows[i].Cells[2].BackColor = System.Drawing.Color.Red;

//gv.BackColor = System.Drawing.Color.Red;

}

}

}

}Please, move this to appropriate forum.