איך לא לחלק באפס בדו"ח SSRS

By | 8 בפברואר 2012

נוסחאות מחושבות ב- SSRS אינן אוהבות חלוקה באפס.

דו"ח נפוץ ב- SSRS מראה את עמידתינו ביעד מסויים. מגרסת 2008 R2 ומעלה יש לנו גם אובייקטים גראפיים יפים להצגת העמידה או אי עמידה ביעד. כדי לחשב את אחוז העמידה ביעד צריך לעשות פעולת חילוק פשוטה של כמות נמכרת לדוגמה לעומת יעד המכירות. אך לא תמיד מכניסים יעדים לכל החנויות באותו זמן ויכול להיות מצב בו לחלק מהחנויות אין עדיין יעד. במצב זה מתנהג SSRS בצורה תמוהה.

לצורך הדוגמה השתמשתי בבסיס הנתונים ContosoRetailDW. ניתן להוריד בסיס נתונים זה מכאן. בעיני הוא הרבה יותר נוח מ- AdventureWorks.

איפסתי את היעדים של החנות באמסטרדם:

-- מחיקת היעדים לחנות מספר 240 , אמסטרדםupdate  [dbo].[FactSalesQuota] set SalesAmountQuota=0 , SalesQuantityQuota=0 where storekey=240 /* Contoso Amsterdam Store */and ScenarioKey=2

 

ולאחר מכן בניתי שאילתא אשר לחמש חנויות תציג את ערך המכירות לעומת יעד המכירות:

select     StoreName    , sum(SalesAmount) as SalesAmount    , sum(SalesAmountQuota) as SalesAmountQuota

from (SELECT        s.StoreName      , SalesAmount    , 0 SalesAmountQuota  FROM dbo.FactSales f inner join dbo.DimStore s on f.StoreKey=s.StoreKey  where s.StoreKey between 240 and 245 /* רק כדי להקטין מעט את גודל הדוח */

union all 

 select s.StoreName    , 0 as SalesAmount    , SalesAmountQuota from dbo.FactSalesQuota F inner join dbo.DimStore s on f.StoreKey=s.StoreKey where f.ScenarioKey=2 /* Budget */ and   s.StoreKey between 240 and 245 /* רק כדי להקטין מעט את גודל הדוח */) H group by     StoreName

 

השימוש ב-  Union ולא ב- Join נחוץ כאן כיוון שיכול להיות מצב בו ישנה חנות ללא יעדים ומנגד יכול להיות שהגדרנו יעדים לחנות שעדיין לא היו לה מכירות כלל.

תוצאת השאילתא הינה:

image

דו"ח שבניתי באמצעות שאילתא זו מכיל את החישוב כמות חלקי יעד לצורך בדיקת אחוז עמידה ביעד:

image

 

לאחר הרצת הדו"ח קיבלתי את התוצאה המכוערת הבאה עבור חנות אמסטרדם לה לא הוגדר יעד:

image

שניתי את הנוסחה לביטוי:

=  iif(Fields!SalesAmountQuota.Value=0,"",

Fields!SalesAmount.Value

/

Fields!SalesAmountQuota.Value)

כדי להציג תא ריק במידה ולא הוגדר יעד לאחת החנויות. להפתעתי זה לא שינה מאומה. נסיונות נוספים בשימוש הפונקציות IsNumeric, IsNothing לא הועילו.

מחקר קטן שעשיתי גילה את העובדה המדהימה הבאה: SSRS מחשב את כל חלקיו של משפט ה- IF כולל החלקים שאותם לא יצטרך להציג כלל. כלומר. במידה והיעד (Quota) הוא אפס, עדיין יחשב SSRS גם את הביטוי amount/quota אותו אין כל צורך לחשב במקרה כזה. כיוון שהוא לא מצליח לחשב את הביטוי שמלכתחילה לא היה צורך לחשבו, הוא משאיר אותנו עם הודעות השגיאה.

שני פתרונות אפשריים:

1. ביצוע החישוב ברמת שאילתת ה- SQL. זה לא תמיד נוח. במיוחד מאז גרסת R2 בה נכנס לשימוש עניין ה- Report parts. אנו עשויים לרצות שהחישוב יהיה ברמת השעון בדו"ח למשל.

2. כתיבת הביטוי המוזר הבא:

=  iif(Fields!SalesAmountQuota.Value=0,"",Fields!SalesAmount.Value/iif(Fields!SalesAmountQuota.Value=0,1,Fields!SalesAmountQuota.Value))

 

כלומר, אם היעד הוא 0 אז שים שום דבר ("") אם היעד אינו אפס אז חלק את המכירות ביעד בתנאי שהיעד אינו אפס אחרת חלק באחד (לאחד הזה אין משמעות ואפשר לשים שם כל מספר, ממילא אם היעד אפס חישוב זה לא יוצג).

תוצאת הדו"ח המתוקן:

image

Share

כתיבת תגובה

האימייל לא יוצג באתר.