I am doing a project for which I have several reports; I have already managed to visualize them all complete by means of a jsp ; however, I want to give the user the option to filter data. For example, in my sales report I want to filter my data by idventa using a text box in a jsp and only show me the data of that id and, therefore, if the user does not write anything in the text box that does not filter and show the full report ...
So far, in my report, I have created a parameter with the option that it has:
I have declared it as a integer , since the field I have in my database is Integer , so I imagine it should work like this ... now in my query I have this code:
SELECT
Receta."IDReceta" AS Receta_IDReceta,
Receta."IDPersonal" AS Receta_IDPersonal,
Receta."Fecha_Receta" AS Receta_Fecha_Receta,
Venta."IDVenta" AS Venta_IDVenta,
Venta."IDReceta" AS Venta_IDReceta,
Detalle_Receta."IDReceta" AS Detalle_Receta_IDReceta,
Detalle_Receta."Nummed" AS Detalle_Receta_Nummed,
Detalle_Receta."IDMedicamento" AS Detalle_Receta_IDMedicamento,
Detalle_Receta."Cantidad" AS Detalle_Receta_Cantidad,
Detalle_Receta."Indicaciones" AS Detalle_Receta_Indicaciones,
Kardex."IDKardex" AS Kardex_IDKardex,
Kardex."IDMedicamento" AS Kardex_IDMedicamento,
Kardex."IDVentas" AS Kardex_IDVentas,
Kardex."TipoMovimiento" AS Kardex_TipoMovimiento,
Kardex."Cantidad" AS Kardex_Cantidad,
Medicamento."IDMedicamento" AS Medicamento_IDMedicamento,
Medicamento."Nombre" AS Medicamento_Nombre,
Medicamento."Descripcion" AS Medicamento_Descripcion,
Medicamento."ComponenteActivo" AS Medicamento_ComponenteActivo,
Medicamento."Presentacion" AS Medicamento_Presentacion,
Medicamento."Existencia" AS Medicamento_Existencia,
Medicamento."UnidadMedida" AS Medicamento_UnidadMedida,
Medicamento."PrecioUnitario" AS Medicamento_PrecioUnitario
FROM
"dbo"."Receta" Receta INNER JOIN "dbo"."Venta" Venta ON Receta."IDReceta" = Venta."IDReceta"
INNER JOIN "dbo"."Detalle_Receta" Detalle_Receta ON Receta."IDReceta" = Detalle_Receta."IDReceta"
INNER JOIN "dbo"."Kardex" Kardex ON Venta."IDVenta" = Kardex."IDVentas"
INNER JOIN "dbo"."Medicamento" Medicamento ON Kardex."IDMedicamento" = Medicamento."IDMedicamento"
WHERE Kardex."IDVentas" = $P{idventa}
Where the WHERE Kardex."IDVentas" = $P{idventa} is is where my filter should be to the sales id that has kardex; or at least that is what I propose. However, you do not want to do the filter when I do the preview of the report. Please help, I'm a novice in this SQL and I also do not have much experience. I do not know if I'm doing the reference right. Thank you!