TRYING TO CREATE A DATE RANGE CONDITION IN DESIGNER

[Former Member]

I am trying to create a date condition in Designer (DESKi XI) to go with a scheduled report, so that when it runs it brings back the appointments for yesterday and today. If I can get this to work I would also like to create other date ranges like between 60 days ago and today.

The actual SQL string that appears in the SQL Viewer in DESKI when you manually type in the dates is:

(CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME BETWEEN {d '2010-05-11'} AND {d '2010-05-12'})

I have tried several variations of typical date syntax, but they all fail the Parse Test..."Parse failed: Driver not capable : Transactional Mode"

When using the Designer condition editor I get the following format...I presume the magic syntax needs to go within the { }?

@Select(Patient Appointment\Appointment Date Time) BETWEEN {???} AND {???}

Any suggestions?

[Former Member · Accepted solution]

Hi,

Which RDBMS are you using?

The SQL syntax is dependent of the database you are using.

Didier

[Former Member · Accepted solution]

Sorry I did not respond to Bashir Awan or Didier sooner...I have someone in our MID department who said she would help me, but apparently she has other priorities ahead of mine...

Bashir Awan: The site you sent me to seemed very rich with a lot of syntax solutions, but I am not fluent in SQL, so this is all a bit overwhelming to me...that is why I was hoping for some local help in addition to your tips. I did not see any example that focused on setting date ranges...

Didier: You asked "Which RDBMS are you using?"...Beyond knowing that an RDBMS is a relational database management system I would not know how to determine which one I am using. When I sign-on to my PC, one of the start-up utilities that runs automatically flashes a "Intersystems Cache' Post-relational Database" screen for a second...does that help?

Sorry to sound so helpless...

[Former Member · Accepted solution]

If you are using BW as your data source you will know because when you log in to Designer the structure window (the right-hand side) will be empty. The syntax posted earlier appears to be related to MDX which is the language used to talk to BW or other cube sources.

If you have tables and joins and whatnot on the right-hand side of your Designer window, here's how to find out what database you are connecting to. First, of course, is to log in to Designer and open your universe. Next, click on File and then Parameters, this will bring up the window that contains your connection information. Click the "Test" button just below the connection name, and it will attempt to connect to your database and return the information about where you are going. Click on the "Details" button and you'll see something like this:

BusinessObjects Configuration :

Version : 11.5..1470.1470

Network Layer : Teradata

DBMS Engine : Teradata V2 R6

From there we can help with SQL syntax to get you what you need.

[Former Member · Accepted solution]

This is exciting!!! ( I need to get a real life) Here is the output from the details section:

BusinessObjects Configuration :

Version : 11.5..417.417

Network Layer : ODBC

DBMS Engine : Generic ODBC datasource

Language : en

Charset : CP1252

Library : C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\dbd_odbc.dll

SBO : C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\odbc.sbo

RSS : C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\odbc.rss

PRM : C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\odbc.prm

Strategies : C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\access.stg

Middleware and DBMS Configuration :

Charset : CP1252

Driver version : 5.0.6202.0

Driver API level : 02.50

ODBC Manager API level : 03.52.0000

DBMS name : InterSystems Cache

DBMS version : Cache Objects Version 5.0.6202.0 Cache xDBC Server 6.10

[Former Member · Accepted solution]

Hm. Well. You've presented a bit of a challenge. I am not sure what to do with the fact that you have "Generic ODBC datasource" showing up in your report. It also says you're using the strategy file for Microsoft Access (access.stg) so let's take a shot in the dark here...

If you are using Access, or something similar, then there might be a function called date() which returns the current date. You might be able to create a predefined condition that returns rows between yesterday and today using the following:

( CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME BETWEEN date()-1 AND date() )

It all depends on whether the date() function is supported by your database engine or not.

[Former Member · Accepted solution]

OK, Parse check failed, but I went ahead and ran it and got this "Connection or SQL sentence error: (DA0005); Exception: DBD, [Microsoft][ODBC Driver Manager] Function sequence errorState: S1010.

I went into the SQL viewer within the DESKi query and this is what it looks like after the WHERE statement...

Aren't their too many parentheses in the last datetime condition? Just a novice observation...

WHERE

( CHCS.PATIENT_APPOINTMENT_44_2.CLINIC=ALI_44_CLINIC_44_2.IEN )

AND ( CHCS.PATIENT_APPOINTMENT_44_2.MEPRS_CODE=ALI_8119_MEPRS_CODE.IEN )

AND ( CHCS.PATIENT_APPOINTMENT_44_2.PROVIDER=ALI_6_PROVIDER_44_2.IEN )

AND ( CHCS.PATIENT_APPOINTMENT_44_2.APPT_DIVISION=ALI_40_8_APPT_DIVISION_44_2.IEN )

