Posts

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>>%'
1 comment
Read more

XL3SetProperty Connection

Image
With V9.1 we can dynamically change a connection during runtime , using XL3SetProperty. The general statement looks like this: =XL3SetProperty("connection","<<NumberOfTheBaseConnection>>","copy",   “<<numberOfTheAlternativeConnection”) To get this to work you need at least 3 connections to your source 1 = Connection to your base source (This will be your “working” connection) 2 = Connection to your alternative source 3 = Connection to your base source   (You need this to be able to switch back to your base source) In this example we are using 1 as working , 5 as alternative and 6 as base source. Add a slicer to your report which fills a cell with the connection you want to use: Add a cell with the following formula: Be sure to have your working connection between quotes! And the cell properties of B1 should be set to “text”. Till Next Time
Post a Comment
Read more

XLCubed XL3Toolbar:SAVETOPOWERPOINT

You already might know the XL3Toolbar buttons . XL3Toolbar:REFRESH              XL3Toolbar:PRINT              XL3Toolbar:SUBMITCHANGES            XL3Toolbar:SAVETOEXCELFIXED            XL3Toolbar:SAVETOEXCELLIVE            XL3Toolbar:SAVETOREPOSITORY            XL3Toolbar:PRINTSHEET            XL3Toolbar:PRINTWB              XL3Toolbar:CLOSETAB              XL3Toolbar:SAVETOEXCELFIXEDSHEET I found a (currently) undocumented one: XL3Toolbar:SAVETOPOWERPOINT It does exactly as the name suggests, it opens the save / export to PowerPoint dialog. The XL3Toolbar: are very handy if you use picture links . Till Next Time
Post a Comment
Read more

XLCubed who's calling

Image
If you have multiple reports calling a master report like this : You can XL3WebReportLink to jump to the master report. Do you know that you can use this also to see which report is calling the master report and use this info to jump back. Add the following (WEB) parameter to your master report: CallingReportName CallingReportAdress CallingReportWorksheet From your linking report create a link using XL3WebReportLink including these parameters: =XL3WebReportLink( "Master Report.xml";"<<Master Report Name>>";0; "CallingReportName";"<<Linking Report Namer>>"; "CallingReportAdress";"Linking Report.xml"; "CallingReportWorksheet";"Sheet1"; "XL3ActiveWorksheetname";"Sheet1";"closecopies";TRUE) On your master report you now can make a back link like: =XL3WebReportLink( ...
Post a Comment
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),...
Post a Comment
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:
Post a Comment
Read more