Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get a report looks some thing like this which is an example from bi manager.
| Orders/Mth 2016 | Under250g | C250to500g | C500to750g | c750gto1Kg | c1Kgto125Kg | c125Kgto1_5Kg | c1_5kgto175Kg | c175kgto2Kg | Over2kg |
| 1 | 2,821 | 1,696 | 946 | 525 | 268 | 156 | 94 | 81 | 286 |
| 2 | 2,679 | 1,342 | 732 | 384 | 228 | 124 | 70 | 84 | 233 |
| 9 | 1,393 | 901 | 555 | 340 | 204 | 111 | 66 | 60 | 152 |
| 10 | 1,542 | 1,028 | 655 | 343 | 214 | 93 | 58 | 51 | 129 |
| I |
I have used below query
Select
ih.[Order GuID],
sum(il.[Gross Weight]) as ShipWeight,
cn.[Actual Packaging], itm.[Description],
count(il.No_) as coutn
FROM [TBW_BI].[dbo].[dreams$Sales Invoice Line] as il
inner join [TBW_BI].[dbo].[dreams$Sales Invoice Header] as ih
on il.[Document No_]=ih.No_
inner join [TBW_BI].[dbo].[dreams$Consignment] as cn
on ih.[Consignment ID]=cn.[id]
left join [TBW_BI].[dbo].[dreams$Item] as itm
on cn.[Actual Packaging]=itm.[No_]
where ih.[Ship-to Country_Region Code]='HK'
and ih.[Shipment Date]>='2016-10-01' and ih.[Shipment Date]<'2017-03-31'
group by ih.[Order GuID],itm.Description, cn.[Actual Packaging]
to get table like below
now how can I classify them or group them as per the request. Got to get how many orders which count column are under 250 grams , and how many are under 250 to 500 grams etc..??
I have created new straight table took only orderguid to used dimensions and under add calculated dimension i am using this. correct?
Would you be able to share what you have typed in your dimension here? as text and not as an image
