[Solved] Data Pilot to create dynamic report (Pivot Chart)

[liquidcool123]

I've done some research on this and decided to post because I cannot find an answer from this forum or other websites.

I would like to create a data pilot that I could use to create a make-shift pivot chart. (I learned this is not an OO feature yet).

My data looks like this:
X-axis Y1 Y2 Y3
7.5 2 3 2
15 3 4 4
25 3 4 3
I created a chart that graphs all three data sets on the same graph using x-y scatter.

My problem is, how do I put this data into a data pilot table so that I can chose to display only one of the 3 y-categories?

I am imagining a data pilot where when I select "Y2" my data becomes:
X-axis Y2
7.5 3
15 4
25 4
I plan to graph this and then when I want to see only Y1 or Y3 I will change the data pilot to show only what I want.

I read up on the data pilot, but can't seem to display only 1 of my categories in a dynamic chart. Any help you can offer would be much appreciated!

[MrProgrammer]

I created a chart that graphs all three data sets on the same graph using x-y scatter.

That's step 1.

My problem is, how do I put this data into a data pilot table so that I can chose to display only one of the 3 y-categories?

Now, instead of using DataPilot, just hide the columns you don't want to see on your chart (Format > Column > Hide). Say you hide B (Y1) and D (Y3). You'll just see the Y2 series. To show Y1, type B1 in the name box to select that hidden cell and Format > Column > Show. Now you can hide column C and see only Y1.

You can even create macros to show/hide the columns. I recorded these two with Tools > Macros > Record Macro. You could just record a longer macro to perform show B, hide C, and hide D. Then you'd have three macros ShowY1, ShowY2, and ShowY3 to display the series individually.

Code: Select all

sub ShowB
dim document   as object
dim dispatcher as object
dim args1(0) as new com.sun.star.beans.PropertyValue
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
args1(0).Name = "ToPoint" : args1(0).Value = "B1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:ShowColumn", "", 0, Array())
end sub

sub HideB
dim document   as object
dim dispatcher as object
dim args1(0) as new com.sun.star.beans.PropertyValue
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
args1(0).Name = "ToPoint" : args1(0).Value = "B1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
end sub

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[FJCC]

The attached file shows how you can do this with Match() and VLOOKUP() functions. For some reason I have never taken to DataPilot tables.

[liquidcool123]

Thank you MrProgrammer and FJCC. I decided to mark this solved because although the solution does not use the data pilot, both solutions will give me exactly what I want. I prefer FJCC's method because I am less familiar with macros and I like the ability to use the drop downs. I have used the validity features before to create drop downs, but I never thought to combine it with other functions to hide/show data - very powerful and elegant.

Thank you!

[liquidcool123]

FJCC-

I liked your solution and was able to get it to work in my particular situation. However, I just realized that perhaps I could use your solution to save me from having to create the same graph several times: I have several data sets organized into columns. Each column has unique data, but several columns need to be grouped together for a single graph.

I was using a separate sheet for each group and then creating a graph for each group. I was hoping to just create 1 graph and then select the group I want to display.

I modified your file to show what I am trying to do. I you have any suggestions, please let me know.

[FJCC]

What you want can be done with an OFFSET() function. I have suggested one version in the attached file. The formula in B15:D18 is an array function using OFFSET and VLOOKUP. The VLOOKUP just determines the column offset relative to B2. The OFFSET function has the form OFFSET(reference, row offset, column offset, height, width). Take a look at the help sections for Array functions and OFFSET() and see if what I have done makes sense.