group toggle works in desinger but not when published

Folks -
I have a date group in a simple detail report that toggles the detail
records by date. I used the report desinger wizard to create the initial
report, which toggles fine in the desinger. When published, the toggle
functionality does not work, and no detail records can be
accessed/displayed. RS
server and my VS install have all the latest SPs and patches.
Ideas?
Please reply to the group.
Eric HallI had a very similar problem and worked out by accident that it was because I
was using "lines" in the page header and footer. I removed the lines and used
very thin rectangles and all is good. Very weird though...I think that this
must be a bug.
"Eric Hall" wrote:
> Folks -
> I have a date group in a simple detail report that toggles the detail
> records by date. I used the report desinger wizard to create the initial
> report, which toggles fine in the desinger. When published, the toggle
> functionality does not work, and no detail records can be
> accessed/displayed. RS
> server and my VS install have all the latest SPs and patches.
> Ideas?
> Please reply to the group.
> Eric Hall
>
>
>sql

Group Test Field

When adding a text box in my group section, the textbox width is limited to the width of the first column in the detail of the report, or any detail column. It cannot span multiple columns, even though it is the only textbox on the entire 8 inch line, it is limited to my .5 inch column 1 width.
How do I expand my group by text box to use some of that empty space ?
Thanks in advance,
ChrisFigured it out, right-click on the cell select Merge Cells and can spread across entire row. Nice!
I'm a huge fan!
Chris
"Chris" wrote:
> When adding a text box in my group section, the textbox width is limited to the width of the first column in the detail of the report, or any detail column. It cannot span multiple columns, even though it is the only textbox on the entire 8 inch line, it is limited to my .5 inch column 1 width.
> How do I expand my group by text box to use some of that empty space ?
> Thanks in advance,
> Chris

Group Sum over multiple pages with IIF

I am using an IIF statement to sum a group of detail records that spans more
than 1 page. The sum looks like this:
Sum(IIf(Fields!MySwitch.Value = 'A', Fields!MyValue.Value,0))
Im doing the sum in the table1_group1 footer and it works fine if all the
data in the group remains on the same page, but once it is expanded to 2 or
more pages I get #Error. Any suggestions?
Michael CIf you can bring from a query then it is really good instead of summing two
pages rows.
Amarnath
"Michael C" wrote:
> I am using an IIF statement to sum a group of detail records that spans more
> than 1 page. The sum looks like this:
> Sum(IIf(Fields!MySwitch.Value = 'A', Fields!MyValue.Value,0))
> Im doing the sum in the table1_group1 footer and it works fine if all the
> data in the group remains on the same page, but once it is expanded to 2 or
> more pages I get #Error. Any suggestions?
> Michael C

group sum

I have a table with groups. I have a detail field (textbox66) that has the
following expression:
=iif(Fields!endDate.Value = Nothing, 1, iif(Fields!endDate.Value <
dateadd("d", 14, Parameters!startDate.Value), 0, 1))
I need to have a field in the group footer sum this column. How do I do
this? If I do =Sum(ReportItems!textbox66.value) in a group footer field, I
get the following errors:
Aggregate functions can be used only on report items contained in page
headers and footers.
AND
Report Item expressions can only refer to other report items within the same
grouping scope or a containing grouping scope.Just use this expression for the group footer:
=Sum(iif(IsNothing(Fields!endDate.Value), 1, iif(Fields!endDate.Value <
dateadd("d", 14, Parameters!startDate.Value), 0, 1)))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:CC1EE200-443F-45B8-95CA-08FEFF2941C6@.microsoft.com...
>I have a table with groups. I have a detail field (textbox66) that has the
> following expression:
> =iif(Fields!endDate.Value = Nothing, 1, iif(Fields!endDate.Value <
> dateadd("d", 14, Parameters!startDate.Value), 0, 1))
> I need to have a field in the group footer sum this column. How do I do
> this? If I do =Sum(ReportItems!textbox66.value) in a group footer field,
> I
> get the following errors:
> Aggregate functions can be used only on report items contained in page
> headers and footers.
> AND
> Report Item expressions can only refer to other report items within the
> same
> grouping scope or a containing grouping scope.

Group Footers?

