Lección 5: Agregar un parámetro del informe para pasarlo a una función definida por el usuario

Artículo
12/15/2008

Actualizado: 12 de diciembre de 2006

Los parámetros del informe se pueden enlazar a parámetros de entrada pasados a una función definida por el usuario (ufn) o un procedimiento almacenado en una consulta del conjunto de datos. Las funciones definidas por el usuario pueden devolver una tabla o un valor escalar (un solo resultado). Una consulta del conjunto de datos, por definición, recupera un resultado de una función definida por el usuario o procedimiento almacenado. Una ventaja de una función definida por el usuario respecto de un procedimiento almacenado es que puede incluirla directamente en una instrucción SELECT de Transact-SQL, con lo que se combina el resultado de la función con otras columnas en el conjunto de resultados de la consulta.

Las funciones definidas por el usuario y los procedimientos almacenados no aceptan directamente parámetros con varios valores, es decir, matrices. Para pasar una matriz con varios valores a un procedimiento almacenado, puede utilizar la función JOIN de Visual Basic para crear una cadena concatenada de todos los valores y escribir la función definida por el usuario para aceptar un parámetro de cadena con varias selecciones separadas por el delimitador que elija.

La función definida por el usuario ufnGetContactInformation acepta un identificador de base de datos para la tabla Person.Contact y recupera valores para ContactID, Name, Phone, EmailAddress, JobTitle y ContactType. En este informe, creará un parámetro para que el usuario pueda seleccionar que se muestre un subconjunto predefinido de campos.

En esta lección creará un nuevo informe denominado "Contact Information" con un parámetro que acepta un identificador de base de datos para la persona seleccionada como contacto de un almacén de distribuidor. Creará una consulta que llama a la función definida por el usuario ufnGetContactInformation desde la base de datos AdventureWorks y enlaza el parámetro del informe con el parámetro de entrada @ContactID de la función definida por el usuario.

Preparará este informe para utilizarlo como un informe integrado en el informe Resellers Worldwide, creando parámetros para el color de fondo y de texto que se utilizará, quitará el espacio en blanco sobrante del informe y establecerá márgenes y el ancho del informe. Creará un parámetro con varios valores para este informe que permite que el informe principal especifique los campos que se mostrarán para la información de contacto.

Para abrir el proyecto Advanced Parameters Tutorial del servidor de informes

En SQL Server Business Intelligence Development Studio, abra el proyecto Advanced Parameters Tutorial del servidor de informes si aún no está abierto.

Para crear un nuevo informe a partir de una función definida por el usuario

En el Explorador de soluciones, haga clic con el botón secundario en la carpeta Informes. Seleccione Agregar y, a continuación, Nuevo elemento. Se abre el cuadro de diálogo Agregar nuevo elemento.
En el panel Plantillas, seleccione Informe.
En el cuadro de texto Nombre, escriba Contact Information.rdl.
Haga clic en Agregar. El informe se abre en la vista Datos.
En la lista desplegable Conjunto de datos, seleccione <Nuevo conjunto de datos>. Se abre el cuadro de diálogo Conjunto de datos.
En la ficha Consulta, en el cuadro de texto Nombre, escriba ContactInformation.
Compruebe que Origen de datos es Resellers.
Compruebe que Tipo de comando es Text.
Pegue la siguiente consulta en el panel de cadenas Consulta.
```
SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
   c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
   FROM ufnGetContactInformation(@ContactID) udf
   JOIN Person.Contact c ON udf.ContactID = c.ContactID
```
Esta consulta utiliza un parámetro denominado @ContactID para pasar un identificador de base de datos a la función definida por el usuario denominada ufnGetContactInformation, la cual acepta ContactID como parámetro de entrada.
Haga clic en Ejecutar (!) en la barra de herramientas. Se abrirá el cuadro de diálogo Definir los parámetros de la consulta.
En la columna Valor de parámetro, para @ContactID escriba 1. El conjunto de resultados muestra la información de contacto para el contacto del almacén identificado por ContactID. Las columnas devueltas en el conjunto de resultados se definen en la función de usuario.
(Opcional) Compruebe que el parámetro @ContactID está enlazado con el parámetro del informe ContactID. En la barra de herramientas del informe, haga clic Editar el conjunto de datos seleccionado(…) y luego seleccione la ficha Parámetros. El parámetro @ContactID se establece para el valor =Parameters!ContactID.Value.

En el siguiente procedimiento, creará un valor predeterminado para el parámetro del informe ContactID. Al proporcionar un valor predeterminado para cada parámetro, el informe se procesará automáticamente cuando haga clic en Vista previa.

Para agregar un valor predeterminado para ContactID

