Revert to Parent Security en PowerShell

Bon un petit tip aujourd’hui: je n’ai rien trouvé de tel sur Stack ou autre. Le case study est un serveur SSRS dont toute les sécurités ont été cassées par des Content Manager peu scrupuleux. Comment réparer ce désastre sans avoir à vérifier tous les rapports? La solution: PowerShell et 3 méthodes du WebService de SSRS (ReportServer2010.asmx):

  • ListChildren(path) pour parcourir les descendants
  • GetPolicy(path, out inheritsParentSecurity) qui va donner les policies et surtout un booléen qui renseigne si l’élément adopte la sécurité de son parent ou pas
  • InheritParentSecurity(path) qui va restaurer la sécurité parente sur l’élément

Un bête parcours d’arbre dans une fonction récursive résoud rapidement le problème

Function RevertToParentSecurity ([string]$Path)
{
    $InheritParent = $true
    $CurrentPolicies = $Proxy.GetPolicies("$Path", [ref] $InheritParent)

    #If the current Path is not the root and it has custom policies applied, reset them
    if ( ( ! $InheritParent ) -and ( $Path -ne "/" ) )
    {
        Write-Host $Path -foregroundcolor "red"
        $Proxy.InheritParentSecurity($Path)
    }
    else
    {
        Write-Host $Path -foregroundcolor "green"
    }

    #If the current is a folder, recursive call on its children
    $ItemType = $Proxy.GetItemType("$Path")
    if ( $ItemType -eq "Folder" )
    {
        $Proxy.ListChildren("$Path", $false) | ForEach-Object {
            RevertToParentSecurity $_.path
        }
    }
}

Il suffit alors de l’appeler.
 

$WebServiceVersion = "2005"
$ReportServerUri = "http://localhost/ReportServer/ReportService$WebServiceVersion.asmx"
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService$WebServiceVersion -UseDefaultCredential;
Write-Host Connected to $Proxy.Url
RevertToParentSecurity "/"

Voilà, si ça peut servir à d’autres et de vous éviter la prise de tête que ça m’a occasionné 🙂
Bonne soirée!

Je n’ai plus de groupes dans mon Tablix: comment recréer un groupe de détail?

Situation fâcheuse s’il en est, et vous avez probablement comme moi déjà été confronté à ça: un vieux rapport pourri d’expressions, vous en supprimez les lignes, faites une sauvegarde, et boum: plus de groupe de détail. Et contrairement aux versions précédentes, depuis 2008 il n’y a pas moyen d’en créer un facilement.

En fait il suffirait simplement que Microsoft permette de convertir tout groupe statique – en advanced mode – en groupe de détail, mais passons…

Alors comment faire: on va s’amuser avec un peu de RDL tout bête.

On commence par passer en mode code, et aller chercher les lignes sans groupe associé, elles apparaissent dans la balise TablixRowHierachy du Tablix sous forme de TablixMember vides. La première correspond à ma ligne d’entête, la seconde à ce que je veux qui devienne ma ligne de détail.

<Report xmlns="http://schemas.microsoft.com...">
  <Body>
    <ReportItems>
        <Tablix>
            <TablixRowHierarchy>
                <TablixMembers>
                    <TablixMember />
                    <TablixMember />
            </TablixRowHierarchy>

Il suffit donc d’ajouter un groupe sur ce TablixMember, comme ça:

<Report xmlns="http://schemas.microsoft.com...">
  <Body>
    <ReportItems>
        <Tablix>
            <TablixRowHierarchy>
                <TablixMembers>
                    <TablixMember />
                    <TablixMember>
                        <Group Name="detail"/>
                    </TablixMember>
            </TablixRowHierarchy>

Voilà un truc toutbête qui vous permettra d’éviter de jeter votre Tablix en pareil cas 🙂
Bonne rentrée!
 

[SSRS] Un tableau croisé dynamique (oui dynamique!) avec Reporting Services

Alors celle là c’est l’arlésienne des questions Reporting Services.

Peut-on utiliser RS pour générer un TCD?

La réponse que l’on entend souvent tourne autour de ce gloubiboulga de Consultant MSBI « Oui mais c’est de la bidouille, RS n’est pas un client ad-hoc. Sinon vous connaissez Excel?« 

