Parametrizar una consulta en la que usamos un servidor vinculado de SQL Server mediante openquery puede ser necesario en diferentes escenarios. Por ejemplo podemos necesitarlo para cargar una tabla intermedia con los datos de venta que sacamos de MySql a través de un servidor vinculado en SQLServer. También en un informe de SQL Server Reporting Services.

[[ad]] Vamos con un ejemplo donde consultamos una tabla de Mysql directamente:

SELECT *
FROM Ventas.CABECERAS
WHERE date_format(FECHA, '%Y-%m-%d') >= '2012-12-01'

Esta misma consulta, lanzada desde SQL Server usando openquery y un servidor vinculado llamado 'MY' para acceder sería:

SELECT *
FROM openquery(MY, 'SELECT * FROM Ventas.CABECERAS
WHERE date_format(FECHA, ''%Y-%m-%d'') >= '''2012-12-01''')

** En otro este post explico como crear el servidor vinculado

[[ad]] Si quisiéramos parametrizarla, se nos ocurre primero añadirle un parámetro fecha, o segundo, construir la cadena de texto con las fechas usando variables tipo varchar (usare esta segunda forma para luego concatenar el resultado con el de otra query). Pero nada de esto funciona, ni aun pasando la consulta que pasa por openquery por variable:

DECLARE @fecha varchar;
SET @fecha = '2012-12-01'

SELECT *
FROM openquery(MY, 'SELECT * FROM Ventas.CABECERAS
WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + '''')
GO

DECLARE @fecha VARCHAR
DECLARE @consulta VARCHAR(8000)
SET @fecha = '2012-12-01'
SET @consulta = 'SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''''

SELECT * FROM openquery(MY, @consulta)
GO

La única manera de que funcione y lo interprete es meter la consulta y el openquery en la variable @sql para llamarlo con EXEC como si fuera un procedimiento almacenado:

DECLARE @fecha VARCHAR
DECLARE @consulta VARCHAR(8000)
SET @fecha = '2012-12-01'
SET @consulta = 'SELECT * FROM openquery(MY, ''SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''')'

exec (@consulta)
GO

Un ejemplo como este puede suponer un problema si queremos llamarlo desde un informe de SSRS. Report builder no nos dejara declarar variables que realmente no son parámetros a fijar del dataset. Para poder usarlo como origen de datos de un informe lo que debemos hacer es meterlo dentro de un procedimiento almacenado para evitar el problema con la variable @sql (que es de uso interno) y permitir usar la fecha como filtro:

/* --- Creación del procedure en Sqlserver --- */

CREATE PROCEDURE [dbo].[procVentasMy]
@fecha datetime

AS

SET NOCOUNT ON
BEGIN

DECLARE @consulta VARCHAR(8000)

SET @consulta = 'SELECT * FROM openquery(MY, ''SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''')'

exec (@consulta)

END

/* --- Uso del mismo en Report Builder ---*/

EXEC [dbo].[procVentasMy] @fechaMinima

[[ad]] Este ejemplo va relacionado con este otro post sobre SQL Server donde se explica cómo concatenar en SQL Server resultados de dos origenes de datos distintos mediante openquery. En ese caso también puede ser necesario parametrizar la consulta y podemos hacerlo exactamente igual.

Antes de maravillarme con lo bonito que pueda ser Power View y Sql Server 2014 dejo unas cosillas útiles que se deben saber sobre Report Builder 2.0. La mayoria son para el diseño de informes y otras son sobre el funcionamiento en el dia a dia y alguna cosa que nos podemos encontrar y en la que no sabemos por donde empezar.

Ahí van estos 10 tips o recomendaciones sobre Microsoft Report Builder:

Mantener todos los elementos en la misma página.

Para mantener todos los elementos en la misma página en el momento de la presentación en html del informe basta con añadir todos los elementos dentro de un rectángulo y en él establecer la propiedad KeepTogether a True. 

Formato condicional de una propiedad

