asked
Average in SSRS
I need a new column which is an average price of the three columns before it - the average needs to stay in the scope of the item...but when I try it calculates the average of the sum - not the item level avg.. Price 1 Price 2 Price 3 AVG Item1 1.00 2.00 3.00 2.00 Item 2 3. 00 3.00 6.00 4.00 I've Tried this =AVG((PRC1) +(PRC2)+(PRC3)) - but it gives me like the average of the grand total of all the projects... --------------------------------------------------------------------------------
This work - I have it embedded in the sql script which is my stored procedure. it works if i run the sql but if i call the stored procedure - it does not generate the avg columns - can you help? create table #temp ( ndc_num char(11), cmpt_nam_id bigint, prc_dt datetime) insert into #temp select ndc_num, ci.cmpt_nam_id, max(prc_dt) prc_dt from sms_datamart.dbo.t_dm_cmpt_prc p join sms_datamart.dbo.t_dm_cmpt_info ci on ci.cmpt_nam_id = p.cmpt_nam_id where ci.cmpt_nam_id in (1,47,17) group by ndc_num, ci.cmpt_nam_id create index tmp_idx on #temp(ndc_num,cmpt_nam_id,prc_dt) /* anda = 1 abc = 47 cardnial = 17 */ SELECT i.em_item_num, i.ndc_num, splr_acct_nam, i.sell_dscr, i.DM_SDC * 1.0526 os_main, (i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15) net_ssf, --anda cia.cmpt_nam, ta.ndc_num anda_ndc, pa.prc_dt as anda_prc_dt, pa.cmpt_prc as anda_prc, (pa.cmpt_prc) - ((pa.cmpt_prc)*.05) as Anda_Net, (i.DM_SDC * 1.0526) - (pa.cmpt_prc) as OS_VS_INVC_ANDA, ((i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15) - ((pa.cmpt_prc) - ((pa.cmpt_prc)*.05))) as OS_VS_NET_ANDA, --abc cib.cmpt_nam as abc, tb.ndc_num abc_ndc, pb.prc_dt as abc_prc_dt, pb.cmpt_prc as abc_cmpt_prc, (pb.cmpt_prc) - ((pb.cmpt_prc)*.06) as ABC_Net, (i.DM_SDC * 1.0526) - (pb.cmpt_prc) as OS_VS_INVC_ABC, ((i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15) - ((pb.cmpt_prc) - ((pb.cmpt_prc)*.06))) as OS_VS_NET_ABC, --cardnial cic.cmpt_nam as card, tc.ndc_num card_ndc, pc.prc_dt as card_prc_dt, pc.cmpt_prc as card_prc, (pc.cmpt_prc) - ((pc.cmpt_prc)*.12) as CARD_Net, (i.DM_SDC * 1.0526) - (pc.cmpt_prc) as OS_VS_INVC_CARD, ((i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15) - ((pc.cmpt_prc) - ((pc.cmpt_prc)*.12))) as OS_VS_NET_CARD, --average case when (i.DM_SDC * 1.0526) is null --1 and (pa.cmpt_prc) is null --2 and (pb.cmpt_prc) is null --3 and (pc.cmpt_prc) is null --4 then null else (isnull((i.DM_SDC * 1.0526), 0.0) + isnull((pa.cmpt_prc), 0.0) + isnull((pb.cmpt_prc), 0.0) +isnull((pc.cmpt_prc), 0.0) ) / ( case when (i.DM_SDC * 1.0526) is null then 0 else 1 end + case when (pa.cmpt_prc) is null then 0 else 1 end + case when (pb.cmpt_prc)is null then 0 else 1 end + case when (pc.cmpt_prc) is null then 0 else 1 end ) end AvgInvoicePrice, case when (i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15) is null --1 and (pa.cmpt_prc) - ((pa.cmpt_prc)*.05) is null --2 and (pb.cmpt_prc) - ((pb.cmpt_prc)*.06) is null --3 and (pc.cmpt_prc) - ((pc.cmpt_prc)*.12) is null --4 then null else (isnull((i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15), 0.0) + isnull((pa.cmpt_prc) - ((pa.cmpt_prc)*.05), 0.0) + isnull((pb.cmpt_prc) - ((pb.cmpt_prc)*.06), 0.0) + isnull((pc.cmpt_prc) - ((pc.cmpt_prc)*.12), 0.0) ) / ( case when (i.DM_SDC * 1.0526) - ((i.DM_SDC * 1.0526) * .15)is null then 0 else 1 end + case when (pa.cmpt_prc) - ((pa.cmpt_prc)*.05) is null then 0 else 1 end + case when (pb.cmpt_prc) - ((pb.cmpt_prc)*.06)is null then 0 else 1 end + case when (pc.cmpt_prc) - ((pc.cmpt_prc)*.12) is null then 0 else 1 end ) end AvgNetPrice FROM reference.dbo.t_iw_em_item i left join (sms_datamart.dbo.t_dm_cmpt_prc pa join sms_datamart.dbo.t_dm_cmpt_info cia on cia.cmpt_nam_id = pa.cmpt_nam_id join #temp ta on cia.cmpt_nam_id = ta.cmpt_nam_id and pa.ndc_num = ta.ndc_num and pa.prc_dt = ta.prc_dt and cia.cmpt_nam_id = 1) on i.NDC_NUM = pa.NDC_NUM left join (sms_datamart.dbo.t_dm_cmpt_prc pb join sms_datamart.dbo.t_dm_cmpt_info cib on cib.cmpt_nam_id = pb.cmpt_nam_id join #temp tb on cib.cmpt_nam_id = tb.cmpt_nam_id and pb.ndc_num = tb.ndc_num and pb.prc_dt = tb.prc_dt and cib.cmpt_nam_id = 47) on i.NDC_NUM = pb.NDC_NUM left join (sms_datamart.dbo.t_dm_cmpt_prc pc join sms_datamart.dbo.t_dm_cmpt_info cic on cic.cmpt_nam_id = pc.cmpt_nam_id join #temp tc on cic.cmpt_nam_id = tc.cmpt_nam_id and pc.ndc_num = tc.ndc_num and pc.prc_dt = tc.prc_dt and cic.cmpt_nam_id = 17) on i.NDC_NUM = pc.NDC_NUM WHERE i.os_main_flg = 'y' --and i.DM_SDC > 0 --and i.DM_SSF > 0 ORDER BY i.em_item_num
@siera_gld The only reason it does not generate new columns if you did not actually altered the procedure yet, so it still has the original script. You can check the current source code of the proc from SSMS. Once the procedure is altered, you will see newly added to your select columns.
@Oleg, @siera_gld, sometimes it happens, that when you alter a procedure which you then call from SSRS, BIDS doesn't refresh the columns. Especially in SSRS 2005. You alter procedure in DB, and in BIDS you right-click dataset, choose refresh and nothing happens. Then You have to close and reopen BIDS and then everything should work correctly. This happen several times to me.
If you need average of the three columns on a item level, then put simple expresion `=((PRC1)+(PRC2)+(PRC3)) / 3` to the new column and you have an average. :-) Update from the commnet: If there can be nulls in the PRC1, PRC2, PRC3 columns and you do not want to calculate with nulls, then you cad add below expression for calculation `=(IIF(IsNothing(PRC1),0,PRC1) + IIF(IsNothing(PRC2),0,PRC2) + IIF(IsNothing(PRC3),0,PRC3)) / IIF(IsNothing(PRC1),0,1) + IIF(IsNothing(PRC2),0,1) + IIF(IsNothing(PRC3),0,1)` If all 3 columns can be null and you want to avoid division by zero then a slight modification can be done: `=(IIF(IsNothing(PRC1),0,PRC1) + IIF(IsNothing(PRC2),0,PRC2) + IIF(IsNothing(PRC3),0,PRC3)) / IIF(IsNothing(PRC1) AND IsNothing(PRC2) AND IsNothing(PRC3), 1, IIF(IsNothing(PRC1),0,1) + IIF(IsNothing(PRC2),0,1) + IIF(IsNothing(PRC3),0,1))` This will result in calculation 0/1 = 0 if all three column will be null.
Yeah, then here you go: `=(IIF(IsNothing(PRC1),0,PRC1) + IIF(IsNothing(PRC2),0,PRC2) + IIF(IsNothing(PRC3),0,PRC3)) / (IIF(IsNothing(PRC1),0,1) + IIF(IsNothing(PRC2),0,1) + IIF(IsNothing(PRC3),0,1))` Or use the @Oleg proposal on the DB Layer if you are able alter the query. :-)
@Pavel Pawlowski Your way is cleaner and much faster, I did not read the question carefully enough to see that it is SSRS related. If you move the expression to your answer from the comment, the answer will be complete and ready to be accepted (after the possibility of a division by zero is handled should all prices be nothing) :)
I hope I understand the question correctly. If none of the prices is null then Pavel's answer does the trick. However, if one of the prices is null then the result should be the sum of 2 not null prices divided by 2, and if 2 prices are null then the result should be whatever the price is not null. Here is the sample which should work: select Item, Price1, Price2, Price3, case when Price1 is null and Price2 is null and Price3 is null then null else (isnull(Price1, 0.0) + isnull(Price2, 0.0) + isnull(Price3, 0.0)) / ( case when Price1 is null then 0 else 1 end + case when Price2 is null then 0 else 1 end + case when Price3 is null then 0 else 1 end ) end AvgPrice from dbo.the_table; The part reading **when Price1 is null and Price2 is null and Price3 is null then null** is there to avoid division by zero. P.S. This means that the activity has been outsourced to the database, which is probably undesirable. The logic should be pretty straightforward to translate to VB expression though. Oleg
