Crystal Reports not running for Query using XML Path

[former_member1281848]

Hi fellows,

First question in this forum so please pardon me for any mistake.

I am using the following query in of the view in SQL 2012 & my Crystal Report Version is 2012

query.jpg (See the attachment for query details) couldn't paste it here.

it's working fine and giving me the results I am after. However, I am call this view from Crystal reports it comes up with the following error

If I remove the end part from the same query i.e.

TYPE).value('(./text())[1]','varchar(max)') it works fine.

I have tried to SET_ANSI_NULLS & QUOTED_IDENTIFIER OFF but that didn't give me any joy.

Can someone please help me as it's making me crazy and delaying the project further.

P.S.

Alternatively, if someone could please propose a solution for concatenation in CR 20018 as below scenario:

Table Data

Solution required.

Adapted from

https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

Thanks in advance, looking forward for a prompt response.

Cheers,

Sarfraz

[DellSC · Accepted solution]

Which SQL Server ODBC driver are you using and what version of SQL Server are you connecting to?

One way to do this using just Crystal formulas would be this:

1. Group by EntityID. Suppress the group header and details section.,

2. Create two formulas, both of which will look something like this:

{@ConcatValue1}
WhilePrintingRecords;
StringVar v1;
if OnFirstRecord or previous({Entity ID}) <> {Entity ID} then v1 := "";
if v1 = "" then 
  v1 := {Value1}
else
  v1 := ", " + {Value1};

3. Put both of these formulas in the suppressed details section.

4. Create another pair of formulas to display the values:

{@ShowValue1}
WhilePrintingRecords;
StringVar v1

5. Display the data in the group footer section.

-Dell

[former_member1281848 · Accepted solution]

Thanks Dell for the prompt response, I have been pulling my little hair for a while.

Driver = SQL Server Native Client 11.00 ( Crystal Report is 2008 NOT 2012) Sorry it was a typo before. The SQL Server I am connection is SQL 2012.

There is another supplementary question:

Will I be able to Compare/Match the output string with another string from a different table using above method ?

Once again thanks a lot for this proposed solution, I will update the outcome shortly.

Sarfraz

[former_member1281848 · Accepted solution]

Hi Dell,

The solution you provided has given me the desired results. I only had to tweak little bit but more or less same logic.

Now the last (hopefully) problem left , that is I am comparing two strings using formula, if two values "matched", result is printed on the report and if not matched, it prints " Mismatched" , it's also working perfectly fine.

Now, I want to use a parameter for user to select either "Matched" or "Mismatched values", unfortunately what I am reading it's not possible in CR to use formula field as parameter. Unless I am missing something?

Second option I could print only 'mismatched' values on the report but even this is not achievable for me. I will appreciate if you could look into this for me.

Thanks,

[DellSC · Accepted solution]

Assuming that your formula for matched/mismatched is called {@IsMatched}:

1. Create a parameter with the options Matched, Mismatched, All. I'll call this {?RecordType} for this example.

2. In the Group Select Expert (not the record select expert...), use a formula like this:

{?RecordType} = 'All' or
{?RecordType} = {@IsMatched}

This should give you what you're looking for.

-Dell

[former_member1281848 · Accepted solution]

Thank you very much for all the assistance. It worked fine !

P.S. The initial 'Quoted Identifier' was resolved by updating drivers to

Microsoft ODBC Driver for SQL Server Version 14.00.1000

Now report is working fine.