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 columns rather than rows using table control?

Is there a way to transform the table object to display group data in columns
instead of rows? Here is my example:
Report services table can do this when grouping on YEAR
[1 GROUP Header (YEAR)
[Header
[ BODY Parameter1 Parameter 2 Parameter 3
[FOOTER
[1 GROUP Footer (SUM)
Example
Year 2000
Mike John Mary
5 1 4
5 2 2
SUM 10 3 6
Year 2001
Mike John Mary
1 6 5
2 2 2
SUM 3 8 7
What I want is this:
[ Group Header ] [Table Header] [DATA] [Table Footer] [Group
Footer]
YEAR Parameter 1
SUM
Parameter 2
Parameter 3
2000 SUM 2001 SUM
Mike 5 5 10 1 2 3
John 1 2 3 6 2 8
Mary 4 2 6 5 2 7
So the idea is to group by Year but display the SUMs in a column not in a
row. I just can't figure out how to use the Matrix control, I want to use the
table control functionality but with column output.
Thanksyou can use a matrix to do just that
"Ramez" wrote:
> Is there a way to transform the table object to display group data in columns
> instead of rows? Here is my example:
> Report services table can do this when grouping on YEAR
> [1 GROUP Header (YEAR)
> [Header
> [ BODY Parameter1 Parameter 2 Parameter 3
> [FOOTER
> [1 GROUP Footer (SUM)
> Example
> Year 2000
> Mike John Mary
> 5 1 4
> 5 2 2
> SUM 10 3 6
> Year 2001
> Mike John Mary
> 1 6 5
> 2 2 2
> SUM 3 8 7
> What I want is this:
> [ Group Header ] [Table Header] [DATA] [Table Footer] [Group
> Footer]
> YEAR Parameter 1
> SUM
> Parameter 2
> Parameter 3
> 2000 SUM 2001 SUM
> Mike 5 5 10 1 2 3
> John 1 2 3 6 2 8
> Mary 4 2 6 5 2 7
> So the idea is to group by Year but display the SUMs in a column not in a
> row. I just can't figure out how to use the Matrix control, I want to use the
> table control functionality but with column output.
> Thanks

Group By question

Hello!
I would like to get my result grouped by 15 min or 30 min instead of
1 min like I have now, see below.
Can anyone help me ? Any ideas?
select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
from s_case (nolock)
where CAS_USG_PK_OPEN=255543
AND day(CAS_DT_OPE)=day(getdate())
AND month(CAS_DT_OPE)=month(getdate())
AND year(CAS_DT_OPE)=year(getdate())
GROUP BY datepart(mi,CAS_DT_OPE)
order by 2
****Result*******************
2 2006-04-27 06:52:54.783
1 2006-04-27 07:22:54.973
1 2006-04-27 07:23:56.493
2 2006-04-27 08:05:04.217
1 2006-04-27 08:07:41.723
1 2006-04-27 08:27:04.600
1 2006-04-27 08:29:37.840
1 2006-04-27 08:33:13.347
1 2006-04-27 08:39:23.620
1 2006-04-27 08:41:35.240
1 2006-04-27 09:12:00.930
2 2006-04-27 09:13:06.540
1 2006-04-27 09:16:34.030
1 2006-04-27 09:18:00.717
2 2006-04-27 09:26:51.957
1 2006-04-27 09:31:31.507
1 2006-04-27 09:53:32.873
regards
MikeFor group by 15 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /15
For group by 30 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /30|||Below is an example using an interval table variable. This technique has
the benefit of reporting intervals even when no related data exists. If
this is something you do often, consider creating a permanent table for this
purpose:
SET NOCOUNT ON
DECLARE @.IntervalInSeconds int
DECLARE @.StartTime datetime
DECLARE @.EndTime datetime
---
-- **** specify interval size and range below ***
---
SET @.IntervalInSeconds = 900 -- 15 minutes
SET @.StartTime = '20060427 00:00:00'
SET @.EndTime = '20060428 00:00:00'
---
-- create and load Intervals table
DECLARE @.Intervals TABLE
(
StartTime datetime NOT NULL
PRIMARY KEY,
EndTime datetime NOT NULL
)
WHILE @.StartTime < @.EndTime
BEGIN
INSERT INTO @.Intervals VALUES(@.StartTime, DATEADD(ss,
@.IntervalInSeconds, @.StartTime))
SET @.StartTime = DATEADD(ss, @.IntervalInSeconds, @.StartTime)
END
SELECT
COUNT(*) AS Count_Case,
i.StartTime AS IntervalStart,
MIN(CAS_DT_OPE) AS TIME_DATE
FROM s_case (NOLOCK)
JOIN @.Intervals i ON
s_case.CAS_DT_OPE >= i.StartTime
AND s_case.CAS_DT_OPE < i.EndTime
WHERE
CAS_USG_PK_OPEN=255543
AND CAS_DT_OPE >= CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)
AND CAS_DT_OPE < CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) + 1
GROUP BY
i.StartTime
ORDER BY 3
Hope this helps.
Dan Guzman
SQL Server MVP
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1146139098.374595.30350@.v46g2000cwv.googlegroups.com...
> Hello!
> I would like to get my result grouped by 15 min or 30 min instead of
> 1 min like I have now, see below.
> Can anyone help me ? Any ideas?
> select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
> from s_case (nolock)
> where CAS_USG_PK_OPEN=255543
> AND day(CAS_DT_OPE)=day(getdate())
> AND month(CAS_DT_OPE)=month(getdate())
> AND year(CAS_DT_OPE)=year(getdate())
> GROUP BY datepart(mi,CAS_DT_OPE)
> order by 2
> ****Result*******************
> 2 2006-04-27 06:52:54.783
> 1 2006-04-27 07:22:54.973
> 1 2006-04-27 07:23:56.493
> 2 2006-04-27 08:05:04.217
> 1 2006-04-27 08:07:41.723
> 1 2006-04-27 08:27:04.600
> 1 2006-04-27 08:29:37.840
> 1 2006-04-27 08:33:13.347
> 1 2006-04-27 08:39:23.620
> 1 2006-04-27 08:41:35.240
> 1 2006-04-27 09:12:00.930
> 2 2006-04-27 09:13:06.540
> 1 2006-04-27 09:16:34.030
> 1 2006-04-27 09:18:00.717
> 2 2006-04-27 09:26:51.957
> 1 2006-04-27 09:31:31.507
> 1 2006-04-27 09:53:32.873
> regards
> Mike
>|||Didn't do the trick...
/Mike|||Thanks Dan that did the trick!!
/Mike|||On 27 Apr 2006 04:58:18 -0700, McA wrote:

>Hello!
>I would like to get my result grouped by 15 min or 30 min instead of
>1 min like I have now, see below.
>Can anyone help me ? Any ideas?
GROUP BY DATEDIFF(minute, '20060101', CAS_DT_OPE) / 15
Hugo Kornelis, SQL Server MVP|||Thanks Hugo that worked 2!!
And very easy to!!
/McA

Group by in a view can this be used ?

In a complex view the group by gives strange (wrong) results.
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A