XLCubed Show Hide Columns with button

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
,XL3Address(Parameters!$B$11),TRUE,
XL3Address(Parameters!$B$12),TRUE
)
,XL3Link(XL3Address($A$1),"Hide Details",3
,XL3Address(Parameters!$B$1),FALSE
,XL3Address(Parameters!$B$2),FALSE
,XL3Address(Parameters!$B$3),FALSE
,XL3Address(Parameters!$B$4),FALSE
,XL3Address(Parameters!$B$5),FALSE
,XL3Address(Parameters!$B$6),FALSE
,XL3Address(Parameters!$B$7),FALSE
,XL3Address(Parameters!$B$8),FALSE
,XL3Address(Parameters!$B$9),FALSE
,XL3Address(Parameters!$B$10),FALSE
,XL3Address(Parameters!$B$11),FALSE
,XL3Address(Parameters!$B$12),FALSE
))


Till Next Time!

Comments

Post a Comment

Popular posts from this blog

XLCubed XL3ExecuteSQLProc error

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='...
Read more

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

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
Read more