I believe there are plug-ins that add a similar feature, but why not do the analysis in Excel? You can make the whole thing seamless, or re-introduce as many checks along the way as you want. Export the data from FM as a merge file. Create an Excel sheet and pivot it the way you want. Set the speadsheet to refresh on open, no dialogue. Then:
Create a script that exports the data and opens that spreadsheet for the data and pivot. As far as the user is concerned they just click a button in FM and next thing they are looking at a pivot table.
Hi Thanks for reply.
Do you know what plug-in is that definitely not the CC pivot since i tried that already.
Are you saying MS Excel or Access is much better than FMP in this part?
I have some VBA programs already that can do that in Excel, I'm just thinking to jump in FMP fully but If I will depend on Excel, then FMP will be useless and not an option for me anymore.
It may well have been the CC Plug in I used. I remembered it as being very good, but we didn't continue with it because we realised we were simply trying to replicate in FM what already existed perfectly in Excel, so we linked it the way I suggested. We also embedded the Excel sheet as an object in a container field and viewed it by double-clicking the field. It seemed a brilliant 'discovery', but no-one in FM would help us get round some of the bugs. In the end it didn't matter - the 'auto-linking' works for us.
FM can draw graphs, if that's all you want, but pivots are another matter. Presumably you have already experienced some limitations with the existing VBA programs, hence you looked to FM. It's not clear to me why exploiting the strengths of FM and then adding the integration with Excel's tools wouldn't be reasonable, but obviously you know best.
Seems what you said is quite interesting to try. No worry I will explore on this auto-link feature you just mention.
Perhaps pivot is complicated.
The concept I just want to do is to create a graph in FMP wherein instead of selecting the fields for X or Y axis, I'm thinking to create a form wherein i can just select and drag it inside the ready made graph in FMP. But seems like impossible to do it in FMP.
If a graph, even a 'variable' graph is what you want then there are other possibilities. Lets say you want to flexibly graph:
- Date of order
- Date shipped
- Date paid
- Sales value
- Quantity ordered
- Quantity shipped
You could specify 4 new fields:
- gXAxisChoice (global, text)
- gYAvisChoice (global, text)
- XAxisValue = calculation:
gXAxisChoice = "Date of Order" ; DateOfOrderField ;
gXAxisChoice = "Date shipped" ; DateOfShippingField ;
gXAxisChoice = "Date paid" ; DateOfPaymentField
- Similarly for the YAxisValue field.
Then draw a graph using the XAxisValue and YAxisValue fields, and choose the pair you want from a pop-down list.
Hi Admin & Friends,
I'm Beginner for FileMaker Pro12, will you please guide me the procedure to prepare & import a pivot table concept on FMP12.