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.

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.

Site 1

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.

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?

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.

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.

Edited by nrkc99 - 27 Jan 2012 at 9:09am