Computed Column Won't Show in Available Fields

[Bob Cergol]

Since I'm new to this forum, let me just say that I am working hard to put together some "must-have" mgt. information dashboards in order to sell this product to my employers -- so I hope you won't think I am asking too many questions as I work through the learning curve.

I created a dynamic view that contains a calculated column. I can click the execute button any my calculated column QPrice displays in the result set.

However in report design that column does not show as an available field. According to the documentation it should. What might I be doing wrong? I can probably work around this by defining the calculated field in the report definition itself, but I want this field to be available across multiple reports.

Code: Select all

SELECT
D.[IDNO], D.[JCREPS_IDNO], F.[LASTNAME], D.[CUST_IDNO], E.[NAME], D.[PLANNER], D.[PROD_CODE], D.[ORDER_CODE], D.[ENTDATE], D.[PROMISED],
D.[SHIPPED], D.[INVOICED] BillDate, H.[INV_AMT] BillAmt, D.[PRICE], D.[QUOTED_PRICE],
CASE WHEN d.[Price] > d.[Quoted_Price] THEN d.[Price]
    ELSE d.[Quoted_Price]
END QPrice,
D.[QUANTITY], D.[TITLE], D.[SPECS_1], D.[SPECS_2], D.[LBR_ACT], D.[MATL_ACT], D.[PAPER_ACT], D.[PURCH_ACT], D.[SELL_LBR], D.[SELL_MATL], D.[SELL_PAPER], D.[SELL_PURCH], H.[JOB_COST], H.[BILL_COST], H.[LBR_CST]

FROM dbo.JCJOBS01 D

LEFT JOIN dbo.JCREPS01 F ON D.JCREPS_IDNO = F.IDNO
LEFT JOIN dbo.JCCUST01 E ON D.CUST_IDNO = E.IDNO
LEFT JOIN dbo.JCJHST01 H ON D.IDNO = H.JOB_NO
Where D.[ENTDATE] > '01/01/2007'


Thanks,
Bob

[Carsten Sørensen]

Hi Bob,

First of all - you are very welcome to post your questions to the forum! that way we can share information with as many as possible! We appreciate your interest in Dynamic AI!

Your method to include a calculated column across all reports by including it in a Dynamic AI SQL View - is 100% valid! I.e. I don't see anything wrong at all with your posted example.

I just recreated the tables of your join and tested your view.

Apart from my SQL Server failed to recognize d.[Price] and d.[Quoted_Price] - I had to enter it in UPPERCASE:

CASE WHEN D.[PRICE] > D.[QUOTED_PRICE] THEN D.[PRICE]
ELSE D.[QUOTED_PRICE]
END QPrice,

I can browse and see the columns:

available.jpg (42.86 KiB) Viewed 5577 times

Please be aware that "calculated columns" being part of your underlying data-source will show as a normal column in Available fields.

If you added the calculated column after initially building the report/Form then you'll have to manually add it to the Form.

Another reason that you might not see the column in Available fields could be if you are editing the view and save it in a second tab/browser window after having created the report initially. To save "roundtrips" to the underlying database Dynamic AI refresh the available fields-listings initially when you enter design and then use the cached results for the rest of that session on that particular report. You can however force Dynamic AI to refresh the available fields of an underlying report by Saving and opening the report again in Design (however this is very seldom necessary).

I hope above can help you - otherwise please let us know!

Best regards,
Carsten

[admin]

Hi Bob,

I just wanted to add that the Server Edition of Dynamic AI, provides a Dictionary where you can define extra columns globally (as well as do global maintenance of column captions etc.)

Another thing I think you will find pleasing is the Dynamic Joins (found here:)

Joins defined here are used only if your report includes columns from the tables involved, meaning that if your join works on orders and order_details, and your report only lists information from the orders table on the first level, then the order_details table is never queried. If the report then includes order_detail information on the second or third level, that table is dynamically included when needed.
Often, this makes it possible to create one Dynamic Join, and use that for most reports, without seeing a performance degradation. When combined with the Dictionary from the Server Edition, the Dynamic Join becomes even stronger, as it enables the global extra columns as well as transparent user filtering, language versions and a couple of other tricks.

Please keep the questions coming, and if you're ever interested in a Server edition trial, we'd be happy to get you started.

Best regards
Bo Andersen

[Bob Cergol]

Bo,

Yes, I had added the computed field in the view after I had designed the report, so that must explain it not showing up right away.

I definitely would want to define these sorts of things, captions, and especially the joins, in DAI's data dictionary! I also want to learn how user-profiles can be utilized to create a controlled experience for separate users. I would like to follow up with you on the server edition trial and will email you off line for details.

I placed a post on DAW's VDF forum yesterday about my enthusiasm for your product. You guys are geniuses! I have some experience with SAGE CRM and I thought it was an amazing product. But it pales in comparison to DAI when it comes to the tools for customizing database interaction. (Be careful -- Sage may want to acquire you!)

Bob