Search code examples
sql-serverreporting-servicescrosstabssrs-tablixcross-join

How to Eliminate #Error Columns in Tablix Crosstab Group

Imagine a one-table query that accepts a year (range) as a parameter and then yields a dataset grouped by State, City, and Month, with a count of UFOs, if any, for each month.

Tie that dataset to an SSRS Tablix, with a crosstab layout:

  • The Row Groups are State, City;
  • The Column Group is the Month (Jan through Dec); and
  • The row-column intersections are the # of UFOs sighted, if any.

PROBLEM:

If I run the (parameterized) report for one location, even one with few sightings, all goes well: I see columns only for months with sightings.

However, if I report more than one location (say, "Albany," with daily sightings, and "Troy," with October-only), Troy's grouping will come out ugly: If reported alone, just the October column would show, but, now, October along with 11 columns of "#Error" for the month name and a "0" for UFO count appear.

I suspect that one solution may involve some 0- or NULL-fill of missing-month data for Troy and other cities (How?), but is there some other way, via the Tablix widget, to achieve the desired result?

Solution

  • I don't understand what you're doing wrong but the matrix usually works with filling the headers correctly and leaving blanks where there is no data for a single group.

    It might help if you show your design view.

    Design View:

    enter image description here

    Report View:

    enter image description here

    Many of the users don't have data for previous months but at least one does so the month is displayed in the header. The blanks are where there is no data for that month/user combo.

    I don't see why your report isn't working like this. If I only selected one user, then only the months that user had will be displayed.