XLCubed Select / Deselect All

I had a request to create a select all deselect all button in XLCubed





After some deep thoughts I found the following solution
Step 1:
Create a table slicer:





Set your selection and initial value range
Set "Set initial value when" to "Available items change"
Edit your query:


The part:
"Union All
Select 'Dummy' + Cast(NewId() As VARCHAR(50))"
Is necessary to "tell" the slicer it has new data after each refresh.


Connect your table or grid to the slicer in the normal way:
"Select tParameters.*
From tParameters
Where tParameters.UserName In (@UserName)"


The source for the intial values in also a table

Query:
"Select Top (1) tParameters.UserName
From tParameters
Union
Select tParameters.UserName
From tParameters
Where Cast(1 As BIT) = @SelectAll"


This query returns the first row if @SelecAll = FALSE else you get all the rows.


On your parameters sheet add "=XL3RefreshObjects(B2,TRUE,TRUE,TRUE,TRUE,TRUE)" to cell B3



And finally lets create the button:
=XL3Link(XL3Address(A1),IF(Parameters!B1=FALSE,"SELECT ALL","DESELECT ALL"),3,XL3Address(Parameters!B1),NOT(Parameters!B1),XL3Address(Parameters!B2),TRUE)


Till Next Time







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

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

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