[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Crystal Help

Hello All,
I have the below query
 select h.facid, h.patid, h.rxno, h.RoNo, h.PriceCd,d.sig, case when len(ltrim(coalesce(h.ccid, ''))) = 0 then ndc else h.ccid end AS 'NDC/CCID',
  h.druglabelname, h.dispensedt, h.dayssupply,e.PatName,
  h.IVType,h.MOP,h.InvoiceGrp,h.Qty
 from hrxs h
 inner join Pat..vPatNames e on h.facid = e.facid and h.patid = e.patid
 inner join dbo.[vw_crystal_ReorderDirectionsExpandedSig] as d on h.FacID = d.facid and h.PatID = d.patid and h.RoNo = d.rono
 
 where h.facid = @FacID and  IVType IS NOT NULL and h.DispenseDt between @MStart and @MEnd and h.MOP in(Select * from MOP
The above query gives me a list of drugs.
The client maintains an excel sheet of drugs which appear in the list above but they manually calculate the billing for those exclusion drugs as the system does not have any providence for this.The client is requesting a report to show those excel sheet exclusion drugs out of the list above.How can it be done?Can I implement those drug names in the report or should I create a table for those exclusion drugs and then populate the exclusion list by innerjoining with the above query.

Please suggest ideas.
0
Star79
Asked:
Star79
3 Solutions
 
mlmccCommented:
You can use their spreadsheet as the source for the exclusion drugs if you want.

mlmcc
0
 
Star79Author Commented:
You mean I can implement the drug list in the crystal itself as
Recordselect formula
{Drugtable.Drugid} in [""]
and provide the list of exclusiondrugs in the braces so that when the report runs it would return only those exclusion drugs
0
 
vastoCommented:
Your query has parameters and the only way I can see this implemented using a stored procedure or command. This means that you will be not able to retrieve the data directly from excel - you need to get the excel data first in your database and then ignore it.
You will be be able to exclude the data using JOIN or NOT in clauses. Both will not require changes in the record selection formula.

for example

SELECT *
FROM MainTable m LEFT JOIN ExcelTable e on m.ID=e.ID
WHERE e.ID is NULL

or

SELECT *
FROM MainTable
WHERE ID NOT IN (SELECT ID FROM ExcelTable )

The first approach will have better performance


Record Selection formula will have the worst performance even if you find a way to get the IDs to the report without bringing them to the database ( for example if you hardcode them in the record selection formula)
0
 
mlmccCommented:
Since you want to show the drugs in the exclusion table if you include your drug table and the Excel table as sources you can select as

{DrugTable.DrugID} = {ExcelTable.ID}

mlmcc
0
 
James0628Commented:
CR can use a sheet in an Excel file as a datasource for a report, in the same way that you would use a db table.  I suspect that the Excel sheet has to be pretty simple, but I really don't know.  I've done that a few times and kept the sheet simple, to try to avoid problems, so it was just a heading row with column names and then rows of data.  I don't know if it _has_ to be that simple.  If the Excel sheet with the list of drugs is simple enough, you might be able to have the report read your original data and the Excel sheet, linked by a drug name/ID, and produce the report from that.  That's what mlmcc was suggesting.

 Depending on the report's current datasource, adding an Excel file as a second datasource may or may not be possible/practical.  Even if it's possible, using Excel files that way can be pretty slow, unless you're dealing with a pretty small number of records (rows).

 If your db happens to be MS SQL Server, another option is to create a linked server to the Excel file, and then use that in a stored procedure, for example.  Or use another method to read the Excel sheet in the db (instead of in the report).  The advantage there is that you can read the Excel sheet into a table variable or temp table with an index and then use that in your query.  That should be much faster than trying to do random reads on an Excel sheet.


 FWIW, a basic issue with trying to use an Excel file in a report is "access".  In my experience, the Excel files can only be used by one person at a time, so if anyone has the Excel file open, the report won't run, and only one person can run the report at a time.  There could be ways around that.  In my case, the few reports that used Excel files were specialized, not used often or by many people, so it wasn't normally a problem, so I never really tried to see if there was a way to get around that limitation.


 To answer a couple of your questions:

 If you can put the drugs in a normal db table, that would probably be the best solution.  The basic issue there is maintenance.  If a drug is added or removed from the Excel file, someone will presumably need to make the same change to the table.

 You could just manually enter the list in a record selection formula, as long as the list isn't too long.  Something like:
{Drugtable.Drugid} in [ "drug1", "drug2", "drug3", <and so on> ]

  There may be limits on how long a record selection formula can be (how many characters or conditions or something).  Even if that's not a problem, if the list is too long, it could just be slow.  And there's the maintenance issue again - If the list in the Excel file is changed, someone will presumably need to go in and edit the record selection formula accordingly.

 Hope some of this helps.

 James
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now