Para cambiar el formato de una celda, ya sea el color de fondo, color de la fuente etc... basta con editar la fórmula de la casilla y normalmente establecer un switch en base a un valor o una condición determinada. Por ejemplo, para cambiar el color de la fuente a rojo o verde según el valor de un porcentaje (sea negativo o positivo) ponemos en Color una fórmula como la siguiente (en base a un campo Margen).

Navegación entre reportes

Es posible que queramos en alguno de nuestros reportes, permitir la posibilidad de navegar entre informes para ver información más detallada y no saturar la vista poniendo todos los datos en una megatabla etc etc. Esto lo podemos hacer insertando un Cuadro de Texto con el título que queramos y luego estableciendo la Acción al pulsar el botón sobre el. Es útil saber que podemos definir el informe que abriremos y los parámetros que queramos pasarle.
No está de más saber que ese nuevo informe se abre en el mismo visor de informes y oculta el informe actual. También, cuando abrimos el nuevo, si pulsamos Atrás volvemos al informe donde hemos clicado sin hacer la carga de información de nuevo.
Yo particularmente suelo marcar a nivel visual estos elementos tipo Enlace subrayando la fuente y cambiándole el color.

Parámetros tipo Fecha en un informe y como transformarlos a MDX

Si construimos un informe sobre nuestro cubo, y uno de los parámetros visibles que puede cambiar el usuario es de tipo fecha, nos encontraremos que el diseñador nos construye por defecto un desplegable del tipo lista tabulada.

Si queremos facilitarle la vida al usuario hemos de modificar el tipo de datos del parámetro a Fecha y Hora para que nos aparezca el calendario en lugar de la lista tabulada, además de no permitir más de un valor ni valores en blanco. Debemos quitar también los valores disponibles (que se sacan de un dataset auto generado en el momento de marcar el parámetro como visible y que podemos eliminar) y si hace falta los valores predeterminados (en todo caso podemos setear a la fecha de hoy). A continuación debemos ir al Dataset filtrado por este parámetro y modificar el valor del parámetro para construir dinámicamente el valor equivalente en MDX desde el valor de la fecha del calendario. Algo así:

Reutilizar Datasets dentro de un mismo informe para mostrar datos filtrados en lugar de usar Datasets duplicados casi idénticos

Puede darse el caso que en la construcción artesanal de un cuadro de mando pongamos dos indicadores con gráficos muy bonitos que muestran una métrica idéntica, por ejemplo el % de beneficio para dos canales de venta distintos. Aquí nos podríamos ver obligados a repetir conjunto de datos para cada tipo con un valor parametrizado del canal o lo que es mejor, ponemos un solo dataset filtrando los datos por canal en cada gráfico. Esto es extensible a tablas, kpi, etc. Siempre lo podremos cambiar en el dialogo de Propiedades del elemento.

Mostrar el top X de una tabla filtrando y ordenando por algún campo de valor.

Parece obvio y puede que nos enfrasquemos en la propia consulta para no recuperar más datos de los necesarios pero es posible que en un mismo informe debamos mostrar los por ejemplo los 5 mejores. Una manera de hacerlo es usar la opción de Filtros en el dialogo de Propiedades y filtrar usando el operador Superior N.

Mostrar los valores de la barra/segmento etc en gráficos y Kpi.

Para mostrar los valores de, por ejemplo, la barra de un gráfico al pasar el cursor por encima debemos establecer la propiedad “Información sobre Herramientas” en el dialogo de Propiedades de la Serie y poner #VALY{N2}.
 

Alinear en una misma tabla valores de distintos orígenes de datos.

En resumen, podría ser un caso donde tenemos que completar métricas de nuestro cubo con datos de fuera, por ejemplo del ERP etc... La mejor manera que se me ocurre si tenemos una instancia de SqlServer de por medio es la que explico en este post.

Depurar ejecuciones de informes basadas en quejas de usuario

Para validar una queja de usuario, saber EXACTAMENTE lo que ha tardado en ejecutarse el informe en questión y los parámetros que ha seleccionado el usuario podemos hacer la siguiente consulta en la base de datos configurada para Reporting Services.

