Crystal Reports 2013 กับ ปัญหาเส้นผมบังภูเขา

เขียนโดย พงษ์พันธ์ ศิวิลัย

พงษ์พันธ์

 

ห่างหายไปเสียนาน ในที่สุดก็มีเรื่องมาเขียนบทความแล้วครับ ทั้งนี้คงจะเป็นเพราะ ผมใช้ Crystal Reports คล่องแล้ว อีกทั้ง Tips & Trick ต่าง ๆ ผมได้ใส่ไว้ในหนังสือ “สร้างรายงานอย่างมืออาชีพด้วย Crystal Reports 2013” ค่อนข้างเยอะที่เดียว จนผมคิดว่า … ไม่น่าจะมีคำถามเข้ามาอีก

                แต่ที่ไหนได้! … ยังมีคำถามที่ผมนึกไม่ถึงอยู่อีก คราวนี้รวบรวมมาได้ 2 คำถาม ดังนี้

  1. ปัญหาผลรวมของทุก Sub Total ไม่เท่ากับ Grand Total
  2. ปัญหาจากค่า NULL

หลายคนอาจจะเคยมีประสบการณ์กับปัญหาเส้นผมบังภูเขากันมาบ้างไม่มากก็น้อย ซึ่งปัญหาในข้างต้น หากมองผิวเผินแล้ว ก็ไม่น่าจะมีอะไรยาก ทว่า ผู้ที่ถามกับผม พยายามแก้ปัญหาอยู่หลายสัปดาห์ทีเดียว หลังจากผมบอกสาเหตุและแนวทางแก้ปัญหาให้แล้ว เธอก็ถึงบางอ้อแทบจะในทันที

ผมต้องขอบคุณ ผู้ถามมากครับ ที่ช่วยให้ผมได้หัวเขียนบทความและมีโอกาสได้เผยแพร่ความรู้นี้ให้กับคนอื่น ๆ ด้วย

ปัญหาผลรวมของทุก Sub Total ไม่เท่ากับ Grand Total

                ตอนแรกที่ได้ยินคำถามนี้ ผมถึงกับนิ่งไปชั่วขณะ เนื่องจากเรื่องแบบนี้ไม่น่าจะเกิดขึ้น แต่เมื่อได้เข้าดูก็พบว่า เป็นอย่างนั้นจริง ๆ  เพื่อให้สามารถอธิบายได้ง่ายขึ้น ผมจึงขอยกตัวอย่างเป็นกรณีศึกษา มีรายละเอียด ดังต่อไปนี้

  • รายงานเรียกใช้ข้อมูลจากตารางชื่อ SalesOrderDetail ในฐานข้อมูล ชื่อ AdventureWorks บน Server ชื่อ SQL2014 ( เป็นฐานข้อมูล SQL Server 2014 ) ซึ่งมีข้อมูลดังรูปที่ 1

 

รูปที่ 1 แสดงข้อมูลในตาราง Sales.SalesOrderDetail

  • ในรายงานใช้ตาราง SalesOrderDetail โดยใน Section Detail วางฟิลด์ SalesOrderDetailID , OrderQty , UnitPrice , LineTotal และจัดกลุ่มตามฟิลด์ SalesOrderID พร้อมทั้งเพิ่ม Summary Field เพื่อหา Sub Total และ Grand Total นอกจากนี้ผู้เขียนใส่เงื่อนไขให้แสดงเฉพาะค่า SalesOrderID ระหว่าง 43659 ถึง 43700 เพื่อให้ข้อมูลไม่เยอะจนเกินไป ดังรูป

 

รูปที่ 2 แสดงข้อมูลในรายงานหน้าแรก

รูปที่ 3 แสดงข้อมูลในรายงานหน้าสุดท้าย

จากรูปที่ 2 และรูปที่ 3 จะเห็นว่า ก็เป็นรายงานธรรมดาทั่วไป ไม่น่าจะมีอะไรน่าสนใจ … ถ้าหากคิดเช่นนั้นถือว่า ผิดถนัดครับ

                ผลรวม Grand Total ของรายงานเท่ากับ 500,405.93 หากนำทุก Sub Total มารวมจะได้ค่าเท่ากับ 500,405.91 ซึ่งจะต่างกันอยู่ 0.02 ( ลองหยิบเอาเครื่องคิดเลขมากดดูก็ได้นะครับ จะเห็นค่ามันต่างกันอยู่ 0.02 จริง ๆ )

                ค่าที่ต่างกัน 0.02 แม้จะน้อย แต่อย่านึกว่า ไม่เป็นไรนะครับ!

                ในระบบบัญชีหรือระบบงานที่เกี่ยวข้องกับเงิน ผลรวมที่ออกมาแตกต่างกัน แม้จะเป็นแค่สตางค์แดงเดียว ก็สามารถสร้างปัญหาปวดเศียรเวียนเกล้าให้ได้อย่างไม่น่าเชื่อเลยล่ะ

