dashboard using Data Visualization → Single Score

[kack l]

Hi Community,

I’m currently creating a dashboard using Data Visualization → Single Score to display the number of accepted records for the current fiscal year (from April 1, 2024 to April 1, 2025).
I'm using the following filter in my report:

However, I realized that if I use a normal report filter  I’ll have to manually update the dates every year.

So I want to use a Scripted Data Source to calculate the fiscal year range dynamically. But I’m not sure if it's possible to achieve this kind of logic in a Scripted Data Source.

Also, I'm using a personal developer instance, and I couldn't find the Scripted Data Source menu in my environment.

Can Scripted Data Source be used for this purpose?
And is there a way to enable it in a personal developer instance?

Thanks in advance!

[OlaN]

Hi,

To my knowledge, this is not possible using the reports and the filtering options available there.

I can think of at least one other approach for you.

You can create a custom field that sets the appropriate (fiscal year) value with a business rule upon insert of a record, then use this value in your reports to get the data needed.

Note, you will still need to change the reports to update to the most recent fiscal-year-value as time goes by.

[Ankur Bawiskar]

@kack l 

had it been for sys_report, you could run a scheduled job yearly just before the fiscal period ends and then update the report filter condition via script so that the report has new filter condition.

the data visualizations are stored in "par_visualization".

See somewhere it stores the filter condition, and if you find then you can try to programmatically update the filter condition

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@kack l 

Here is the workaround

1) the filter condition is stored in this JSON key filterQuery in the Properties field of "par_visualization" table

2) grab that, change it programmatically and then set it again

I shared solution on how to change report filter condition here, grab that and enhance to get your next fiscal dates and update the filter condition by doing GlideRecord

Can we write script to change reports filter condition? 

Sample script to get value from json and then how to replace

(function() {
    var jsonString = '{"filterConfigurations":[],"scoreSize":"auto","headingPosition":"top","sparklineStyle":"area","scoreIcon":"","iconStyle":"background","showScoreDate":false,"useRelativeScoreTime":false,"showScoreUpdateTime":false,"showZero":true,"newReporting":false,"enableAddZerosAtApi":true,"additionalGroupByConfig":[],"showHeader":true,"showTotalAggregate":false,"showSubAggregate":false,"showFirstGroupAggregate":false,"showSecondGroupAggregate":false,"hideMatrixAggregate":false,"wrapTitle":false,"showLoadingIndicator":false,"showHeaderSeparator":false,"contextMenuActions":[],"headingLevel":2,"showBorder":true,"bareBorder":false,"telemetry":null,"showDataPassthrough":false,"refreshRequest":"","showAdditionalGroupBySelector":false,"allowAdditionalGroupByPerMetric":false,"dataPassthrough":null,"sortByOrder":"desc","showChange":false,"showChangePercentage":false,"showTarget":false,"showForecast":false,"showForecastRange":false,"showTrend":false,"showConfidenceBand":false,"showGapPercentage":false,"showSparkline":false,"showMetricLabel":false,"showThreshold":false,"showComment":false,"followFilters":true,"showFilterIcon":true,"enableRealTimeUpdate":false,"enableDrilldown":true,"enableClickAndStay":false,"showDataTable":false,"enableCustomEmptyState":false,"emptyStateIllustration":"no-data","emptyStateHeading":"No data available.","emptyStateHeadingLevel":3,"emptyStateContent":"There are no scores available for the selected criteria. Contact your admin for more info.","emptyStateAlignment":"vertical-centered","hideDevSettings":false,"configPropModifiers":{},"showMaximumRange":false,"allowChangeDateRange":false,"showDateRangeByDefault":false,"applyDateRange":false,"numberOfPeriods":3,"showAbsolutePeriod":false,"scoreType":null,"showChangeFrom":false,"useCurrentDateForEnd":false,"period":"M","useDataCache":false,"cacheExpirationTime":0,"showCacheTime":true,"dateFormat":"default","showRefresh":true,"showFilterAsSeparateSeries":false,"componentId":"brcpkwgr","filterPerMetric":[],"sortBy":"value","configVersion":"23.0.0-ci-SNAPSHOT","colorConfig":{"type":"default"},"dataSources":[{"isDatabaseView":false,"allowRealTime":true,"label":"Account","sourceType":"table","tableOrViewName":"customer_account","filterQuery":"nameISNOTEMPTY","preferredVisualizations":["d24d53f60350de7a652caf3188a46ed2"],"id":"dGFibGVjdXN0b21lcl9hY2NvdW50MTc1NDU1Njc2NDE2Mw==","dataCategories":["trend","group","simple"]}],"metrics":[{"dataSource":"dGFibGVjdXN0b21lcl9hY2NvdW50MTc1NDU1Njc2NDE2Mw==","id":"ZEdGaWJHVmpkWE4wYjIxbGNsOWhZMk52ZFc1ME1UYzFORFUxTmpjMk5ERTJNdz09MTc1NDU1Njc2Njk4Nw==","aggregateFunction":"COUNT","numberFormat":{"customFormat":false},"axisId":"primary"}],"headerTitle":"My header"}';

    // Parse the JSON
    var jsonObj = JSON.parse(jsonString);

    // Extract current filterQuery value
    var currentFilterQuery = null;
    if (jsonObj.dataSources && jsonObj.dataSources.length > 0) {
        currentFilterQuery = jsonObj.dataSources[0].filterQuery;
        gs.info('Current filterQuery value: ' + currentFilterQuery);

        // Replace with new value
        jsonObj.dataSources[0].filterQuery = 'status=active';
    } else {
        gs.info('No dataSources found in the JSON.');
    }

    // Convert back to JSON string
    var updatedJsonString = JSON.stringify(jsonObj, null, 2);

    // Output the updated JSON string
    gs.info('Updated JSON:\n' + updatedJsonString);
})();

Output: I replaced the filter with status=active in the json

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Dr Atul G- LNG]

Hi @kack l 

Can you share some screenshots showing exactly what you're looking for?
It seems like this might be doable without a script, but having a screenshot would help a lot to understand better.