Implementation Guide › Implementation › Create Deliverables (Contract Manager) › How to Create Reports › Free-form Reports › Generic Histogram Free-form Reports

Generic Histogram Free-form Reports

The following query may be used in a free-form report in order to present the distribution of values in a table by percentage, as displayed in the following chart:

It is possible to see in the above chart what proportion (percentage-wise) of the values are below 11.5 (0%), below 804.74 (~50%), and below 1435.53 (100%).

If the SLA specifies targets such as; "x% of the values should be below y", the results of this free-form assists in finding the x, y values that ensure compliance with the SLA.

The following parameters are used in the query:

• @Query-a select statement with the following form; select some_value val from some_table. The alias val is obligatory. This query provides the values for which the distribution is analyzed.
• @Buckets-the number of values on the x axis. Source values are rounded to these numbers. For example, if you specify @Buckets = 100, then values of the source data is divided into 100 groups and the query will calculate how many values fell within each group. The higher @Buckets is, the more accurate is the result, but the chart is not visually appealing.
• @Relation-the direction of accumulation. Possible values; "<=" (when target is no less than) or ">=" (when otherwise).

The query may be run against the data source or T_SLALOM_OUTPUTS for the best results.

The following query produces the graph as shown above:

select val,100*records/(select count(*) from (@Query))
from
(
  select x.bucket_val val,
    sum(y.records) records
  from
  (
    select round(val/bucket_size,0)*bucket_size bucket_val,
      count(*) records
    from
    (
      select (max(val)-min(val))/@Buckets bucket_size
      from
      (
        @Query
      )
    ) params,
    (
            @Query
       ) source
    group by round(val/bucket_size,0)*bucket_size
    order by round(val/bucket_size,0)*bucket_size
  ) x,
  (
    select round(val/bucket_size,0)*bucket_size bucket_val,
      count(*) records
    from
    (
      select (max(val)-min(val))/@Buckets bucket_size
      from	
      (
        @Query
         )
    ) params,
    (
            @Query
       ) source
    group by round(val/bucket_size,0)*bucket_size
    order by round(val/bucket_size,0)*bucket_size
  ) y
  where y.bucket_val @Relation x.bucket_val
  group by x.bucket_val
  order by x.bucket_val
)

Following is a Sample Parameter list (as XML) which could be used:

<custom>
     <connection>
          <params/>
     </connection>
     <query>
          <params>
               <param name="@Query" disp_name="Data Type" type="LIST">
                    <value>PDP Context Activation Success</value>
                    <list>
                         <item>
                              <value>select success_rate as val from PDP_Context_Activation_Success.CSV</value>
                              <text>PDP Context Activation Success</text>
                         </item>
                         <item>
                              <value>select throughput as val from [gprs throughput volume by apn.csv]</value>
                              <text>Throughput of a Single APN</text>
                         </item>
                         <item>
                              <value>select throughput as val from [Generic GPRS Throughput.CSV]</value>
                              <text>Generic Throughput</text>
                         </item>
                    </list>
               </param>
               <param name="@Buckets" disp_name="X Axis Values" type="LIST">
                    <value>100</value>
                    <list>
                         <item>
                              <value>25</value>
                              <text>25</text>
                         </item>
                         <item>
                              <value>50</value>
                              <text>50</text>
                         </item>
                         <item>
                              <value>100</value>
                              <text>100</text>
                         </item>
                         <item>
                              <value>250</value>
                              <text>250</text>
                         </item>
                         <item>
                              <value>500</value>
                              <text>500</text>
                         </item>
                         <item>
                              <value>1000</value>
                              <text>1000</text>
                         </item>
                    </list>
               </param>
               <param name="@Relation" disp_name="Violation of threshold means" type="LIST">
                    <value>providing too little</value>
                    <list>
                         <item>
                              <value>&gt;=</value>
                              <text>providing too little</text>
                         </item>
                         <item>
                              <value>&lt;=</value>
                              <text>providing too much</text>
                         </item>
                    </list>
               </param>
          </params>
     </query>
</custom>

Comments

• The query has been optimized for Oracle and SQL Server. For other ODBC data sources, one may need to add 'as' before column aliases and apply other modifications.
• When exporting results to Excel, it is recommended that the user generate an XY (Scatter) report to represent it. When the chart is dotted it will also be possible to view the scattering of the values.