XLCubed101

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),...

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:

I didn't see explicitly mentioned in the manual , although you can sort assume it based on " XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] ) ". There is a limit of 13 on the number of Range / Value combos you can make. This will work: =XL3Link(XL3Address($B$1) , IF(Parameters!$B$01=TRUE,"-","+"),3 ,XL3Address(Parameters!$B$01),IF(Parameters!$B$01=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$02),IF(Parameters!$B$02=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$03),IF(Parameters!$B$03=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$04),IF(Parameters!$B$04=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$05),IF(Parameters!$B$05=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$06),IF(Parameters!$B$06=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$07),IF(Parameters!$B$07=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$08),IF(Parameters!$B$08=TRUE,FALSE,TRUE) ,XL3Address(Parameters!$B$09),IF(Parameters!$B$09=TRUE,FALSE,TRUE) ,XL3Add...

Yes, I know, this is RTFM .... When you configure the scheduler in the XLCubed admin tool it takes Y and N as parameters for "Use SSL" and "Is Global", not T or F! Till Next Time

First of al this is a RTFM if you know where to look ;-). Consider the following (simplified) procedure) CREATE PROCEDURE uspGetNewId @UserName varchar ( 250 ) AS BEGIN -- -- Some more magic -- SELECT NEWID () INTO TBL_LOG END If you add =XL3RunSqlProc(B1,1,"uspGetNewId",XL3UserName()) to your sheet you get the following error: Procedure or function 'uspGetNewId' expects parameter '@UserName' , which was not supplied. The problem is dat XLCubed access the database with hardcoded parameters: @Parameter1,@Parameter2,@Parameter3.... There are 3 approches for fixing this: 1 Rewrite or wrap your procedure like this, using the default parameters : ALTER PROCEDURE uspGetNewId @Parameter1 varchar ( 250 ) AS BEGIN -- -- Some more magic -- SELECT NEWID () INTO TBL_LOG END 2 Use XL3RunSQL instead and compose your own SQL string: =XL3RunSql(B2,1,"EXEC dbo.uspGetNewId @UserName='...

One of our user had the need for a button to "Show" or "Hide" the monthly details in order to avoid report clutter: This how we did it: On the parameters sheet we added an entry for each column we might want to hide: On the top row of our data sheet we added the following formules: =XL3ColumnVisible(Parameters!$B1) =XL3ColumnVisible(Parameters!$B2) =XL3ColumnVisible(Parameters!$B3) =XL3ColumnVisible(Parameters!$B4) etc Once you have tested everything you can hide this row! In Cel B3 we added the following formula: = IF(Parameters!B1=FALSE, XL3Link(XL3Address($A$1),"Show Details",3 ,XL3Address(Parameters!$B$1),TRUE ,XL3Address(Parameters!$B$2),TRUE ,XL3Address(Parameters!$B$3),TRUE ,XL3Address(Parameters!$B$4),TRUE ,XL3Address(Parameters!$B$5),TRUE ,XL3Address(Parameters!$B$6),TRUE ,XL3Address(Parameters!$B$7),TRUE ,XL3Address(Parameters!$B$8),TRUE ,XL3Address(Parameters!$B$9),TRUE ,XL3Address(Parameters!$B$10),TRUE ,XL3Addres...