Soyons honnête c’est assez vrai. Mais ce n’est pas parce que tordre un outil n’est pas vraiment complètement prévu qu’il ne faut pas le faire. Bah oui sinon on n’aurait jamais eu de dimensions techniques dans des cubes AS, et puis la vie serait ennuyeuse il faut avouer. Alors pourquoi ce post, et pourquoi aujourd’hui? Parce qu’on vient de me demander ce que fait la fonction Fields() – oui je dis bien la fonction Fields() – dans SSRS. Et que l’utilité principale que je lui connais est précisément de faire ça.

La fonction Fields()?

La fonction Fields(string) permet de convertir un nom de champ passé en paramètre en l’expression Fields!.Value correspondante dans la portée de l’expression. Oui, en quelque sorte c’est de la Reflection, Reporting Services Staïle.

Oui mais quel rapport avec un TCD?

J’y viens, j’y viens. Créons un rapport avec une matrice. Ce rapport s’appuie sur une requête bêbête en SQL, qui renvoie des trucs de vente.

Créons deux datasets bidons. Un s’appelle Mesures, l’autre Axes. Chacun comprend respectivement les noms de champs du dataset Données du type considéré, à savoir que Mesures contient ‘PU’, ‘Qté’ et ‘CA’ et Axes le reste des noms. Appuyons maintentant trois paramètres, deux monovalués s’appuyant sur Axes et s’appelant respectivement Lignes et Colonnes et un multivalué s’appelant Mesures et s’appuyant sur… bravo vous avez deviné, le dataset Mesures. OK ça ne fait absolument rien pour le moment, mais il faut avouer, c’est beau.

La partie drôle arrive. Créons une matrice, avec un groupe de lignes, un groupe de colonnes, et trois cellules pour nos trois mesures. L’expression de groupement en ligne est donc basée sur Fields et vaut:

=Fields(Parameters!Lignes.Value).Value

L’expression en colonnes est du même esprit. Enfin, il ne reste plus qu’à conditionner la visibilité des cellules de mesures à une expression du genre:

=InStr(Join(Parameters!Mesures.Value, », »), »PU »)=0

Une fois que l’on fait tourner tout cela, on a donc quelque chose du style (et on ne se MOQUE PAS des couleurs :)):

On pourrait aller beaucoup plus loin, gérer un nombre d’axes dynamique (bon avec une limite certes…) mais ce serait exagérer. Simplement c’est un usage marrant de la fonction Fields je trouve! Vous en pensez quoi?
A bientôt!
PS: Pour les nostalgiques, le DataSet s’appuie sur Northwind, j’étais en mode hommage à SQL 2000 et j’en avais assez d’AW.

Edit: Charles-Henri Sauget a lu ce papier et, voyant que je ne me décidais pas à partager mon RDL, a implémenté la solution que je décris sur son blog. Allez faire un tour 😉

[SSRS] Dépasser 65536 lignes dans un export Excel

Cette astuce est un petit peu connue mais entre les forums et les clients, cela fait trois fois en quelques jours que l’on me pose cette question: comment faire pour exporter des rapports SSRS de plus de 65536 lignes en XLS sans avoir pour résultat ça:
Donc je me permets de la poster, si cela peut rendre service.
Alors l’idée est d’exploiter une fonctionnalité de l’exporteur Excel, qui crée une nouvelle sheet lorsque l’on insère un PageBreak explicite. Pour cela j’ai d’abord créé une table dans un rapport SSRS qui s’appuie sur une requête de test qui me renvoie 100 000 lignes sur… 2041 pages A4. 
Evidemment, l’export de celle-ci en Excel rate lamentablement (cf plus haut). Alors la solution pour pallier à cela est donc de créer des PageBreak toutes les 65536 lignes: on va donc créer un groupe dans la table, qui groupe les données sur le résultat de la division entière du numéro de la ligne par 65536. Si vos souvenirs de CM1 sont bons, alors vous ne serez pas étonné que 1 DIV 65535 = 0, 65536 DIV 65535 = 1…etc. L’expression équivalente en RS se traduit par Int(RowNumber(Nothing) / 65536) comme visible ci-dessous:
Il ne reste plus qu’à dire à SSRS d’insérer un PageBreak entre chaque instance du groupe (i.e. entre chaque valeur distincte de Int(RowNumber(Nothing) / 65536)). Cela s’active dans l’onglet PageBreaks de l’éditeur de groupe. Une fois ceci fait, l’export Excel se déroule sans problèmes et me sort un Excel de deux sheets.

