Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slink9
Frequent Visitor

Duplicate PivotChart Functionality

Hi,

I have a solution that was developed using Access 2010. The data is entered using a form or it can be imported from an Excel sheet. The user will then pull up Chart view and can drag headers from the top of the window onto the desired axis on the pivot table to display the chart. Then the header can be deleted from the axis and another inserted. This provides a truly user-friendly interface for a non-technical person.

Now we are upgrading to Access 2016 and there is no Chart view. From what I can tell, the pivot chart was removed from Access in the 2013 version. What can I do to retain this functionality in Access 2016?

When researching this, I found an Excel-datasource suggestion, but the user is not comfortable with this solution. I also found PowerBI desktop, but seem unable to get the desired ease-of-use from this software. Has anyone found a workable solution for PivotCharts utilizing an Access datasource?

1 ACCEPTED SOLUTION

It is impossible to give "exact steps" without sample data.

 

Can you provide a screenshot of your underlying data structure?  Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:

 

Month+Date Key |  2012 data  |  2013 data  |  2014 data  |  Date Axis Sort Order

Jan 1                          x                      x                   x                         1

Jan 2                          x                       x                   x                        2

Jan 3                          x                      x                    x                        3

...

etc.

 

Then you would create a line chart and put the Month+Date Key as the axis.  Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe).  Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.

 

If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.

View solution in original post

19 REPLIES 19
GarryFarrell
Advocate III
Advocate III

Hi,

 

Have you upgraded the Access databse from 2010 to 2016? I think you just open the database in 2016 and it will prompt you to upgrade.

 

Next step is to work out if Access 2016 is 64bit or 32bit as someone else mentioned. Then download the matching Power BI Desktop software. Then make sure you can make a Then write back on this topis to tell everyone which versions you are using. If they match my versions I will make a sample PBX file if you email me the Access DB.

 

Regards,

Garry

The actual database version is 2003+, but it works fine in Access 2010. If I upgrade the db itself to 2016, will it support the current 2010/2016 machines that we have in place?

 

I have PowerBI on a separate Win10 VM that we have set up. I am able to connect to the database fine, after removing the password. Am I missing functionality that would be provided by PowerBI if it the database itself were updated to 2016?

 

The VM is running Win10Pro 64bit and the PowerBI (PBIDesktop.exe) version is 2.41.4581.361. The only indicator that it is a 64bit version of the software is that it is in the Program Files directory.

Hi,

 

MS Access is generally not backwards compatible. So upgrading the 2016 will stop another accessing the database using older versions.

 

There is no need to upgrade the database. You should be able to connect with the correct driver installed.

 

There is no functionality in Power BI that use will miss out on by not upgrading. I though it would be best to have the database on the latest version but that's up to you.

 

Did you manage to create a chart for your user?

I think if you give Power BI a chance it will grow on you. It is exactly what you describe wanting (a UI for easily creating visuals) you just aren't used to the new data connection/ETL experience

I was thinking that it would, but I have not been impressed so far. Do you have some guidance on accomplishing this feat? It doesn't matter whether the solution requires back end programming or can be accomplished directly through UI setup, as long as the result is drag and drop (or check/uncheck) for the end user. I have come up completely empty on trying to research this.

My recommendation is to connect Power BI to Access and use Power BI as your visualization layer.  To connect to Access, make sure your PBI version (32x or 64x) matches your version of Access.  Additionally, you may be required to download the correct database engine (see here for more info: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-access-database-errors/).

 

Once connected, you will be able to select the data you want to connect to, and perform any transformations you wish (all via a UI).  Then, the data gets loaded to a data model, and you can create relationships (just like the UI in Access).  Then, all you have to do is select which type of visual and drag and drop the fields you want to the axis, values, etc.  

 

I'm not sure why you are coming up "completely empty" on researching this.  If you have a specific example/question about functionality or replicating something you did in Access, please provide a screenshot and sample data.

Thanks DKay for the quick response. I apologize for being short on details. I have a screen shot and will explain the need as I understand it.

 

rework.PNG

Data is entered or imported from an Excel sheet and displayed using an Access query called Variable Query. Then, in Access 2010, the user is able to click on View-PivotChart View. to display the chart shown above. The user can then drag any of the fields at the top to the bottom to add/combine data points in the view. One of the duplication issues seems to be getting multiple lines to display based on one date column. The user can drag down Date by Month and compare three years on separate lines. Then the user can view year-month-day. I get the year-month-day capabilities by using levels in PowerBI, but still can't get the individual years comparison to work.

