Solved

Crystal Help

Posted on 2013-01-23
5
284 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
5 Comments
 
LVL 100

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 100

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 34

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now