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
No comments:
Post a Comment