Crear reporte de Excel con datos(SAP) conexión SQL

kriptonniano · 23 Agosto, 2021 19:57

Hola, comparto este método, ya que a veces uno tarda mas en abrir SAP B1 ya que tiene que cargar todo lo necesario, sin embargo si abrimos el Excel será mas rápido consultar los datos que requerimos.

Primero que nada, debemos crear una vista con los datos que necesitamos en SQL Server. En mi caso pondré una vista muy amplia de ejemplo. Mi vista se llamara EmbarquesN

Query para vista

SELECT DISTINCT 
                         CONVERT(varchar, T1.U_FechaI, 3) AS [Fecha Ingreso], T0.DocDate AS [Fecha de Contabilizacion], T2.CardName AS [Nombre del Cliente], T0.U_PedidoCte AS Pedido, T1.U_Unidad AS Hopper, 
                         T1.Dscription AS Material, T0.U_DirEnvio AS Destino, T3.DocStatus AS [Status Pedido], T0.DocNum AS [Folio Salida], T1.Quantity AS Peso, T3.U_PedLiber AS [Pedido Liberado], T5.DocNum AS [Factura Clientes], 
                         T0.U_Gto_Transporte AS [Gasto Transporte], T0.U_TRANSP AS Transportista, ISNULL(T0.U_CARTAP, T8.U_CARTAP) AS [Carta Porte], T8.DocNum AS [Orden Compra], T8.DocStatus AS [Status OC], 
                         T12.DocNum AS Entrada, T12.DocStatus AS [Status Entrada], T12.NumAtCard AS [Factura Transporte], T0.U_TRACTO AS Tracto, T0.U_REMOLQUE AS Remolque, T0.U_OPERADOR AS Operador, 
                         T0.Comments AS [Documento del Cliente], T0.U_Gasto_Transporte AS [No Gto Transporte], T0.U_No_Transp AS [No de Transporte]
FROM            dbo.OIGE AS T0 LEFT OUTER JOIN
                         dbo.IGE1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN
                         dbo.OCRD AS T2 ON T1.U_Cliente_CardCode = T2.CardCode OR T2.CardCode = T0.U_Cliente LEFT OUTER JOIN
                         dbo.ORDR AS T3 ON CAST(T3.DocNum AS nvarchar) = T0.U_PedidoCte LEFT OUTER JOIN
                         dbo.RDR1 AS T4 ON T4.DocEntry = T3.DocEntry LEFT OUTER JOIN
                         dbo.OINV AS T5 ON T5.DocEntry = T4.TrgetEntry LEFT OUTER JOIN
                         dbo.POR1 AS T6 ON T6.U_CartaP = T0.U_CARTAP LEFT OUTER JOIN
                         dbo.OPOR AS T8 ON T8.U_FolioE_S = T0.DocNum AND T8.U_CARTAP = T6.U_CartaP LEFT OUTER JOIN
                         dbo.PCH1 AS T9 ON T9.BaseEntry = T6.DocEntry AND T9.BaseType = T6.ObjType AND T9.BaseLine = T6.LineNum LEFT OUTER JOIN
                         dbo.OPCH AS T10 ON T9.DocEntry = T10.DocEntry LEFT OUTER JOIN
                         dbo.PDN1 AS T11 ON T11.BaseEntry = T6.DocEntry AND T11.BaseType = T6.ObjType AND T11.BaseLine = T6.LineNum LEFT OUTER JOIN
                         dbo.OPDN AS T12 ON T11.DocEntry = T12.DocEntry OR T12.U_FolioE_S = T8.U_FolioE_S
WHERE        (T0.U_Cliente NOT IN ('C00026', 'C00022', 'C00008', 'C00133', 'C00004', 'P00987')) AND (T0.Series IN (67, 173)) AND (T3.Series NOT IN (313))

