Alias Tables and LOJs or SubReports...

[jcrawford08]

Hi all,

I'm Running CR XIR2 in a SQL environment;

The report I'm working on needs four seperate ID Numbers displayed, for ease, lets call them:

Num1
Num2
Num3
Num4

These are contained in table "Provider_IDNums" that we have linked to the primary "Provider" table:

{Provider.ProviderID}={Provider_IDNums.ProviderID}

In this situation Num1 is ALWAYS present, so in the main report I presently have the selection criteria set:
{Provider_IDNums.DocName}="Num1" and then the field {Provider_IDNums.IDNumber} in the details of the report.

The other three numbers I have being brought in through sub-reports presently. I have them linked on the primary field and then some advanced formula and grouping work to actually show "None" if there isn't a match for the given {Provider_IDNums.DocName} otherwise display the {Provider_IDNums.IDNumber}.

My question is - would it be better to pull in three additional 'Alias' tables and do a LOJ on them to pull this information in?

My goal is to potentially improve performance, so is there a performance advantage in using Alias tables vs. Sub-reports?

Tips, ideas and thoughts are always welcome!!


Thanks!


jcrawford

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

This is hard to follow since you are trying to simplify. Instead, please show (mock) sample data for several rows of data that use the fields of interest (with no selection formula). I can't tell whether Num1, Num2, etc., are instances of the same field, or whether they are actually different fields. Also can't tell how the fields naturally relate to each other as raw data.

-LB

 

[jcrawford08]

They are instances of the same field; my apologies for convoluting it with simplifications:

Name NPI License Medicare DMAP
Dr. A 123 456 789 912
Dr. B 234 567 890 123
Dr. C 345 678 901 234

The NPI, License, Medicare, and DMAP numbers are all recurring instances of the same field {Provider_IDNums.IDNumber}. The NPI is always present; however, the others may or may not be present in the table.

Presently, I have License, Medicare, and DMAP brought in with separate sub-reports for each - would Alias tables work better though from a performance standpoint?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

It looks like you could insert a crosstab and use {Provider_IDNums.IDNumber} as the column field and Name as the row field. Not sure what you are summarizing for the summary field.

-LB

 

[jcrawford08]

Presently this report is just being used to do a massive export for another system to import; we are changing our software vendor and so we're rather limited on changing the arrangement of columns.

There are about 480 or so Drs. on the report, one row per doc, and then their respective identification numbers should be listed. Unfortunately the table structure of our existing software doesn't make it that easy. Hence the use of sub-reports.

So, for multiple recurring instances of the same field (each one with different selection criteria needed), is it the easiest to use sub-reports as I've done, or would pulling in Alias tables work better?

Each subreport has its own set of selection criteria designed to pinpoint the exact number we want (for instance Drs. may have more than one License number, but we want only the one for OR that isn't expired), while as others really don't (only one Medicare number for a dr. at our system).

I hope I'm not convoluting it further - the sub-report method is working right now, but it is just VERY SLOW, because it has to calculate three sub-reports for each line of data...

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

You haven't really explained why a crosstab wouldn't work. Why don't you show some examples of selection criteria applied to the field.

-LB

 

[jcrawford08]

See the attached report; I have a very odd workaround for listing "None" on the subreports if there indeed isn't any Medicare or Medicaid number found for a given provider. It's based on a Null Check display formula, a "None" text field, and conditional suppression.

The only sub-report that has additional selection criteria is the State License one, which in addition to linking on the primary key of the main report lists the following:

{RV_Practitioner_ID_Numbers.DocumentName} = "State License" and
{RV_Practitioner_ID_Numbers.State} = "OR"

There are also several other columns within the primary report that are exporting data into excel, such as Office information, address information, and effective dates. In using a cross-tab, would I be able to preserve the export ability to Excel?

I speak with ignorance, as I've never had to build a cross-tab report before. Would you advocate that I pull in additional Alias tables and build it as a cross-tab for better performance?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18