9 Março 2016

Criação de Relatórios SSRS com DAX

Tanto os modelos Tabulares como os Multidimensionais podem ser consultados utilizando ambas as linguagens: MDX e DAX. Nós estamos habituados a criar relatórios cujos data sets são construídos com recurso ao MDX. Este processo é muito fácil e podemos inclusivamente fazer drag and drop de atributos das dimensões, níveis das hierarquias e métricas para o query designer, sendo a query em MDX automaticamente gerada para nós.

O processo de criação de um data set utilizando DAX não é tão direto. Nem sequer temos um editor de DAX e, claro, também não temos um gerador automático de queries. Por este motivo devemos sempre editar e testar as queries no SSMS ou no DAX Editor antes de as colocarmos no data set de um relatório.

Assim, pode seguir estes passos para criar um relatório SSRS simples que obtém dados de vendas de uma base de dados SSAS Tabular (por exemplo, eu utilizei a base de dados AdventureWorks Tabular Model SQL 2012) e recebe dois parâmetros.

1. Criar um data source partilhado para se ligar a um modelo tabular ou multidimensional (neste cenário, eu utilizei um tabular)

2. Criar um relatório com o data source partilhado definido anteriormente

3. Criar um data set com uma query DAX

Quando se abre a janela do Query Designer, o modo de edição selecionado por defeito é “Command Type MDX” com o desenho gráfico ativado.

Para criar um data set com recurso ao DAX, é necessário trocar para a opção “Command Type DMX”. Na mensagem que aparece, clicar em Yes.

A seguir clicar no botão “Design mode” para trocar para a edição não gráfica da query query.

Como disse anteriormente, as queries devem ser escritas e testadas fora do editor DMX (por exemplo, no SSMS):

A seguir, a query pode ser copiada para o editor DMX, fazendo OK. Se houver algum erro de sintaxe de DAX, aparecerá uma mensagem com o erro, não sendo possível gravar o data set enquanto este não for corrigido.

4. Adicionar uma tabela ao relatório definindo como fonte o data set criado anteriormmente

Clicar no botão de “Preview” para visualizar o resultado:

5. Adicionar parâmetros de relatório

Agora vamos adicionar alguns parâmetros de relatório, por exemplo, a categoria do produto vendido e o ano da venda.

Antes de mais, devem ser criados os data sets que retornam a lista de valores distintos para cada um dos parâmetros. Para o efeito, é necessário seguir os mesmos passos que fizemos para criar o data set de vendas, apenas alterando a query DAX utilizada.

Para criar o data set de Categoria de Produto, utilize a query DAX ilustrada na imagem seguinte:

Para criar o parâmetro Product Category e especificar a lista de valores possíveis, utilizando o data set criado anteriormente:

Podem ser efetuados os mesmos passos para o parâmetro Date, utilizando a seguinte query DAX (note-se que este data set irá retornar os anos em ordem descendente):

EVALUATE

VALUES('Date'[Calendar Year])

ORDER BY 'Date'[Calendar Year] DESC

6. Alterar o data set principal para receber os parâmetros

Agora temos de voltar ao data set de vendas (Sales) e modificar um pouco a query DAX definida anteriormente. A função SUMMARIZE que recebia a tabela Internet Sales passa a receber essa mesma tabela filtrada pelos valores recebidos nos parâmetros do relatório. Para o efeito, é utilizada a função CALCULATETABLE.

Após modificar a query, é necessário especificar os parâmetros da query e associar os valores por defeito.

Repare que na comparação de anos é utilizada a função VALUE para converter o parâmetro Year de texto para inteiro, uma vez que o campo Calendar Year da tabela Date é numérico. Este é um passo necessário mesmo que o parâmetro do relatório seja um inteiro.

Ao clicar no botão “Preview”, podemos agora filtrar o relatório de vendas:

Se quisermos que seja possível seleccionar mais do que um valor num parâmetro, teremos não só que alterar a definição do mesmo mas também alterar a query DAX que recebe esse parâmetro. Para ilustrar, vamos alterar o parâmetro Category de forma a permitir a seleccão de vários valores:

Após esta alteração, ao clicar outra vez no botão “Preview”, o parâmetro Category permite agora que sejam escolhidos múltiplos valores.

No entanto, ao escolher vários valores e clicar em “View Report” para aplicar os parâmetros ao relatório, não existe qualquer erro mas a tabela aparece vazia:

Uma vez que o parâmetro Category é agora multivalor, não é possível utilizar o sinal de igual (“=”). Em vez disso, deve ser utilizada a função PATHCONTAINS onde é aplicado o filtro na query DAX e alterar a expressão passada ao parâmetro Category da query utilizando a função JOIN que concatena os diversos valores seleccionados com o símbolo (“|”).

EVALUATE
(
SUMMARIZE
(
CALCULATETABLE
(
'Internet Sales',
PATHCONTAINS(@Category, 'Product Category'[Product Category Name]),
'Date'[Calendar Year] = VALUE(@Year)
),
'Product'[Product Name],
'Product Subcategory'[Product Subcategory Name],
'Product Category'[Product Category Name],
'Date'[Calendar Year],
"Total Sales Amount", sum('Internet Sales'[Sales Amount]),
"Total Product Cost", sum('Internet Sales'[Total Product Cost]),
"Total Tax Amount", sum('Internet Sales'[Tax Amount])
)

Após esta alteração, já é possível filtrar o relatório seleccionando múltiplas categorias de produto:

 

.

.

.

.

       Helena Cabral
   Associate Manager
Solutions Development