Hi everyone, I've been working on this server for a short time, and I just have a problem, I need to consult a table to create a report with reporting services (SSRS).
My problem:
I need to show the benefits of the company of the period that the client selects as parameter and also I have to show the benefits of the previous month to that period and the previous year ie
Benefit Current Benefit Over Previous Benefit Year before
The problem is that each of these fields requires a different place and I do not know how to do it. I have tried several ways but none of them yields the expected results. Besides that reporting services does not let me capture the data set values external to the tablix so I can not do 3 independent queries.
Here I detail a little more the case
In this case it should be possible to subscribe to different users so that a report arrives with the results of the Clients assigned to them as a Seller. It would be filtered by monthly period and the report should show the information indicated below. I give you another mail for the subject of the invoices.
SELLER
CLIENT
PERIOD
FACT.MES ANALIZADO BENEFICIO RENT.% FACT.MES ANTERIOR BENEFICIO RENT.% FACT.MES ANALIZADO EJERCICIO ANT. BENEFICIO RENT.% TOTAL AÑO ANT. BENEFICIO % AÑO ANT.
Report filters: SELLER: PARAMETER (Commercial Customers.COD_Sellier) CLIENT: PARAMETER (Clients.Id_Client) PERIOD: PARAMETER (OperationsSummary.DateBusiness)
FACT. ANALYZED MONTH: It is (TransactionsSummary.Customer_ID_ID)
Benefit: it is (OperationsSummary.Benefit)
RENT%: is (a calculated field) *
* (%: is the result of the following formula: Result = (Profit * 100) / (Revenue)
Where Benefit = Expected sales + Sales invoiced - Expenses - Expected expenses
Where income = Sales invoiced + sales planned
)
For the fields of previous month and previous year the same fields described above will be shown with the condition of the date depending on the period
In addition to that it must be able to filter equally by seller and by client.
Proven query
select BeneficioBruto, @mesA as FechaNegocio, Clientes.[ID-Cliente], OperacionesResumen.[ID-ClienteFactura], ClientesComerciales.[COD-Vendedor] from OperacionesResumen
inner join ClientesFactura on OperacionesResumen.[ID-ClienteFactura] = ClientesFactura.[ID-ClienteFactura]
inner join Clientes on ClientesFactura.[ID-Empresa] = Clientes.[ID-Empresa]
inner join ClientesComerciales on Clientes.[ID-Cliente] = ClientesComerciales.[ID-Cliente]
where (FechaNegocio = @date) and (Clientes.[ID-Cliente] = 69748)and(OperacionesResumen.[ID-ClienteFactura]=69748)and(ClientesComerciales.[COD-Vendedor] = 000030)
Another
Select
OperacionesResumen.[ID-ClienteFactura],
Clientes.[ID-Cliente] as Cliente,
ClientesComerciales.[COD-Vendedor] as Vendedor,
FORMAT(OperacionesResumen.FechaNegocio, 'dd/MM/yyyy') as FechaNegocio,
OperacionesResumen.BeneficioBruto as Beneficio,
OperacionesResumen.VentasFacturadas as [Ventas Facturadas],
OperacionesResumen.VentasPrevistas as [Ventas Previstas],
OperacionesResumen.Gastos as Gastos,
OperacionesResumen.PrevisionNoContabilizada as [Previsión no Contabilizada],
OperacionesResumen.PrevisionPendienteCancelar as [Previsión Pendiente Cancelar]
from OperacionesResumen
inner join ClientesFactura on OperacionesResumen.[ID-ClienteFactura] = ClientesFactura.[ID-ClienteFactura]
inner join Clientes on ClientesFactura.[ID-Empresa] = Clientes.[ID-Empresa]
inner join ClientesComerciales on Clientes.[ID-Cliente] = ClientesComerciales.[ID-Cliente]
where YEAR(@mesA) = YEAR(OperacionesResumen.FechaNegocio)
--AND (Clientes.[ID-Cliente] = 36040 OR ClientesComerciales.[COD-Vendedor] = 052406)
Order BY FechaNegocio DESC
The expected result is as is the table detailed above, the table has the fields:
Benefit Business Date between another
And I must fill in the fields previously requested as detailed in the table
I appreciate any help for 4 days trying to get it out. Greetings