You are viewing limited content. For full access, please sign in.

Question

Question

Crystal Reports

Integrations Laserfiche
Updated February 18, 2016
asked on November 19, 2015 • Show version history

Does anyone have experience making Crystal Reports from the Laserfiche sql database? I am using the propval table to grab the information from to make the report.  I need to take certain values from the str_val column such as vendor and credit card type and make separate columns out of them on the report. Then, I need to tie the values in the num_val column and date_val column to each credit card transaction.  

 

Any suggestions would be greatly appreciated. 

0 0

Answer

SELECTED ANSWER
replied on November 19, 2015

The trick to getting different field values into a single row is joining the toc table to the propval table one time for each field value you want.  So you could do something like:

select toc.name as ReportName, vendor.str_val as VendorName, amount.num_val as Amount
from toc inner join propval as vendor on toc.tocid = vendor.tocid and vendor.prop_id = 42
inner join propval as amount on toc.tocid = amount.tocid and amount.prop_id = 50

The key to keeping this query readable is using sensible aliases for the instances of the propval table.  The values 42 and 50 are the hypothetical ids for the fields you want, it's possible to join on propdef if you want to look up by name, but that complicates the query.

This query will give you every entry that has both of those fields set, you'll probably want to add a "where" clause.  If some fields won't be assigned, you'll need to do a different kind of join (left outer) on that instance of propval.

1 0

Replies

replied on November 23, 2015 • Show version history

Hi Brian,

 

Thank you for your advice.  When I input that query into SQL Server Management Studio, I am getting the following error: 

 

Msg 208, Level 16, State 1, Line 1
Invalid object name 'toc'.

 

Additionally, when I hover the pointer over the red underlined parts of the form, I get this error: 

The multi-part identifier "toc.name" could not be found

 

Any ideas why this might be?

0 0
replied on November 23, 2015 • Show version history

In SSMS, make sure you have the correct database selected in the database dropdown next to the "Execute" button or run the command "USE [Sql Server Database Name]".

1 0
replied on November 23, 2015

Thank you! That fixed the errors

0 0
replied on February 16, 2016 • Show version history

Brian, I am using a similar query for a crystal report:

select toc.name AS ReportName, PONumber.str_val as PONumber, dates.date_val as Date, EQ.str_val as EQNumber, MakeModel.str_val as MakeModel, ProdLineNum.str_val as ProdLineNum, PartOfLine.str_val as PartOfLine, VendorName.str_val as VendorName, Company.str_val as Company, UnitPrice.num_val As UnitPrice, Quantity.num_val as Quantity, Total.num_val as Total, MVPNum.num_val as MVPNum, Requestor.str_val as Requestor


from toc inner join propval as PONumber on toc.tocid = PONumber.tocid and PONumber.prop_id = 87
inner join propval as dates on toc.tocid = dates.tocid and dates.prop_id = 5
inner join propval as EQ on toc.tocid = EQ.tocid and EQ.prop_id = 652
inner join propval as MakeModel on toc.tocid = MakeModel.tocid and MakeModel.prop_id = 653
inner join propval as ProdLineNum on toc.tocid = ProdLineNum.tocid and ProdLineNum.prop_id = 654
inner join propval as PartOfLine on toc.tocid = PartOfLine.tocid and PartOfLine.prop_id = 655
inner join propval as VendorName on toc.tocid = VendorName.tocid and VendorName.prop_id = 13
inner join propval as Company on toc.tocid = Company.tocid and Company.prop_id = 15
inner join propval as UnitPrice on toc.tocid = UnitPrice.tocid and UnitPrice.prop_id = 277
inner join propval as Quantity on toc.tocid = Quantity.tocid and Quantity.prop_id = 278
inner join propval as MVPNum on toc.tocid = MVPNum.tocid and MVPNum.prop_id = 295
inner join propval as Requestor on toc.tocid = Requestor.tocid and Requestor.prop_id = 539
inner join propval as Total on toc.tocid = Total.tocid and Total.prop_id = 279

 

All of the data is collected from purchase order forms.  Users have the ability to order multiple items per purchase order, and that is where my problem comes in.  If the form has 2 items on it, my query returns each of those items 2 times for the columns Unit Price and Quantity.  If the form has 3 items on it, my query returns 3 items, and so on.  All of the data is being saved properly so that leaves my query as being the cause of the issue.

       

Do you know how I can change my query to return the items only one time each? 

0 0
View 4 previous replies
replied on February 16, 2016 • Show version history

Multi-value fields are stored as individual rows in the propval table, with the 'pos' column indicating the order.  What you probably want is to select those values out as individual columns in your result set.  The best solution I can come up with is something like:

inner join propval as FirstUnitPrice on toc.tocid = FirstUnitPrice.tocid and FirstUnitPrice.prop_id = 277 and FirstUnitPrice.pos = 0
left outer join propval as SecondUnitPrice on toc.tocid = SecondUnitPrice.tocid and SecondUnitPrice.prop_id = 277 and SecondUnitPrice.pos = 1
left outer join propval as ThirdUnitPrice on toc.tocid = ThirdUnitPrice.tocid and ThirdUnitPrice.prop_id = 277 and ThirdUnitPrice.pos = 2

Where you essentially split the UnitPrice table up into multiple tables, one for each position.  You need to do an outer join here, so that you get nulls back for values that don't exist.  Unfortunately, it's not a very flexible query since the number of times the table has to appear in the query is determined by the maximum number of values you expect for that field; if you go above it you have to adjust the query.  Maybe someone else has a more elegant way to handle this.

0 0
replied on February 17, 2016

Thank you for your response Brian.  Your suggestion is a step in the right direction but there are still some issues.  Here is what comes up when I add your suggestion to my query: 

 

In this case, there were only 2 items on the purchase order.  However the items are still showing up twice in two separate rows.  Does anyone have any suggestions on how to make it so the items only show up one time?

0 0
replied on February 18, 2016

Dane, I am working on a solution for this issue.  Can you let me know why you need the unit prices in the same row as separate columns?  Would you also need the ProductLine, QTY or any other value in separate columns in the same row?

Bill

0 0
replied on February 18, 2016

Thank you for the help William.  I am trying to make a Crystal Report from the data which is why I thought it would be easiest if the unit prices and quantities were in separate columns in the same row.  The columns I would like to have as separate columns in the same row are MakeModel, ProdLineNum, PartOfLine, EQ, Unit Price, and Quantity.  These columns each apply to one item, so if there are multiple items on a purchase order, there would be multiple values for these fields.  I also need the date, PO Number, VendorName, Company, and total as columns but there will only be one piece of information for these per purchase order.  

0 0
replied on February 18, 2016

Ok.  I think I understand.  I have attached a PDF output of a crystal report with PO and PO Item content.  Is something like you are interested in?

Example PO report.pdf (39.18 KB)
Download
0 0
replied on February 18, 2016

Yes, that is very close to what I had in mind.  The only difference is that I am not  capturing per item dollar amount totals, but rather just a total dollar amount of the entire purchase order.  

0 0
replied on February 18, 2016

OK.  I am very interested in following this through and would be happy to make the changes you would want so you can have a finished report. 

I would need to have a more interactive session with you to get the particulars and specifics of the template fields.

 

If you would like, you can contact me directly at:  bill.maners@solutionsplusconsulting.com

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...