Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a wly breakout of the COUNT(PoNumber)?
I want a column for each w and the number of PONumbers Per Datasource and
NamePrep for that w.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a wly breakout of the COUNT(PoNumber)
?
> I want a column for each w and the number of PONumbers Per Datasource a
nd
> NamePrep for that w.
> Example Output:
> Service Line | PO Created By | Total Of PONumber | 3/5/2005
|
> 3/12/2005 | ETC...
> MNS JOHN 10
> MNS ERIC 25
> FMS CARL 8
> Hope my question makes sense :)
> John.
>
>sql

Grouping by Page Count in CR 8.5

Datasource: Access 2000
CrystalReports: 8.5
Language: Visual Basic 6

I wonder if someone can help with this.

I am have a report that collates customer details and it is currently grouped on customer number.

These details are used to generate mail shots and it is now a requirement that the report is ordered by page count.

Is there a way that the total number of pages printed for each customer number can be used to sort the report.

So far the formulas I have tried cannot be completed because the report needs to generated first so the page numbers can be calculated.

The solution can be either in code or in the report but if in the report I need to be able to obtain the page totals for each customer number into code.

Many thanks in advance.Do you want to know how many pages does Report have for each Customer?sql

Group By

Hi,

I have the report,Report datasource is stored procedure.The stored procedure is returning results like this:

ID Name Count

1 Own Residence 50

2 Other Residence 20

3 xxxx 89

4 vvvvvv 78

Now i want to display the report like in the following format:

Name Count

Residence

Own 50

Other 20

xxxx 89

vvvvvv 78

How to achevie this

Thanks in advance

Assuming you can't change your store procedure.

To do this you need two distinct tables on your report designer surface. One will have a filter to get rid of all names without 'Residence'. Then, you simply list the names and counts in the details section.

Right underneth, you need to add a second table without heading/footer. This one will have a filter to get rid of the name with 'Residence'. Make the column widths match the one of your first table and things should align properly as if you were using a single table.

Good luck.