Customer Banners (Ads) - SpiceUp. AX and SpotfireX Disclaimer



If you find this site useful and you want to support, buy me a coffee   to keep this site alive and without ads.

Remove (Empty) from cross table without messing number formatting

Normally I use a sn([column],"") to hide "(Empty)" but if my column data type is other than string, it affects the formatting (i.e. currency).

For that reason, Jquery to the rescue. This will replace all divs with class cell-text that contains the "(Empty)" string with "". Here is how:

$('div.cell-text:contains("(Empty)")').text("")

Thanks Jolene Robertson!

3 comments:

kinnan said...

where to put this code? when i put on the text area as new java script it worked for first time. if I navigate to another page and come back the "(EMPTY)" thing re-apprears

Jose Leviaguirre said...

Hello Kinnan,

This works well on old versions of spotfire but in newer versions you can style the Empty color text to match the color background

Unknown said...

Hello Kinnan,

Following is the button script where I generated cross table. Could you please help in formatting the following columns as mentioned below.

1.Column [Total Item#] required as Number formatting

2.Column [Automatching %] required as percentage formatting.

Rest all columns default formatting.


Current Script:
from Spotfire.Dxp.Application.Visuals import CrossTablePlot
myVis = Document.ActivePageReference.Visuals.AddNew[CrossTablePlot]()

#The myVis variable now references the newly created visual
myVis.Title = 'PayTm usage per accountant & country'

# data table
newTable = Document.Data.Tables.Item['PayTM KPIs']
myVis.Data.DataTableReference = newTable
# Cell values:
myVis.MeasureAxis.Expression = "Sum(case when [Report Date]=Max([Report Date]) then [Total Item Nr] end) as [Total item #],Sum(case when ([Report Date]=Max([Report Date])) and ([Recon Status]='Recorded') and (([Matched]='NO') or (([Matched]='FUZZY') and ([Recon Status]='Recorded'))) then [Total Item Nr] end) as [Unmatched Recorded #],Sum(case when ([Report Date]=Max([Report Date])) and ([Matched]='YES') and ([auto_matched]=True) then [Total Item Nr] end) as [Auto match#],Sum(case when ([Report Date]=Max([Report Date])) and ([Matched]='YES') and ([auto_matched]<>True) then [Total Item Nr] end) as [Manual Match#],Sum(case when ([Report Date]=Max([Report Date])) and (([Matched]='NO') or ([Matched]='FUZZY') or ([Matched]='PARTIAL')) and (([Recon Status]='Uncleared') OR ([Recon Status]='Open')) then [Total Item Nr] end) as [Uncleared & Open #],Sum([GapSum Uncleared,Partial & Open]) as [Uncleared, Partial & Open USD@GAP sum],Sum(case when ([Report Date]=Max([Report Date])) and ([Matched]='YES') and ([auto_matched]=True) then [Total Item Nr] end)/Sum(case when [Report Date]=Max([Report Date]) then [Total Item Nr] end) as [Automatching %],(Sum(case when ([Report Date]=Max([Report Date])) and ([Matched]='YES') and ([auto_matched]=True) then [Total Item Nr] end) + Sum(case when ([Report Date]=Max([Report Date])) and ([Recon Status]='Recorded') and (([Matched]='NO') or (([Matched]='FUZZY') and ([Recon Status]='Recorded'))) then [Total Item Nr] end) + Sum(case when ([Report Date]=Max([Report Date])) and ([Matched]='YES') and ([auto_matched]<>True) then [Total Item Nr] end)) / Sum(case when [Report Date]=Max([Report Date]) then [Total Item Nr] end) as [PayTM Usage]"