SELECT UserName, l.TimeStart,   
           (l.TimeDataRetrieval)MS_SUMDATARETRIEVAL,    
           (l.TimeProcessing)MS_SUMPROCESSING,   
           (l.TimeRendering)MS_SUMRENDERING, 
           (DATEDIFF(ms, l.TimeStart, l.TimeEnd))AS MS_SUMTOTAL, 
            l.ReportPath,    
            l.Parameters 
FROM ReportServer.dbo.ExecutionLog2 l   
WHERE UserName = 'DOMAIN\ReportUser' 
ORDER BY TimeStart desc

En este otro post encontraréis otras consultas de utilidad sobre las tablas de esta base de datos que permiten ver algunas estadísticas interesantes.

Acceder directamente a un informe con el visor de informes maximizado en un entorno integrado con Sharepoint Services.

Puede ser necesario presentar para algún usuario un informe al estilo cuadro de mando. La forma más sencilla, si trabajamos en modo integrado con Sharepoint Services y con bibliotecas de documentos es añadirle a Favoritos de su navegador la url con un enlace de este estilo: 
https://miservidor/ReportServer?http%3a%2f%2fmiservidor%2fBiblioteca%2fInforme.rdl&rs%3aParameterLanguage=es-ES

Otra forma de no equivocarse o darle acceso rápido es cada día enviar un correo marcando necesariamente en la suscripción, en Opciones de entrega la opción Incluir vínculo.

Esta opción también soluciona, por decirlo de alguna manera, el problema que tiene Mozilla Firefox con los visores de Informes y el área de datos. Lo que sucede es que al abrir el informe sin maximizar veremos que el Visor tiene el tamaño correcto pero dentro solo veremos un mini area de datos con barra de desplazamiento.

Siguiendo la línea del anterior post sobre Report Builder 2,0, dejo quizás una última lista de cosas a tener en cuenta y como satisfacer otras necesidades que me he ido encontrado con el tiempo. Cualquier aporte en los comentarios será bienvenido porque yo doy por cerrado mi aportación a este tema.

Como rellenar el fondo de una fila del mismo color sin los temidos espacios en blanco

Una manera muy sencilla y seguramente a la que llegamos todos la primera vez que queremos usar un formato condicional para el fondo de una fila es escribir una fórmula como la siguiente en la propiedad BackgroundColor de cada celda de la misma fila en cuestión:

=Switch(Sum(Fields!Margen.Value) > 0.00, "SeaGreen", Sum(Fields!Margen.Value) < 0.00, "Red")

El problema viene cuando lo intentamos hacer en una matriz y en una de las celdas no tenemos ningún valor. Esa celda por defecto se mostrará en blanco. Para subsanarlo debemos trabajar con variables. Generalmente trabajaremos con grupos, y bastará crear una variable en el grupo, asignándole ahí el valor con la fórmula anterior. Luego, en cada celda de la misma fila ponemos el valor de la variable.

La expresión para la Variable del grupo de filas (BACKCOLOR) sería:

=Switch(Sum(Fields!Margen.Value) > 0.00, "SeaGreen", Sum(Fields!Margen.Value) < 0.00, "Red")

El valor para la propiedad BackgroudColor de cada celda

=Variables!BACKCOLOR.Value

Como presentar matrices y gráficas unidas de forma visual usando los mismos colores de fondo por grupo/serie

El título es un poco rebuscado pero lo dice todo. Puede darse el caso que queramos presentar una matriz de resultados de ventas por tipos de cliente y queramos incorporar un gráfico al pie para ver la misma información de manera más intuitiva, de por ejemplo, la cuota de cada grupo sobre el total. Para hacerlo podemos hacerlo añadiendo un nuevo miembro calculado en el dataset y en la fórmula podemos a manija el color para cada serie.

En el dataset creamos el nuevo miembro con esta expresión como valor:

=Switch(   Fields!GrupoCliente.Value="Cliente web","LightSteelBlue",   Fields!DS_GRUPO1_ART_COM.Value="Cliente tienda","LightSteelBlue"
)

