PDA

View Full Version : Linking to a different report with filter values



Benjamin
27-Mar-2018, 11:35 AM
Hi,

I have two reports, on gives me a summary of hours by date without the detail of the transactions detail beneath it. Therefor I build another report that contains all the details I need.

I let an extra column generate an http link to the report with the filter values in the url.



'<a href = /ai6/ai.asp?UID=&PW=&DAI_R=1576&DAI_S_STARTDATE='+CONVERT(VARCHAR, Trans_WeekStart, 101)+'&DAI_S_J2AIQQ046EMPLOYEEIDNO='+CAST(Empl_EmployeeId no AS VARCHAR)+' target=new >'+D_EmployeeName_D+'</a>'

Transferring the EmployeeIdno is easy and working just fine but how do I apply a filter between two dates?

Ben

Bob Cergol
27-Mar-2018, 12:49 PM
Hi Ben,

Between date filter in URL is not supported. Instead define two filters in the report on the data column using >= on the first one and <= on the 2nd one. Save the change. Run the report. Then click on Tools, Report Documentation. Scroll down to the integrate information and see how it named the duplicate filter. You can then place valid data values in the URL and it will work. Don't be tempted to change the "=" sign Dynamic AI shows in the example URL to include the other boolean operators. That won't work. It understands you are setting the filter values to the passed constants and it knows how those filters are defined in the report.

If it seems to not work for you on the first try -- run the report from the menu and use Tools, Show SQL and see how it formats the date values. Date formats can vary widely and most usually work the same but depending on .... sometime you have to tweak. For example Dynamic AI itself seems to prefer MM/DD-YYYY in the SQL it generates. SQL Server should accept: YYYY-MM-DD, MM/DD/YYYY, YYYY-MM/DD, etc.

Regards,
Bob

Benjamin
27-Mar-2018, 01:44 PM
Well Yes, I am lost with the dates.
So here is what I get from "ShowSQL" when running the report by itself:


WHERE ((DAI_.StartDate> = '3/4-2018' )
AND (DAI_.StopDate< = '3/17-2018' )


and then my link from the browsers address bar:


http://192.168.1.1/ai6/ai.asp?UID=&PW=&DAI_R=1576&DAI_S_STARTDATE=3/4-2018&DAI_S_STOPDATE=3/17-2018&DAI_S_J2AIQQ046EMPLOYEEIDNO=139


Still not working?

Bob Cergol
27-Mar-2018, 02:19 PM
What names did Dynamic AI show you for the date filters in its Report Documentation?

StartDate and StopDate are caption names for the same single data column, right? I don't think that's what Dynamic AI shows you in the ReportDocumenation.

-Bob

Benjamin
27-Mar-2018, 03:05 PM
Here is the Filters section from the report documentation:




StartDate
&DAI_S_STARTDATE=DATE_variable
&DAI_P_STARTDATE=DATE_variable


StopDate
&DAI_S_STOPDATE=DATE_variable
&DAI_P_STOPDATE=DATE_variable


J2.EmployeeIdno
&DAI_S_J2AIQQ046EMPLOYEEIDNO=INTEGER_variable
&DAI_P_J2AIQQ046EMPLOYEEIDNO=INTEGER_variable




If this makes a difference, I am using parameters @PAR1@ and @PAR2@ for the date filters in the Dynamic SQL Join.

Edit:
After playing around with the report, I changed the date and finally found two that worked (101 - mm/dd/yyyy and 110 - mm-dd-yyyy):


CONVERT(smalldatetime, @PAR1@, 101) As Trans_WeekStart,
CONVERT(smalldatetime, @PAR2@, 101) AS Trans_WeekStop,


Now there are some other issues I thought are creating an unexpected behavior:
1. A default value on the filter in the report takes precedence. It simply overwrites the one provided by the filter through the URL. Shouldn't this be the other way around?
2. The checkbox "First page loads blank" on the reports options page prevents an execution of the URL and its filters, unless the "Go" button is clicked. Takes another step for the user - easy fixed by removing the check.

Removing both of the items above, leaves a problem, if you want to run the report all by itself. Now on opening, the entire data (or "max records") is getting loaded.

Ben

Bob Cergol
28-Mar-2018, 01:08 PM
Yes it makes a difference. Report filters are not view parameters.

The URL arguments for parameters are: &FParm#_ where # = the number of the parameter 1..10.
You still can't pass two date values for use with between operator in a single parameter. You would need to define 2 @PAR#@ in your view and write something as below, and pass two parameter values on the command line.


Where My_Date between @Par1@ and @Par2@

Please note that I did not place quote marks around the parameter names! When these parameters are typed as dates in the view design, Dynamic will add the quote marks around the date values, otherwise the parameters a strings and you must add the quotes youself. Dynamic will replace its symbolic name with the actual string value.


Bob