Notifications
Clear all
Topic starter
I have seen several reports with tables with the same name but with _ after it (or however its named). Usually used when needing to compare two fields of the same name. Like Document & Document_1 within the same report. Used to pull one Doc Type from Document table & a different Doc Type from Document_1 table to compare the data. Can anyone explain what this is & how to create a "duplicated" table within the same report as shown explained above?
Thank you
Posted : June 28, 2017 4:53 am
The more likely example of this would be to join USR (or USRINFO) to Document twice.
You might first join USR.PVID to DOCUMENT.PUBUSER to get the user who first signed the document. You would then join USR.PVID to DOCUMENT.USRID which gives you the responsible provider for the document. (the name as it appears in the document list)
You can join document to itself but that is a more complex topic.
Posted : June 28, 2017 5:26 am
Topic starter
Thanks for that info, but that not I am referring to. In the field explorer in Crystal Reporting it has Document as a table then it has another table underneath named Document_ which is a "duplicate" table from Document. How can I do this?
For the particular report its pulling by one Doc Type from Document table then a different Doc Type from Document_ since reporting on two different doc types for the same patient. Hope this explains this better.
Posted : June 28, 2017 6:21 am
It's called an "Alias" and it can be created in the Database Expert by adding the same table twice to a report by using the > button. The second time you add the same table it will confirm you really want to add another alias. If "Yes" then it will create another entry in the Selected Tables column and call it what it normally does and append "_1" to the end of it; i.e. obs_1. If you add another alias it will call it obs_2, and so on.
I might use aliases like this for a query that asking for all observations, like BMI, for patients that have had at least one high BMI (>30).
Posted : June 28, 2017 6:24 am
That means that the crystal reports is using a SQL query that has the documents table joined twice. You can see the (messy) sql query by clicking on Database -> Show SQL query.
Without seeing your specific report, it is difficult to determine why it was done that way. I have joined a document table twice if I wanted to show a document as well as any appended documents to it. Based on your description it is probably something like this:
Display all patients who have a document of doctype 1 as well as a document of doctype 2. To add something like this to your own report simply add the documents tabe to your report twice when you are selecting tables, just make sure you specify criteria for both joins.
Posted : June 28, 2017 6:31 am
Topic starter
Thank you! I got an alias added.
What is the difference in using alias vs sub report? Can you provide some examples where I would use alias vs sub report?
Thanks
Posted : June 28, 2017 7:10 am
Sometimes the difference between joining the same table multiple times using aliases and using it in a subreport is preference, sometimes it is due to the size of the data, sometimes it is due to the way the designer wants the report to look.
If the data you are working with is a large amount of data in the secondary table, then it is sometimes more efficient for you to put this data in a subreport and then join that subreport to the main report based on criteria in the main report. Sometimes a report will never finish running if you try to work with too much data in your main report. If this is the only reason for you deciding to use a subreport then adding a command, that uses criteria, in the data definition might be just as efficient without the need to use a subreport. So definitely check into using commands in your data definition if this is what you are trying to do.
Now if you have a report that has some line data that you are displaying and then you want to then add tabular data at the end of each line data section, then a subreport is usually the easiest way to go. For instance, lets say I want an immunization report for patients and I want it designed like the following, then I'll want to use a subreport for the table. This will then produce the table at the end of each patient's section before going on to the next patient (dependent on you placing the subreport in the correct section that is).
Patient Name: John Doe Patient ID: 123987
Patient DOB: 01/01/2015 Patient PCP: Dr. Seuss
Immunization Date Given Given By
Tdap 06/15/2017 Nurse Jones
MMR 06/15/2017 Nurse Jones
Posted : June 28, 2017 7:35 am