En el gráfico, en la propiedad Color, del Relleno de la Serie asignamos el valor del nuevo campo:

=Fields!NUEVO_COLOR.Value

Graficos circulares 3D con etiqueta de porcentaje

Si no te lo dicen no se te ocurre ya que tampoco aparece en el asistente para añadir un nuevo gráfico. Para hacer el gráfico circular en 3D, podemos habilitarlo en las propiedades del área del gráfico. Podremos establecer el ángulo de giro y la inclinación. Podemos cambiar otras opciones 3D desde fuera, seleccionando el elemento, en el panel de Propiedades>Area3DStyle.

Las etiquetas las habilitamos haciendo clic con el botón derecho en la serie y seleccionando "Mostrar etiquetas de datos". Lo interesante es también que desde las propiedades de la serie podemos elegir que mostrar como etiqueta: el valor, % sobre el total, etc...

Grupos, subgrupos, clasificaciones y muchos puntos de vista diferentes

En un entorno complicado pueden llegar a convivir diferentes maneras de clasificar un grupo de ítems: habrá quien lo haga con dos niveles, otros con tres, el resto con cinco. Luego alguien importante en la organización con una variación de la primera clasificación de dos niveles, poniéndole nombre a su gusto a lo que ya existe.

Si hemos implementado un dwh puede que hayamos conseguido aunar criterios, pero si no es el caso podemos trampear en dos puntos distintos. Primero en la carga diaria de dimensiones usando una tabla de traducción a una clasificación totalmente distinta a la original. El segundo, en el caso de reports, podemos trampear para informes pequeños, con miembros calculados. No es algo de lo que estar orgulloso pero puede salvarnos el cuello en algún momento dado. Basta con incluir en el dataset los datos originales y luego implementar el nuevo grupo en un miembro calculado. Según el tamaño de la corrección usaremos un Iif o Switch. Solo nos faltará agrupar por el nuevo campo en la matriz, si es el caso, para agregar campos como ventas, etc...

Agregar posibilidad de cambiar ordenación de forma interactiva (en formato web)

En informes diseñados para su uso intercativo nos puede interesar añadir la posibilidad al usuario para que ordene (o cambie la ordenación asc/desc) de una matriz. Podemos habilitarle esa posibilidad cambiando las propiedad Ordenación Interactiva en la celda de la cabecera de la columna. Seleccionaremos Habilitar la ordenación interactiva de este cuadro de texto. Si no estamos trabajando con grupos, en la parte inferior seleccionaremos Filas de detalles. En ordenar por la expresión (normalmente el campo valor para la columna). Si trabajamos con grupos, en lugar de Detalles, seleccionamos Grupos y especificamos el nombre del grupo a ordenar.

Log de errores

Igual que los otros servicios incluidos con la propia base datos, ya sea Analisys Services o Reporting Services, tenemos un fichero plano a modo de log. Se encuentra en el directorio de la instancia, concretamente en la carpeta de Reporting Services>LogFiles. Ahí encontraremos un fichero que rota diariamente que contiene el log de ejecución y de errores. Es útil para sacar errores de suscripciones de informes o problemas que hayan podido afectar al servicio sin ser un problema propiamente del informe. Si queremos consultar el log actual (del día en curso), debemos provocar su volcado. Lo podemos hacer con un copiar y pegar del propio fichero (aunque veamos que ocupa 0KB).

Copia de seguridad de "urgencia" (en modo integrado con Sharepoint services)

Ante la necesidad de un cambio de "urgencia" que pueda no salir bien, si trabajamos con la instancia de SSRS integrada con Sharepoint podemos hacer una copia rápida con un copiar/pegar. Basta con abrir la página y en la carpeta de contenido utilizamos si está habilitada la opción Acciones > Abrir en Windows Explorer. Eso abrirá el explorador del sistema y veremos el contenido a modo de ficheros y carpetas del sitio de Sharepoint. Con ello podremos hacernos un backup o deploy de urgencia...

Añadir una descripción de nuestros informes (en modo integrado con Sharepoint Services)

