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!