Es conveniente renombrar campos o ponerles Alias para al momento de verlos en Excel se refleje el Alias que es lo que hice con todos estos campos:
[Fecha Ingreso]
[Fecha de Contabilización]
[Nombre del Cliente]
Pedido
Hopper
Material
Destino
[Status Pedido]
[Folio Salida]
Peso
[Pedido Liberado]
[Factura Clientes]
[Gasto Transporte]
Transportista
[Carta Porte]
[Orden Compra]
[Status OC]
Entrada
[Status Entrada]
[Factura Transporte]
Tracto
Remolque
Operador
[Documento del Cliente]
[No Gto Transporte]
[No de Transporte]

Segundos: Abriremos un archivo de Excel nuevo. Dependiendo de la versión de office que tengamos, nos iremos a la cinta o pestaña de datos. Estando ahí buscaremos la opción que dice: Desde Microsoft Query.

Damos clic en Nuevo Origen de datos

Tercero
Asignamos un en la opción 1 nombre y seleccionamos en el numero 2 SQL Server Native Client 11.0.

Después damos clic en Conectar e ingresamos la IP del servidor o nombre, usuario de sql server y contraseña, damos clic en mas options. En database seleccionamos la que usaremos. Nota: Estas no nos aparecerán si no colocamos todo correctamente.

Una vez realizado lo anterior se nos habilita el botón de aceptar. Damos clic en aceptar.

Y nos regresa a la pantalla Inicial. Damos clic en aceptar seleccionando el nombre que acabamos de crear.

Cuarto: Buscaremos nuestra vista y presionamos la flecha para pasarlo al lado derecho de esta manera.

Proseguimos en darle siguiente. siguiente.

En el criterio de ordenación puedes elegir de que forma ordenarlo, en mi caso elijo fecha de contabilización.

Quinto: En la ultima opción en finalizar elijo la opción de ver datos o modificar consulta.

Nos aparece algo así con muchos datos

Sexto: Agregaremos un filtro para elegir nuestro rango puedes hacerlo conforme a los datos que requieres, en mi caso yo los buscare por fecha de contabilización. Damos clic sobre este icono

Cambios los criterios en mi caso pondré Fecha de contabilización.

En la parte de Valor voy a colocar lo siguiente.

Entre [Fecha Inicial] y [Fecha Final]

Quedara de esta forma

Para ver que funciona ejecutare el Query dando clic en el icono

En mi caso me pedirá el rango de fecha inicial.

En mi caso me pedirá el rango de fecha Final.

Si vemos que nos filtra los datos correctamente y todo esta bien proseguimos a la siguiente fase.

Clic en archivo => después clic en Devolver datos a Microsoft Excel.

Nos aparece esta ventana, donde indica donde seamos empezar la tabla de nuestros datos y aceptar. En este caso seleccionaremos la A3: porque usaremos las celdas de arriba.

Mi tabla quedara así:

Sexto: En la celda A1 y A2 colocare lo siguiente completándolo con las celdas B1 y B2.

Una vez echo eso iremos a seleccionar nuestro primer campo (en mi caso fecha de contabilización) lo que hará que se habilite en la cinta de datos, o pestaña, la opcion de propiedades daremos clic.

Propiedades.

Clic en el icono siguiente ubicado en propiedades.

Abrirá otra ventana como esta:

Séptimo: Vamos a la pestaña de Definición: Palomeamos guardar contraseña ( te saldrá una advertencia dar que si), y damos clic en el botón Parámetros. Cadena de conexión debe tener los datos de tu base de datos, solo fueron borrados por seguridad.

Una vez que damos clic sobre Parámetro, Seleccionaremos la opción 3 Tomar el valor de la siguiente Celda: nos pide seleccionar la celda que vamos a estar cambiando ( en este caso los usuarios buscan datos por un rango de fechas)
En la Fecha Inicial puse la celda B1 como se muestra y palomeo actualizar automáticamente.

