Visionplanner Studio - Query Manager
- Arjan den Braber
- Edwin Knoppert (Unlicensed)
De Query Manager is een onderdeel van Visionplanner waarmee direct gegevens uit een administratie database kunnen worden gerapporteerd. Is de koppeling juist ingesteld dan kan de klant de volgende zaken gebruiken:
- Realtime Drilldown vanuit het financiele overzicht en de financiele dashboard tabellen
- Openstaande posten uit de administratie rapporten (niet voor alle administratiepakketten beschikbaar)
- Dashboard tabellen direct vanuit een database selectie rapporteren
- Tabellen in teksten (dus zowel in dashboards als rapportage) vanuit een database selectie rapporteren
- Grafieken vanuit een database selectie rapporteren
- Filters op dashboards toepassen
Databases
De Query Manager is beschikbaar voor een aantal specifieke databases.
Voor deze database zijn specifieke imports ontwikkelt waardoor er naast de bovengenoemde directe rapportages ook financiële gegevens uit de database kunnen worden geimporteerd in Visionplanner.
De Query Manager kan ook gebruikt worden met een SQL Server database, MSAccess database, SQL Lite database, Excel bestand en een Standaard directe database koppeling. Wil men daaruit gegevens importeren dan kan dat ook maar dan moet de klant zelf de selectieciteria voor die import opgeven. Men kan er ook voor kiezen om die niet op te geven. Dan kunnen geen cijfers worden geïmporteerd en kan men de Drill-down functionaliteit niet gebruiken.
Alleen databases die via ODBC benaderbaar zijn en SQLite databases kunnen gebruikt worden.
Instellen
Om met de Query Manager te werken moet de databasekoppeling ingesteld worden via het importscherm. Afhankelijk welke cijfers er gerapporteerd worden moet dat apart gebeuren voor de actuele, budget en prognose cijfers.
Er kan een import worden toegevoegd of worden bewerkt. Eén van de 'Directe' database koppelingen moet worden gekozen en de database moet worden ingesteld. Hoe dit moet kan worden bekeken op de pagina's van de specifieke imports. Wordt de import gebruikt dan kunnen er voor een aantal typen imports ook nog een aantal specifieke opties worden gekozen.
Of de database te benaderen is kan worden getest door op het knopje 'Kolommen toewijzen' te klikken. Via de hulpknop (links onderin het scherm) kan gekozen worden voor 'Informatie' Als er een juiste verbinding kan worden gemaakt wordt er een scherm getoont waarin een query kan worden ingetikt. Wordt een juiste query ingegeven dan kan vervolgens op F5 gedrukt worden om het resultaat daarvan te bekijken. Zie de informatie hieronder voor de werking van de query editor.
Rapporteren
Realtime Drilldown vanuit het financiele overzicht en de dashboard tabellen
In het financiele overzicht kan gekeken worden naar de individuele transacties die in de database staan. Dat kan gewoon door dubbel te klikken op de mutatie. Zijn er transacties aan die mutatie gekoppeld dan worden die direct opgehaald en getoont.
Dashboard tabellen direct via een databaseselectie rapporteren
Wanneer een dashboard tabel wordt gebruikt dan kan in het eigenschappenschem van de tabel gekozen worden voor 'Databasetabel'. Er kan dan een selectie worden opgebouwd waarmee de cijfers uit de database worden opgehaald. Nadat de selectie is gemaakt kunnen net als voor elke dasboardtabel wat tabelopties worden ingesteld.
Nadat er een geldige query is gemaakt moeten de kolommen nog toegevoegd moeten worden in de tabel. Dat kan met de rechtermuistoets door te kiezen voor 'kolom' - 'Toevoegen'. Er moet in ieder geval een database veld ingesteld worden om dat zichtbaar te maken.
Grafieken direct via een database selectie rapporteren
Wanneer een grafiek wordt gebruikt dan kan in het eigenschappenschem van de grafiek gekozen worden voor 'Databasetabel'. Er kan dan een selectie worden opgebouwd waarmee de cijfers uit de database worden opgehaald.
Er moeten daarna reeksen worden aangemaakt waarin. In elke reeks moet een database veld worden gekozen om weer te geven in de grafiek. Voor een normale grafiek moet er ook op het tabblad X-As een database veld worden gekozen.
Tabellen in teksten (dus zowel in dashboards als rapportage) via een databaseselectie rapporteren
In een tekst kan met de rechtermuistoets een databasetabel worden ingevoegd in een tekst in een dashboard of rapport. Er kan dan een selectie worden opgebouwd waarmee de cijfers uit de database worden opgehaald.
Er kunnen ook een aantal opmaak mogelijkheden worden gekozen voor de weergave van de tabel en de individuele velden.
Query's
Wil men met de Query Manager meer dan alleen doorzoomen dat moeten er database selecties (Query's) worden gemaakt. Omdat deze meestal niet alleen database specifiek, maar ook klant specifiek zijn. Moet de klant bepalen wat voor een selectie er gemaakt moet worden.
Een typische query heeft de volgende opbouw
SELECT <veldnamenlijst>
FROM <tabelnaam>
WHERE <voorwaarden>
ORDER BY <volgorde aanduiding>
Vaak wil men zeer specifieke gegevens ophalen uit een database. Om dat te kunnen opvragen is specifieke kennis van niet alleen query opbouw maar ook van de applicatie en de database nodig. De basis opbouw van elke query is welliswaar gelijk. Welke mogelijkheden er in een query kunnen worden gebruikt verschilt heel veel per bronsysteem en ODBC driver. Een Visionplanner consultant zal meestal niet over alle kennis daarvoor beschikken. Het is dan ook van belang dat de klant zelf de benodigde query's kan bouwen en indien nodig aanpassen. Visionplanner kan alleen ondersteuning bieden bij het rapporteren van de opgehaalde gegevens.
Op de volgende pagina staat meer informatie over het maken van query's: Wikipedia
Editor
Een query in Visionplanner kan worden bewerkt met de query editor.
In het grote vak kan de query ingevuld worden, Specifieke querytermen worden automatisch in een andere kleur weergegeven om de leesbaarheid te vergroten. De algemene functies welke in de meeste databases kunnen worden gebruikt worden herkent. Het is echter mogelijk dat een functie toch niet goed uitgevoerd kan worden in de gekozen databasedriver. Met behulp van F5 kan het resultaat ervan bekeken worden. Is de query ongeldig dan wordt er weergegeven waarom de database de query niet kan uitvoeren.
Aan de rechterzijde van het scherm staan de tabellen welke in de database beschikbaar zijn. Klikt men op de [+] dan worden de velden uit de tabel getoont. Indien er een definitie aanwezig is dan worden primaire en secundaire sleutels vet getoont.
Door dubbel op een tabel- of veldnaam te klikken wordt die naam ingevoegd in de query op de huidige positie.
Klikt men met de rechtermuistoets op een tabel dan kan er gekozen worden voor 'Toon gegevens'. Daarmee kunnen de gegevens van de tabel direct ingekeken worden. Door met de rechtermuistoets op een veldnaam te klikken kan het type van een veld bekeken worden.
Het is mogelijk dat de tabellen en/of velden van specifieke databases niet getoont kunnen worden. Dat hangt ervan af of het specifieke databasetype deze functie ondersteunt. In zo'n geval kunnen er nog steeds gewoon gegevens met behulp van de query opgevraagd kunnen worden maar zal de gebruiker de namen van de tabellen en velden zelf moeten weten.
Parameters
Het is mogelijk om parameters te gebruiken in een database query. Daarvoor kan het standaard ADO parameter formaat gebruikt worden. Parameters kunnen in het SQL command meegegeven worden door een ':' gevolgd door de naam van de parameter, spaties zijn nooit toeegestaan. Op het tabblad 'Opties' kan vervolgens de waarde van de parameter ingegeven worden. Door dit principe kan een eind gebruiker een citeria voor een selectie opgeven zonder daarvoor specifieke kennis van de SQL query te hebben.
Voorbeeld van een query met een parameter
SELECT Datum, Van, Naar, Uren, KM
FROM Ritten
WHERE Auto=:Wagen
ORDER BY Datum
In dit geval is Wagen de parameter die op het Tabblad opties ingevuld kan worden. Ook kan Wagen als filter gebruikt worden in de dashboards.
Filters
Het is met de Visionplanner Query Manager mogelijk om filters te gebruiken in de Dashboards. De waarden voor de gebruikte parameters kunnen daarmee worden opgegeven of geselecteert in de pagina selecties van het dashboard.
Filters moeten handmatig gedefinieerd worden. Dat kan via de verwijzing 'Database filters' welke beschikbaar is in de dashboards indien een directe databasekoppeling aanwezig is.
Met behulp van de [+] kan een nieuw filter aangemaakt worden. De naam van het filter moet altijd exact overeenkomen met de naam van de parameter welke gebruikt wordt in de query's in het dashboard.
Via het knopje [...] kan een selectie weergegeven worden om een lijst met filterkeuzen te tonen. Wordt er geen query ingegeven dan kan de filter door de gebruiker ingetikt worden.
Voorbeeld van een filter selectie
SELECT Wagen AS Code, "Wagen: "+Wagen AS Name
FROM Ritten
GROUP BY Wagen
ORDER BY Wagen
De veldnamen die teruggegeven worden zijn van belang. Name is de waarde die de gebruikers in het filters ziet. Code is de waarde die in de parameters van de dashboard query's gebruikt zal worden.
Of en welke filters kunnen worden ingesteld wordt door Visionplanner automatisch bepaald aan de hand van de tabellen en grafieken die in het dashboard worden getoont. Dus wordt in een tabel of grafiek een parameter gebruikt met dezelfde naam als een filter dan wordt die filters weergegevens in de paginaselecties van het dashboard. Niet gebruikte filters kunnen ook niet worden gekozen.
Doorklikken tussen overzichten
Bij database tabellen kan aangegeven worden dat er bij het klikken op een kolomwaarde een ander dashboard wordt geopend.
Wanneer in het dashboard waarheen gesprongen wordt filters staan dan kunnen deze automatisch gevuld worden met de waarde waarop geklikt is. Om dat te laten werken moet er in de query achter de tabel een kolom bestaan met exact dezelfde naam als dat filter. Dat hoeft dus niet de kolom te zijn waarop geklikt wordt.
Voorbeeld
- Er is een dashboard 'Klanten' waarin onder andere de namen van alle klanten in een tabel wordt weergegeven.
- Er is een dashboard 'Klant' waarin allerlei gegevens van een klant staan. Van welke klant er gegevens worden getoond kan de gebruiker bepalen via een filter 'Klantnummer'.
Er kan nu ingesteld worden in de klanten tabel dat de kolom 'klantnaam' springt naar het dashboard 'Klant'. Als er ook gezorgt wordt dat de query die in die tabel gebruikt wordt een kolom 'Klantnummer' bevat dan wordt het filter in het dashboard 'Klant' automatisch gevuld wordt met het klantnummer van de klant waarop geklikt is.