Report Tells User ABOUT The Data

Last week a coworker, who was working on a report to validate some data sent to us by a client, asked me how to highlight null values in a chart in the report. I showed her how to do that (Click here to read it). And I offered another suggestion: you don’t want your report user to have to scroll through a lot of data or have to sort the chart to find out if any null values exist; it will help the report user if you provide a text box that tells right away if any nulls or zeros or whatever other interesting conditions exist in the data. Then, the report user can decide how to use the other objects on the report to analyze the data.

This report was a data validation report but the idea can be used for executive summaries and other kinds of reports. The goal is to have a text object that uses easy-to-understand language to tell the report user about important aspects of the data. The idea is similar to the discussion of putting counts in a chart title from a few months ago (Click here to read it). When you design a text object like this you should ask yourself: “what things will the report user be looking for in this report?” and “what questions will the report user be asking of the data?” The text box is telling the report user important things About the data not just summarizing the data.

Here’s what we tried out in the text box of a draft version of that report:

='Current Selection Data Issues:
'&
If(sum(If(Len(Trim(product))=0,1,0))>0,'Data contains null product values.
')&
If(sum(If(Len(Trim(ship_loc))=0,1,0))>0,'Data contains null ship_loc values.
')&
If(sum(If(Len(Trim(mkt_channel))=0,1,0))>0,'Data contains null mkt_channel values.
')&
If(sum(If(Len(Trim(ship_loc))>0 and Len(Trim(ship_loc))<>4,1,0))>0,'Data contains wrong length (not 4) ship_loc values.
')&
If(sum(If(qty<0,1,0))>0,'Data contains negative qty.
')&
If(sum(If(qty=0,1,0))>0,'Data contains zero qty.
')&
If(sum(If(qty<>floor(qty),1,0))>0,'Data contains fractional qty.
')&
If(sum(If(Len(Trim(qty))=0,1,0))>0,'Data contains null or blank qty.
')&
If(sum(If(qty>100000,1,0))>0,'Data contains unusually large qty.')

That text expression only shows the messages if the data conditions actually exist. If some important data condition can only be detected during during data loading then consider capturing the important condition in a document variable while the loadscript is running and then use the document variable in the text box.

We also added this little text box of various counts:

='Current Selection Contains:
'&Num(Count(Distinct product),'###,###')&' products
'&Num(Count(Distinct ship_loc),'###,###')&' ship_loc
'&Num(Count(Distinct mkt_channel),'###,###')&' mkt_channels
'&Num(Count(Distinct product&'.'&ship_loc&'.'&mkt_channel),'###,###')&' SKUs (product/ship_loc/mkt_channel)
'&Num(Count(Distinct orderid),'###,###')&' orders
'&Num(Count(Distinct shipdate),'###,###')&' shipdates, '&min(shipdate)&' to '&max(shipdate)

The data issues text box could also have been written to show counts of the various issues.

Another interesting way to use text expressions like that is in a straight table chart. You can set up the chart with a dimension like shipping-location and put the text expression (minus that first line which is a title) in the chart expressions and then you have a chart that uses easy-to-understand language to tell about data conditions for each shipping-location.