Si trabajamos con Sharepoint y permitimos al usuario que se sirva de un portal donde puede ver un listado con los informes disponibles, y partiendo de la base que estan en "Bibliotecas de Documentos", crearemos en la vista de la misma una columna "Descripción". Para hacerlo debemos editar la configuración de la biblioteca y en el apartado Columnas añadirle una nueva del tipo "Linea de texto". Luego, para cada item de la biblioteca podremos editar su "Descripción" haciendo clic en su Propiedades>Editar Propiedades.

Por defecto cuando pensamos en el rendimiento de la ejecución de informes en reporting services lo primero que nos viene a la cabeza es el último informe que hemos creado. Se supone que antes de subirlo al portal de informes se supone que hemos probado en el entorno de test, con un volumen de datos similar y lo hemos validado a nivel lógico de datos. En términos de rendimiento es obligatorio, al menos para mi, controlar la ejecución de la consulta y optimizarla mirando que falla en el plan de ejecución y si hace falta donde está el cuello de botella usando el Profiler.

La cuestión es que cada día no podemos mirar ni controlar todo lo que se hace en la base de datos ni que sucede con aquel informe 'básico' implementado hace un año que coge los datos del ERP. Puede suceder que las tablas que usa hayan crecido de forma exponencial etc etc. Aunque podemos monitorizar también con los contadores de rendimiento desde el sistema operativo, cosas como esta suceden y si empezamos a recibir alertas o un usuario se percata de cierta lentitud lo suyo es tener alguna herramienta y saber cual es la carga de trabajo media y que de forma puntual nos permita analizar anomalías o picos de trabajo no habituales. Para eso podemos consultar las tablas de la base de datos que usa repoting services.

Para empezar debemos saber como se llama. Nos vamos a la Administrador de Configuración de Reporting Services, nos conectamos a la instancia que toque y miramos la pestaña Base de Datos.

Toda la información que podamos sacar casi siempre parte de las vistas ExecutionLog, ExecutionLog2 y ExecutionLog3 (SqlServer 08 R2). Estas contienen el registro de ejecución de cada informe. Otras tablas de uso obligado son Catalog y Subscriptions que contienen el detalle de informes y las suscripciones existentes respectivament.

A continuación algunas consultas útiles (suponemos que bbdd es ReportServer):

-- Informes mas usados

SELECT Name Informe, b.path Ruta, 
       COUNT(*) AS Ejecuciones
FROM ReportServer.dbo.ExecutionLog EL 
     JOIN FTXReportServer.dbo.CATALOG b 
     ON EL.reportid = b.itemid 
GROUP BY Name,b.path
ORDER BY COUNT(*)DESC;

-- Principales consumidores (usuarios)

SELECT UserName Usuario, COUNT(*) AS Ejecuciones,
       SUM(l.TimeDataRetrieval) MS_SUMDATARETRIEVAL, 
       SUM(l.TimeProcessing)MS_SUMPROCESSING, 
       SUM(l.TimeRendering)MS_SUMRENDERING,
       SUM(DATEDIFF(ms, l.TimeStart, l.TimeEnd)) AS MS_SUMTOTAL
FROM ReportServer.dbo.ExecutionLog2 l 
GROUP BY UserName ORDER BY MS_SUMTOTAL DESC;

-- Reports más lentos

SELECT TOP 10 Name Informe, b.path Ruta, Parameters Parametros,
       FORMAT Formato,TimeStart Inicio, TimeEnd Fin, ByteCount,
       (TimedataRetrieval+Timeprocessing+TimeRendering)/1000 AS Seg_TiempoTotal
FROM
FTXReportServer.dbo.ExecutionLog EL 
     JOIN FTXReportServer.dbo.CATALOG b 
     ON EL.reportid = b.itemid
ORDER BY TimedataRetrieval+Timeprocessing+TimeRendering DESC;

-- Control de suscripciones que se entregan via mail

SELECT c.Name AS INFORME, s.LastRunTime AS ULTIMA_EJECUCION,
       CONVERT(char(60), s.LastStatus) AS RESULTADO
