Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Solved
Correct column/view specification for retrieving data as correct type via NotesSQL 3.02g
Posted on 2006-07-04
Hi All,
I am trying to design a view in Lotus Notes in a way that when retrieving data via NotesSQL into Crystal Reports 11 - the fields that ARE date, DISPLAY as date ( not strings ).
I know I can convert them when in Crystal but it's much nicer if it happens by default.
Have tried setting the columns as Date/Time but no effect at the Crystal end. ( still appear as Strings ).
The NotesSQL driver had a option "Return Notes Implicit Fields", when turning this on and going into Crystal I can see fields that were previously hidden such as:
- NoteCreated ( type DateTime )
- NoteAccessed ( type DateTime )
- NoteID ( type number )
These columns are not displayed in the view so I do not know what settings to use. When I create a column using "Simple Function" - CreationDate - it still only appears as a string in Crystal.
Any ideas?
I have tried the Crystal Forum and they suggested using another driver. Reference:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21896634.html
Any help greatly appreciated, thanks for reading ;)
Broad
I am trying to design a view in Lotus Notes in a way that when retrieving data via NotesSQL into Crystal Reports 11 - the fields that ARE date, DISPLAY as date ( not strings ).
I know I can convert them when in Crystal but it's much nicer if it happens by default.
Have tried setting the columns as Date/Time but no effect at the Crystal end. ( still appear as Strings ).
The NotesSQL driver had a option "Return Notes Implicit Fields", when turning this on and going into Crystal I can see fields that were previously hidden such as:
- NoteCreated ( type DateTime )
- NoteAccessed ( type DateTime )
- NoteID ( type number )
These columns are not displayed in the view so I do not know what settings to use. When I create a column using "Simple Function" - CreationDate - it still only appears as a string in Crystal.
Any ideas?
I have tried the Crystal Forum and they suggested using another driver. Reference:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21896634.html
Any help greatly appreciated, thanks for reading ;)
Broad
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
4
10 Comments
Active 3 days ago
Thanks for thanking me for reading your question ;)
Firstly, I know nothing of CR, little of NotesSQL and a little more of Notes.
Just a suggestion: can you try with Access (or Paradox or whatever) to see what type of fields it comes up with? It might at least show that it is possible to get the type transfered.
Firstly, I know nothing of CR, little of NotesSQL and a little more of Notes.
Just a suggestion: can you try with Access (or Paradox or whatever) to see what type of fields it comes up with? It might at least show that it is possible to get the type transfered.
Hi BroadAustralia,
How are you gathering the data in crystal? are you using a standard SQL statement, or are you letting crystal grab it? Crystal is very much like Access, so, when you use ODBC to grab notes information you can grab either the forms or the view. You can use a SQL statement to collect the records, or you can use Crystal import/link (Access import/link) to link to and import the view or form.
Since I don't have Crystal here, I used Access... I got "TEXT" whenever the column "style" was not set to "Date/Time", or when the formula or field wasn't explicitly set to Date/Time.
The Notes implicit fields are just that, the hidden default fields.
If Crystal is doing it's own thing, then you may have to use a standard SQL statement: cast(thisVAlue as smalldatetime) or Cast(thisValue as datetime)
Check out CAST and CONVERT in SQL server Books on line.
Regards!
How are you gathering the data in crystal? are you using a standard SQL statement, or are you letting crystal grab it? Crystal is very much like Access, so, when you use ODBC to grab notes information you can grab either the forms or the view. You can use a SQL statement to collect the records, or you can use Crystal import/link (Access import/link) to link to and import the view or form.
Since I don't have Crystal here, I used Access... I got "TEXT" whenever the column "style" was not set to "Date/Time", or when the formula or field wasn't explicitly set to Date/Time.
The Notes implicit fields are just that, the hidden default fields.
If Crystal is doing it's own thing, then you may have to use a standard SQL statement: cast(thisVAlue as smalldatetime) or Cast(thisValue as datetime)
Check out CAST and CONVERT in SQL server Books on line.
Regards!
Hi Sjef, the Azzurri huh? Now we are on the same side!
Great suggestion I pulled the data in Access using the same driver and no problems - each column interpreted correctly. So the type does come across... I haven't bought Crystal yet so I have put this into the hands of the reseller. ( along with this info that Access works fine ) It is after all the only thing stopping the sale! :)
Hi Marliyn,
I am gathering the data via Crystal.. ( not SQL statement ).
I am assuming a custom, hidden view, is the best / fastest way to generate the reports from the Domino database ( working on the theory less data in the view the better )? Why would you use the form option? If you haven't got the ability to create/update custom view / or don't want to get someone in to make it for you? Is there some other reasons? Am I missing something?
Thanks for the responses.
Broad.
Great suggestion I pulled the data in Access using the same driver and no problems - each column interpreted correctly. So the type does come across... I haven't bought Crystal yet so I have put this into the hands of the reseller. ( along with this info that Access works fine ) It is after all the only thing stopping the sale! :)
Hi Marliyn,
I am gathering the data via Crystal.. ( not SQL statement ).
I am assuming a custom, hidden view, is the best / fastest way to generate the reports from the Domino database ( working on the theory less data in the view the better )? Why would you use the form option? If you haven't got the ability to create/update custom view / or don't want to get someone in to make it for you? Is there some other reasons? Am I missing something?
Thanks for the responses.
Broad.
YOu would use the form to use a SQL statement... :) You could use the view, but then the fields might not be available.
It is possible to get the data coming across as DateTime without having to do a conversion in the reporting application.
-------------
9. Reporting off a View with the native driver returns all data types as string type. In order for the data types to retain their original type, the View must be based off a single Form and the View must be based on the condition “Form used”.
-------------
THIS IS ALSO TRUE FOR NOTESSQL DRIVER ( 3.02g atleast ).. Specifically what happened was I had a slightly complex selection formula in the view I was reporting off. Replaced it with a simple: SELECT ((Form = "FrmLead")) selection formula and now my column types are retained...
The more complex selection can then be done in your reporting application.
Broad.
-------------
9. Reporting off a View with the native driver returns all data types as string type. In order for the data types to retain their original type, the View must be based off a single Form and the View must be based on the condition “Form used”.
-------------
THIS IS ALSO TRUE FOR NOTESSQL DRIVER ( 3.02g atleast ).. Specifically what happened was I had a slightly complex selection formula in the view I was reporting off. Replaced it with a simple: SELECT ((Form = "FrmLead")) selection formula and now my column types are retained...
The more complex selection can then be done in your reporting application.
Broad.
Reporting off a View with the native driver returns all data types as string type << Really? When I do this in Access, it doesn't. Now if I'm doing a SQL statement: i.e. select this as Name1, cast(this2 as smalldatetime) as CreateDate, etc. then I may have to load the statement with the correct parameters since SQL, MySQL, etc., or has smalldatetime, datetime. Older versions of SQL had only Date and separate time.
Not sure if I answered your question, though. Usually if it doesn't automatically import correctly, and changing the view or form doesn't help, then I do the conversions in the SQL statement and the massaging that goes on after.
Not sure if I answered your question, though. Usually if it doesn't automatically import correctly, and changing the view or form doesn't help, then I do the conversions in the SQL statement and the massaging that goes on after.
This is from a Crystal Help Document:
9. Reporting off a View with the native driver returns all data types as string type. In order for the data types to retain their original type, the View must be based off a single Form and the View must be based on the condition “Form used”.
----------
This is true for NotesSQL( and apparently native driver ) WHEN the selection formula is used, it seems, for anything more than form selection... SELECT ((Form = "FrmLead"))
Ie. Have this: SELECT (Form = "FrmLead" & LeConfirmWrapUp = "Confirmed" & LeInHomeResult = "") and all of a sudden you do not get your column TYPES transferred - you then have to create a Formula Field in Crystal to get your dates in date format etc. which is just a pain. Why u cant use a more complex formula I would love to know.
Marliyn is it possible to accept:
Comment from BroadAustralia
Date: 07/18/2006 10:10AM EST as answer, with yours as assisted ( with all points ) as it is an alternative?
Broad.
9. Reporting off a View with the native driver returns all data types as string type. In order for the data types to retain their original type, the View must be based off a single Form and the View must be based on the condition “Form used”.
----------
This is true for NotesSQL( and apparently native driver ) WHEN the selection formula is used, it seems, for anything more than form selection... SELECT ((Form = "FrmLead"))
Ie. Have this: SELECT (Form = "FrmLead" & LeConfirmWrapUp = "Confirmed" & LeInHomeResult = "") and all of a sudden you do not get your column TYPES transferred - you then have to create a Formula Field in Crystal to get your dates in date format etc. which is just a pain. Why u cant use a more complex formula I would love to know.
Marliyn is it possible to accept:
Comment from BroadAustralia
Date: 07/18/2006 10:10AM EST as answer, with yours as assisted ( with all points ) as it is an alternative?
Broad.
You know I haven't been in Crystal for awhile, but it's about a clutzy as Access. However, both allow you to create your own SQL query for the recordset. Now, the down side to this is that you have to do a bunch of code to open the odbc connection, pass a sql statement to collect a recordset and then step through the recordset. In access, you do this in VB. I seem to remember that I could create SQL queries and ODBC connections in Crystal.
At any rate, if you feel you've answered your own question in your post, then say so and as Customer Service to refund your points.:)
At any rate, if you feel you've answered your own question in your post, then say so and as Customer Service to refund your points.:)
sorry for some reason i thought u were admining this section now.. wonder why i thought that! i will advise community support.
Featured Post
Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.
Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month6 days, 10 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.