ตัวอย่างของผมใช้ข้อมูลไม่มากนักยังได้ผลรวมต่างกัน 0.02 ถ้าหากข้อมูลเยอะกว่านี้ล่ะ ค่าที่ออกมาจะต่างกันมากแค่ไหน

                ตอนนี้ผู้ท่านทุกท่านเข้าใจถึงปัญหาที่เกิดขึ้นแล้วนะครับ!

สาเหตุของปัญหา

   ก่อนอื่นขอออกตัวเลยนะครับ ปัญหานี้ไม่ใช่ข้อผิดพลาดหรือบัคของโปรแกรม Crystal Reports 2013 แต่อย่างใด

                แต่ปัญหานี้เกิดจาก … การปัดเศษทศนิยมในการหาผลรวมต่างหาก

  ลองเข้ามาดูข้อมูลในตารางกันก่อนนะครับ โดยผมจะเน้นที่ฟิลด์ LineTotal

 

รูปที่ 4 แสดงข้อมูลในตาราง Sales.SaleOrderDetail

จากรูปที่ 4 จะเห็นว่า ข้อมูลในฟิลด์นั้น เป็นเลขทศนิยม 4 ตำแหน่ง ดังนั้น เมื่อหาผลรวมย่อมจะต้องเกิดการปัดเศษอย่างไม่ต้องสงสัย

                หลายท่านอาจจะเกิดคำถามตามมา … แม้จะมีการปัดเศษ แต่ผลรวม Grand Total และ Sub Total ก็มีการปัดเศษเหมือนกัน ทำไมเมื่อเอาทุก Sub Total มาหาผลรวม แล้วผลลัพธ์ไม่เท่ากับ Grand Total ล่ะ!

                ก่อนจะเฉลยคำตอบ ขออธิบายกระบวนการทำงานของ Crystal Reports ให้เข้าใจก่อนนะครับ

  • การหาผลรวมในแต่ละกลุ่ม ( Sub Total ) จะนำข้อมูลทุก Row ในกลุ่มนั้นมาหาผลรวม เมื่อได้ผลลัพธ์แล้ว จึงค่อยปัดเศษก่อนจะแสดงผลเป็น Sub Total
  • การหาผลรวมของทั้งรายงาน ( Grand Total ) จะนำข้อมูลของทุก Row มาหาผลรวม เมื่อได้ผลลัพธ์แล้ว จึงค่อยปัดเศษก่อนจะแสดงผลเป็น Grand Total

ตอนนี้คำตอบคงแจ้งชัดกับทุกท่านแล้วนะครับ … ในขั้นตอนปัดเศษของ Sub Total ย่อมจะมีการปัดขึ้น – ปัดลง ย่อมจะส่งผลให้ เมื่อนำ Sub Total มารวมกันแล้ว ได้ค่าไม่เท่ากับ Grand Total

แนวทางแก้ปัญหา

                เมื่อทราบถึงสาเหตุของปัญหาแล้ว ขั้นตอนต่อไป คือ การแก้ไขปัญหา ซึ่งผมแบ่งออกเป็น 2 แนวทาง ประกอบด้วย

  1. ในเมื่อการปัดเศษเป็นสาเหตุ ดังนั้น ก็ไม่ต้องให้มีการปัดเศษเกิดขึ้น

แนวทางนี้ เป็นการแก้ปัญหาที่ค่อนข้างตรงไปตรง แบบกำปั้นทุบดิน

เมื่อกำหนดให้ข้อมูลในรายงานให้แสดงทศนิยมทุกตำแหน่ง โดยไม่มีการปัดเศษ ย่อมไม่เกิดปัญหาขึ้นอย่างแน่นอน แต่ทว่า ใช้ไม่ได้กับทุกงาน เนื่องจาก หากเจอบางรายงานที่มีข้อกำหนดให้แสดงทศนิยม 2 เท่านั้น ปัญหานี้ก็จะตามหลอกหลอนอีกเหมือนเดิม

  1. เขียนฟอมูล่า เพื่อหาค่า Grand Total

