Report

[impalacrazy]

hi all, here is what i am trying to do and im wondering if it will work. i have a data universe (MPDB) that has the following feilds:
MPID / lastname / fistname / Title / Site / Medical Director name / Regional Director name.

i have a report that has the following

MPID|lastname|firstname|title|site|medical Director name|Regional director name.

now the MPID is the key for all data in the universe. i am trying to write a query that will allow me to have the report do the following:

MPID|lastname|firstname|title|site|MPID of medical director|medical Director name|MPID of regional director|Regional director name.

The query i ahve thus far is:

SELECT
dbo.ProviderTitleHistory.ProviderMasterID,
dbo.ProviderTitleHistory.TitleCode,
dbo.ProviderMaster.LastName,
dbo.ProviderMaster.PreferredName,
dbo.ProviderTitleHistory.SiteLocationName,
SiteLocationLookupFlat.RegionalDirectorName,
dbo.ProviderMaster.ProviderMasterID
FROM
dbo.ProviderTitleHistory,
dbo.ProviderMaster,
SiteLocationLookupFlat,
dbo.StaffHistory,
dbo.ProviderSiteLocationStaffGroup
WHERE
( dbo.ProviderMaster.ProviderMasterID=dbo.StaffHistory.ProviderMasterID )
AND ( dbo.ProviderTitleHistory.ProviderMasterID=dbo.StaffHistory.ProviderMasterID and dbo.ProviderTitleHistory.StaffGroup=dbo.StaffHistory.StaffGroup )
AND ( dbo.ProviderSiteLocationStaffGroup.ProviderMasterID=dbo.StaffHistory.ProviderMasterID and dbo.ProviderSiteLocationStaffGroup.StaffGroup=dbo.StaffHistory.StaffGroup )
AND ( SiteLocationLookupFlat.SiteLocationID=dbo.ProviderSiteLocationStaffGroup.SiteLocationID )
AND (
dbo.ProviderTitleHistory.ActiveFlag = 'Y'
AND dbo.ProviderTitleHistory.TitleCode = 'MD '
)
ORDER BY
1


i tried a case statement and an if statement to have the 2nd and 3rd MPID's in the report only pull if the title code was RD (regional director) but kept getting an error. Can anyone sugest a fix.

If i have made you head explode please let me know.

Thanks

Ivan

 

[skrandel]

It's really impossible to tell what you're trying to do. But, if I had to guess, you're going to need a subquery.

Steve Krandel
Intuit

 

[impalacrazy]

Sorry i had a feeling it would be hard to understand but i think i can explain better. We have one database (masterbase) were we store all of our doctor information. The MPID (master provider id) is the key in the database. So the issue i am having is being able to have the report show me two different ID's on teh same report. Because MPID is driving everything in the system if i try to have an MPID for a medical director and in the same row have a MPID for a regional director it shows me the same MPID in both colums associated with teh medical director. For some reason it locks into that name first and doesnt lock into the second name and there is where my problem lies being able to have the ID show for two differnt people in the same row...

 

[blom0344]

O boy, this is hard to get at. I have the distinct feeling you have a recursive table to deal with.
Am I right in assuming that there is a hierarchy between regional and medical director, like a number of medical directors falling under one regional director?

If you have a recursive structure just one level deep, you can solve this quite easily with derived tables in the universe..

Ties Blom

 

[hilfy]

If I understand you correctly, you can do this using table aliases.

I'll give you an example from one of our universes.

I have an Employee table, the key to which is the Employee_key. Inside the Employee Table I have a Manager_Key field which contains the Employee_Key of the manager. To get the manager's name for an employee, I have created and "alias" on the employee table called "Manager". So, I have two copies of the employee table in the universe, one is "Employee" and the other is "Manager". There is a link from Employee.Manager_Key to Manager.Employee_Key. From this, I have an "Employee" class with dimensions based on the Employee table. The Employee class has a "Manager" sub-class with dimensions containing informatin about the manager.

You'll probably need to do something like this to get your data.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[blom0344]

If you use derived tables (with the correct table expression) then you can create a set that really contains employees and one that contains just the managers.
Just using an alias will give you 'to many' entries. Which will eventually lead to the question how to draw a list for all managers from the employee table..

Ties Blom

 

[impalacrazy]

Thanks for your help. in snooping around some other reports i was able to find a way. What i had to do was create a query with one set of data, then create a new data variable off the same query so the report had two querys running off the same data in the report. when unlinking them in the defenition tab i was able to haev both querys running at once to produce the info i needed. Thanks Ties bloom your bringing up the derived tables in the unervirse got me to thinking. The report works great now...

Cheers,

Ivan