Selecciono el segundo valor que es Fecha final y y vuelvo a seleccionar la opción 3 (Tomar el valor de la siguiente Celda: Quedando así

Damos clic en aceptar, después aceptar en propiedades y por ultimo aceptar otra vez aceptar.

y Ahora cada vez que cambiemos los datos de las celdas B1 y B2 cambiara la consulta.

Ejemplo

Todo los demás ya son plus que tu puedes personalizar, como inmovilizar la barras, personalizar los campos de fechas, números, monedas, etc.

Consejo estos reportes son para los usuarios que tienen muy poco conocimiento en TI o que jamás se metería con la base de datos ya que al darle en propiedades puedes ver la contraseña que se guarda en el archivo.

Espero les sirva.

joseramonsoli · 24 Agosto, 2021 10:45

Grande @kriptonniano muchísimas gracias por tu aporte.

Carnavso · 8 Septiembre, 2021 00:17

Muchas gracias. Es cierto, para consultas es mejor el traer los datos desde el SQL ya que permite liberar un poco la utilización del SAP, y en ese caso el Excel ayuda bastante.

Por si a alguno le sirve, yo lo que hago es utilizar los macros en Excel con VBA y ejecutar un procedimiento almacenado de sql (SP) algo como esto:

Public Sub Trae_Datos()
Dim fechainicio As String
Dim fechafin As String
Dim iCols As Integer
ActiveSheet.Cells(3, 2).Select
Dim cnn As Object
Set cnn = CreateObject(“ADODB.Connection”)
Dim rst As Object
Set rst = CreateObject(“ADODB.Recordset”)
Dim cmd As Object
Set cmd = CreateObject(“ADODB.Command”)
Dim ConnectionString As String
Dim StrSproc As String
cnn.ConnectionString = “Provider = SQLOLEDB.1;” & _
“Data Source = 192.168.15.6;” & _
“Initial Catalog = SBO_MiEmpresa;” & _
“User ID = sa;” & _
“Password = miClave;”

'Abre la conexión con la base de datos
cnn.Open ConnectionString
cnn.CommandTimeout = 0

'Asigna a la variables de fecha las celdas de los datos digitados por el usuario
'Celda B3 para fecha de inicio y C3 para fecha final
fechainicio = "'" + ActiveSheet.Cells(3, 2).Value + "'"
fechafin = "'" + ActiveSheet.Cells(3, 3).Value + "'"

StrSproc = "set nocount on; "
    
StrSproc = StrSproc & "EXEC [SBO_Complementos].[dbo].[Reporte_Ventas] " & _
                    fechainicio & "," & _
                    fechafin
       
rst.ActiveConnection = cnn
rst.Open StrSproc

' Elimina los datos viejos de la hoja
Worksheets("Datos").Cells(4, 1).Value = "Borrando"
Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Rows("4:4").Select
Selection.Delete Shift:=xlUp
    
' Inicia ciclo para insertar encabezados
For iCols = 0 To rst.Fields.Count - 1
    Worksheets("Datos").Cells(4, iCols + 1).Value = rst.Fields(iCols).Name
    Worksheets("Datos").Cells(4, iCols + 1).Select
    With Selection.Interior
        .Color = 5287936
    End With
Next
        
' Copia los datos retornados por el SP desde la celda A5
Range("A5").CopyFromRecordset rst
Formatea

End Sub

Private Sub Formatea()
Range(“E:E,F:F,G:G,H:H,I:I,J:J,K:K”).Select
Selection.NumberFormat = “#,##0.00”
Columns(“R:U”).Select
Selection.NumberFormat = “#,##0.0000”
Range(“M:M,O:O”).Select
Selection.NumberFormat = “dd/mm/yyyy;@”
Range(“N:N,P:P,Q:Q”).Select
Selection.NumberFormat = “#,##0”
Range(“A4”).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range(“A1”).Select
End Sub

El proceso Trae_Datos puede ser llamado como un macro o asignarlo a un botón en el Excel.
Para evitar que el usuario pueda ver los datos de conexión, si se hace de esta forma, se puede ir al VBA de la hoja y en Herramientas escoger "Propiedades de VBAProject y en protección bloquear el proyecto y asignarle una contraseña.

Espero también les pueda servir.