แนวทางนี้ เป็นการแก้ปัญหาที่ยืดหยุ่นที่สุด เนื่องจากสามารถประยุกต์ได้ตามสถานการณ์  ดังนั้น ผมจะขอเลือกใช้แนวทางนี้ในการปัญหานะครับ

ขั้นตอนและวิธีการแก้ปัญหา

  1. เริ่มจากเข้าไปสร้างฟอมูล่าฟิลด์ ชื่อ eval_grand แล้วเขียนสูตร ดังนี้

 

 

รูปที่ 5 แสดงสูตรในฟอมูล่า eval_grand

จากสูตรข้างต้น เป็นการหาผลรวม Sub Total ในแต่ละกลุ่ม แล้วมาปัดเศษให้เหลือทศนิยม 2 ตำแหน่ง ก่อนจะนำไปบวกกับค่าในตัวแปร เพื่อเป็นผลรวมสะสม

หมายเหตุ : สูตรในตัวอย่างนั้น ผมจงใจเขียนให้ยาวขึ้น เพื่อให้เข้าใจง่าย แต่สำหรับผู้เชี่ยวชาญหรือคนที่เขียนฟอมูล่าคล่อง ๆ สามารถเขียนแค่บรรทัดเดียวก็ได้ผลลัพธ์ที่ต้องการแล้ว

       2.นำฟอมูล่าฟิลด์ชื่อ eval_grand ไปวางที่ Section Group Footer ดังรูป

 

 

รูปที่ 6 แสดงการวางฟอมูล่าฟิลด์ในรายงาน

 

      3. มาที่แท็บ Preview เพื่อดูลัพธ์ของรายงาน ผลลัพธ์จะแสดง ดังรูป

รูปที่ 7 แสดงผลลัพธ์ของรายงาน

      4. จากรูปที่ 7 จะเห็นว่า ผลรวมสะสมของกลุ่มสุดท้ายได้ค่า Grand Total ที่เราต้องการแล้ว ดังนั้น ขั้นตอนต่อไป เป็นการเรียกค่าในตัวแปรไปแสดงเป็น Grand Total ของรายงาน โดยสร้างฟอมูล่าฟิลด์ใหม่ ชื่อ ShowGrand และเขียนสูตร ดังรูป

 

                    WhilePrintingRecords ;

                    currencyvar grandSale ;

รูปที่ 8 แสดงสูตรในฟอมูล่า ShowGrand

      5. จากนั้น นำฟอมูล่าชื่อ ShowGrand ไปวางที่ Section Report Footer แทนที่ Summary Field เดิม พร้อมทั้งกำหนดรูปแบบการแสดงผลตามต้องการ นอกจากนี้ให้ทำการ Suppress ฟอมูล่าฟิลด์ชื่อ eval_grand ด้วย ( ขอย้ำ! แค่ Suppress เท่านั้น อย่าลบออก … หาไม่แล้ว! ผลลัพธ์จะกลายเป็นค่า 0 ) ผลลัพธ์ของรายงานจะแสดง ดังรูป

รูปที่ 9 แสดงผลลัพธ์ของรายงาน

      6. ตอนนี้ก็จะได้รายงานที่แสดงผลลัพธ์ตามที่ต้องการแล้ว เพียงเท่านี้ปัญหานี้ก็จะหมดไป

Notes : วิธีข้างต้นสามารถประยุกต์ได้หลากหลาย หากในรายงานต้องการปัดเศษ เพื่อแสดงเป็นเลขจำนวนเต็มหรือแสดงทศนิยมที่ไม่ใช่ 2 ตำแหน่ง ก็เพียงแก้สูตรในการปัดเศษในฟอมูล่าฟิลด์ชื่อ eval_grand เท่านั้น