Mais car il y a un mais, ce PageBreak disgracieux vient s’ajouter à tous les exports, y compris à ma visualiseuse HTML. J’ai donc un saut de page inutile page 1308 de mon rapport de 2000 pages.  Pointillisme peut-être mais cela peut heureusement être évité, grâce à la fantastique variable globale RenderFormat introduite depuis SQL Server 2008 R2. Cette globale permet de récupérer l’exporteur utilisé dans une expression, et donc de conditionnellement changer un élément en fonction de l’extension de rendu. Dans mon cas, je vais désactiver le PageBreak, sauf si je suis en Excel. La valeur de PageBreak.Disabled sur le groupe est donc dynamique et vaut (Globals!RenderFormat.Name <> « EXCEL »).

Et c’est tout de suite plus joli, les autres extensions ne sont plus affectées par ce hack.

Tout cela deviendra inutile (ou presque) avec SQL Server 2012 qui dès l’année prochaine permettra d’utiliser le XLSX pour les exports. Bon ça servira lorsqu’on voudra dépasser les 1.000.000 de lignes 🙂
A bientôt!

Utiliser la fonction Lookup pour traduire les parties statiques des rapports

Analysis Services offre des possibilités de traduction intéressantes, qui permettent évidemment de traduire les rapports qui s’appuient dessus. Grâce à la communication du LCID de l’interface cliente, la structure et le contenu du cube sont alors exposées dans la langue adéquate.

Mais quiconque à déjà réalisé des rapports sait que ceux-ci sont aussi composés de parties « statiques », non dépendantes du cube, qui par conséquent ne tirent aucun bénéfice des traductions OLAP. Les titres des rapports, entêtes de tableaux, descriptions, sont alors autant de libellés qu’il va falloir gérer dans un contexte internationalisé.

Une solution intéressante à ce problème est apportée par la fonction Lookup de SQL Server 2008 R2, celle ci permettant de récupérer dans un DataSet l’enregistrement correspondant à une valeur de clé. En effet, stocker les libellés dans une base est une chose, les récupérer en utilisant les (limitatives) fonctions d’agrégations de SSRS en est une autre.

1) 1ère étape, on stocke dans une table au format Clé/Valeur/Langue les différents libellés du rapport. Prenons comme exemple Produit:

Rapport;Libelle;Traduction;Langue;
Ventes;Product;Produit;FR-FR;
Ventes;Product;Product;EN-EN;
...

2) 2ème étape on crée un DataSet, par exemple nommé « Dictionnaire », qui récupère uniquement et dynamiquement les libellés correspondant au rapport et à la langue en cours:

SELECT Libelle, Traduction
FROM dbo.Traductions
WHERE Rapport=@Rapport AND Langue=@Langue

@Rapport est mappé à la valeur globale Globals!ReportName, @Langue à User!Language, deux valeurs exposées par défaut par Reporting Services qui donnent respectivement le nom du rapport en cours et la culture de l’utilisateur connecté.

3) 3ème étape dans chaque cellule ayant besoin d’être traduite, on va chercher le libellé correspondant, avec une expression Lookup:

=Lookup("Dictionnaire";Fields!Libelle.Value; "Product";Fields!Traduction.Value)

Magie de la technique, les libellés sont traduits automatiquement, et surtout on peut rajouter des langues à volonté sans retoucher aux rapports!
En espérant vous avoir donné des idées.

PS1: pour ceux que cela intéresse on aurait pu effectivement imaginer stocker dans la table le nom du contrôle SSRS plutôt qu’une clé gérée par le développeur. Le problème est qu’il est difficile de manière propre de connaître le nom d’un contrôle dans une expression SSRS. Essayez vous verrez c’est très sale et pas assuré d’être stable. Cela oblige donc à documenter.
PS2: On peut aussi vouloir gérer la traduction par un paramètre, auquel cas on n’utilisera pas User!Language mais un paramètre à soi.
PS3: Attention à l’interaction avec SSAS: Reporting Services travaille avec des libellés de culture (fr-fr, en-us…), SSAS avec des LCID par défaut.

