Grouping my months in a chart

Hi

I'm trying to create a chart with monthly comparisons, but when i throw my months into the chart it groups them under one another instead of next to one another. Please Help!!

Kind regards

Carel GreavesARE YOU USING A TABLE OR MATRIX AS YOUR DATA REGION?|||I'm using a standard line chart, no tables in this report, only charts. I had to do two pie charts for Jan2007 which came out fine but now i need to do a past 4 month trend.

Here's my SQL Code if it will help, i created temp tables for each month and then i just select the values how i needed them.

DECLARE @.OctTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.OctTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 10
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.OctTable

DECLARE @.NovTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.NovTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 11
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.NovTable

DECLARE @.DecTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.DecTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 12
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.DecTable

DECLARE @.JanTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.JanTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2007
AND DT.[Month] = 1
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.JanTable

DECLARE @.MyTable TABLE (UID INT Identity(1,1), October Varchar(255), November Varchar(255), December Varchar(255), January Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), OctSales FLOAT, OctQuantity FLOAT, NovSales FLOAT, NovQuantity FLOAT, DecSales FLOAT, DecQuantity FLOAT, JanSales FLOAT, JanQuantity FLOAT, JanTotalSales FLOAT, JanTotalQuantity FLOAT)

INSERT INTO @.MyTable (October, November, December, January, [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity)--, JanTotalSales, JanTotalQuantity)

SELECT DISTINCT OT.Month_Name, NT.Month_Name, DT.Month_Name, JT.Month_Name, OT.[Description], OT.Manufacture_Name, OT.Sales, OT.Quantity, NT.Sales,NT.Quantity, DT.Sales, DT.Quantity, JT.Sales, JT.Quantity--, SUM(JanSales), SUM(JanQuantity)
FROM @.OctTable OT, @.NovTable NT, @.DecTable DT, @.JanTable JT
WHERE JT.UID = OT.UID
AND JT.UID = NT.UID
AND JT.UID = DT.UID
GROUP BY OT.Manufacture_Name, OT.[Description] ,OT.Sales, OT.Quantity, NT.Sales,NT.Quantity, DT.Sales, DT.Quantity, JT.Sales, JT.Quantity, OT.Month_Name, NT.Month_Name, DT.Month_Name, JT.Month_Name

SELECT October, November, December, January, [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity, (SELECT SUM(JanSales) where Manufacture_Name like '%ADCOCK INGRAM%') AS 'Adock Sales', (SELECT SUM(JanQuantity) where Manufacture_Name like '%ADCOCK INGRAM%') AS 'Adcock Quantity'
FROM @.MyTable
GROUP BY [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity, October, November, December, January|||I came right again, thanks. All i did was create a column that had all months_names in, instead of 4 columns with a single month_name

Grouping in crystal reports

Hi

I m using crystal reports ver 8.0.

I have a report which is grouped on a field called "states".

My requirement is that the data for each state has to begin from a fresh page. i.e, each group item has to start from the next page.

I m not able to do this in crystal reports. Can anyone tell me if this is possible in crystal reports. and if possible, how it can be done ?

Thanking in advancethis works for 8.5 I don't know about version 8.0

Set "New Page Before" in the Group Header

grouping a few columns

/*
Hi
I need to query this table to get results where ids are found with every
searchNum, i.e. the results of this would be:
id
1
2
because both id 1 and 2 are found with searchNum 1,2,3. The table could be
any size with any variation of ids and searchNum so I need some sort of
general grouping query. Hope this makes sence. I've been bashing my head
against the wall all day.
thanks Andrew
*/
declare @.table table (searchNum int, word varchar(50), id int)
insert into @.table values (1, 'cambridge', 1)
insert into @.table values (1, 'northampton', 2)
insert into @.table values (1, 'hull', 4)
insert into @.table values (2, 'laboratory', 1)
insert into @.table values (2, 'chemistry', 2)
insert into @.table values (2, 'chemistry', 5)
insert into @.table values (2, 'laboratory', 2)
insert into @.table values (2, 'laboratory', 4)
insert into @.table values (3, 'scientist', 1)
insert into @.table values (3, 'scientist', 2)
select * from @.tableJ055 wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
>
Thanks for posting the DDL and sample data. Please do also include keys
and constraints with your DDL. It can make a big difference to the
solution. Here's one suggestion:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(DISTINCT searchnum)
FROM @.table);
If searchnum is a foreign key you could also reference the other table:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(*)
FROM search);
David Portas
SQL Server MVP
--|||Try this:
SELECT [id] FROM
(
SELECT id, COUNT(*) AS NofRecs
FROM (SELECT DISTINCT searchNum, [id] FROM @.table) AS inn
GROUP BY [ID]
HAVING COUNT(*) IN
(
SELECT COUNT( DISTINCT searchNum ) FROM @.table
)
) AS cnt
"J055" wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
> */
> declare @.table table (searchNum int, word varchar(50), id int)
> insert into @.table values (1, 'cambridge', 1)
> insert into @.table values (1, 'northampton', 2)
> insert into @.table values (1, 'hull', 4)
> insert into @.table values (2, 'laboratory', 1)
> insert into @.table values (2, 'chemistry', 2)
> insert into @.table values (2, 'chemistry', 5)
> insert into @.table values (2, 'laboratory', 2)
> insert into @.table values (2, 'laboratory', 4)
> insert into @.table values (3, 'scientist', 1)
> insert into @.table values (3, 'scientist', 2)
> select * from @.table
>
>
>|||This is division, the usual approach is:
SELECT id
FROM ( SELECT id, COUNT( DISTINCT searchnum)
FROM tbl
GROUP BY id ) D ( id, num )
WHERE ( SELECT COUNT(DISTINCT searchnum)
FROM tbl ) = num ;
Anith

Groupby Unions

hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bonoWhat about creating a view with the union statement?
On the view you can perform the group by on the whole result set.

Sneaky Pie|||use pubs

select U.city, count(U.city)
from
(
select city from authors
union all
select city from publishers
) U
group by u.city|||Hi HanafiH,

your solution is much better than mine, I didn't know that this could work. So I've learned something new.

Thanks for that

Sneaky Pie

group by query

Hi

I have the following query:

select tbl.id, nvl(sum(x),0) as A, nvl(count(y),0) as B from ... where tbl.id in (1,2,3) group by tbl.id

And here are the results I am currently seeing:

tbl.id A B
1 232 343
3 3434 343

The table where tbl.id=2 has 0 for both columns so it does not show up.
How can I modify the query so that I will get a result set as the following:

tbl.id A B
1 232 343
2 0 0
3 3434 343> "The table where tbl.id=2 has 0 for both columns so it does not show up"

i'm having trouble believing this

your query must return a row for the 2 group, regardless of whether the 2 row(s) have 0 in the x and/or y columns, or nulls, or anything else

if at least one row for 2 exists, there will be a 2 group in the results, unless it's eliminated by a HAVING clause

rudy
http://r937.com/