This is a somewhat non-technical user, which is the reason that he likes the drag-and-drop approach. I can sell him on check/uncheck instead as long as the same general functionality can be provided. Do these details help?

You can drag fields to the rows, columns, values, filters, etc.  And you can but slicers in the chart.  I'm not sure what you're describing with regards to getting multiple lines to display based on one date column.  But, I can assure you that, based on your screen shot, everything you want to do is doable in Power BI.  Power BI is essentially a local instance of SSAS tabular, which is designed exactly for doing this type of pivot table/charting.

 

If you want to provide some sample data from your excel sheet I (we) can show you how to quickly get up and running, but there is a plethora of material online to get you comfortable with the level of complexity you are describing.

Thanks. Can you provide some of the sites that you reference for guidance on creating pivot charts with PowerBI?

 

Normally, I would be glad to send you data samples and truly accepting of any time saving help. I hesitate to send company data outside so I will just have to do the research and work all on my own. So far, my attempts at getting a pivot table laid out like this have been quite futile.

Hi,

 

Take a look at these samples I made. Download both files and place them in the same folder. Open the pbix file in Power BI Desktop. Click on the categories in the charts to see what the brushing does. (like cross filtering.)

Northwind.pbix

Northwind2016.accdb

 

Regards,

Garry

Thanks Garry,

 

That looks like it will work quite well for what the user wants, except that I still don't see a way to compare, let's say, three years of data on a line chart. He would like to see the 2014 year compared to 2015 and 2016. Next level in would be monthly comparison, weekly comparison, etc. There are many years of data in one dataset so breaking thsi dataset into individual years is where I am coming up short.

 

Since this was typed but never posted, I have a comment to post for review/suggestion. It sounds like I can't divide a single dataset into multiples by year within PowerBI. If I create three queries by year with Access and use those three queries as the data sources, will that show three lines that I can compare as explained above.

Are you saying you want three lines on the same chart with month as the X axis, with each line for each of the years?  You could very easily put month name on the axis and your data as the values and then add a slicer for Year, but that will only show one year at a time.  Otherwise, you will have to do some more "advanced" ETL to get the data in a format where your have different fields for different years.

Comparing three years of data on one line chart would seem to be a basic expectation of PowerBI since it is an attempt to replace the Pivot Chart that was removed from Access. Why this is so hard to accomplish is beyond me. I have created three queries with a year filter in Access. Then I placed the data from all three queries on a line chart. I see three lines, but only one year is broken out. The other two years display a solid line with the total for the year.

I will take a look at the exact steps later but you should be able to do this easily if you first add a calculated column (in query editor) to extract year, transform the date to only be month, week, or day of year etc, then perform a pivot to get distinct years as column headers with subsequent values for each month, week or day of year.

I am still quite baffled. It finally made sense that I should be able to create three queries, in this case for the 2012/2013/2014 data. I should then create expressions with Month([Date]) and Day([Date]). Then I thought I could add these three queries as data sources and place the three fields (either day or month) on the line chart, but that didn't work either. Creating calculated columns on an existing query seems convoluted ... awaiting exact steps.

It is impossible to give "exact steps" without sample data.

 

Can you provide a screenshot of your underlying data structure?  Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:

 

Month+Date Key |  2012 data  |  2013 data  |  2014 data  |  Date Axis Sort Order

Jan 1                          x                      x                   x                         1

Jan 2                          x                       x                   x                        2

Jan 3                          x                      x                    x                        3

...

etc.

 

Then you would create a line chart and put the Month+Date Key as the axis.  Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe).  Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.

 

If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.

The "difficulty" is a function of your data structure and data model, not the tool.  To have three separate lines, you need three separate fields in your table.  So you would have column 1 is a day of year or Month+Day key, and then column 2 would be 2014 values, column 3 would be 2015 values, etc for those days.  If your values data is in one continuous field and not broken out by year, you will need to do that.

The matrix visual combined with slicers is the way to go here.

As for sample data, you can anonymize it.
slink9
Frequent Visitor

One additional detail is that the user can also filter by clicking on the bottom label after it has been added to the pivot chart.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.