Hi,
I have a vertical bar chart and on the horizontal axis there are months (1-12).
What I would like to do is create a functionality for users that if they double click on the bar for month 9 for instance that it leads them to a table in a different report section with details of month 9 in it.
I know things can be done with parameters, but I can't see how I can use one in this case.
Thanks for help or suggestions
Rgds
BB
Accepted Solutions
This is done using linking.
First, you create a second page, which includes *all* of your detail data. Here, I use a separate dataset that has the detail data; you could do this entire report with just one dataset if you had a computed total, but this is slightly easier - I like to do summaries outside of VA.
data htemp.bike_usage;
input month miles;
datalines;
1 100
2 200
3 300
4 400
5 500
6 600
7 500
8 500
9 800
10 1200
11 1100
12 1200
;;;;
run;
data htemp.month_detail;
input month means_of_transport $ miles;
datalines;
9 Bicycle 200
9 Bus 200
9 Car 200
9 Van 200
10 Bicycle 300
10 Bus 400
10 Car 200
10 Van 300
;;;;
run;
Then load these to CAS if you haven't already, and then I make a report, using the month summary dataset for the chart you made (the bar chart), titled "Overview", and then a second tab, "Detail", which is a list table. You can of course do something other than list table, but the important thing is that it needs to have "month" as a field somewhere - it can be a "hidden" field, but it has to be associated with the chart somehow.
Then, go to the Overview report and select Actions, then the bar chart, and open Page Links. There should be a selection for Detail, and a note that you need to map the data (since it's from a different dataset).
Click on Map, and if the month variable is the same name in both datasets it'll auto-select as the mapping criteria - if not, pick the right mapping variable (think the variable which tells SAS which row should be mapped to which row from the left and right datasets).
Once you've mapped the variable, then bars should become clickable, and you'll get the drilldown you wanted! It will filter the Detail tab with the Month value from the bar. Note the breadcrumb at the top which lets you go back easily, and the filter button showing what was filtered by.
This is done using linking.
First, you create a second page, which includes *all* of your detail data. Here, I use a separate dataset that has the detail data; you could do this entire report with just one dataset if you had a computed total, but this is slightly easier - I like to do summaries outside of VA.
data htemp.bike_usage;
input month miles;
datalines;
1 100
2 200
3 300
4 400
5 500
6 600
7 500
8 500
9 800
10 1200
11 1100
12 1200
;;;;
run;
data htemp.month_detail;
input month means_of_transport $ miles;
datalines;
9 Bicycle 200
9 Bus 200
9 Car 200
9 Van 200
10 Bicycle 300
10 Bus 400
10 Car 200
10 Van 300
;;;;
run;
Then load these to CAS if you haven't already, and then I make a report, using the month summary dataset for the chart you made (the bar chart), titled "Overview", and then a second tab, "Detail", which is a list table. You can of course do something other than list table, but the important thing is that it needs to have "month" as a field somewhere - it can be a "hidden" field, but it has to be associated with the chart somehow.
Then, go to the Overview report and select Actions, then the bar chart, and open Page Links. There should be a selection for Detail, and a note that you need to map the data (since it's from a different dataset).
Click on Map, and if the month variable is the same name in both datasets it'll auto-select as the mapping criteria - if not, pick the right mapping variable (think the variable which tells SAS which row should be mapped to which row from the left and right datasets).
Once you've mapped the variable, then bars should become clickable, and you'll get the drilldown you wanted! It will filter the Detail tab with the Month value from the bar. Note the breadcrumb at the top which lets you go back easily, and the filter button showing what was filtered by.