I am new to reporting services. On a detail table of my report, I would like
to print
a large comment field after each row. The comment should span the entire
width of the table. (Sort of like the outlook preview row capability) Is
this possible?
Thanks,
Deniseright click to the left of you detail row and 'insert row below'.
Select all cells in this row, right click and 'merge'. You can make this any
size you need and will show up between your report detail. You can format as
needed.
--
U. Tokklas
"Denise" wrote:
> I am new to reporting services. On a detail table of my report, I would like
> to print
> a large comment field after each row. The comment should span the entire
> width of the table. (Sort of like the outlook preview row capability) Is
> this possible?
> Thanks,
> Denise

Group Footer on Next Page

Anyone know if there is a way to keep at least one detail row with the group
footer at page break?
It doesn't make for a very nice report when some pages begin with a group
footer and no details to go with it.
Thanks to anyone that can help with this.I am having the same problem. MS documentation says that "a table group
footer and the last previous detail row before the footer are kept together".
This is not happening for me. I get the group footer on a page by itself
when the last table detail line ends a group.
Any ideas?
"Alison" wrote:
> Anyone know if there is a way to keep at least one detail row with the group
> footer at page break?
> It doesn't make for a very nice report when some pages begin with a group
> footer and no details to go with it.
> Thanks to anyone that can help with this.
>
>|||Sorry forgot to mention: For me, the table group footers page breaks look
great when previewing in rs, but after saving to PDF, then the group footer
is not "kept together" with the last detail row.
Help?
"LesW" wrote:
> I am having the same problem. MS documentation says that "a table group
> footer and the last previous detail row before the footer are kept together".
> This is not happening for me. I get the group footer on a page by itself
> when the last table detail line ends a group.
> Any ideas?
> "Alison" wrote:
> > Anyone know if there is a way to keep at least one detail row with the group
> > footer at page break?
> > It doesn't make for a very nice report when some pages begin with a group
> > footer and no details to go with it.
> >
> > Thanks to anyone that can help with this.
> >
> >
> >

group by....

Hi,
I am new to RS... here is problem.
Its a simple report which shows the detail of top sale performers
Nationally. We have 4 regions... east, west, north and south. I the
report detail I print out a report card... showing each performer's
detail... somthing along the lines
Region Rep $<sales> $<commission> .....
I want to have a snapshot at the top which will list the 4 regions and
numbers of reps from each region that made the cut...
National Summary:
East 4
West 5
North 16
Sount 0
I dont want to query the DB again as I already have that info.
Basically I need to use a group by <region> in reporting serices with
counting the reps? How do I accomplish this?I see that you could use one of three ways. You could created a graph
grouping by region and counting the reps or just insert another table above
your current table with just the region grouping and counting the reps. The
last way is a bit more complicated and ivolves using the case statement and
you could put it in your header. Example:
Region 1 Region 2 Region 3 Region 4
5 3 7 1
if you are interested in the case statement then let me know and I will help
you with that.
"zomer" <noneee@.gmail.com> wrote in message
news:1165257406.682491.148580@.n67g2000cwd.googlegroups.com...
> Hi,
> I am new to RS... here is problem.
> Its a simple report which shows the detail of top sale performers
> Nationally. We have 4 regions... east, west, north and south. I the
> report detail I print out a report card... showing each performer's
> detail... somthing along the lines
> Region Rep $<sales> $<commission> .....
> I want to have a snapshot at the top which will list the 4 regions and
> numbers of reps from each region that made the cut...
> National Summary:
> East 4
> West 5
> North 16
> Sount 0
> I dont want to query the DB again as I already have that info.
> Basically I need to use a group by <region> in reporting serices with
> counting the reps? How do I accomplish this?
>

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron

Group and subreport

Hi,
I have a report and I have defined a group in it.
both the group header and group footer has some information the detail section contain two subreports.
My requirement is that if there are more than 10 records in any of the subreport then only first 10 records will be displayed on each page along withh the group header and group footer. Somhow I managed to print the 10 records on each page with group header; but I am not able to get the footer on each page!!
Is there any option avaliable for this type of scenario.

I am using Crystal Report XI with ASP.NET.Hey.

Did u put a 'New Page After' in the group header or detail section of your main report?
If, then remove them there and place them in the Group Footer section.

Do not use any of the 'New Page Before/After' feathures in ure sub-reports.

Hope this helps.