Solved

Crystal Help

Posted on 2013-01-23
5
286 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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