Force bar chart to show missing years within 10-year range (system year + 9)

Question

I have a standalone file geodatabase table called Projects. I've created a bar chart from the table.

That works, but I need bars in the bar chart for each year within a range: system year + 9.
In other words, I want bars for 10 years, starting with the current year (currently 2023).

So I need to force ArcGIS Pro to show empty bars for 2023, 2030, and 2032.

It would look like this:

I mocked that up by manually adding filler rows to the table (not the desired solution):

Question:

Is there a way to dynamically force the bar chart to show the missing years? (10-year range: system year + 9)

Thanks.

ArcGIS Pro 3.0.3

ChristopherAllen · Answer

Hi @Bud , thanks for logging the Idea to support charts on mobile geodatabase views. We will look into this.

To address your original question, I just wanted to mention that converting the year to a Date type should fill in the empty years between the range of valid values (for example, 2030 in your case). This can be achieved with Calculate Field, which would update your attribute table.

Alternatively, in Pro 3.1 we will have the option to configure some chart parameters using Arcade expressions, which would allow you to convert the year to a Date type without needing to update the attribute table. For example, here I have a bar chart that's created on a "year" integer field. This chart is missing data for the year 1951, and since the field is an integer, the x-axis label is not shown for the missing year:

But in this example, I've converted the integer "year" field to a Date type using Arcade, and now the 1951 x-axis label is shown:

With respect to your question about filling in values that extend beyond the range of your data (in your case, 2023 and 2032), we are currently exploring solutions that would give users the ability define custom ranges.

Thanks for the feedback!

Chris

Bud · Answer

I was able to get the view idea to work in an Oracle enterprise geodatabase (18c; 10.7.1; ArcGIS Pro 2.6.8).

Oracle: Generate rows for missing years within range (sysyear + 9)

--create or replace view a_test_tbl_vw as 
select
    cast(rownum as number(38,0)) as rownum_,
    cast(year   as number(38,0)) as year_, 
    amount
from
    (
    select    
        y.year,
        t.amount
    from      
        (select extract(year from sysdate) + level - 1 as year from dual connect by level <= 10) y
    left join 
        infrastr.a_test_tbl t 
        on t.year = y.year
    order by  
        y.year, 
        t.amount
    )

Note: I initially had a problem when naming the column "YEAR" in the view. I fixed it by changing it to "YEAR_".

EGDB view — YEAR column displayed incorrectly as zeros in attribute table

Bud · Answer

For what it's worth, I tried copying the table to a mobile geodatabase (since mobile geodatabases have full SQL support, unlike file geodatabases). Then I created a view that dynamically generates filler rows for the missing years:

WITH RECURSIVE cte AS (
SELECT 2023 AS year_

UNION ALL

SELECT year_ + 1
FROM cte
WHERE year_ < 2023 + (10) -1
)
SELECT row_number() over (ORDER BY cte.year_, amount) as OBJECTID, cte.year_, p.amount
FROM cte
LEFT JOIN Projects_MobileGDB p
ON cte.year_ = p.year_

I was able to successfully create the view. And even add the view to the map. The filler rows were viewable in the attribute table, as expected.

But I wasn't able to create a chart from the view. I'm not sure why. I used the same steps that I used for the table's chart, but when I did it for the view, a chart wasn't shown in the chart window. Instead, this message was shown: "No data is available to display due to empty data field(s) and/or combination filters."

So I wasn't able to get the view idea working.

Idea: Create chart on mobile geodatabase view

Here's the SQL source:

* SQLite
* Oracle (untested)
* SQL Server (untested)
* PostgreSQL (untested)

Edit #1: I tried creating a simpler view: SELECT * FROM TABLE. But I wasn't able to create a chart on that view either.

Edit #2:

BUG-000155377: ArcGIS Pro: Unable to create a bar chart on a database view created in a mobile geodatabase.

“One thing i noticed is that other charts (ex: Line and Scatter Plot charts) work fine. So the issue is mainly with Bar charts.”

Esri Canada Case #03249403

Edit #3:

Idea: Bar Chart — Set min/max x-axis bounds using calculated values; fill in gaps with empty bars