Tricky filter for SQL Query from Variables.

[victorinable]

Hi,

I would like to do something a little bit tricky and I'm not able to achieve. Basically, I'm creating a filter for a query (So a parametrized query) wich is created dynamically depending on users criteria. Basically, we have a form before the execution of the report where users can select different options (Which are the parameters for the query).

Those options are:

- Active or inactive users --> parm0
- Period of data users want to see (Start/End Date) -->parm1, parm2
- Reference day to decide if users are active or not. -->parm3

So, what I'm trying to do is to have a variable, setted as an expression, wich will define the "skeleton" of the filter, which will be completed with other variables which store the values of Start/End Date and the reference day. As an example, it will be something like this:

Code: Select all

IIF(parm0 = 1, "Where someColumn = "A" and startDate = {parm1} and endDate = {parm2} and refDate = {parm3}",Where someColumn = "B" and startDate = {parm1} and endDate = {parm2} and refDate = {parm3}")

And then, in the Dictionary, in the DataSource, the query gets this variable and compund the full sql query to retrieve desired Data

Code: Select all

"Select anyColumn1, anyColumn2 From anyTable {varFilter}"

I'm having problems on setting the filter, as I don't know how to concatenate variables to a string variable. Should I do it from the expression editor or somewhere else? If you want to see the report, I can send it to you whith test data.

Thanks in advance.

[HighAley]

Hello.

You should use C# syntax. The expression should be next:

Code: Select all

IIF(parm0 = 1, "Where someColumn = 'A' and startDate = "+parm1+" and endDate = "+parm2+" and refDate = "+parm3, "Where someColumn = 'B' and startDate = "+parm1+" and endDate = "+parm2+" and refDate = "+parm3)

Thank you.

[victorinable]

Hi,

This has been solved by using "Replace" function, now it's working almost as I want to. But, there is no "Concat" function to use when setting variables in expression? Here you can see my walkaround to do what I was describing:

Code: Select all

Replace(Replace(Replace(" SADESO <= TO_DATE ('{fecha_Situacion}',  'DD/MM/YYYY') AND (SADSOR IS NULL OR (SADSOR >= TO_DATE ('{fecha_Situacion}', 'DD/MM/YYYY'))) AND (NEXT IS NULL OR H1DFIN = SADSOR)	AND (NEXT IS NULL OR H1DFIN = SADSOR) AND H1DDEB >= TO_DATE('{period_StartDate}') AND (H1DFIN <= TO_DATE('{period_EndDate}') OR H1DFIN is NULL) ","{fecha_Situacion}", fecha_Situacion), "{period_StartDate}",period_StartDate),"{period_EndDate}",period_EndDate)

Thanks in advance.

[HighAley]

Hello, Victor.

You don't need to use Replace method. Just use such expression:
" SADESO <= TO_DATE ('"+fecha_Situacion"+"', 'DD/MM/YYYY') AND ... and so on

Thank you.

[victorinable]

Hi Aleksey,

I'll try to do it this way. Just another question... there's anyway to convert a formatted string into a date? e.g: "21/12/2014" If this can be done somehow will make things much easier for me otherwise I've to get the string, parse it manually, convert to date and then use it to obtain different values such as Year, Month... I can do it manually, but if there's a functionality to do that would be great!

Thanks in advance.

[HighAley]

Hello.

You could use next expression

Code: Select all

fecha_Situacion.ToString("MM/dd/yyyy") 

to get right string.

Thank you.

[victorinable]

Hi Aleksey,

Sorry if I've not expressed myself correctly. I had the string and wanted to convert to date. I used Date.Parse(String) and functioned fine. Finally, I solved my issue via SQL Expression, so I guess that if now it's something wrong, it's because of the query. But I'm formatting it well, so issue is solved (By now).

Thanks for the support!

[victorinable]

Hi Aleksey,

I've got a problem when executing this report, I've executed from the editor and all is working fine. But If I execute it from Web Editor, it seems that some variables are not setted. So the query it's not well formed as they're missing some parameters.

I'm setting the parameters from Code, and they're ok since I'm printing them on the report. But when I print the composed query, which is affected by those parameters, is being displayed without the concatenations. How can be that? May I send you the report to check if this is normal?

Thanks in advance.

[HighAley]

Hello, Victor.

Yes, please, send us a sample to us.
We will do the best.

Thank you.

[victorinable]

Hi Aleksey,

Here you have. As I mentioned, values that are setted from the code are ok. The problem is with variables "filtro_activos", "filtro_ausente" and "wherestr", which are the ones not setted in the code but using variables setted in the code. I hope this helps.

Thanks in advance

Nuevo_Test_Variable.mrt

Report

(24.03 KiB) Downloaded 272 times