Solved

Crystal Help

Posted on 2013-01-23
5
291 Views
Last Modified: 2013-01-28
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
Comment
Question by:Star79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 38811170
You can use their spreadsheet as the source for the exclusion drugs if you want.

mlmcc
0
 

Author Comment

by:Star79
ID: 38811260
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
 
LVL 18

Assisted Solution

by:vasto
vasto earned 167 total points
ID: 38811366
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 166 total points
ID: 38811608
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
 
LVL 35

Accepted Solution

by:
James0628 earned 167 total points
ID: 38813607
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question