ปัญหาจากค่า NULL

                NULL Value หรือค่า NULL มีความหมายว่า  “ ข้อมูลที่ไม่ทราบค่า ” ซึ่งค่า NULL เป็นสิ่งที่หลีกเลี่ยงมิได้กับการทำงานด้านฐานข้อมูล ส่งผลให้ Crystal Reports ที่นำข้อมูลในฐานข้อมูลไปออกรายงานหลีกหนีค่า NULL ไม่พ้นตามไปด้วย

                ที่มาของคำถามนี้มาจากการต้องการนำค่าในหลาย ๆ ฟิลด์มาคำนวณหาผลลัพธ์ทางคณิตศาสตร์ ทว่า บางฟิลด์กลับมีค่า NULL ส่งผลให้ผลลัพธ์ที่ได้ในรายงานเป็นแค่ช่องว่างเท่านั้น

                เป็นที่ทราบกันดี “ค่าใดก็ตามที่นำมาคำนวณกับค่า NULL จะได้ผลลัพธ์เป็น NULL เสมอ”

                ดังนั้น ฟิลด์ใดก็ตามที่มีความเป็นได้ที่จะมีค่า NULL หากต้องการคำค่าในฟิลด์มาคำนวณหาผลลัพธ์ทางคณิตศาสตร์จะต้องมีการตรวจสอบค่า NULL เสียก่อนเสมอ

แนวทางแก้ปัญหา

                สำหรับปัญหานี้ Crystal Reports มีทางออกเตรียมไว้ให้แล้ว ซึ่งพระเอกขี่ม้าขาวของเรา ก็คือ ฟังก์ชัน IsNull (fld)

                ฟังก์ชัน IsNull จะได้ผลลัพธ์เป็น Boolean ( มีแค่ 2 ค่า คือ True กับ False ) ใช้สำหรับตรวจสอบว่าค่าในฟิลด์นั้นเป็นค่า NULL หรือไม่

เมื่อใช้ฟังก์ชันนี้ หากค่าในฟิลด์เป็น NULL จะได้ผลลัพธ์เป็น True แต่ถ้าหากค่าในฟิลด์ไม่ใช่ค่า NULL จะได้ผลลัพธ์เป็น False

                แนวทางแก้ปัญหา :  ก่อนจะนำค่าในฟิลด์มาคำนวณให้ทำการตรวจสอบฟังก์ชันเสียก่อน

ตัวอย่างการแก้ปัญหา

                เพื่อให้เข้าใจมากขึ้น ผมจึงขอยกตัวอย่างเป็นตาราง dbo.income ที่ผมสร้างขึ้นเอง ซึ่งมีข้อมูลดังรูป

 

รูปที่ 10 แสดงข้อมูลในตาราง dbo.income

 

 

 

จากรูปที่ 10 จะเห็นว่า ในฟิลด์ income_type 1 , income_type 2 , income_type 3 และ expenses จะมีค่า NULL ประปรายอยู่ในเกือบทุก Row ซึ่งตัวอย่างนี้จะนำข้อมูลในตารางนี้ไปออกรายงานด้วย Crystal Reports 2013 โดยจะมีการหาค่าผลบวกสะสมของค่า income_type ทั้ง 3 ฟิลด์ แล้วเอามาลบกับค่าในฟิลด์ expenses ในแต่ละ Row

                ที่ต้องยกตัวอย่างแบบนี้ เนื่องจาก การหาผลรวม Sub Total หรือ Grand Total ของทุก Row เพียงแค่ใช้ Summary Field หรือ Running Total Field หรือ ฟังก์ชัน Sum () ก็สามารถหาผลลัพธ์ได้แล้ว

                ในบางงานก็จำเป็นต้องหาค่าผลลัพธ์ทางคณิตศาสตร์กับฟิลด์ที่อยู่อยู่ใน Row เดียวกันด้วย เช่น กำไร/ขาดทุน ได้จากค่าฟิลด์รายได้ลบด้วยค่าฟิลด์ค่าใช้จ่าย เป็นต้น

 

รูปที่ 11 แสดงรายงานตั้งต้น

จากรูปที่ 11 เป็นหน้า Preview ของรายงานที่เราจะนำค่า NULL มาผลลัพธ์ทางคณิตศาสตร์ โดยแต่ละ Row จะหาผลลัพธ์ดังนี้

  • ผลรวมของข้อมูลฟิลด์ income_type1 , income_type 2 , income_type 3 โดยจะแสดงผลลัพธ์ในช่อง Total Income
  • ผลรวมในช่อง Total Income จะเอามาลบกับค่าฟิลด์ expenses โดยจะแสดงผลลัพธ์ในช่อง Profit

เมื่อพิจารณาโดยรวมแล้ว รายงานนี้คล้ายกับ Cross-Tab จนหลายท่านอาจจะเกิดคำถามตามมา “ทำไมไม่ใช้ Cross-Tab ไปเสียเลยล่ะ”

