Gateway PC: Windows Server 08
Ignition 7.5.1 (b1122)
SQL Server 2008 R2
Client PC: Windows 7
I have a dataset in a report that contains some boolean columns. When this data is placed on a report and printed it only shows . If I use a case statement in the query like:
WHEN (L1Log_Winder.AutoCutOnSS=0) THEN 'Off'
WHEN (L1Log_Winder.AutoCutOnSS=1) THEN 'On' END) as AutoCutOnSS
then the text shows up fine.
I do not know how long this has been a problem. I looked in the dataset and the values do contain a “True” or “False” value.
Can you confirm whether you see this on your end?
Are the boolean values in your dataset actually of the data type Boolean and not String or Integer?
It is a ‘Bit’ data type which is the type used for Boolean in SQL Server.
I am unable to recreate this issue here. I set up a table in SQL Server with a column of type bit. Added 5 rows of data, alternating true and false. I then bound the dataset of a report viewer component to:
SELECT * from my_tableFinally I added a table to my report based on my dataset and dragged the “boolean_col” key into the first column. When I view the report true and false are displayed as the values.
I created the example as you describe and I get the same results you had. I am not using the table in my report though.
Open the designer
Click the ‘Key’ tab
Drag and drop a single field onto the report.
When the report is viewed, the ‘bit’ values show up as while other data types are OK.
I attached a window showing an example of what I am getting.
bit_test.proj (11.3 KB)
Ok, so what seems to be going on is that the key is part of a dataset and when you drag that key on as a label it displays because it thinks that there are multiple rows of data even if there is only one row. What you should do is add a boolean dynamic property to the report viewer and use your select query to bring back the one value that you want. Then you can drag this new property into your report and it should show up fine.
Yes, a dynamic property will work. Using a case statement will work as outlined in the original post. Another option would be to include a row index after the key is dropped to the form
Every data type works properly except the boolean. Ideally this should be fixed rather than having to go back and fix several reports that used to work.
So this report used to work without the CASE statement? If so, do you recall which version of Ignition it worked with?
Yes, but age prevents me from answering the version question.
We started with FactoryPMI version 3.? and upgraded along the way until the latest version of Ignition. A few years ago I had some complaints about what the ones and zeros meant so I replaced some of them with the case statement to give more meaningful text. I would guess it would be in the last two years it stopped working.
Well I’ll add a ticket to look into this issue but I honestly can’t think of anything that would have changed that would have made this go from working to not working. I don’t see how it ever could have worked. Not sure where this will end up on the priority list but it will be in our system as something to be investigated.