[SSRS] Une Parent Child mise à plat dans plusieurs champs

Une petite note rapide: dans Reporting Services, lorsque l’on veut utiliser une hiérarchie Parent/Enfant, tous les membres sont exposés dans la même colonne, peu importe leur niveau.
Et si vous désirez avoir un champ par niveau? Impossible de CrossJoiner sur une même hiérarchie (C’est interdit par MDX!), et si vous googlisez, on vous dira soit que c’est un soucis de provider et que vous n’avez qu’à utiliser l’OLE DB, soit de créer des membres calculés, soit d’arrêter les P/C voire Reporting Services tout court…
Mais on peut hacker, un peu de la même manière que pour le DrillThrough dans un de mes vieux posts.
La propriété « DbpropMsmdFlattened2 »

Effectivement, ce soucis de colonne est un soucis lié au provider, plus exactement de configuration de la commande AS. Sur un sujet lié lui aussi à RS, Greg Galloway m’a fait tilter dans ce post, en disant que le provider Analysis Services pour RS met les propriétés ReturnCellProperties et DbpropMsmdFlattened2 à true. Son post parle du premier, le deuxième nous intéresse aujourd’hui.
Tout d’abord son assertion est vraie: vous pouvez le constater dans le Profiler sur les events Query Begin.
Quel est l’effet de ces deux propriétés? Dans la doc des propriétés XMLA, il est spécifié que la première demande l’exposition des propriétés de cellules en tant que colonnes – ce dont traite Galloway – et que la seconde « Fournit tous les membres d’une hiérarchie parent-enfant dans une seule colonne de table« .

Bingo?
On pense alors qu’il ne reste plus qu’à rajouter DbPropMsMdFlattened=false à la connection string…. Et bien non car cela ne marche pas: la propriété est insérée dans la commande par RS, donc après établissement de la connexion, et override le choix fait dans la connection string.
La solution? Switcher en mode de requête DMX.
Dans ce cas, ces deux propriétés ne sont pas affectées (on le voit là aussi dans le Profiler, les deux lignes ne sont pas présentes dans l’entête de commande), mais ce pour des raisons que je ne connais pas. On perd le designer mais la requête marche comme on le souhaite, j’ai bien mes niveaux dans des champs différents.
Conclusion
Après est-ce la bonne approche? A vous de juger, effectivement cela ressemble beaucoup à un hack douteux mais dans le cas (qui est le mien) ou on ne fait pas un usage extensif des P/C, si un hack certes douteux mais utilisé rarement évite de recréer et maintenir une data source dédiée ça peut valoir le coup…
A bientôt!

Dernière valeur d’un DataSet comme valeur par défaut

C’est drôle comme parfois une solution toute bête peut vous échapper pendant pas mal de temps. Lorsque vous utilisez un DataSet pour remplir les available values d’un paramètre dans SSRS, il vous est sûrement déjà arrivé de souhaiter sélectionner la dernière valeur comme valeur par défaut (allez par hasard pour une dimension temps).

Donc première intuition vous sélectionnez une expression comme valeur par défaut, expression qui fait référence au DataSet en cours (Fields!…) pour prendre la dernière valeur (aggrégation Last()).

Et vous prenez une erreur comme quoi vous ne pouvez pas utiliser Fields! dans ce type d’expression d’où vous faites comme tout le monde et vous réécrivez un DataSet spécifique pour la dernière valeur, ce qui est quand même dommage d’un point de vue bases de données, parce que l’info vous l’avez déjà dans le DataSet précédent…

Et en réfléchissant un peu on trouve rapidement une solution: il suffit de déclarer un paramètre « dummy », internal et multivalué, qui prend les valeurs du même DataSet que le vrai paramètre intéressant en available et en default. Dans notre paramètre initial, section default, il suffit alors de saisir l’expression:

=Parameters!Dummy.Value(Parameters!Dummy.Count-1)

Et ça marche: on n’a pas le droit de référencer Fields! mais on peut référencer Parameters dans cette expression.
En espérant que ça vous serve, à bientôt!