ขอตอบตามตรงนะครับ … เมื่อพิจาณาจากลักษณะข้อมูลและโครงสร้างตารางแล้ว พบว่า ไม่สามารถใช้ Cross-Tab ออกรายงานตามที่ต้องการได้ ดังนั้น หนทางเดียวที่เหลืออยู่ คือ ต้องทำเองทั้งหมด ด้วยการเขียนฟอมูล่าเพื่อหาผลลัพธ์

ตัวอย่างนี้จะสร้างฟอมูล่าฟิลด์ขึ้น 2 ตัว ประกอบด้วย

  • TotalIncome หาผลรวมของฟิลด์ Income_type1 ,  income_type 2 , income_type 3
  • Profit นำค่าผลรวมของฟิลด์ Income_type ทั้ง 3 ฟิลด์ มาลบกับค่าฟิลด์ expenses
  1. สร้างฟอมูล่าฟิลด์ใหม่ชื่อ TotalIncome แล้วเขียนสูตรโดยใช้ฟังก์ชัน IsNull (fld) มาตรวจสอบค่า NULL ก่อนจะนำค่าในฟิลด์มาคำนวณ คำสั่งจะเป็นดังรูป

 

currencyvar type1 ;

currencyvar type2 ;

currencyvar type3 ;

currencyvar Total ;

 

if isnull({income.income_type1})

then type1:=0

else type1:={income.income_type1} ;

 

if isnull({income.income_type2})

then type2:=0

else type2:={income.income_type2} ;

 

if isnull({income.income_type3})

then type3:=0

else type3:={income.income_type3} ;

 

Total:= type1 + type2 + type3

 

รูปที่ 12 แสดงสูตรในฟอมูล่า TotalIncome

 

จากสูตรข้างต้น อาจจะมีคนแปลกใจว่า “ทำไมจึงต้องเก็บผลลัพธ์ไว้ในตัวแปร Total ด้วย” 

คำตอบก็คือ ผมทำไปด้วยเหตุผล เพื่อต้องการนำค่าในตัวแปรมาใช้คำนวณต่อในฟอมูล่า Profit ครับ

          2. สร้างฟอมูล่าฟิลด์ใหม่ชื่อ Profit แล้วเขียนสูตรโดยใช้ฟังก์ชัน IsNull (fld) มาตรวจสอบค่า NULL ก่อนจะนำค่าในฟิลด์มาคำนวณ คำสั่งจะเป็นดังรูป

currencyvar Total ;

 

if isnull({income.expenses})

then Total

else Total – {income.expenses}

 

รูปที่ 13 แสดงสูตรในฟอมูล่า Profit

         3. จากนั้น ให้นำฟอมูล่า ชื่อ TotalIncome และ Profit ไปวางในรายงาน แล้วจัดให้เป้นระเบียบและกำหนดรูปแบบฟิลด์ใหม่เหมาะสม ผลลัพธ์จะแสดง ดังรูป

 

รูปที่ 14 แสดงผลลัพธ์ในรายงาน

       4. เพียงเท่านี้ เราก็สามารถหาผลลัพธ์ทางคณิตศาสตร์จากค่าในฟิลด์ที่มีค่า NULL ได้ตามที่ต้องการแล้ว

บทสรุป

                ผ่านไปแล้ว สำหรับปัญหาเส้นผมบังภูเขา แม้ปัญหาเหล่านี้จะมองดูเหมือนไม่น่าจะมีอะไรยุ่งยาก แต่ในความเป็นจริง ถ้าหากไม่รู้สาเหตุก็จะสร้างความยุ่งยากให้ไม่น้อยเลยทีเดียว … ผมเชื่อว่า บทความนี้จะต้องเป็นประโยชน์กับผู้อ่านไม่มากน้อยก็น้อยอย่างแน่นอน

                ท้ายที่สุดนี้ … ผมก็ไม่รู้ว่าจะมีคำถามที่น่าสนใจเกี่ยวกับ SQL Server หรือ Crystal Reports มาอีกเมื่อไร ดังนั้น ผมคงจะว่างเว้นจากการเขียนบทความไปอีกสักพักนะครับ ช่วงที่รอ SQL Server และ Crystal Reports เวอร์ชันใหม่ ผมจะขอใช้เวลาว่างจากงานสอนและ Database Consult  ไปนั่งเขียนนิยายแฟนตาซีของผมต่อละกัน ตอนนี้เขียนนิยายเล่ม 2 เสร็จแล้ว กำลังจะเขียนเล่ม 3 ต่อ ซึ่งจะวางขายที่ App : OOKBEE ( แอพรองรับทั้ง iOS และ Android )