subreport grouping error

[highsmith]

this is driving me mad. i have a report made in CR10. it selects all invoice lines from our SQL server via a command statement that generates a temp table.

there are a few subreports that read from this temp table. they have always worked perfectly and still do.

now I try to add a new subreport that selects from the same temp table, this time grouping by customer name, but it's as if the grouping mechanism in CR is broken. I can see many groups with the same customer name and I don't know how it is possible when I only have this one group and several summations for each customer (av sales amount, invoice, outstanding etc.)

when I import the subreport, all lines are grouped by a formula field and it works fine. when i change the grouping to be based on any other field it just goes whacko, not grouping properly. even if i change back to the original formula field grouping it had when i imported it.

has anyone else seen this? i can't seem to find any info searching.

i tried it in CR11 and it does the same thing...

thanks for the help

 

[hilfy]

Are there any other tables in the subreport or just your temp table? How is it linked to the main report?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[highsmith]

Dell, thanks for the reply.

There is just 1 SQL temp table selected with the command (select * from ##dailysales) and the subreport is not linked to the container report in any way through Crystal.

The subreport is in the Report Footer B section. This is for a dashboard type report so the user sees several independent reports in one. Each report groups our sales&invoicing numbers in different ways, 1 for top sales reps, 1 for top industries et cetera. The one in question is just to show the top 10 customers.

I have been making complicated Crystal reports for about 5 years. To me this seems routine & simple. Nothing tricky at all involved here.

Thanks again

 

[highsmith]

If I were troubleshooting this question from someone else, I would say:

Look into your groupings because grouping on customer name is a bad practice. Anyone who's sold to the Real Estate industry knows there may be 100 customers called RE/MAX Such and Such.

I'm actually grouping on customer number which I know is unique, and I am applying a customized group name from the customer name field.

So I tried switching the grouping to other fields and nothing works.

Imagine a table with customer number, invoice number, invoice date, and invoice amount, and you can't make it sum the amount and group simply by customer number...

 

[lbass]

Please show us the SQL query generated by the subreport (database->show SQL query).

-LB

 

[highsmith]

i'm pretty certain it's not an issue with the SQL, but just in case...

here is the select for my Subreport, all subreports use the same command:

select *
from ##dailysalesrpt
union
select *
from ##dailyinvoicerpt
union
select *
from ##dailycreditrpt

and here is the select in the master report:

-------------------------------
--the next 3 queries insert sales lines, then invoice lines, then credit memo lines into temporary tables
--******EACH PART MUST SELECT THE SAME FIELDS & DATATYPEs
--------------------------------

SELECT 'Open Orders' as "Type",
c."Business Type",
sh."No_" as "Header No_",
sl."Line No_" as "Line No_",
sl."No_" as "Item No",
sl."Description",
cast(sl."Quantity" as int) as 'Quantity',
(sl."Line Amount"/sl."Quantity")*(sl."Quantity"-sl."Quantity Invoiced") as "Amount",

--Finds the last vendor price per item
(isnull((select top 1 ip."Direct Unit Cost"
from [MBS USA].[dbo].[Purchase Price] ip
WHERE sl."No_"=ip."Item No_"
order by "Starting Date" desc),0)
*
(sl."Quantity"-sl."Quantity Invoiced"))*-1 as 'Cost',

sl."Gen_ Prod_ Posting Group",
sh."Order Date",
null as "Posting Date",
sh."Shortcut Dimension 1 Code",
sh."Shortcut Dimension 2 Code",
sp."Name" as "Order Taker",
sp."Group" as "Order Taker Group",
c."No_" as "Customer No_",
c."Name" as "Customer Name",
c."Customer Price Group",
c."Creation Date",
c."Salesperson Code"
into ##dailysalesrpt
FROM ((xxxxxx."dbo"."Sales Header" sh
INNER JOIN xxxxxx."dbo"."Sales Line" sl
ON sh."No_"=sl."Document No_")
INNER JOIN xxxxxx."dbo"."Customer" c
ON sh."Sell-to Customer No_"=c."No_")
LEFT OUTER JOIN xxxxxx."dbo"."Salesperson_Purchaser" sp
ON sh."Salesperson Code"=sp."Code"
Where
sh."Document Type"=1
and sh.[Status]=1
and c."Customer Posting Group"<>'INTER-CO'
--AND year(sh."Order Date") = year(getdate())
and sl.[Quantity]>0;

SELECT 'Invoiced Orders' as "Type",
c."Business Type",
sih."No_" as "Header No_",
sil."Line No_" as "Line No_",
sil."No_" as "Item No",
sil."Description",
cast(sil."Quantity" as int) as 'Quantity',
(sil."Amount") as "Amount",

--Finds the last vendor price per item
(isnull((select top 1 ip."Direct Unit Cost"
from [MBS USA].[dbo].[Purchase Price] ip
WHERE sil."No_"=ip."Item No_"
order by "Starting Date" desc),0)
*
sil."Quantity")*-1 as 'Cost',

sil."Gen_ Prod_ Posting Group",
sih."Order Date",
sih."Posting Date",
sih."Shortcut Dimension 1 Code",
sih."Shortcut Dimension 2 Code",
sp."Name" as "Order Taker",
sp."Group" as "Order Taker Group",
c."No_" as "Customer No_",
c."Name" as "Customer Name",
c."Customer Price Group",
c."Creation Date",
c."Salesperson Code"
into ##dailyinvoicerpt
FROM ((xxxxxx."dbo"."Sales Invoice Header" sih
INNER JOIN xxxxxx."dbo"."Sales Invoice Line" sil
ON sih."No_"=sil."Document No_")
INNER JOIN xxxxxx."dbo"."Customer" c
ON sih."Sell-to Customer No_"=c."No_")
LEFT OUTER JOIN xxxxxx."dbo"."Salesperson_Purchaser" sp
ON sih."Salesperson Code"=sp."Code"
Where sih."Source Code" <> 'DELETE'
and c."Customer Posting Group"<>'INTER-CO'
and year(sih."Posting Date") = year(getdate());


SELECT 'Credit Memos' as "Type",
c."Business Type",
cmh."No_" as "Header No_",
cml."Line No_" as "Line No_",
cml."No_" as "Item No",
cml."Description",
cast(cml."Quantity" as int)*-1 as 'Quantity',
(cml."Amount"*-1) as 'Amount',

--Finds the last vendor price per item
isnull((select top 1 ip."Direct Unit Cost"
from [MBS USA].[dbo].[Purchase Price] ip
WHERE cml."No_"=ip."Item No_"
order by "Starting Date" desc),0)
*
cml."Quantity" as 'Cost',

cml."Gen_ Prod_ Posting Group",
cmh."Posting Date" as "Order Date",
cmh."Posting Date",
cmh."Shortcut Dimension 1 Code",
cmh."Shortcut Dimension 2 Code",
sp."Name" as "Order Taker",
sp."Group" as "Order Taker Group",
c."No_" as "Customer No_",
c."Name" as "Customer Name",
c."Customer Price Group",
c."Creation Date",
c."Salesperson Code"
into ##dailycreditrpt
FROM ((xxxxxx."dbo"."Sales Cr_Memo Header" cmh
INNER JOIN xxxxxx."dbo"."Sales Cr_Memo Line" cml
ON cmh."No_"=cml."Document No_")
INNER JOIN xxxxxx."dbo"."Customer" c
ON cmh."Sell-to Customer No_"=c."No_")
LEFT OUTER JOIN xxxxxx."dbo"."Salesperson_Purchaser" sp
ON cmh."Salesperson Code"=sp."Code"
WHERE cmh."Source Code" <> 'DELETE'
and c."Customer Posting Group"<>'INTER-CO'
AND year(cmh."Posting Date") = year(getdate());

--selects all data from the temp tables created in the above queries
select *
from ##dailysalesrpt
union
select *
from ##dailyinvoicerpt
union
select *
from ##dailycreditrpt