Query parameters

[victorinable]

Hi,

I have a doubt in a very specific scenario. I'm using Reports.Web (HTML5 Editor/Viewer) with last pre-release build (2015/08/07). I'm developing an MVC4 application that allows user to create some predifined DataSets with predifined filters where they can pass the values to them before being created. Once this is done, the application creates the Datasets (with relations if needed and exists and creats the dictionary and so on...) and pass it to the report they are creating/editing.

Since there is no possibility to create some kind of reports because of our Data Model and limitations of web Designer (For example, no cross-tab) and requirements (Users cannot create their queries as the data we provide is confidential and we filter the data they can retrieve), I will have to pre-define some speceific queries and then pass to them the filters they want to have (Basically, period of time) via parameters. Those filters, are being defined in a form by users, and I need to pass them to the report. My idea is:

1- Read form, take values and store it in a report value.
2- In the query, where I have set the parameters, pass the value from the variable to the query parameter

So my questions are basically 2:

- I've set a parameter and in the expression, I selected the variable I'm trying to get the value from. Is this possible? I've seen that it is in documentation, but I guess I'm doing something wrong. Please see attachment.

Query_parameter.PNG (39.57 KiB) Viewed 4973 times

- Can I pass values via code to the parameters? or should I pass the value to the variable and then assign the parameter with variable data? Is there any other possibility or a better way to implement what I've described

Please, I know it's a little bit confusing trying to describe all this in words, if something's not clear, don't hesitate asking for more.

Thanks in advance for the support I've already received, I appreciatte it!

Regards,



UPDATE: The connection I'm using is an Oracle connection.



UPDATE2: I Succeeded on the very first approach, I was able to pass a simple variable to the report and then using it directly in the query with no "Parameter". I guess how to pass Ranges to variables.

parameter.PNG (51.42 KiB) Viewed 4967 times

[Alex K.]

Hello,

As a way, in this case you can use the Request From User variables for the parameters value.
You can find sample report ("Parameters Selecting Country" report) in our demo - http://mobile.stimulsoft.com/

Thank you.

[victorinable]

Hi,

Well, my idea was to pass the range from the MVC App Code, so they can use a standard web form to introduce that parameters. How I can do this?

Thanks.

[HighAley]

Hello.

You could set value of the variables using code from our Knowledge Base.

Thank you.

[victorinable]

Hi Aleksey,

Yes, this I know. But when a variable is a range, how you set Max/Min??

Thanks.

[victorinable]

Hi,

In addition, there's something I can't succeed on. I have next query (For an ORACLE connection, and using last pre-release)

Code: Select all

SELECT b.literal,a.SACH2, a.ETCMAT
	FROM SADMIN a
Inner Join 
  (
select 
	Min(Fecha) as lowerDay, Max(Fecha) as higherDay, Min(fecha)||' - ' || Max(Fecha) as literal
  from DIM_TIEMPO
  Where Año = 2015 and fecha <= SysDate
  Group By Año, Mes
  ) b on (trunc(a.H1DDEB,'MONTH') <= b.lowerDay and (a.H1DFIN >= b.higherDay or a.H1DFIN is Null))
Where 
	SADESO <= TO_DATE ('01/04/2015',  'DD/MM/YYYY') 
	AND (SADSOR IS NULL OR (SADSOR >= TO_DATE ('01/04/2015', 'DD/MM/YYYY'))) 
	AND (NEXT IS NULL OR H1DFIN = SADSOR) AND H1DDEB >= TO_DATE('01/01/2010') 
	AND (H1DFIN <= TO_DATE('01/12/2015') OR H1DFIN is NULL) 
	--Filtro para añadir todos los ETCMATS perteneciente a todos los NICS [NICS < ETCMATS]
  AND ETCMAT in 
  (
Select 
	ETCMAT 
	From 
	ETACIV 
	Where 
	NIC in
        ( 
Select 
	x.NIC 
	From 
	ETACIV x, SADMIN s 
          WHERE 
            x.ETCMAT = s.ETCMAT 
            AND SADESO <= TO_DATE ('01/04/2015',  'DD/MM/YYYY')  
            AND (SADSOR IS NULL OR (SADSOR >= TO_DATE ('01/04/2015', 'DD/MM/YYYY')))  
            AND (s.NEXT IS NULL OR s.H1DFIN = s.SADSOR)
        )
  )  
Group By b.literal,a.SACH2, a.etcmat
order by  a.etcmat, b.literal

There where you can see HARDCODED dates, there should go a parameter/variable. In fact, what I'm trying to do is to insert a variable which is setted in my app code. I've several problems.

My question is: what should I use?? Named/unnamed parameters? May I use directly the variable in the SQL code? May I create the SQL from my MVC Code and pass it as a variable and then, execute it??

Thanks.


Update: If I use named parameters, it says "Missing expression", if I use unnamed, it says "Invalid character".

[victorinable]

Hi,

Solution is found. I had to pass directly the variables to the SQL Text, no using any kind of parameters... So I guess parameters are only used for static Data (??¿¿), or they can be assigned as a variable (I mean. parm=var=value_from_App)?

So here we can see the solution:

Code: Select all

SELECT b.literal,a.SACH2, a.ETCMAT
	FROM SADMIN a
Inner Join 
  (
select 
	Min(Fecha) as lowerDay, Max(Fecha) as higherDay, Min(fecha)||' - ' || Max(Fecha) as literal
  from DIM_TIEMPO
  Where Año = 2015 and fecha <= SysDate
  Group By Año, Mes
  ) b on (trunc(a.H1DDEB,'MONTH') <= b.lowerDay and (a.H1DFIN >= b.higherDay or a.H1DFIN is Null))
Where 
	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 H1DDEB >= TO_DATE('{period_StartDate}') 
	AND (H1DFIN <= TO_DATE('{period_EndDate}') OR H1DFIN is NULL) 
	--Filtro para añadir todos los ETCMATS perteneciente a todos los NICS [NICS < ETCMATS]
  AND ETCMAT in 
  (
Select 
	ETCMAT 
	From 
	ETACIV 
	Where 
	NIC in
        ( 
Select 
	x.NIC 
	From 
	ETACIV x, SADMIN s 
          WHERE 
            x.ETCMAT = s.ETCMAT 
            AND SADESO <= TO_DATE ('{fecha_Situacion}',  'DD/MM/YYYY')  
            AND (SADSOR IS NULL OR (SADSOR >= TO_DATE ('{fecha_Situacion}', 'DD/MM/YYYY')))  
            AND (s.NEXT IS NULL OR s.H1DFIN = s.SADSOR)
        )
  )  
Group By b.literal,a.SACH2, a.etcmat
order by  a.etcmat, b.literal

and variables inside are of type String. If using DATE and not using those transforming functions it fails.

hope it helps to help other Oracle users

Thanks for the support.

[Alex K.]

Hello,

Yes, this I know. But when a variable is a range, how you set Max/Min??

Please try to use the following code:

Code: Select all

report.Dictionary.Variables["Variable1"].ValueObject = new DateTimeRange(new DateTime(2000, 1, 1), new DateTime(2015, 1, 1));

Thank you.

[victorinable]

Hi Aleksey,

Great, thanks for the answer and the support!

[Alex K.]

Hello,

We are always glad to help you!
Let us know if you need any additional help.

Thank you.