[% INCLUDE 'help-top.inc' %]

<h1>Add a Custom Report</h1>

<h2>Guided Report Wizard</h2>

<p>If the report you need is not already available, the 'Guided Reports' Wizard will walk you through creating a custom report.</p>

<ul>
<li>Go to the 'Reports' module (usually under 'More' in your Global Nav)</li>
<li>Choose 'Guided Reports' under 'Guided Reports Wizard'</li>
</ul>

<p>Answer the 6 questions presented in order to create your report.</p>

<p><strong>Step 1: Choose a Module to Report on</strong></p>

<ul>
<li>Choose a Koha module to build a report on (patrons, circ, cataloging, etc)</li>
<li>Click 'Next'</li>
</ul>

<p><strong>Step 2: Pick a Report Type</strong></p>

<ul><li style="color: #990000">IMPORTANT: Only Tabular should be chosen with this option</li></ul>

<p><strong>Step 3: Select Columns for Display</strong></p>

<p><strong>Step 4: Select Criteria to Limit</strong></p>

<ul><li>This is optional, if you don't want to limit results by any one field, just leave this section blank.</li></ul>

<p><strong>Step 5: Pick which columns to perform mathmatical functions on</strong></p>

<ul><li>This is optional, if you don't want to do any math on the results, just leave this section blank.</li></ul>

<p><strong>Step 6: Choose how you want the report ordered</strong></p>

<ul><li>This is optional, if you want the results to come out as they are in the table, just leave this section blank.</li></ul>

<h1>Edit Reports</h1>

<p>Custom reports can be edited in their entirety from the Saved Guided Reports page.</p>

<ul><li><em>Get there:</em> More > Reports > Guided Reports > Use Saved</li></ul>

<ul>
<li>To edit a report click 'Edit SQL' beside the report</li>
<li>A page with editable fields will open so that you can change the title, notes and SQL related to the report</li>
</ul>

<h2>Report from SQL</h2>

<p>In addition to the report wizard, you have the option to write your own queries using SQL. To find reports written by other Koha users, visit the Koha Wiki: <a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library">http://wiki.koha-community.org/wiki/SQL_Reports_Library</a>. You can also find your database structure in /installer/data/mysql/kohastructure.sql or online at: <a href="http://schema.koha-community.org">http://schema.koha-community.org</a>.</p>

<p>To add your query, click the link to 'Create from SQL' on the main reports module or the New button at the top of the Saved Reports page.</p>

<p>Fill in the form presented</p>
<ul>
    <li>The name is what will appear on the Saved Reports page to help you identify the report later. It will also be searchable using the filters found the left of the Saved Reports page.</li>
    <li>Notes will also appear on the Saved Reports page, this can be used to provide more details about the report or tips on how to enter values when it runs</li>
    <li>The type should always be 'Tabular' at this time since the other formats have not been implemented</li>
    <li>In the SQL box you will type or paste the SQL for the report</li>
    <li>If you feel that your report might be too resource intensive you might want to consider using runtime parameters to your query. Runtime parameters basically make a filter appear before the report is run to save your system resources.
<ul>
    <li><strong>Note:</strong> There is a limit of 10,000 records put on SQL statements entered in Koha. To get around this you want to add 'LIMIT 100000' to the end of your SQL statement (or any other number above 10,000.</li>
    <li>There is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is &lt;&lt;Question to ask|authorized_value&gt;&gt;.</li>
    <li>The &lt;&lt; and &gt;&gt; are just delimiters. You must put &lt;&lt; at the beginning and &gt;&gt; at the end of your parameter</li>
    <li>The 'Question to ask' will be displayed on the left of the string to enter.</li>
    <li>The authorized_value can be omitted if not applicable. If it contains an authorized value category, or branches or itemtype or categorycode, a list with the Koha authorized values will be displayed instead of a free field Note that you can have more than one parameter in a given SQL Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". For example entering nothing for : "title=&lt;&lt;Enter title&gt;&gt;" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like &lt;&lt;Enter title&gt;&gt;" and enter a % at run time instead of nothing</li>
</ul>

<p>Examples:</p>

<ul>
    <li>SELECT surname, firstname<br />
    FROM borrowers<br />
    WHERE branchcode=&lt;&lt;Enter patrons library|branches&gt;&gt; AND surname like &lt;&lt;Enter filter for patron surname (% if none)&gt;&gt;</li>
    <li>SELECT *<br />
    FROM items<br />
    WHERE homebranch = &lt;&lt;Pick your branch|branches&gt;&gt; and barcode like &lt;&lt;Partial barcode value here (use % for wildcard)&gt;&gt;</li>
</ul>
</li>
    <li style="background-color: #ffe599;">Tip: To generate a date picker calendar to the right of the field when running a report you can use the 'date' keyword like this: &lt;&lt;Enter Date|date&gt;&gt;</li>
    <li style="background-color: #ffe599;">Tip: You have to put "%" in a text box to 'leave it blank'. Otherwise, it literally looks for "" (empty string) as the value for the field.</li>
    <li style="color: #990000;">Important: In addition to using any authorized value code to generate a dropdown, you can use the following values as well: Branches (branches), Item Types (itemtypes) and Patron Categories (categorycode). For example a branch pull down would be generated like this &lt;&lt;Branch|branches&gt;&gt;</li>
</ul>

<p>Once everything is entered click the 'Save Report' button and you'll be presented with options to run it. Once a report is saved you do not have to recreate it you can simply find it on the Saved Reports page and run or edit it.</p>

<h1>Questions</h1>

<p><strong>Is there a repository for reports?</strong></p>

<p>There is a page on the Koha Wiki: <a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library" target="_new">http://wiki.koha-community.org/wiki/SQL_Reports_Library</a> with reports written by Koha libraries around the world.</p>

<p>NEKLS also has a great page (<a href="http://www.nexpresslibrary.org/training/reports-training/" target="_new">http://www.nexpresslibrary.org/training/reports-training/</a>) with a list of reports they have written.</p>

<p><strong>See the full documentation for Custom Reports in the <a href="http://manual.koha-community.org/3.8/en/reports.html#customreports">manual</a> (online).</strong></p>

[% INCLUDE 'help-bottom.inc' %]