Super Contributor.. JHarris941 Super Contributor..
Super Contributor..
911 views

Pivot Tables for Excel Reports runs

Jump to solution

Hello All,

Using the excel report cookbook I was able to create an excel report that runs in PPM. There is instructions on how to use Pivot Tables in these reports, but I havent been able to make it work.

Basically, at runtime, I want the report to generate not only the data, but an additional worsheet in the same file containing a pivot table to make the data in the first tab mean something. Anyone have any experience on how to do this? The instructions are hard to understand.

Thanks,

Jajcen 

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Jajcen,

What you're asking is exactly what the Excel Reports documentation sample does when demonstrating how to build a pivot portlet with Excel Reports.

Link to online help is here: https://admhelp.microfocus.com/ppm/en/9.50/Help/Content/RG/ExcelReports/0802_AddPivotChtTbl.htm 

And you're not correct in assuming that Excel needs the data to create a pivot chart - the trick is to use a dynamic named range as pivot data source, and to change the pivot table setting in Excel to reload data on opening file. All of this is explained in the help link above (check the sub-sections on creating a Dynamic Name range and Adding a Pivot Table or Pivot Chart).

If you need to look at the sample documents mentioned in the help, I attached the zip file here. ==>

Let me know if you need more help, but please read the documentation first 🙂 

Cheers,

Etienne.

 

 

 

 

0 Likes
11 Replies
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hello Jajcen,

Please use the attached template as an example.

Also, please if you want to use the same template like the guide details add it to the below path:

<PPM_HOME>/conf/custom_excel_templates

I will be aware for your comments.

Bryan. 

0 Likes
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Bryan,

I'm not seeing the attachement...was it uploaded?

Thanks,

Jajcen 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Jajcen,

Can you make the "example" pivot table from the Excel report cookbook Samples work? If it does, you should be able to create a Pivot Table yourself by starting from this sample template and modifying it gradually to meet your needs.

You can also share your current template where you're trying to get data in a Pivot Table, and we should be able to point at what's still missing from it to get it to work.

Cheers,

Etienne.

0 Likes
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hello Etienne,

Sorry Was work traveling.  So basically I want to be able to slice and dice the data on tab 1 of the attached report to make the data make more sense.  Since pivot tables need data to exists at runtime, currently my format isnt working.  I would like to create a pivot tableon tab 2 based on the data retrieved from tab 1 at runtime.

Thanks,

Jajcen  

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Jajcen,

What you're asking is exactly what the Excel Reports documentation sample does when demonstrating how to build a pivot portlet with Excel Reports.

Link to online help is here: https://admhelp.microfocus.com/ppm/en/9.50/Help/Content/RG/ExcelReports/0802_AddPivotChtTbl.htm 

And you're not correct in assuming that Excel needs the data to create a pivot chart - the trick is to use a dynamic named range as pivot data source, and to change the pivot table setting in Excel to reload data on opening file. All of this is explained in the help link above (check the sub-sections on creating a Dynamic Name range and Adding a Pivot Table or Pivot Chart).

If you need to look at the sample documents mentioned in the help, I attached the zip file here. ==>

Let me know if you need more help, but please read the documentation first 🙂 

Cheers,

Etienne.

 

 

 

 

0 Likes
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Etienne,

I can't thank you enough for your help! thanks for the step in the right direction and the sample reports.

So here is my disconnect. I follow the document, type out the formula, and then a pop up window shows up asking me to select a file. I dont undertsand why its doing this and how the drop down arrows are showing up for each Column header.  I dont even see the formula on the spreadsheet.  Its asking me to select a value for "TITLE" and if I don't, it gives me a "VALUE#!" excel error.  Pardon my ignorance, but I just cant get pass this......did you run into this?

Thanks,

Jajcen 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Jajcen,

1) To have the drop down show on each column header, you have to turn your header cells into "filter" colums:
- Select all your header cells
- In the "Data" tab of excel tool bar, click on the "Filter" button in the "Sort & Filter" section.
That's it! Now you'll be able to sort your table & filter its rows in the generated excel report. That's just a nice built-in Excel feature, there's no impact on the Excel reports.

2) Where do you type the formula for the Dynamic Data Range? Do you type the formula in the Name Manager as instructed in the help document? I tried creating the Dynamic Range in the GEARequestData.xslx document that you attached earlier, and it works fine. The formula for the Dynamic Range I came up with is:

=OFFSET('Completed Weekly Releases'!$A$3,0,0, COUNTA('Completed Weekly Releases'!$A$3:$A$999999), 55)

Let me know if that helps you move forward. Otherwise, send me an email to etienne.canaud@microfocus.com and we'll set up some remote session to get this to work.

Cheers,

Etienne.

 

0 Likes
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hey Etienne,

Ok think im almost there.  The report isnt counting however even when i reopen or open the file.  I followed the cookbook in regards to adding the formula, using the dynamic range for the pivot chart, add whitespace instead of the field name in the cell, etc. I made sure to have refresh data when file is open as well but it is still not updating the count as it still remains one. Can you look at the report and see if you notice anything off? I ran the sample reports and they were successful but I cant see what im missing. 

Thanks!

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Jajcen,

Main problem is that the data source of your pivot table is the first row of the table: In Excel 2013, you can view it by: Select your pivot table, then select the ANALYZE tab from the toolbar at the top of the screen. In the Data group, click on Change Data Source button and select "Change Data Source" from the popup menu. 

Right now, your pivot data source value is Test!$A$2:$BC$3 . That's wrong, this will always only pick the first line. You should replace it with your dynamic range. However, in your template, in the name manager, I cannot see any correctly defined dynamic data range ; I can only see a "DateRange" with value "='Count by Weekly Release'!$D$6", which is clearly incorrect. 

Check how the dynamic range is defined in the sample excel template (check in the Name Manager) , set it up the same way in your template, and set the datasource of your pivot to use that dynamic range, and you should be all set.

If you still don't manage to get this to work, don't hesitate to mail me for some remote session - or ask some help from your local Excel guru, as what seems to be lacking here are advanced Excel skills - that has nothing to do with PPM Excel Reports 🙂

Cheers,

Etienne.

 

0 Likes
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Pivot Tables for Excel Reports runs

Jump to solution

Hi Etienne,

Haha busted!...yea i'll work on the excel skills 🙂

Got it to run as expected. Thanks again for all your help! Cheers!

Jajcen 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Pivot Tables for Excel Reports runs

Jump to solution

Glad to know it's finally working as expected 🙂

Have a great day!

Cheers,
Etienne.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.