Reportviwer matrix show all months/weeks of year

Question

I'm new here, and i have a situation. I need to alter one report that i created a few time ago, that shows the planning of maintenance. But not all months/weeks have plan defined, and i want to show all months/weeks even in that situation. The table where i go get the data, have a column Month and onother named Week, and the matrix column group is on Month, and the second group is on Week.

I show you a image of what i have done, and the result.

And here is the query:

SELECT [dbo].[Equip_PIMP].[ID_Equip_PIMP]
          ,[dbo].[Contrato].[ID_Contrato] AS [Contrato_Nr]
          ,[dbo].[Cliente].[Nome] AS [NomeCliente]
          ,[dbo].[Instituicao].[Nome] AS [NomeInstituicao]
          ,[dbo].[Servico].[Nome] AS [NomeServico]
          ,[dbo].[Equip_Marca].[Nome] AS [NomeMarca]
          ,[dbo].[Modelos].[Nome] AS [NomeModelo]
          ,CASE WHEN @DIA_OU_X = 1 THEN CAST([dbo].[Equip_PIMP].[Dia] AS VARCHAR(2))
                WHEN @DIA_OU_X = 0 THEN 'X'
           END AS [VALOR_CEL]
          ,[dbo].[Equip_PIMP].[Data_Plano]
          ,[dbo].[Equip_PIMP].[Data_Execucao]
          ,[dbo].[Rotina].[Nome] AS [NomeRotina]
          ,[dbo].[Rotina].[Tipo] AS [TipoRotina]
          ,[dbo].[Equip_Categoria].[Nome] AS [NomeCategoria] 
          ,CASE [dbo].[Equip_PIMP].[Mes] 
                WHEN 1 THEN 'JANEIRO' 
                WHEN 2 THEN 'FEVEREIRO' 
                WHEN 3 THEN 'MARÇO' 
                WHEN 4 THEN 'ABRIL' 
                WHEN 5 THEN 'MAIO' 
                WHEN 6 THEN 'JUNHO' 
                WHEN 7 THEN 'JULHO' 
                WHEN 8 THEN 'AGOSTO' 
                WHEN 9 THEN 'SETEMBRO' 
                WHEN 10 THEN 'OUTUBRO' 
                WHEN 11 THEN 'NOVEMBRO' 
                WHEN 12 THEN 'DEZEMBRO' 
           END AS [Mes_Nome] -- This is the MONTHNAME
          ,[dbo].[Equipamento].[ID_Equipamento]
          ,[dbo].[Equipamento].[Nome]
          ,[dbo].[Equipamento].[Marca]
          ,[dbo].[Equipamento].[Modelo]
          ,[dbo].[Equipamento].[Categoria]
          ,[dbo].[Equipamento].[Num_Serie]
          ,[dbo].[Equipamento].[Num_Inventario]
          ,[dbo].[Equipamento].[Num_Equipamento]
          ,[dbo].[Equip_PIMP].[Dia]
          ,[dbo].[Equip_PIMP].[Semana] -- This is the WEEKNUMBER
          ,[dbo].[Equip_PIMP].[Mes] -- This is the MONTHNUMBER
          ,[dbo].[Equip_PIMP].[Trimestre]
          ,[dbo].[Equip_PIMP].[Semestre]
          ,[dbo].[Equip_PIMP].[Ano]
FROM ...

Answer 1

At a high level:

Create a temporary table (a subquery or CTE could be used as well, it's up to you) with the week numbers you want to show, and their corresponding month numbers.

Then modify your query as follows:

SELECT b.ID_Equip_PIMP]
      ,b.[Contrato_Nr]
      ,b.[NomeCliente]
      ,b.[NomeInstituicao]
      ,b.[NomeServico]
      ,b.[NomeMarca]
      ,b.[NomeModelo]
      ,b.[VALOR_CEL]
      ,b.[Data_Plano]
      ,b.[Data_Execucao]
      ,b.[NomeRotina]
      ,b.[TipoRotina]
      ,b.[NomeCategoria]
      ,CASE a.[Mes] 
            WHEN 1 THEN 'JANEIRO' 
            WHEN 2 THEN 'FEVEREIRO' 
            WHEN 3 THEN 'MARÇO' 
            WHEN 4 THEN 'ABRIL' 
            WHEN 5 THEN 'MAIO' 
            WHEN 6 THEN 'JUNHO' 
            WHEN 7 THEN 'JULHO' 
            WHEN 8 THEN 'AGOSTO' 
            WHEN 9 THEN 'SETEMBRO' 
            WHEN 10 THEN 'OUTUBRO' 
            WHEN 11 THEN 'NOVEMBRO' 
            WHEN 12 THEN 'DEZEMBRO' 
       END AS [Mes_Nome]
      ,b.[ID_Equipamento]
      ,b.[Nome]
      ,b.[Marca]
      ,b.[Modelo]
      ,b.[Categoria]
      ,b.[Num_Serie]
      ,b.[Num_Inventario]
      ,b.[Num_Equipamento]
      ,b.[Dia]
      ,a.[Semana]
      ,a.[Mes]
      ,b.[Trimestre]
      ,b.[Semestre]
      ,b.[Ano]
  FROM #temp_table a
         LEFT OUTER JOIN (
                          /*  Place entire current query here, except ORDER BY  */
                         ) b ON (a.Mes = b.Mes AND a.Semana = b.Semana)
 ORDER BY /* place current query's ORDER BY here */
;

The temp table will ensure that you have at least one row for every week; however, if there's no actual data for that week, all the related values except the week and month will be NULL.

You may have to make adjustments on the reporting side, depending on how that handles NULL values.