[SSRS] DrillDown to… à la Excel sous Reporting Services

Que voilà une demande intéressante et amusante. Dans Excel, il y a sur les hiérarchies la possibilité d’effectuer un Drill-Down to jusqu’à un niveau spécifique. Comprendre que l’on n’est pas obligé de descendre toute la hiérarchie pour enfin arriver au niveau souhaité. Si il y en a une dizaine, ça économise des clics 🙂

Dans Reporting Services, les boutons de Drill sont positionnés potentiellement sur une TextBox lorsqu’elle est par exemple le ToggleItem d’un groupe. Ce toggle est déplié, ou plié par défaut.
Alors comment faire pour satisfaire à la demande en question? Simplement en instaurant un paramètre masqué DrillDownLevel, qui va permettre via des Actions sur des TextBox de réappeller le rapport avec un DrillDownLevel spécifique. Ce DrillDownLevel paramétrant qui gouverne l’état visible ou invisible du groupe.
Création du paramètre.
Rien de sorcier: un paramètre technique masqué de type integer fera très bien l’affaire. Ne pas oublier de lui mettre une Default Value à 0.

Configuration des groupes
La seule petite subtilité est celle ci: la visibilité du groupe n’est pas True ou False en fixe: elle dépend de la valeur de DrillDownLevel: pour l’item de niveau 1, il est masqué tant que le DrillDownLevel est inférieur à 1.
Sur la même logique la TextBox dépliant ce groupe (généralement dans le groupe parent) est à l’état [-] dès que ce DrillDownLevel est supérieur ou égal à 1.
Une action pour commander tout ça

J’ai fait le choix de commander la chose via des actions sur les entêtes de colonne. Sur chaque entête de colonnes on positionne une action GoToReport, en passant la valeur appropriée de DrillDownLevel si on est sur la bonne colonne.

Et voilà le résultat! (Je sais je suis nul en choix de couleurs, c’est un de mes défauts préférés).

Bon reporting!

[SSRS] Un paramètre pour rechercher… en SQL

Une petite variante sur le même mode de ma recherche dans une dimension en MDX qui commence à dater: la version SQL.
Deux choses à créer
– Un paramètre multivalué (le champ de recherche)
– Un DataSet SQL (le résultat)

On commence par créer un joli DataSet sur une source SQL qui a la structure de ce que l’on veut renvoyer. Pour moi ce sera ça:
SELECT FrenchProductName
FROM AdventureWorksDW2008..DimCustomer
On le valide pour avoir les métadonnées. Maintenant on va créer une expression en lieu et place de cette requête, en cliquant sur le bouton ad-hoc:

L’expression a pour but de générer une requête like %% pour chaque mot recherché, le tout unionné et ordonné par nombre de matches (bon je vous l’accorde ça n’est pas PageRank).

SELECT FrenchProductName
FROM
(
SELECT ProductKey, FrenchProductName
FROM AdventureWorksDW2008..DimProduct
WHERE FrenchProductName LIKE ‘%vélo%’
UNION ALL
SELECT ProductKey,FrenchProductName
FROM AdventureWorksDW2008..DimProduct
WHERE FrenchProductName LIKE ‘%randonnée%’
)
AS T
GROUP BY ProductKey, FrenchProductName
ORDER BY COUNT(*) DESC, FrenchProductName ASC
Pour générer cela on va donc créer une expression qui est d’abord constituée du début et de la fin de la requête.
=
« SELECT FrenchProductName, COUNT(*) Rank « +
« FROM « +
« (« +
« ) »+
« AS T « +
« GROUP BY ProductKey, FrenchProductName « +
« ORDER BY COUNT(*) DESC, FrenchProductName ASC »
Entre chaque élément contenu dans le paramètre Recherche, on va intercaler du SQL suffisant à générer l’ensemble de requête UNION visibles au dessus.
Join
(
Parameters!Recherche.Value,
« %’ « +
« UNION ALL « +
« SELECT ProductKey,FrenchProductName « +
« FROM AdventureWorksDW2008..DimProduct « +
« WHERE FrenchProductName LIKE ‘% »
)
Ne reste plus qu’à rajouter le code SQL suffisant à traiter le premier et le dernier élément de la liste et on a le résultat final:

