Total Distnct Count of Top N by Year

[crogers111]

CRXI
SQL

I have a Stacked Bar Chart in the RH that displays the TOP N Plans for each year. Thus the chart is set on change of @Year & Plan. N is inputted by the user via parameter. Let's say N=3

I have the top 3 Plans showing for each of 4 years of data. Each of the 4 bars in the chart is a different year and 3 plans are stacked in each bar. Since the TOP N Plan can change from year to year, this makes for total of 5 Plans total which is what shows in the legend.

How do create a formula to get the count of 5 ?

My goal is to use this count in a CrossTab I have which is in the RH section immediately below the Chart.
I'd like to set the Crosstab's Top N/Sort Expert to be the TOP N formula value = to this count (5 in this example) so the Crosstab will show a row for all 5 plans.

 

[lbass]

Use the same parameter in the formula area for the topN on the crosstab.

-LB

 

[crogers111]

That's what I have now and if the parameter entry is 3, I get the top 3 plans for the entire 4 years in the crosstab. For the bar chart I get the top3 for each year (bar) which is a total of 5 plans across 4 years. The bar chart is working as I want. The crosstab, which has a year for each column only shows 3 plans total. I'd like to show all plans that appear in the bar chart in the crosstab.

I've been trying to find a way to get a the count of total plans used in the bar chart (5) to use in the TopN formula in the crosstab but no luck so far.

 

[lbass]

Okay, what you would need to do is insert a separate crosstab per year, since the topN will only be performed on the total column, and if they are the not the same top3 each year (or in the same order), one inserted crosstab cannot handle it. Since topN also reorders the rows, the row labels would also differ in each case.

-LB