FROM ReportServer.dbo.Subscriptions s 
     INNER JOIN ReportServer.dbo.Catalog c 
        ON c.ItemID = s.Report_OID 
WHERE DeliveryExtension LIKE '%Email%'
ORDER BY ULTIMA_EJECUCION DESC;

Log de errores

Para el que no lo sepa el log de errores en ejecución de informes de reporting services es un fichero que podemos encontrar en la capeta definida para la instancia de RS, carpeta ReportingServices\LogFiles. Esto es útil saberlo porque por ejemplo en modo de ejecución integrada con Sharepoint Services, si se produce un error en la ejecución de un informe el mensaje que aparece es bastante escueto y da poca información...

Suele necesitarse en entornos empresariales la entrega de informes que se generen de forma automática y que se tengan que entregar por correo o ubicarse en directorios compartidos. Reporting Services lo permite por ejemplo en el modo integrado con Sharepoint Services 3.0.Actualmente existen dos tipos de suscripción: la normal y la controlada por datos:

• Suscripción normal: Por decirlo de alguna manera es una suscripción estática. En esta elegimos el modo y tipo de entrega, formato de presentación y el evento que lanza la suscripción (fecha, periodicidad etc).
• Suscripción controlada por datos: Es algo más completa que la anterior y permite pasar parámetros al informe en cuestión desde una consulta contra una base de datos y conseguir valores como destinatarios, parámetros de fecha etc de forma dinámica.

Howto:

Nos ponemos en situación. Empresa retail dedicada a la venta al público donde cada agente necesita un reporte diario con el estado de ventas comparados con el año anterior y los objetivos. En este caso lo más normal es que vaya con su blackberry y solo pueda leer correos, eso nos obliga por ejemplo a elegir la entrega por correo del informe con la hoja de datos en formato excel por si quiere hacer sus cálculos...

Empezamos ya con el informe ya diseñado, con sus parámetros nos vamos a Administrar suscripciones y en la nueva página elegimos 'Agregar suscripción controlada por datos'.

Paso 1

Nos aparece un primer formulario donde indicamos descripción y la parte de datos. Primero debemos saber contra que base de datos hacer la consulta para sacar en nuestro ejemplo el listado de comerciales con sus correos y una columna con el valor para cada parámetro. También podemos generar valores en la consulta como el asunto del correo.

También es cierto que en reportes de estado de venta puede no ser necesario sobrescribir valores por defecto como por ejemplo la fecha actual que la podemos poner como Default value en el report. En este ejemplo usamos algo así:

• Descripción: Report diario entregado por correo.

• Tipo de conexión: Origen de datos personalizado

• Tipo de origen de datos: Elegimos Microsoft SQL Server

• Cadena de conexión: Data source=servidor; Initial catalog=comercial;

• Consulta: SELECT ds_comercial p_comercial, ds_email p_email, GETDATE() p_hasta_fecha, 'Estado de ventas hasta ' + CONVERT(varchar, GETDATE() ) as p_asunto FROM dbo.Comerciales

Validamos y vamos al siguiente paso.

Paso 2:

Cuando hayamos terminado con esta parte saltamos a la parte de parámetros. Aquí debemos elegir entre asignar los valores con un valor estático o el generado por la consulta anterior.

Paso 3:

Tipo de entrega. Si elegimos por correo debemos elegir el Para, CC, CCO, Asunto. El resto son cosas como el tipo de presentación (elegimos excel).

• Tipo de entrega: Correo electronico

• Para: Elegir del despleglable Seleccionar un valor de la base de datos el valor p_email

• Incluir informe: True

• Formato de presentación: Excel

• Asunto: p_asunto

• Comentarios: Aqui podemos ponerle un texto al correo.

Paso 4:

Programación. Fecha y hora de la ejecución y entrega. Elegimos enviarlo de forma diaria:

Le damos a finalizar y listos!

El proceso en si es bastante simple pero para el usuario es la mar de cómodo que cada mañana pueda ver el estado de sus ventas mientras se toma el café al inicio de su jornada...