Grouping question

Hi guys,

on my report i have 3 separate matrices with monthly sales results, each gives a different view on the same data. This all works nicely, but the customer wants these matrices grouped by product (the product is a parameter of the report, they can choose several or all products to report on).

Exactly what would be the best way to achieve this grouping? It seems i can't house them within a table, and i can't put them in another matrix without running into scope errors.

Thanks!!!

sluggy

Sounds like those three matrics are using the same dataset. In that case, you should be able to remove the dataset set on these matrices respectively. Add a list that includes the three matrices, set the dataset on the list, and add a group on the list by the product.|||

Thanks Fang,

that did the trick nicely.

One further (possibly stupid) question: how could i then have a set of matrices at the end with the totals (ie the aggregations across all products)? Should i just have a copy of the original matrices and make them invisible if there was only one product selected? Or is there a more proper or cooler way to do it?

Many thanks,

sluggy

|||

There are multiple ways to do this:

1. Add a copy of the original matrices outside of the list.

2. Use a matrix instead of list to host the three matrices, add a matrix row group by product, and enable subtotal on the row group. This way you will automatically get the matrics across all products in the subtotal row.

|||

Excellent, thanks!!

sluggy

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 based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).