AND ( ALI_44_5_APPT_TYPE_44_2.IEN=CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_TYPE )

AND ( CHCS.APPOINTMENT_STATUS_8514.IEN=CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_STATUS )

AND ( ( ( CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME ) BETWEEN date()-1 AND date() ) ) )

[Former Member · Accepted solution]

You do seem to have an extra set.

I put parenthesis around my entire expression because that's how you had it before. They're not needed. But you have parenthesis around the column name itself, and that won't work. The items in red below are likely incorrect:

( ( ( CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME ) BETWEEN date()-1 AND date() ) ) )

The expression should be:

CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME BETWEEN date()-1 AND date()

The only parenthesis that are required are the ones with the date() function.

Is there someone that you can talk to in order to confirm exactly what flavor of database you are connecting to? Otherwise I'm just flailing around guessing...

[Former Member · Accepted solution]

Unfortunately the Navy bought the licences for the application, shipped it out to the field with instructions on how to load it and we are out here stumbling around in the dark trying to learn how to use it. As I mentioned earlier, our MID department has a technician with SQL and Oracle experience who is going to help me, but they are getting their network recertified right now and I am not a priority.

When you ask what flavor of database are we using are you referring to the "DBMS Engine : Generic ODBC datasource"?

I tried to go into the SQL viewer in the query and eliminate the red parentheses, but it tells me the server is not responding (QP0016) when I check the "Do not generate SQL before running" box, so I can't test out the revised syntax. The server is up however...I logged into the CMC and InfoView and was able to refresh other queries.

[Former Member · Accepted solution]

So ODBC refers to how you are connecting to your database. It stands for (if my memory is correct) Open DataBase Connectivity. It itself is not your database. I can use ODBC to connect to SQL Server, Oracle, MySQL, Access... even Excel. The problem is that each of those databases has a different function that will tell you what "today" is. Unless you want me to list all of them (and try them out) it would be easier to know what the actual database is that your connecting to.

I tried to help you figure that out earlier; that's when we got the "helpful" note that you were connecting to a generic source. Not so helpful, really. That's why I asked if you could find out the exact database that you are connecting to.

At least we have determined that it's not MDX, so that's a start.

For SQL Server you can use getdate()

For Microsoft Access it would be date()

For Oracle you can use sysdate

For MySQL it would be current_date

Note that the last two options do not include () of any kind. If you were to create objects with just this syntax (one at a time) then most of them would not parse because they don't reference a table. But you could create a query with a real object (something that does parse) and add one of these date objects at a time to your query and see which one works.

It may also be that the database you are connecting to is some sort of obscure database that does not offer this functionality.

[Former Member · Accepted solution]

I really hope I don't have an obscure database that does not offer the functionality. Can you tell me where "someone" would look beyond the universe properties to get that answer?

As for the syntax suggestions, I wanted to repeat that the View SQL window is not letting me test run any of your syntax suggestions...it keeps saying "server is not responding" when I check the "do not generate SQL before running". If I don't check that it reverts to the original syntax. I looked up Free-Hand SQL in the help menu, but I do not see where the Free-Hand SQL Editor is accessed from. Is that an add-on application that I may not have?

Also, in Designer when I use the Edit Properties box to create the syntax for the created condition it initially looks like this:

@Select(Patient Appointment\Appointment Date Time) BETWEEN getdate()-1 AND getdate()

When you check the "show object SQL" it changes to this:

( CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME ) BETWEEN getdate()-1 AND getdate()

So that is where the extra () are coming from...and none of the suggestions parse out successfully in designer.

I really appreciate you helping me with such limited information...any other ideas?

[Former Member · Accepted solution]

Do you have rights to edit the connection? You can tell if when you open the parameters screen the Edit button is available. That will walk you through a series of screens and should let you know what database you're connecting to. I should have thought of that yesterday. Since you're using ODBC, try this:

Open the universe

File + Parameters

Click the "Edit" button on the connection

The first screen is probably going to show the Data Source Name. Make a note of that.

Next, start the Control Panel application in Windows. Click on Administrative options, then ODBC Administration. There will be a tab on the top labelled System (or maybe Machine) DSN, click it. Scroll through the list until you find the name mentioned in Designer. The name will be listed with a Driver, what driver is listed? Then you can click on that name and then click the Configure button and it should let you see more about the data, like where it's located, if it's a file system object (like Microsoft Access) or not.

The parenthesis around your object... they're coming in because you are using the @Select() function. You should be able to go to the table / column and not use the previously defined object. If you want to use the object, there is a parameter that can be changed to remove those extra parenthesis.

[Former Member · Accepted solution]

Yes I have the Edit button...

1. Data source name = CHCS-Cache;