="SELECT FrenchProductName, COUNT(*) Rank "+
"FROM "+
"("+
"SELECT ProductKey, FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"+
Join
(
Parameters!Recherche.Value,
"%' "+
"UNION ALL "+
"SELECT ProductKey,FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"
)+
"%') AS T "+
"GROUP BY ProductKey, FrenchProductName "+
"ORDER BY COUNT(*) DESC, FrenchProductName ASC"

Une fois l’expression définie comme requête du DataSet on peut la tester, par exemple en la donnant comme source d’un paramètre:

Voilà, à bientôt!

[SSRS] Traitement des hiérarchies Ragged dans le Query Designer de RS

Le Query Designer de Reporting Services pour la partie MDX a une particularité sympathique que vous avez peut être déjà remarqué si vous ou vos utilisateurs utilisez Report Builder.
Il gère l’utilisation des hiérarchies Ragged n’importe comment. Et je me suis rappellé de ça hier soir en faisant une démo.
Pour rappel une hiérarchie ragged est une manière de gérer des hiérarchies déséquilibrées (ou la distance entre (All) et le membre le plus fin d’une branche n’est pas constante: cas typique des employés d’une entreprise…) avec une hiérarchie utilisateur « classique » (et non une Parent Child) principalement pour améliorer les perfs et contourner la relative mauvaise gestion des P/C par SSAS. On utilise pour ça la propriété HideMemberIf sur les niveaux de la hiérarchie utilisateur pour cacher des niveaux superflus.
Une fois réglé les problèmes connus (on borde par le bas, i.e. pour que ça marche les niveau « à vide » devront être vers le bas et non pas au milieu de la hiérarchie, cf ce post de Chris Webb) on est tous contents: ça marche dans Excel, dans les OWC (BIDS + SSMS) et dans Reporting Services… voir ci dessous, sans commentaires:

Pour information ici j’ai projeté une Ragged de 6 niveaux créée en HideMemberIf=ParentName, ainsi qu’une mesure de base, et j’ai filtré avec un attribut qui stocke le niveau (i.e. la distance au membre (All)) pour ne prendre en compte que les membres jusqu’au niveau 5, donc pas le niveau fin.

Et ça ne me renvoie rien: alors que je vous assure, j’ai des données.
Alors pourquoi?
Tout simplement parce que ce Query Designer est stupide à souhait. Désolé de le dire mais c’est le cas. Si je fais rapidement une analyse de ce qu’il me projette en lignes je verrais que c’est ce set là:
{ ([Organisation].[Hiérarchie Organisations].[Niveau 6].ALLMEMBERS ) }
Or effectivement, je n’ai pas de membres niveau 6. Si j’écrivais ce MDX, dans le but de projeter tous les membres de toute une hiérarchie j’écrirais quelque chose de la sorte:
{ ([Organisation].[Hiérarchie Organisations].ALLMEMBERS ) }
Puisque je n’ai jamais demandé de me filtrer uniquement les membres du Niveau 6. Bref en éditant le MDX je récupère ce que je veux , des (null) aux niveaux non renseignés. Au passage le NON EMPTY m’enlève le niveau 6 (cf mon filtrage plus haut).

Donc je dois faire écrire du MDX à mes utilisateurs dans Report Builder?

Hmmm. Autant vous dire tout de suite que ça pourrait être une solution immédiate mais que ce n’est pas forcément celle que je vous recommande :).
L’investissement en formation serait à mon avis un poil disproportionné. Comme ça je vois une solution utilisable dans le designer: rendre les niveaux de la hiérarchie utilisateur visibles.
Dès lors il suffit de notifier aux utilisateurs que ce sont ces hiérarchies d’attributs qui doivent être utilisées dans ce type de cas pour afficher les niveaux par CrossJoin, la précédente hiérarchie étant alors utilisée uniquement comme filtre. Problème: avec mes 6 niveaux cela risque rapidement de hurler. L’utilisateur n’aime pas trop enchaîner les glisser-déposer.

Si vous avez une suggestion, n’hésitez pas à la donner, je suis preneur.
Bonne journée!
PS: Vous avez le droit de voter pour corriger cela, sur Connect.