Empty data source returning a relation error
-
- Posts: 13
- Joined: Mon Jul 18, 2016 12:20 pm
Empty data source returning a relation error
Hello,
I had a problem using relations in data sources, when the sql of a data source return 0 rows and the data source has a relation with other data source, the software return an error, but it should just print the report clean, with out the databand that is using the data source, just the page header and the page footer.
In my case the data source 'servidor' return 0 rows when the variables 'matricula' and 'nome' don't exist in the database, but the user could enter an invalid value to the report.
The images above show the data sources and the relation between then, and the error that i had.
SQL data source 'servidor':
SELECT
T1001_Matricula_Fun,
T1001_Nome_Fun,
LPAD(T1001_CPF_Fun, 11 ,'0') as T1001_CPF_Fun,
IF(T1001_Flag_Portador_Doenca = 'S' OR T1001_Classe_Fun = 'Auxílio Doença', 'Sim', 'Não')
as IsentoIR,
(select Count(*) from tab09003 WHERE T9003_Matricula_Fun=T1001_Matricula_Fun) as
qtdDependentes,
T1001_Data_Admissao,
T1001_Data_Nasc,
T1001_Regra_Aposentadoria
FROM tab01001
WHERE
exists(
SELECT
*
FROM tab01024
WHERE
T1024_Matricula_Fun=T1001_Matricula_Fun
AND T1024_Competencia = '{referencia}'
)
AND IF(LENGTH('{nome}') > 0, T1001_Nome_Fun LIKE CONCAT('%',TRIM('{nome}'),'%'), 0 = 0)
AND IF('{matricula}' <> 0, '{matricula}' = T1001_Matricula_Fun, 0 = 0)
I had a problem using relations in data sources, when the sql of a data source return 0 rows and the data source has a relation with other data source, the software return an error, but it should just print the report clean, with out the databand that is using the data source, just the page header and the page footer.
In my case the data source 'servidor' return 0 rows when the variables 'matricula' and 'nome' don't exist in the database, but the user could enter an invalid value to the report.
The images above show the data sources and the relation between then, and the error that i had.
SQL data source 'servidor':
SELECT
T1001_Matricula_Fun,
T1001_Nome_Fun,
LPAD(T1001_CPF_Fun, 11 ,'0') as T1001_CPF_Fun,
IF(T1001_Flag_Portador_Doenca = 'S' OR T1001_Classe_Fun = 'Auxílio Doença', 'Sim', 'Não')
as IsentoIR,
(select Count(*) from tab09003 WHERE T9003_Matricula_Fun=T1001_Matricula_Fun) as
qtdDependentes,
T1001_Data_Admissao,
T1001_Data_Nasc,
T1001_Regra_Aposentadoria
FROM tab01001
WHERE
exists(
SELECT
*
FROM tab01024
WHERE
T1024_Matricula_Fun=T1001_Matricula_Fun
AND T1024_Competencia = '{referencia}'
)
AND IF(LENGTH('{nome}') > 0, T1001_Nome_Fun LIKE CONCAT('%',TRIM('{nome}'),'%'), 0 = 0)
AND IF('{matricula}' <> 0, '{matricula}' = T1001_Matricula_Fun, 0 = 0)
- Attachments
-
- Error message shown by the report.
- Report_Error_Msg.PNG (6.14 KiB) Viewed 4800 times
-
- Relation between the data sources.
- Data_Sources_Relation.PNG (60.08 KiB) Viewed 4800 times
Re: Empty data source returning a relation error
Hi Facsistemas,
Please check that the name of the Relations is unique for every DataSource in the Dictionary.
And also please check that T1024_Maricula_Fun column is also of the int type as the parent column in that relation.
Also this comparing might fail as there is a comparing of the string value with a string value and an integer value:
If you still have a problem and none of the suggestions worked, please send us the report template to support[at]stimulsoft.com
Thank you,
Edward
Please check that the name of the Relations is unique for every DataSource in the Dictionary.
And also please check that T1024_Maricula_Fun column is also of the int type as the parent column in that relation.
Also this comparing might fail as there is a comparing of the string value with a string value and an integer value:
Code: Select all
'{matricula}' = T1001_Matricula_Fun
Thank you,
Edward
-
- Posts: 3
- Joined: Mon Sep 11, 2017 3:12 pm
Re: Empty data source returning a relation error
Hello,
I'm facing the same issue. I already changed the relations' names and the field types.
This is my current report, the error happens with the Databands 3 and 4.
Thanks in advance
I'm facing the same issue. I already changed the relations' names and the field types.
This is my current report, the error happens with the Databands 3 and 4.
Thanks in advance
- Attachments
-
- Romaneio de Venda.mrt
- (42.17 KiB) Downloaded 189 times
Re: Empty data source returning a relation error
Hi Luiz Augusto,
In the above report we also have noticed some issues with the fields that were used in the relations if I remember that correctly. I have noticed you are using the Postgre SQL data-adapter for your report.
Could you please send a sample data for your report as well so we can test the issue in case it does relates to that specific data-adapter?
Thank you,
Edward
In the above report we also have noticed some issues with the fields that were used in the relations if I remember that correctly. I have noticed you are using the Postgre SQL data-adapter for your report.
Could you please send a sample data for your report as well so we can test the issue in case it does relates to that specific data-adapter?
Thank you,
Edward
-
- Posts: 3
- Joined: Mon Sep 11, 2017 3:12 pm
Re: Empty data source returning a relation error
Hello,
Thanks for the response.
Our model is quite big, so I couldn't send the whole model, but I was able to reproduce the problem on a smaller example.
Attached to this, there is an sql model and a report. If you run the report on the model, it prints fine.
But if you add an where clause on the datasource1 that makes the query doesn't return any row, the error happens.
Thanks for the response.
Our model is quite big, so I couldn't send the whole model, but I was able to reproduce the problem on a smaller example.
Attached to this, there is an sql model and a report. If you run the report on the model, it prints fine.
But if you add an where clause on the datasource1 that makes the query doesn't return any row, the error happens.
- Attachments
-
- example.txt
- (464 Bytes) Downloaded 211 times
-
- example.mrt
- (6.68 KiB) Downloaded 194 times
Re: Empty data source returning a relation error
Hi Luiz Augusto,
Thank you for the samples, we will come back to you when the solution is found.
Edward
Thank you for the samples, we will come back to you when the solution is found.
Edward
-
- Posts: 2
- Joined: Thu Sep 21, 2017 6:32 pm
Re: Empty data source returning a relation error
Hello, I am having the same problem . Do you have the solution ?
Re: Empty data source returning a relation error
Hello,
We are working on the problem. We will let you know when the solution is available.
Thank you.
We are working on the problem. We will let you know when the solution is available.
Thank you.
Re: Empty data source returning a relation error
Hello, Alex.
We have made an improvement.
Please, check our latest build that is already available on our site and let us know about results.
Thank you.
We have made an improvement.
Please, check our latest build that is already available on our site and let us know about results.
Thank you.