Parameter option "Allow Multiple values" in Command

[Former Member]

Hello

I work with CR 2013 and instead of using Select Expert I’m using Command and past into this SQL query:

SELECT "ihRawData_1"."Value", "ihRawData_1"."TimeStamp", "ihRawData_1"."IntervalMilliseconds", "ihTags_1"."Description", "ihRawData_1"."Tagname", "ihRawData_1"."RowCount"

FROM   "ihRawData" "ihRawData_1" INNER JOIN "ihTags" "ihTags_1" ON "ihRawData_1"."Tagname"="ihTags_1"."Tagname"

WHERE "ihRawData_1"."IntervalMilliseconds"={?Interval} AND ("ihRawData_1"."TimeStamp">= '{?Start}' AND "ihRawData_1"."TimeStamp"< '{?End}') AND

("ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_P.F_CV' or 

"ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_1_TO.F_CV') AND "ihRawData_1"."RowCount"=0

ORDER BY "ihTags_1"."Description", "ihRawData_1"."TimeStamp"

and I receive good data.

If I change

("ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_P.F_CV' or 

"ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_1_TO.F_CV'

to

("ihRawData_1"."Tagname" in '{?Tag}')

where Tag is parameter selected as “Allow multiple values”

Then SQL query syntax look like

SELECT "ihRawData_1"."Value", "ihRawData_1"."TimeStamp", "ihRawData_1"."IntervalMilliseconds", "ihTags_1"."Description", "ihRawData_1"."Tagname", "ihRawData_1"."RowCount"

FROM "ihRawData" "ihRawData_1" INNER JOIN "ihTags" "ihTags_1" ON "ihRawData_1"."Tagname"="ihTags_1"."Tagname"

WHERE "ihRawData_1"."IntervalMilliseconds"={?Interval} AND ("ihRawData_1"."TimeStamp">= '{?Start}' AND "ihRawData_1"."TimeStamp"< '{?End}') AND

("ihRawData_1"."Tagname" in '{?Tag}') AND "ihRawData_1"."RowCount"=0

ORDER BY "ihTags_1"."Description", "ihRawData_1"."TimeStamp"

After refreshing report and filling next parameters for creating query

javascript:;

I receive all tags from database, not the only two i chosen.

For this problem i have contacted support of Database manufacturer (my data-source) and they have look into dll which is responsible for transferring query from CR to database. Here is their answer:

We are not able to see what the query structure, that is being passed, looks like but engineering has used a debug dll in the past to see what values are being passed.  That was how we determined that you were requesting all of your tags before we fixed your original query.

As an example if I ask for tag JGLS.PLC7_S2_P_KK2_2_P.F_CV we would see entry similar to this in the debug DataArchiver log.

[09/21/15 15:52:02.904] Beginning TagOpenRecordset API call [Worker Thread]

[09/21/15 15:52:02.904] ConfigFile::TagSearch Beginning TagSearch.

[09/21/15 15:52:02.904] ConfigFile::TagSearch TagMask was not a mask [JGLS.PLC7_S2_P_KK2_2_P.F_CV]. Searching directly.

[09/21/15 15:52:02.904] ConfigFile::TagSearch Completed.

[09/21/15 15:52:02.904] Completed API call [Worker Thread] (returning 0)

[09/21/15 15:52:02.904] [Received from [::ffff:3.26.64.116]] [InQueue=0 ms] MessageId=14 RouteId=0 SourceClientId=3 DestClientId=-1 Key=0 [ResponseType-1 ResponseTransNum-14 ResponseThreadId-3212 TagNames-Buffer DataFields-Buffer StartTime-TimeStruct EndTime-TimeStruct SamplingMode-2 Direction-1 NumSamples-0 CalculationMode-1 FilterTag- AllowTagMasks-1 WaitForReply-1 WindowsUsername-Buffer WindowsFullUsername- WindowsDomainAndUsername-\208043969 AuditedWriter-0 IntervalMillisecs-900000 CalcRepEngExtra-0 DataInterval-900000 QueryModifier-0]

[09/21/15 15:52:02.904] HRMasterDataStore::DataOpenRecordset NumTags=[1] FirstTag=[ JGLS.PLC7_S2_P_KK2_2_P.F_CV] Start=[05/15/15 12:45:00.000] End=[05/16/15 13:00:00.000] SamplingMode=[2] CalculationMode=[1] Threadid=[1972]

[09/21/15 15:52:02.904] HRArchiveFile::DataOpenRecordset Start=[05/15/15 12:45:00.000] End=[05/16/15 13:00:00.000] SamplingMode=[2] CalculationMode=[1] ArchiveName=[User_FV_SKL_CNS_Archive007] Threadid=[1972]

But if I attempt to select multiple tags in CR I see something similar to this:

09/21/15 15:27:14.797] Beginning TagOpenRecordset API call [Worker Thread]

[09/21/15 15:27:14.797] ConfigFile::TagSearch Beginning TagSearch.

[09/21/15 15:27:14.797] ConfigFile::TagSearch TagMask was not a mask [ ?ᵻ ].  Searching directly.

[09/21/15 15:27:14.797] ConfigFile::TagSearch Completed.

[09/21/15 15:27:14.797] Completed API call [Worker Thread] (returning 0)

so it appears that CR is sending us the string of ?ᵻ .

What is wrong with this query?

[DellSC]

Take out the quotes around the parameter:

("ihRawData_1"."Tagname" in {?Tag})

Also, you only need quotes around a parameter in a command if the data type of the parameter is String and it doesn't allow multi-selection.

See my blog post for more information about working with commands:

-Dell

[Former Member]

I removed quotes around the parameter, doesn't help.

?Tag is string type.

Tomas

[DellSC]

?Tag may be a string, but it is multi-value, so you can't put quotes around it.

Is ?Tag a multi-select with hardcoded values or with not values so the user has to enter everything?  Or is it a dynamic parameter?  If it's dynamic, how are you getting the values for it?

Also, please give us the exact version of Crystal 2013 by going to Help>>About to get it.

Thanks!

-Dell

[Former Member]

?Tag is multi-select with imported values from text file and user can chose one ore more tags.

Its not dynamic parameter

CR 2014 Support Pack 4

Version 14.1.4.1327

Tomas

[DellSC]

Sorry I keep asking questions.... I'm just trying to narrow some things down.

First, what type of database are you connecting to and what type of a connection (ODBC, Native, etc.) are you using?

Also, I assume you meant Crystal 2013 - there is no 2014 version.  Crystal is now on SP6, so I would try to upgrade the version you have.  You can get to the upgrade install here:  Downloads for SAP Crystal Reports and others.

-Dell

[Former Member]

I'm using Proficy Historian 5.5 (Proficy Historian is a high performance data archiving system designed to collect, store, and retrieve time-based information at extremely high speed efficiently made by GE)

for detail go to Proficy Historian

Connection to Historian goes true OLEDB driver.

I have install SP6 but doesn't help

Regard Tomas

[DellSC]

You may be running into a limitation of the database.  I see that in your original query you use

("ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_P.F_CV' or

"ihRawData_1"."Tagname"='JGLS.PLC7_S2_P_KK2_2_1_TO.F_CV')

To test whether this is a database issue, try replacing those two lines with this:

"ihRawData_1."Tagname" in ('JGLS.PLC7_S2_P_KK2_2_P.F_CV', 'JGLS.PLC7_S2_P_KK2_2_1_TO.F_CV')

If this works, then the database will allow the "In" syntax, if it doesn't, then you won't be able to use a multi-select parameter.

-Dell

[Former Member]

I have replaced lines as you suggest but still doesn't work. It looks like i won't be able to use this option. Unfortunately i am a little bit disappointed because in CR 8.5 version i didn't have this problem. I was able to use multiple values option. I will keep trying to create query with Select Expert but i doubt that i will succeed because my database doesn't support time stamp as datetime value type. I will send you query made with Select Expert...

Tomas

[Former Member]

Here is query created with select expert, but it doesn't work due the wrong date conversion. If i would be able to remove CONVERT(DATETIME from query it will work is this possible somehow.

SELECT "ihRawData"."TimeStamp", "ihRawData"."Tagname", "ihRawData"."Value", "ihRawData"."IntervalMilliseconds"

FROM   "ihRawData" "ihRawData"

WHERE ("ihRawData"."TimeStamp">=CONVERT(DATETIME, '2015-09-25 13:47:19', 120) AND "ihRawData"."TimeStamp"<CONVERT(DATETIME, '2015-09-25 15:46:20', 120)) AND ("ihRawData"."Tagname" LIKE 'JGLS.PLC7_S2_P_KK2_1_P.F_CV' OR "ihRawData"."Tagname" LIKE 'JGLS.PLC7_S2_P_KK2_2_P.F_CV') AND "ihRawData"."IntervalMilliseconds"=60000

Tomas

[DellSC]

Try setting the parameter type for the dates to be a String instead of a DateTime.

-Dell

[Former Member]

Ok i will try this on Monday and contact you, now i have to go out of my office.

Thanks,

Tomas

[Former Member]

When i set parameters as string, query look the same

Select expert look like

{ihRawData.IntervalMilliseconds} = 10000 and

{ihRawData.Tagname} = {?Tag} and

{ihRawData.TimeStamp} in CDateTime ({?Start}) to  CDateTime ({?End}) and

{ihRawData.RowCount} = 0

and query

SELECT "ihRawData"."TimeStamp", "ihRawData"."Tagname", "ihRawData"."Value", "ihRawData"."IntervalMilliseconds", "ihRawData"."RowCount"

FROM   "ihRawData" "ihRawData"

WHERE  "ihRawData"."IntervalMilliseconds"=10000 AND "ihRawData"."Tagname"='JGLS.PLC7_S2_KK1_HO.F_CV' AND ("ihRawData"."TimeStamp">=CONVERT(DATETIME, '2015-09-25 00:00:00', 120) AND "ihRawData"."TimeStamp"<CONVERT(DATETIME, '2015-09-26 00:00:01', 120)) AND "ihRawData"."RowCount"=0

ORDER BY "ihRawData"."Tagname", "ihRawData"."TimeStamp"

[Former Member]

How can i avoid "CONVERT(DATETIME" expression in query statement?

[DellSC]

Instead of having the parameter type as DateTime or Date, make it a string, put an edit mask on it so that users don't put in any letters, include the date format in the parameter name - yyyymmdd (or whatever it is...)

-Dell

[Former Member]

Dell

I have tried to use booth type of {?Start} and {?End} parameter (string and datetime) and created query look always the same, it contains CONVERT(DATETIME" expression which isn't supported by my OLEdb driver to Proficy Historian.

Is it possible to remove this strikethrough strings from this expression: CONVERT(DATETIME, '2015-09-25 00:00:00', 120 and leave only  '2015-09-25 00:00:00'?

[DellSC]

Can you save the report with data as a .rpt file and upload it here so that I can look at it?  Change the .rpt extension to ".txt" and use the advanced editor to upload it.

-Dell

[Former Member]

Dell

Here are two files,

Rep_1 is created wit only ?Tag parameter and I'm able to receive  data only for past 2 hours, i think this is by default.

Second, Rep_2 file is created with additional two parameters ?Start and ?End and whether I use data type as string or datetime i receive blank report. I see that in this case an SQL expression CONVERT, DATETIME is always present, and in GE support told me that this expression isn't supported by OLEDB driver for Proficy Historian, for this reason we use the command... but with some limits. The main question is if CR is able to create query without this expression if I use ?Start and ?End parameters as string or datetime type?

Tomas

Rep_2.txt.zip

Rep_1.txt.zip

[DellSC]

Looking at these, you're not using a Command, you're just using a single table or view from the database.  Since the Timestamp field is showing in Crystal as a DateTime, Crystal will always try to convert the date parameters to a DateTime to match it.

You might be able to get around this by creating a real Command in Crystal.  A Command is nothing more than a SQL Select statement.  In the Database Expert, under your connection, see whether you have the option to "Add Command".  If you do, this would be the way to get around that limitation.

Another option, if you don't have the Add Command option, would be to create a SQL Expression that would convert the datetime field to a string using the database syntax.  You would then use that SQL Expression in the Select Expert instead of the actual field.

-Dell

[Former Member]

Dell

A real Command in Crystal was made at the beginning of my question to you.

Here i'm sending you two reports made with "Add command", and as you will see "Report1_comm" works ok, but with hardcoded tag-s. In the second report "Report2_comm"I use ?Tag parameter and if i choose "Allow multiple values" option i receive all Tag-s from database. Then You told me that "Allow multiple values" option wont work in my case, then I tried to use Select expert but then i have problem with Timestamp format. Can you explain me why i can't use "Allow multiple values" option, because i must tell my customer why they can't use CR anymore. If i understand You well, there is no way to avoid CONVERT,DATETIME syntax using Select Expert and in Add command i'm not able to use "Allow multiple values" option for ?Tag parameter?

Regards, Tomas

Report2_Comm.txt.zip

Report1_Comm.txt.zip

[Former Member]

Did you have time to look into report2 and report1?

[DellSC]

Unfortunately not.  I'm at a client, so I haven't had time to dig into this further.  I'll try to look at it tonight when I get back to my hotel.

-Dell

[DellSC]

Change this:  ("ihRawData_1"."Tagname" in '{?Tag}')

To this:  ("ihRawData_1"."Tagname" in {?Tag})

You can't use quotes around the parameter when it's a multi-select.  Crystal will take care of adding them if they're needed when the parameters are multi-select.

-Dell

[Former Member]

Dell

You told me to do this before, (see post from sep, 24) and doesn't work. I have try to replace "In" expression with "Like" without success. I came to the conclusion that "IN" expression returns all the data from the database, regardless of whether using parameter or a constant value. Is there any other expression instead of "IN"?

If using of "In" expression could be limited by my database, do you know what needs to be done that "In" expression become supported by my database, maybe this will be helpful for my support team!

[former_member11696]

Hi Tomas,

Add logging to CR and see what SQL is being sent.

Edit your System Environment variables and add these to them:

LOGGING_DIR = c:\logging

LOGGING_ENABLED_ASSERT = 1

LOGGING_ENABLED_RUNTIME = 100

Create the logging folder on your C drive.

Open the Designer and then try running your report again. Close the designer and then look for logs in the folder.

You can send them to the DB guys and see what they have to say about the query. Seems to me like there is some field type definition that either CR is getting the wrong type for the OLE DB driver or it's converting them possibly.

Be patient when logging is enabled, it does capture a log of info.

Don

[former_member11696]

Hi Tomas,

It appears the date field values are not being set correct, could be the time portion.

But are those CR Parameters or Command object parameters?

If they are CR Parameters try using the various date format options on them, set them to either Date or DateTime to see if that helps.

In Report Options it may help if you change the Null Values options. Also, if CR comes across a NULL value we stop processing so you need to handle NULL using "if not isnull"

And be aware sometime if CR cannot convert the date/time field accordingly it simply requests a *.* values and filters client side ( In CR Designer )

Check your formatting... likely the cause.

Don

[Former Member]

My data source doesn't support DateTime format for timestamp value. I must convert it to string and that work ok.

All parameters are Command.

I have set "Convert Database NULL Values to Default" and "Convert Other NULL Values to Default" to true but doesn't help.

Tomas