SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 05:07 PM
| ||||
| ||||
 Sum Problem in Calculated Member In Analysis Services I want to create calculated member which will do the sum of "DollarAmount" depeneds on value in "NewItem" field. If "NewItem" is 1 then take "DollarAmount" else take 0.0 I want to write condition to get sum in calculated member. In following example "ItemId" 2 and 3 is satiesfying condition(NewItem =1) and it should give 2000 + 1000 =3000 as "NewDollarAmount" (calcualted member). This calculated member(will be in Measure Dimension) should give correct sum amount on drilldown level (when doing drilldown Time dimension against Mesures Dimesion) Following is my ItemFact table ItemId DollarAmount ItemDate NewItem -------------------- ---------------------------------------1 1000.0 1/1/99 0 2 2000.0 1/1/99 1 3 1000.0 2/1/00 1 4 3000.0 5/3/00 0 Pls let me know the soltion ASAP.Thanks Regards Prashant *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!      |
|
02-27-2008, 05:07 PM
| ||||
| ||||
| Re: Sum Problem in Calculated Member In Analysis Services One solution to this is to use unary operators. You can create a calculated column in a view that uses the unary operator + or ~ when the NewItem is 1 or 0 respectively. If you can't create a view, you should be able to put a SQL case expression to do this translation inside the unary operator column property. - Matt Carroll -- This posting is provided "AS IS" with no warranties, and confers no rights. "Prashant P" <prashant.parab@tatainfotech.com> wrote in message news:e8jcOGBXDHA.1004@TK2MSFTNGP12.phx.gbl... > > I want to create calculated member which will do the sum of > "DollarAmount" depeneds on value in "NewItem" field. > > If "NewItem" is 1 then take "DollarAmount" else take 0.0 > > I want to write condition to get sum in calculated member. > In following example "ItemId" 2 and 3 is satiesfying condition(NewItem > =1) and it should give 2000 + 1000 =3000 > as "NewDollarAmount" (calcualted member). > > This calculated member(will be in Measure Dimension) should give correct > sum amount on drilldown level (when doing drilldown Time dimension > against Mesures Dimesion) > > Following is my ItemFact table > > ItemId DollarAmount ItemDate NewItem > -------------------- ---------------------------------------1 > 1000.0 1/1/99 0 > 2 2000.0 1/1/99 1 > 3 1000.0 2/1/00 1 > 4 3000.0 5/3/00 0 > > > Pls let me know the soltion ASAP.Thanks > > Regards > Prashant > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! |
Linear Mode
