SQL expressions and parameter issue
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15254
Printed Date: 02 May 2024 at 5:41am
Topic: SQL expressions and parameter issue
Posted By: dbodell
Subject: SQL expressions and parameter issue
Date Posted: 28 Dec 2011 at 12:34pm
|
I have a bit of a quandry and could use some advice. I am making some changes to a rather complex report. It is complex in that the author used a large number of SQL expressions to calculate some columns. As this is a health care oriented report, I want to allow the user to limit the report by physician. The physician table is one of the tables joined in my report, but it contains a huge number of irrelevant or inactive records. I want to limit the physicians that can be selected to a list of active ones. Normally I would do this by creating a command and using the command to feed the values list for the parameter. However, as soon as I create a command, the SQL expressions are no longer available to me. Apparently Crystal (2008) considers a command to be a separate data source. I have a view in another database that lists just active physicians but that, too, would be a separate data source. The only thing I can think of to do would be to create views for each of my tables in my custom code database (the one that has the view in it for the physicians) and substitute those for the actual tables out of the application database. Is this a solution? Is there another way around this? I love SQL expressions but unfortunately Crystal doesn't play well... Thanks in advance for your advice. ------------- Thanks, D. Bodell |
Replies:
Posted By: sgtjim
Date Posted: 28 Dec 2011 at 12:54pm
|
You can always create a dynamic parameter. This is "command / SQL" in the background that will limit the choices your users can select based on the query you write to give the parameter values. You need access to your BOE server to do this, as the query will be called on the server side then the values will be populated in the dynamic parameter and the users will only be able to select values you want them to.
The SQL I use for the department dynamic parameter is; SELECT '0' AS DEPARTMENT_ID, '0' AS REV_LOC_ID, ' All' AS DEPARTMENT_NAME UNION SELECT DEPARTMENT_ID AS DEPT_ID, REV_LOC_ID AS REV_ID, DEPARTMENT_NAME AS Name from DEP Where RECORD_STATUS <> 4 AND RECORD_STATUS <> 2 OR RECORD_STATUS IS NULL AND SPECIALTY IS NOT NULL ORDER BY REV_LOC_ID, DEPARTMENT_NAME Once the dynamic parameter is set up then you can use it in as many reports as needed, you just call it again. There is a ton of resources out there on how to set up dynamic parameters too. |
Posted By: dbodell
Date Posted: 28 Dec 2011 at 1:14pm
|
Are you an EPIC person??? How funny is that! I do have access to BOE server but I have never done what you are suggesting. Can you walk me through it? It would be a huge improvement for me to be able to set all my basic parameters this way. I'm all about not re-inventing the wheel on each report. If you are open to assisting me, I would be very grateful and would certainly return the favor. I am Epic Clarity certified in Ambulatory, PB, Cadence and very good with SQL. I mostly was using SSRS at my previous project because they hated Epic Crystal Integration, but now I have to do everything for this client in Crystal/BOE. ------------- Thanks, D. Bodell |
Posted By: sgtjim
Date Posted: 29 Dec 2011 at 5:17am
Well I am not an "EPIC" person, but I am certified in Ambulatory and Inpatient and decent with SQL query writing.  We do not have the EPIC crystal integration yet... but soon. All our reports are via the BOE servers.
First there are a couple of down sides to dynamic parameters. Displaying the choices of the parameter in the header can be tricky. I use a sub-report. If you are exporting your reports to Excel, (which I do a lot of) then getting the parameter choices to display with out messing up the header is... just plain not going to happen. Now I would start with reading up on these two sites first. They are not totally dedicated to dynamic parameters but are a good place to start. http://www.dotnetheaven.com/UploadFile/SaifiFFC/CrystalReport07122007062723AM/CrystalReport.aspx - Site 1 http://www.informit.com/articles/article.aspx?p=683066&seqNum=3 - Site 2 OK lets start with the crash course in setting up the parameter.(I can't take credit for all of this, I have learned a lot from my coworkers, and I am poaching almost all of their 'guide') 1.Connect to your BOE server, launch Business View Manger 2.Create new Data Foundation - (test your select statement first) a.File - New - Data Foundation. b.You will be prompted to select A Dynamic Data Connection - choose DataConnection1. c.At the Insert Data Tables window, highlight Add Command and click the Add button. d.Paste your SQL Query into the box. e.You have to create a new parameter list item. f.Click the Create button in the Parameter list frame. g.Enter a Name, and Prompting text, click Ok, click Ok, click Close. h.Save in the 1 Data Foundations folder with a name that ends with _DF. i.Close the new Data Foundation. 3.Create new Business Element - a.File - New - Business Element. b.You will be prompted to select a Data Foundation, choose the Data Foundation you just created. c.On the Insert Business Fields window, open the Command in the Data Foundation, highlight each field, and click Add to insert the fields from your Data Foundation. d.Click close after all fields are added. e.Save in the 2 Business Elements folder with a name that ends with _BE. f.Close the new Business Element. 4.Create new Business View - a.File - New - Business View. b.You will be prompted to select a Business Element, choose the Business Element you just created, click Add, and then Close. c.Save in the 3 Business Views folder with a name that ends with _BV 5.Create new List Of Values a.File - New - List of Values. b.You will be prompted to select a Business View, choose the Business View you just created c.On the Create List of Values window - i.Enter a name that ends with _LOV. ii.Move the ‘Code’ or ‘ID’ field from the left to the List of Values Fields window on the right. iii.Choose the ‘Name’ field in the Description Field drop down box. iv.Leave all other options as they are. v.Click OK, and then select the 4 List Of Values folder to save in. 6.Create new List of Values Prompt Group - a.Start a new report in Crystal Reports. b.Create a new Parameter Value. c.Name the parameter with the same name you used for the List of Values and add “- Prompt Group” at the end. d.Choose Dynamic in the List of Values field. e.Enter the appropriate Prompt Group Text. f.Choose the Existing radio button for the data source, and choose the new _LOV you just created. g.Click the row showing the Value, Description and Parameters in the middle of the window, then set the options as required in the bottom section of the window. h.Click OK. Now once the parameter is all set up, select, "Dynamic" from the List of Values drop down and "Existing" as the data source when creating the new parameter and select your Prompt Group from the drop down list. You will need to have Crystal connected to the server to be able to access the dynamic parameters on the server. I usually just open the repository explorer and this prompts the server log in window. Well, I hope this helps. |
Posted By: dbodell
Date Posted: 29 Dec 2011 at 6:26am
|
Your instructions are excellent! I have followed your steps and I am now down to a problem of not seeing the data in my parameter pick list. But before I ask for help on that, I'll see what I can figure out. However, I do have a couple more questions: 1) Is the naming convention you used in any way a requirement? In other words, do the various pieces have to have a _BV, _LOV, etc? Or can they be named whatever I want? 2) Is it possible to use a business view over data in one database to load the pick list and a different database to run the report? I have a custom code database called DWH (data warehouse) and a CLARITY database, of course. I have a view in DWH that selects the right providers. It refers to CLARITY_SER in the Clarity database. Is the data connection for the dynamic parameter supposed to be pointing to CLARITY if I just cut and paste my view's SQL into it? Right now the data connection is pointing to DWH. I can't tell you enough how much I appreciate your help. ------------- Thanks, D. Bodell |
Posted By: sgtjim
Date Posted: 29 Dec 2011 at 6:34am
|
1) The naming convention is not requirement. I found that was really helpful down the line when I had to go back and edit the parameter.
2)I have never tried this. But I would suppose that it should work just fine as long as Crystal is connected to both DBs and BOE is too. Did you build the data warehouse?  Is it custom or an EPIC released version?
|
Posted By: dbodell
Date Posted: 29 Dec 2011 at 7:07am
|
You are a GOD!!!! Thank you so much for teaching me this. It has opened up so many things for me. Where I was before I started consulting had a legacy system (Meditech/LSS) and with over 50 clinics to roll out, we spent a good long time having to merge data. We developed a data warehouse for a number of reasons: 1) To keep my views and stored procedures in separate from Clarity; 2) to merge the data into a common visit table, transaction table and various dimension tables that mapped old values to new ones for things like Patient IDs and Provider IDs, etc. 3) to isolate my work for development from the active production environment. We actually replicated the production tables I needed so that I was never actually working with the Clarity server. I am an organization freak, so it was very helpful. Are you a consultant or working at an Epic client? ------------- Thanks, D. Bodell |
Posted By: sgtjim
Date Posted: 29 Dec 2011 at 7:25am
|
That is really cool and a much better way to deal with legacy data then some of the solution I have heard of.
No consulting for me. I work for a client, for about 6 months now. |
Posted By: dbodell
Date Posted: 29 Dec 2011 at 8:28am
|
Well, if there is anything I can help you with, please let me know. I'll send you my email address if I can find a way to send it privately. ------------- Thanks, D. Bodell |
Posted By: nrkc99
Date Posted: 27 Jan 2012 at 8:36am
|
Hi,
My hospital uses both Epic and Cerner Reporting. As a business objects admin I was asked to integrate Epic with Crystal Reports. I'm Using BOE XI 3.1 and Crystal Reports 2008, I want a planning to integrate my Crystal reports server with Epic reporting. Any ideas how can I start the integration and step by step procedure to follow while integration. |
Posted By: dbodell
Date Posted: 27 Jan 2012 at 1:52pm
|
Happy to help. Email me @ dbodell@comcast.net ------------- Thanks, D. Bodell |
Posted By: dbodell
Date Posted: 17 Feb 2012 at 6:11am
|
Hi. As a lead report developer at a company implementing Epic, I had the opportunity to handle all of the reporting and integration with Epic. I must say that I was disappointed with the way in which Epic and Crystal are integrated. The entire set up and implementation of Epic-Crystal integration is an arduous process that ultimately only allows you to push reports out to users on a scheduled basis. Users cannot run reports ad hoc or on demand at all. For this reason my company chose to implement a data warehouse (data marts) with merged legacy and Epic data and to use both BOE and Microsoft SSRS to do all reporting. We set up a website for user access to reports out of SSRS, we scheduled reports that were Crystal to run out of BOE and some users could run their own Crystal reports by using an inexpensive Crystal Viewer (there are many on the market and a few really good ones). I really don't understand Epic's entire way of dealing with reporting. The SQL database is poorly designed for efficient reporting, although in the 2010 version there will be a lot more data marts and that should help a lot. There is documentation on the Epic Userweb site that gives you step by step instructions for setting up Epic-Crystal Integration. It involves some application set up, installation of BOE functionality on the report developers' machines, and a significant amount of build if you intend to use EC Integration to separate / burst reports out to individual users based on security. If the Crystal report is not set up exactly right, it will bomb out and you have to go through a pain in the butt process to rerun the report each time (remove the published report from BOE, make the fixes, run the batch job again to start the report, wait until it gets delivered to Hyperspace My Reports, etc.) If a report runs successfully and you need to rerun it, you may not be able to because of logic in Reporting Workbench that limits your ability to rerun the same report within a specified timeframe. If I can give you any more info, let me know.
|