...
Many of the tips listed below have been mentioned, or alluded to, already. This list however, covers many of the more common stumbling blocks.
Manual SQL
If someone other than yourself has created a chart and it’s not doing what you would expect, double check that Manual SQL hasn’t been utilised.
Showing All Rows in a Data Table
The SQL generated by piDashboard will always ‘GROUP BY’ the dimension objects you have selected, even on data tables (unless you have used Manual SQL). This is fundamental to its behaviour, it always assumes that you want to summarise and aggregate, and 99% of the time that is what you want to do.
If you did want to show all the rows in a data table then add a unique identifier to the field list. Optionally you can make this an invisible column. It will ‘GROUP BY’ the unique identifier as well, therefore brining back all rows.
Category Filters v Chart Filters
Category filters by default will override chart filters. So, if you filter a chart to only show sales where the year is >= 2015 (perhaps for performance reasons) but then create a category filter (e.g., a Drop List filter) using the same sales year object. The category filter will take precedence and the chart will initially show all sales years. The easiest way to work around this is to create another object that also resolves sales year but give it a different name, use one for the category object and the other for the chart filter.
Dates with Time Element
As per normal freehand SQL if you format a datetime field as a date, but then order it by the unformatted date, and there IS a time element (i.e., the times are not all 00:00:00.000) then you will get some very bizarre results. If you have stripped the time out of the date, then you need to do the same with the order by.
Similarly, if you format a date to be Month-Year (without date) then you need to use an order by that will give the correct result, you cannot order by the original date field. The example below works in SQL Server, it is easier to use EOMONTH however, this won’t work with older versions of SQL Server.
...
Chart Owner
By default, a chart is ‘owned’ by the category it was originally created in. If a chart is copied and/ or moved to different categories it will still be owned by the original category unless the user manually intervenes. Therefore, if you create a chart in a category called ‘Directors’ but then also place it, or a copy of it, into another category for example, ‘All Staff’, only people who have been allocated the category ‘Directors’ will be able to see it. The solution is to change ownership of the chart to ‘All Staff, or indeed to ‘Home’, which everyone has access to automatically.
Date Dimensions
In a traditional Data Warehouse a Date Dimension table (or view) is often created. This can be linked to all the primary date fields in your metadata if required. It may typically look something like this;
...
Primarily due to the ongoing effort to maintain Bank Holidays (not to mention the international implications of this, or even between England / Scotland / Wales and Northern Ireland!) we don’t supply this to users, but feel free to build your own.
We do however have a view, vw_pandash_PreCannedDates, that we can supply:
...
Using this and joining it to certain key date columns (normally using a ‘BETWEEN’ join) ….
...
… you can then configure a Category Object like this ….
...
…. which will enable you to quickly roll out categories like the example below, where the user is initially shown the figures for the ‘Current Fin Year’ but they can then filter to show the same charts for a wide variety of different date ranges.
...
Note that {{Invoice Date Range}} has been referenced in each of the chart subtitles, so there is no ambiguity as to what date range the chart is currently looking at, especially if it is subsequently created as a PDF.
Date Picker
If you want to use a Date Picker Category Object against an Oracle database you will need to pass in an indexed field.
Enter the following in the Index Field TO_CHAR(yourdatefield , ‘YYYY-MM-DD HH24:MI:SS'), replacing the 'yourdatefield’ with the relevant object details - an example is shown in the following screenshot.
...
Non-Dashboard Targets
Whilst the dashboard does have target functionality this is best used for overall companywide targets. However, some targets, especially in ERP/ Accounting/ CRM systems, can be rather complex. It is also often the case that targeting is one of the reasons users are taking data out of the core system and into Excel.
It is normally best to load such targets into the underlying database, if they don’t already exist, either via a normal database load or using our Excel load functionality.
Note that when you do this is it is nearly always advisable to create a view to compare actual with target. Otherwise, you may get problems of some targets being excluded where the customer has no sales, or vice versa.
A simplified pseudo SQL example ;
SELECT SalesValue ‘Value’
Date
Customer
‘Actual’ ‘Type’
FROM ActualSales
UNION ALL
SELECT BudgetValue ‘Value’
Date
Customer
‘Budget’ ‘Type’
FROM TableWithTargets
Of course, your actual targets may actually be at Branch, Product, Account Manager etc. level.
Use Line Measures, Not Header Measures
This may seem obvious, but it has caught people out before. It is generally recommended to obtain measures, whether it be order quantity, invoice value, hours worked etc., from the lowest level of granularity.
So, although Invoice Value may exist on the Sales Invoice header of a system, we would not generally recommend using that. Instead use the line (Detail) values, this is because you can then easily change the chart to split by things that are only held at line value, such as the Product Group or Stock Code.
If you use the header value, then splitting by the Product Group could give you very misleading figures. The only reason to use the header values instead of the line values would be performance; reading from fewer lines across fewer tables, but this is rarely necessary.