2. ODBC Data Source Administrator>System DSN>Driver = Intersystems ODBC

3. Configure button: It lists the CHCS-Cache name again and the host IP, port and administrator's name and password.

When I click on the help button it provides quite a bit of information. The following is listed under SQL:

Unicode SQL Data Types [Misc] When checked, turns on reporting of a Unicode SQL type (u201CSQL_WVARCHAR (-9) SQLTypeu201D) for string data. For example, this allows Visual Basic applications using ADO and Microsoft Office 2000 applications to allocate the properly sized buffers to hold multibyte data. This functionality is only relevant if you are working with a multibyte character set, such as for Chinese, Hebrew, Japanese, or Korean. If you are only using single-byte character set data, do not check this box.Selecting this checkbox unnecessarily can result in an u201CSQL data type out of rangeu201D error from the Microsoft Driver Manager using SQLBindParameter.

Right now the Unicode SQL Types option is not checked. The Test Connection and Ping buttons were successful.

[Former Member · Accepted solution]

Try this, just copy / paste directly into your predefined condition rather than using the @Select() function.

CHCS.PATIENT_APPOINTMENT_44_2.APPOINTMENT_DATETIME BETWEEN DATEADD('dd', -1, CURRENT_DATE) AND CURRENT_DATE

After some research I found the SQL reference for your database here:

http://docs.intersystems.com/cache20101/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL

I looked up the application name, found a press release, found a link to the company web site, found a link to the documentation, and ultimately found the link above which gives the SQL reference manual for your system. It looks like it includes many SQL Server functions so I suspect it's based on the same (or at least similar) core.

I feel like we're getting close...

[Former Member · Accepted solution]

Thank you very much Dave for all of your sleuth work...You have truly gone above an beyond! As luck would have it the server is down this morning, but I put in a trouble ticket and will let you know as soon as I can test out the syntax!!!

[Former Member · Accepted solution]

No problem, or rather, no problem for me to wait until you can try things out. Good luck with the server.

[Former Member · Accepted solution]

Hello Dave,

This turns out to be more of a Business Objects error...We can now successfully log into DESKi, but get this error when we try to refresh an existing query or create a new query:

Exception: DBD, [State : 28000][Native Code 402]

Invalid Username/PasswordState: 28000

The Invalid Username/PasswordState made mr initially think it was a user account issue, even though I was not having any trouble logging in, so I changed my password. That did not help, so we looked at the Username/Passwords of the administrators listed in the backend of the applications and still had no luck...

Do you know anything about this kind of thing, or is there another forum I should post this on? I am going to send this to you now and then go look at the forum descriptions and see if I can try in more than one place to find an answer...

[former_member793810]

Hi will,

Here is the syntax. Try this one...

<OPTIONAL><FILTER KEY="[TimeDimension].[TECH_NAME]"><CONDITION OPERATORCONDITION="Between"><CONSTANT TECH_NAME="@Prompt(Patient Appointment\Appointment Date Time','D',,mono,free)"/><CONSTANT TECH_NAME="@Prompt(Patient Appointment\Appointment Date Time','D',,mono,free)"/></CONDITION></FILTER></OPTIONAL>

If you want to make it mandatory then remove Optional Tag.

Regads,

Bashir Awan

[Former Member]

Hi Bashir,

Are you saying to literally paste that string into the edit condition window, or am I supposed to be changing some of the content in your string to fit my situation? Pasting it in faile the Parse check...

You may have to "dumb this down" for me as I am very new to working with this application...

[former_member793810]

Hi Will,

It will work this way:

<OPTIONAL><FILTER KEY="TimeDimension.TECH_NAME"><CONDITION OPERATORCONDITION="Between"><CONSTANT TECH_NAME="@Prompt(Patient Appointment\Appointment Date Time','D',,mono,free)"/><CONSTANT TECH_NAME="@Prompt(Patient Appointment\Appointment Date Time','D',,mono,free)"/></CONDITION></FILTER></OPTIONAL>

The text which is striked out will be replaced with your time dimension(object) you are using.

"Patient Appointment \Appointment Date Time" text is the text that will appear on your prompt. It should be exactly same as in your prompt.

Regards,

Bashir Awan

[Former Member]

OK, but just to be clear, I am not looking to prompt the user to enter anything.

The goal is to have a fixed date range variable in place so that when the report refreshes at 1 AM it will automatically pull data from the day before. It is my goal to then have InfoView send a copy as .xls to a fixed file destination, so that Access can be linked to that Excel file to populate a table in my database.

[former_member793810]

Hi Will,

You can use fix prompt for that.

Here is the guide which may help you required code.

[http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/c0a45246-ce76-2b10-e688-f5c8206203eb?quicklink=index&overridelayout=true]

Good luck...

Bashir Awan