Levels and Queries

Against the report, we define the Levels and Queries. This enables the report to populate the tabs and know what Stored Procedures are used to fetch data. We do NOT define the specific columns in this bit.

Level

The levels determine the tabs and the drilldown order. The report starts at Level 0, and when a row is clicked, it drills down to Level 1 (with a parameter from the Level 0 row).

This must be a number between 0 and 4. You can click the field title (Level) to set this to zero.

Query

At each level, you can run up to five queries. In the screenshot, Retrospecive (Busines Units), you can see two Queries at Level 2 (the third level, Opportunities); Won Opportunities and Lost Opportunities. This must be a number between 0 and 4. You can click the field title (Query) to set this to zero.

Title

This will be the tab title or the query title (or both). When the query number is zero, the title is used as the text for the tab. For queries 1 through 4, this title is used as the "fancy tab" that appears just above the retrieved data. In the example below, the title for Level 0/Query 0 is Business Unit, Level 1/0 is Salesman and Level 2/0 is Opportunities.

The example shown is slightly unusual in that the first query (Query 0) has a fancy tab of Won Opportunities. This is acheived by setting the title in Level 2/0 as

 Opportunities,Won Opportunities

We've done it like this as we wanted the tab to be called Opportunities, but needed to indicate the returned data was won opportunities rather than the typical open opportunities.

Groups

Columns Groups allows you to put a group heading over several columns.In the example below, the headings Leads, PSQ and Opportunities are column group headings defined in this field.To achieve the above effect, the Groups field is set to contain the following

3,blank,5,Leads,1,PSQ,7,Opportunities

This comma separated list is basically a value for colspan (<td colspan='3'>) followed by the text required.

• Column Span of 3 and leave the heading blank. This does not consume group colour class
• Column Span of 5 and use the heading Leads. Since this is the first (non-blank) heading, it will be assigned colour class columnGroup1
• Column Span of 1 and use the heading PSQ. This will be assigned the colour class of colourGroup2
• Column Span of 7 and use the heading Opportunities. This will be assigned the colour class of colourGroup3

You can have a maximum of 5 column groups (since I've only defined colour classes 1 through 5). You can have multiple blank entries if required. For example, the PSQ could be replaced with blank. The Opportunities group would then be assigned the colour class of colourGroup2.

Obviously, if you insert new columns in the report, then you'll need to alter this field accordingly

Sorting

It is the DataTables plugin that sorts the information, not the order that the data is returned by the Stored Procedure or SQL Statement. The default is to sort on the first column in ascending order. This is normally the desired result, so typically you'd leave this field blank.

However, if you want to sort by another column (or use descending order) then specify the column number and the direction. The first column is 0, the next is 1 and so on. Use asc for ascending and desc for descending.

You can sort by multiple columns if required. This often makes sense when you have a Drill Down All. For example, on the Salesman Summary report, the opportunities are shown in Description order. However, this looks odd if you use the Drill Down All, so you really want to order by Salesman and then Description. If you click a Salesman, they'll all be the same (so it will look as expected) and if you click Drill Down All, it'll sort by Salesman and then Description; again, as expected.

6,desc
0,asc
0,asc,7,desc

Obviously, if you insert new columns in the report, then you'll need to alter this field accordingly

PHP

When the report runs, we need to specify which PHP module to actually fetch the data. Typically, this will be generic.php, but we've allowed any PHP filename to be specified (allow for future complexity). You can set the default by clicking the field title (PHP).

doWhat

My standard PHP uses the parameter of doWhat to execute a required function in the PHP module. Normally, this will be executeSP (which you can quickly set by clicking the field title).

SP

Since most of the data is coming from the CRM Microsoft SQL Database (and sometimes the Data Warehouse), Lynne will typically create a Stored Procedure to actually fetch the data. The name entered here will automatically get prefixed with SFD_ so, for example, entering opportunitiesOpen will actually run the Stored Procedure called SFD_opportunitiesOpen.

When the Stored Procedure is called, we always pass three parameters (@whichYear, @whichMonth and @stageCodeList). These parameters MUST be accepted by the Stored Procedure (even if it doesn't want to use them). Even reports where No Dates have been specified will pass the month and year.

EXEC SFD_example @whichYear = '2017', @whichMonth = '10', @stageCodeList = '200007, 200010, 200011'";

In additional, we also pass the optional clickLink parameters. For example, @salesTeamID = 2which are defined in the columns (not here).

If using generic.php and you have a new parameter (clickLink) that has not been used on any other report before then you will need to edit the executeSP function to use the new parameter in the EXEC call.

YTD Month and Quarter Number

These two fields are always calculated and actually sent to the PHP. However, unlike the year, month and stage code, they are rarely required. If you want them to be passed to a stored procedure, then set the SP parameter as follows:-

salesmanSummary,ytdMonth,quarterNumber

This currently only applies when using the executeSP function in generic.php, so it's best to look in that module to see how it's handled.

SP Extra

Sometimes, Lynne wants to write a single Stored Procedure that has different behaviours depending on a passed parameter. A typical example would be a header or detail query where they're basically the same, but one SUM's and the other returns individual row.

The SFD already allows arbitrary parameters to be passed, but these are via clickLink and thus have a value based on the row clicked upon. Using SP Extra lets you set a static variable that is the same for any row.

@dataType = 'Data'

Anything in the SP Extra field is appended to the end of the EXEC statement (so you can set multiple variables if you wish).

SP More

Some reports need to run a secondary query - for example, By Week. This will run the same stored procedure again, but this time it will add the SP More parameter (eg. @dataType = 'Heading') and return the data in $rstat['myData2'].

You may want to look at the checkForSpecialProcessing() Javascript function which is required to process the secondary data.

Button

This is used to add custom buttons; the best example is the Manual Forecast feature (in the Salesman Summary report). You can add multiple custom buttons by using a comma separated list.

In the getReport() function, search for Add Custom Buttons to find where they're added. The text you enter is used to determine what button gets added. It's very important that the custom button has the CSS class of customButton. This is used to remove them when switching between reports. If you miss that bit, you'll end up with inappropriate and/or repeated buttons.

if (addButton.toLowerCase() == "manualforecast") {
    html = "<button class='btn btn-default customButton btManualForecast'><i class='fa fa-tasks fa-lg'></i>&ensp;Manual Forecast</button>";
}

Since the buttons are added dynamically, you'll need to use a slightly different method to bind to the click event. The example below, appears in the initApp() function for the manualForecast button. This binding occurs before there are any buttons.

$("#reportData").on("click", ".btManualForecast", function() {
    var whichLevel = $(this).parent("div").attr("button-level");
    populateManualForecastModal(whichLevel);

    $('#modalManualForecast').modal();
});

Allow Drill Down All Button

For the most part, it makes sense to offer the ability to Drill Down without clicking a row. However, there are some situations where this would return a huge amount of data. For example, in the Campaigns report, if you use Drill Down All on the campaign list, you'll return over eleven thousand rows - clearly not sensible.