XLCubed Good Housekeeping

When I make a new report I start with adding two extra sheets:
  • Slicers
  • Parameters



The 'slicers' sheet I use as a landing page for all the slicers, the 'parameter' page to store temp parameters and hidden calculations

Comments

Post a Comment

Popular posts from this blog

XLCubed is table used

quick script to see if a table is used in a xlcubed report: IF OBJECT_ID('tempdb..#ReportData') IS NOT NULL DROP TABLE #ReportData SELECT --DISTINCT    fp.cPath  as ReportPath  , r.[cName]  as ReportName  , convert(varchar(8000),f.oData) as ReportData into #ReportData FROM [XLCubedWeb].[dbo].[XL3Reports] r inner join [XLCubedWeb].[dbo].[XL3ReportFiles] rf on r.nReportId = rf.nReportID inner join [XLCubedWeb].[dbo].[XL3File] f on  rf.nFileId = f.nFileID inner join [XLCubedWeb].[dbo].[XL3FoldersWithPath] fp on r.nFolderId = fp.nFolderID where f.cName = 'workbook.xl3xml' select ReportPath,ReportName from  #ReportData where  ReportData like '%<<enter table_name>>%'
Read more

XLCubed clickable region in image

Image
We needed a Q&D way for our clients to indicatie te location of their pain in/on there body. This is how we did it using XLCubed: - Import a picture into Excel, either using Matt Parkers site: Pic to Excel. Or use a bit of C#: PicToExcelV2 ! Don't go overboard with the resolution, larger then 50 x 50 will kill the performance of the browser! Basically you will get a sheet with lots of small cells where the background color is the pixel color:   Each cell has the following formula: "=XL3Link(XL3Address($A$1);" ";3;XL3Address($CA$55);"Click 23 , 15   ";"XL3Tooltip";"Click to indicate were you have pain") The yellow numbers are the X and Y coordinates. Use a viewport to integrate on other pages:
Read more

XLCubed to which Cube or Database is a report connecting?

Image
We are planning a major overhaul on one of our cubes. The problem is that we don't know which reports are connection to this cube. We have over twohunderd possible candidates and don't want to open each one to check. I wrote a simple query to extract the connections from the repository: SELECT DISTINCT   fp.cPath as ReportPath , r.[cName] as ReportName , case when CHARINDEX('<connections>',convert(varchar(max),f.oData)) > 0 THEN SUBSTRING(convert(varchar(max),f.oData), CHARINDEX('<connections>',convert(varchar(max),f.oData)), CHARINDEX('</connections>',convert(varchar(max),f.oData)) - CHARINDEX('<connections>',convert(varchar(max),f.oData))+14) END as CubeConnections , case when CHARINDEX('<sqlconnections>',convert(varchar(max),f.oData)) >0 THEN SUBSTRING(convert(varchar(max),f.oData), CHARINDEX('<sqlconnections>',convert(varchar(max),...
Read more