Create Pivot table from Ideascript

profit814

Forums

I am new to scritpting and have been utilizing history to create most of my scripts. I wanted to try and script to create a pivot table but unfortunatly when you create a pivot table there is no history to review. Is there a snippet or could someone share an example of how to create a pivot table in ideascript?

Brian Element Wed, 04/11/2018 - 18:53

There is an option in the pivot table to extract the IDEAScript code.  Just open your pivot table and click on the IDEAscript icon and the script editor will open up with the code.

 

tstraub Thu, 08/16/2018 - 16:53

Is there any way to script exporting a pivot table with more than one row heading?
This would save a lot of time instead of having to remember to open up the database, open each result, wait for calc, click OK to close the dialog box, then click export to excel, and clicking Ok to save.  For multiple databases and multiple pivot tables, this adds a lot of extra steps that I am hoping to bypass with scripting.

Brian Element Fri, 08/17/2018 - 07:26

I just tried it and it won't allow you to send a file to IDEA with more that one column or row.

How you can get around this is put the two items together.  So if you have a column for the year and another column for the month in your pivot table what you can do is in your file create a new field and call it something like YEAR_MONTH and then join the two fields together so the new field might have something like 2019-01, 2019-02 and so on.  This way you have the information in the two fields but in only one field and you use this field in your pivot table which will be exported to an IDEA file. 

I think that is about the only way to get around this limitation.

Good luck.

Brian

BENOIT Thu, 01/24/2019 - 09:39

Hi Brian,
You wrote :

"Yes, add this line and it should export it as a new database:
task.ExportToIDEA True"
But it does'nt work from my side.
Could you give me the whole code ?
Thank you !
Benoît.

Brian Element Thu, 01/24/2019 - 10:51

Hi Benoit, I have learned since I posted this that you can't export a pivot table to IDEA if it has more than one row, column or data.  That might be the problem.  Here is some example code for you.

' Analysis: Pivot Table
Function PivotTable
Set db = Client.OpenDatabase("My File.IMD")
Set task = db.PivotTable
task.ResultName = "My Result"
task.AddRowField "ROW_FIELD"
task.AddColumnField "COLUMN_FIELD"
task.AddDataField "DATA_FIELD", "Sum: DATA_FIELD", 1
task.ExportToIDEA True
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function

BENOIT Mon, 01/28/2019 - 04:50

In fact, I wanted to create a pivot table to get just one single value of the differents JOURNALCODE values (whitout the duplicates).
extract of my database :
 
extract of my pivot table :
 
 
The purpose is to get the values of my pivot table into a listbox (in a dialog box).
Do you know if it's possible ?
 
Thank's in advance
Benoît