[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!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s