En el menú Informe, haga clic en Parámetros del informe. Se abrirá el cuadro de diálogo Parámetros del informe con ContactID seleccionado en el panel Parámetros.
En la sección Propiedades, en Valores predeterminados, seleccione No consultado. Escriba 1.
Haga clic en Aceptar.

En el siguiente procedimiento, creará una cadena con varios valores con la que seleccionará la información de contacto que desea mostrar. Creará una lista de valores disponibles no consultados para Phone, Email o None.

Para crear el parámetro del informe FieldsToDisplay

En la vista Diseño, en el menú Informe, seleccione Parámetros del informe. Se abrirá el cuadro de diálogo Parámetros del informe.
Haga clic en Agregar. Se creará un nuevo parámetro con valores predeterminados.
En la sección Propiedades, en el cuadro de texto Nombre, escriba FieldsToDisplay. Compruebe que el tipo de datos es String.
En Pedir datos , escriba Select contact information to display:.
Seleccione la opción Multivalor.
Compruebe que está seleccionada la opción Permitir valor en blanco.
En la sección Valores disponibles, seleccione No consultado. Utilice la siguiente tabla para escribir valores.

Etiqueta	Valor
Phone	Phone
Email	EmailAddress
None	<En blanco>

Si un parámetro posee valores disponibles, debe proporcionar un valor disponible que está <En blanco> para seleccionar la opción Permitir valor en blanco.

En el siguiente procedimiento, creará el diseño para este informe. El diseño se simplifica dado que el informe se utilizará en un informe principal como informe integrado: un cuadro de texto para mostrar el nombre de contacto y el puesto, y un cuadro de texto para mostrar condicionalmente la información de correo electrónico y teléfono.

Para crear el diseño del informe para esta información

Haga clic en la ficha Diseño. El informe se abre en la vista Diseño.
Desde el cuadro de herramientas, arrastre un elemento de informe de tabla hasta la superficie de diseño.
Haga clic con el botón secundario en el encabezado de columna y seleccione Eliminar columnas. Quedan dos columnas.
Pegue la siguiente expresión en el primer cuadro de texto en la fila de detalle:
```
=Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
```
Pegue la siguiente expresión en el segundo cuadro de texto en la fila de detalle:
```
=IIF((Parameters!FieldsToDisplay.Count=1) AND 
   (InStr("None",Parameters!FieldsToDisplay.Label(
          Parameters!FieldsToDisplay.Count-1))>0),"",
    IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "EmailAddress")>0,Fields!EmailAddress.Value,"") + 
vbCrLf + IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "Phone")>0,Fields!Phone.Value,""))
```
Esta expresión simplemente busca en los valores seleccionados el parámetro con varios valores FieldsToDisplay. Si se selecciona NONE (sólo un parámetro; la etiqueta es None), la expresión se evalúa como un valor en blanco. Si hay un valor EmailAddress, parte de la expresión se evalúa como "EmailAddress". Si hay un valor Phone, parte de la expresión se evalúa como "Phone". Estos dos valores están concatenados como un retorno de carro. Toda la expresión evaluada aparece en el contenido del segundo cuadro.

En este ejemplo se muestran los siguientes puntos:
- Para buscar el número de valores seleccionados en un parámetro con varios valores:
- Para un parámetro con varios valores, pruebe la última etiqueta de la matriz para una cadena específica (en este caso, "None"):
- Para devolver condicionalmente una cadena basada en si un valor se encuentra en un parámetro con varios valores (se busca en la cadena concatenada de todos los valores seleccionados para el parámetro con varios valores FieldsToDisplay; si encuentra EmailAddress, devuelve el valor específico de EmailAddress desde el conjunto de datos y, si no lo encuentra, devuelve un valor en blanco):
[!NOTA] La función IIF de Visual Basic evalúa todos los valores de parámetros de función pasados a esta función, de manera que no puede utilizar esta expresión con campos del conjunto de datos que pueden ser NULL.
(Opcional) Haga clic en Vista previa. Cambie los valores de parámetro para los dos parámetros y vea los resultados.

En el siguiente procedimiento, aplicará formato a este informe para que se utilice como un informe integrado de manera que se muestre correctamente dentro de un informe principal al desactivar el encabezado y el pie de página, establecer el ancho de la tabla, quitar el espacio en blanco sobrante, y establecer la fuente y el fondo para que coincidan con el informe principal. También proporcionará parámetros para que el color del fondo y de la fuente se pasen como parámetros desde el informe principal.

Para quitar el espacio en blanco sobrante y establecer el tamaño del informe

Haga clic dentro de la tabla para mostrar las asas de la tabla.
Haga clic con el botón secundario en el asa de la fila de detalle. En el menú contextual, seleccione Encabezado de tabla y Pie de tabla y establézcalos en Desactivado. La tabla muestra una fila de detalle.

