calculated column for a report with a list of items

0

Hi,

is the provided SQL a part of an existing statement which is working?
At the moment this doesn't make any sense.

Regardless of my leg of understanding you could:
- Activate the diagnostic mode, load the report and check the executed SQL statement. This way, you can copy the statement and verify what's not working. You will need to add some line breaks to make it readable, but it will be worth it.
- Create data table mimicking the report and create the calculated column there. Ones the column is working you can copy it over to the report. You may need to amend /add the table alias which are used in report. You can get them from the diagnostic log.

That being said:
If you have multiple environments dev/test/prod in place, you will need to use the ids of the production environment. Since we can't use variables in the report you need you would need to correct it in the production environment after each transport of the application.
WFD_DTYPEID = '212'
and DE.DET_WFCONID = '5788'
and DE.DET_IsDeleted = 0
and WFD_STPID <> '935'

Depending on what you want to achieve it may be an option, that the completed subworkflow causes an update of the parent workflow which than stores the value you want to display in the item list.

Best regards,
Daniel

Thank you very much for your response.
Yes, I already have a calculated column in the list of items in the form.
This column is of the type - calculated text value. The SQL works.
However, I am unable to display this column on the report

Hi!
I'm not into the topic but one think paid my attention. Of course, after every import you should change ID's on target enviroment but you can use process const. or global const.
In different way, maybe better will be use column from DB based on GUID ex. DTYPE_GUID?
Guid's are the same in every environment in the process so maybe it will help with changing ID's everytime.

@Katarzyna

Maybe I don't understand your case and I'm completely wrong.
What I understand is the following:
- You have a report which shows fields from the workflow and one item list. So the field values are repeated for each row in the item list for a given report.
- You want to create the sum of fields from another item list in another workflow instance
- This workflow instance is identified by the column Det_Att23 in the report and the WFD_AttChoose55 field in the workflow instance.

If this is true, I have no idea, how you could make this work.
This is a part from the executed SQL statement
FROM V_WFElements as wfelems /*TASKDETAILSJOIN*/ .... LEFT JOIN WFElementDetails on

There's no alias for the WFElementDetails which in turn will means that the Det_Att23 will be ambigues.


@Cezary
Do we have the option to select constants in reports? I did create an SQL function to return the value of a constant by it's Guid in the past to go around this problem.
I've also added the user voice to extend the V_WfElements with the GUIDs because of the issue you mentioned. Using Guids in calculated columns means additional work on the SQL server.

https://community.webcon.com/forum/thread/4134?messageid=4134

Best regards,
Daniel

Hi everyone,
@Daniel
> Do we have the option to select constants in reports? I did create an SQL function to return the value of a constant by it's Guid in the past to go around this problem.

I do not think that there is such option, and wanted to appreciate that SQL function - it's a great idea!

@Maks

it was one of my first posts. :)

https://daniels-notes.de/posts/2021/deploying-database-scripts

As it turned out, I didn’t update the workflow with each version.
At least I did provide the export file of the dictionary in the linked repository. It should contain the function. Also I don’t assume you need it. But then the idea of the post would be something you could need. :)

Although I really like how it can be managed by WEBCON as in your example, I'm using a dedicated tools for managing custom database objects:
- dbt for creating views for BI
- flyway for creating custom schemas and functions

My end goal at this moment is CI/CD pipeline which will run dbt, flyway, and webcon import through API ;)

That’s a great option and I would opt for it, if I would have the option.

In our case, as a partner with multiple different environments, that’s not an option. The only sure thing is, that WEBCON is available. :)