Ik ben de laatste paar weken druk bezig geweest met mijn nieuwe blogsite. Deze is vanaf nu te vinden op http://www.sqlblog.nl/
Deze bloggersite zal ik dan ook niet meer bijwerken! Tot snel op de nieuwe SQLBlog.nl site!
Deze blog-entry beschrijft hoe je eenvoudig een multi-value parameter kunt doorsturen van rapport A naar rapport B. Ik ben zelf lange tijd aan het klooien geweest om dit voor elkaar te krijgen. De oplossing was uiteindelijk erg simpel, het werkt precies hetzelfde als een normale parameter doorsturen. Het probleem was dat SSRS2005 standaard een (0) achter de parameter zet in de drillthrough aktie.
Wat ik zijn de gekozen weeknummer van Report A doorsturen naar Report B.
Report A: Parameter @GeselecteerdeWeekID (Multi-value)
Report B: Parameter @GeselecteerdeWeekID (Multi-value)
Ga naar de cell die je wilt linken naar Report B en klik op "Properties". Ga vervolgens naar "Navigation" en klik daar op "Jump to Report". Selecteerde het Report B.
....vervolgens selecteer je de parameter die je wilt doorsturen.
Klik de parameter aan. Standaard zet hij een (0) erachter, deze moet je weghalen en het werkt goed! Haal je de (0) niet weg stuurt hij alleen de eerste waarde van de reeks waarden door. Kies je bijvoorbeeld de weken: 200801, 200802,200803 , dan stuurt hij alleen 200801 door!
http://www.sqlblog.nl/component/option,com_jomcomment/id,98/opt,com_content/task,trackback/
Ik liep onlangs tegen een vraagstuk aan die ik wel interessant vond om op mijn blog te plaatsen. Het behandelt een veelvoorkomende situatie waarbij je een dataset meerdere keren gebruikt, ook wel een recursieve query genoemd. Men vroeg het aantal gewerkte weken per periode per uitzendkracht.
Per periode kan iemand meerdere declaraties hebben waarbij het getal “weken gewerkt” wordt bijgehouden. Om te bepalen hoeveel weken iemand in een periode heeft gewerkt pakken we de laatste “weken gewerkt” in een periode en trekken daar de laatste “weken gewerkt” uit de vorige periode vanaf, klinkt simpel toch?
Onderstaande afbeelding toont het resultaat wat we als basis gaan gebruiken om “weken gewerkt” per periode vast te kunnen stellen:
We hebben onze data gegroepeerd op jaar, Periode444Nummer, krachtcode en pakken de MAX(WekenGewerkt) per periode. Als we nu willen weten hoeveel weken iemand heeft gewerkt in periode 6-2008 moeten we 76-72 uitvoeren. Iemand heeft tenslotte aan het einde van periode 6-2008 76 weken gewerkt en aan het einde van periode 5-2008 72 weken gewerkt. Dit verschil is wat hij in periode 6 heeft gewerkt.
Om dit alles mogelijk te maken genereren we bovenstaande standaard dataset die we vervolgens kunnen hergebruiken, ook wel Common Table Expression genoemd (CTE). Ook voegen we een rijnummer toe per krachtcode en sorteren dit op krachtcode, jaar en periodenummer. Verder hebben we ook het vorige jaar nodig om te kunnen bepalen hoeveel weken iemand gewerkt heeft in de eerste periode van het huidige jaar! Dit zorgt voor een resultaat dat er ongeveer zo uitziet:
De sortering zorgt ervoor dat alles netjes in volgorde staat zodat we straks eenvoudig de weken van elkaar kunnen aftrekken!
Vervolgens maken we onderstaande query en voeren dit op de eerdere gegenereerde dataset uit:
De kracht zit hem in het stukje met de cirkel eromheen. Hierbij joinen we de tabel op zichzelf zodat we het resultaat van de vorige week gewerkt kunnen vinden. Dit trekken we van de huidige week af en vinden zo het resultaat.
De complete query ziet er ongeveer zo uit:
Uiteraard kun je dit voorbeeld in veel meer situaties gebruiken, bijvoorbeeld voor het berekenen van cumulatieven.
Parameter security
In onze omgeving maken we gebruik van een ASP.NET webportal voor het aanbieden van rapportages welke allemaal zijn gemaakt in Reporting Services 2005. De security met betrekking tot wie welke rapporten mag openen en wie welke parameters mag selecteren wordt bepaald in deze portal aan de hand van de NTUserName van de gebruiker (Active Directory). De portal vergelijkt deze NTUsername met een Werknemerstabel waarin meer informatie staat over de gebruiker, bijvoorbeeld zijn functie en kostenplaats.
Wie mag wat selecteren?
Om te bepalen wie wat mag selecteren (parameters) wordt ook weer gekeken naar de NTUserName van de gebruiker. Met behulp van stored procedures wordt nu de parameterbox op de rapportenportal gevuld met waarden.
Dit lijkt prima te werken tot je met drillthrough rapporten gaat werken. Stel dat ik een rapport (A) maak met een link naar een doorklikrapport (B). A stuurt een aantal parameters mee naar B om enkel de data te tonen die relevant is.
Zodra ik A exporteer naar Excel blijven de hyperlinks naar B intact. Ik kan vanuit Excel klikken op de link en kom netjes op rapport B uit. Het probleem is nu dat alle parameters worden getoond in de url. De gebruiker kan nu met een beetje handigheid de url aanpassen en gegevens te zien krijgen die niet voor zijn ogen zijn bestemd.
Voor dit probleem zijn meerdere oplossing mogelijk:
1) In de query van je rapport neem je iets op mbt security in combinatie met een security tabel
2) Op de database (niet aan te raden)
3) Versleutelingen meesturen in de link
In ons geval hebben we gekozen voor oplossing 3. Ik zal de details besparen waarom we dit gekozen hebben.
Uitleg mbv een eenvoudig voorbeeld
Rapport A heeft een parameter: GeselecteerdeJaar
Rapport B heeft een parameter GeselecteerdeJaar en Parameters SecurityPmt/ SecurityPmt2
Doel: Stel dat de gebruiker het jaar aanpast in de URL dan moet het rapport geen data laten zien.
In de hyperlink van A-> B stellen we in bij parameter mapping:
Linkerkant GeselecteerdeJaar, Rechterkant: =Parameters!GeselecteerdeJaar.Value
Linkerkant SecurityPmt, Rechterkant: =Parameters!GeselecteerdeJaar.Value * 2
We zeggen dus dat de SecurityPmt in Rapport B het gekozen jaar is * 2. Stel dat we 2008 kiezen als jaar zal hij 4016 meesturen als securityparameter.
Vervolgens moeten we nog een controle instellen op Rapport B.
We stellen een default value in voor SecurityPmt2
=IIF(Parameters! SecurityPmt.Value / 2 = Parameters!GeselecteerdeJaar.Value,1,0).
We zeggen hier: deel de securityparameter door 2. De uitkomst moet gelijk zijn aan het gekozen jaar. Als dit waar is dan word ik waarde 1, anders word ik waarde 0.
Tot slot zetten we een beveiliging op de dataregion door de visibility aan te passen:
=IIF(Parameters! SecurityPmt2.Value=1,False,True)
Als de waarde v/d parameter 1 is moet hij NIET ONZICHTBAAR worden. Als hij 0 is wel.
Dit is dus een goede methode om extra beveiliging toe te passen bij het doorsturen van parameters. Het voorbeeld dat ik geef met *2 is natuurlijk niet voldoende. Zoek hier een mooie formule bij die niet te raden is en stuur deze mee.
Oplossing voor het tonen voor bijv. alle klanten waar de afgelopen 2 jaar geen zaken mee is gedaan, uitgaande van een integer als datumveld.
SELECT * FROM AANKOPEN
WHERE LaatsteAankoop <= (YEAR(DATEADD(yy, -2, GETDATE())) * 10000) + (MONTH(DATEADD(yy, -2, GETDATE())) * 100) + (DAY(DATEADD(yy, -2, GETDATE())))
Delen door 0 kan niet in reporting services/sql server. Met deze eenvoudige formule kun je delen door 0 voorkomen zodat de #error melding niet meer verschijnt:
= IIF(SUM(Fields!OmzetVorigJaar.Value) + 0 = 0, Nothing,
SUM(Fields!MargeVorigJaar.Value) / IIF(SUM(Fields!OmzetVorigJaar.Value)+0=0,1,
( SUM(Fields!OmzetVorigJaar.Value) ) ))
Deze formule rekent het margepercentage uit van het vorige jaar (margevorigjaar/ omzetvorigjaar).
Eerst controleer je of het getal waardoor je wilt delen 0 is ( getal + 0 = 0). Als dit zo is vul je de textbox op met Nothing en stopt de verdere berekening. Nothing is hetzelfde als NULL in SQL, alleen met een ander naampje. Je kunt ook een lege string in de textbox stoppen: "". Mijn ervaring is dat je hiermee problemen kunt krijgen als je de uitkomst van deze cel in een andere cel wilt gebruiken (ReportItems). De input is dan een lege string en daar kun je geen formules mee maken.
Als het getal geen 0 is dan wordt de deling uitgevoerd.