En los siguientes tres pasos, necesitará ajustar el ancho del informe a un tamaño conocido. En la siguiente lección, necesitará este tamaño cuando agregue un elemento de informe del informe integrado al informe principal.
Seleccione la tabla. En la ventana Propiedades, compruebe que el ancho de tabla es 3. Ajuste este valor en el mínimo necesario para ver la información de contacto.

El ancho de tabla que defina para el informe integrado determina el aspecto del informe integrado cuando aparece en el informe principal. Para establecer un tamaño fijo para el informe integrado, establezca la altura y el ancho en los valores que desee. También puede establecer las propiedades CanGrow y CanShrink de un cuadro de texto para permitir que el cuadro de texto aumente o se reduzca verticalmente para acomodar el contenido de texto. CanGrow y CanShrink no se aplican al ancho horizontal. Actualmente no puede establecer un cuadro de texto para que ajuste automáticamente su ancho en función del texto.
Seleccione el cuadro de texto del primer encabezado de columna. En la ventana Propiedades, desplácese hasta Ancho y escriba 1,5 o bien, utilizando la medición predeterminada, cualquier valor que necesite para establecer el ancho en la mitad de la tabla.
Seleccione el cuadro de texto del segundo encabezado de columna. En la ventana Propiedades, desplácese hasta Ancho y escriba 1,5 o bien, utilizando la medición predeterminada, cualquier valor que necesite para establecer el ancho en la mitad de la tabla.
Seleccione la tabla. Con las teclas de flecha, mueva la tabla hasta la parte superior de la página del informe y alinee el borde de la tabla con el borde del informe.
Con el mouse, sitúe el puntero sobre el borde de la superficie del informe (el fondo blanco de puntos). Cuando el mouse se convierte en una flecha doble, arrastre el borde de la superficie del informe y muévalo cerca del borde de la tabla. De esta manera, se elimina el espacio en blanco sobrante cuando se representa el informe.
De manera similar, elimine el espacio en blanco sobrante de la parte inferior de la página del informe moviendo la superficie del informe cerca de la parte inferior de la tabla.

Para agregar parámetros para establecer colores

En la vista Diseño, en el menú Informe, seleccione Parámetros del informe. Se abrirá el cuadro de diálogo Parámetros del informe.
Haga clic en Agregar. Se creará un nuevo parámetro con valores predeterminados.
En la sección Propiedades, en el cuadro de texto Nombre, escriba BackgroundColor. Compruebe que el tipo de datos es String.
Seleccione la opción Oculto. Se desactiva el cuadro de texto Pedir datos.
Desactive la opción Permitir valor en blanco.
En la sección Valores disponibles, compruebe que está seleccionado No consultado. Deje el valor de tabla vacío.
En la sección Valores predeterminados, haga clic en No consultado y escriba Azure.

En un informe integrado se utilizarán los valores de parámetro pasados del informe principal al informe integrado. Si el informe principal no especifica un valor para un parámetro concreto, se utiliza el parámetro del informe integrado predeterminado.
Cree un nuevo parámetro denominado FontColor siguiendo los pasos 1 a 6.
En la sección Valores predeterminados, haga clic en No consultado y escriba SteelBlue.
Haga clic en Aceptar.
En la vista Diseño, haga clic con el botón secundario en la fila de detalle de la tabla. En la ventana Propiedades, desplácese hasta BackgroundColor. Establezca el valor para la siguiente expresión:
```
=Parameters!BackgroundColor.Value
```
En la ventana Propiedades, desplácese hasta Color. Establezca el valor para la siguiente expresión:
```
=Parameters!FontColor.Value
```
Haga clic en Vista previa. Compruebe que el color de fondo de la fila de la tabla y el color de la fuente están establecidos para los valores de parámetro para BackgroundColor y FontColor.

Para agregar una descripción de informe

Haga clic en la ficha Diseño.
En el menú Informe, seleccione Propiedades del informe. Se abrirá el cuadro de diálogo Propiedades del informe.
En el cuadro de texto Descripción, escriba el siguiente texto: Subreport to show contact information.
Haga clic en Aceptar.

Pasos siguientes

Creó correctamente un informe para mostrar información de contacto del almacén del distribuidor. Aplicó formato al informe que se utiliza como informe integrado al establecer su ancho, quitar el espacio en blanco sobrante y crear parámetros para controlar la apariencia y el contenido. En la siguiente lección agregará este informe al informe Resellers Worldwide como un informe integrado. Vea Lección 6: Agregar un informe integrado con parámetros.

Historial de cambios

Versión	Historial
12 de diciembre de 2006	Contenido modificado: Se ha corregido el ejemplo de código por `udf.ContactID = c.ContactID`.

Vea también

Otros recursos

Trabajar con parámetros en Reporting Services
Conceptos básicos de las funciones definidas por el usuario

Ayuda e información

Obtener ayuda sobre SQL Server 2005