Table function

If you want to obtain data from a Table-valued Function in your database, you can create a view that queries the function, and set a data type / data store up on the view, same way as you would for a normal table. 

I do this in one scenario where a report is generated by a function that creates/returns a temporary table, ended up being better for performance this way in my scenario, where lots of data is aggregated for a weekly summary.

CREATE view [dbo].[VIEW_FROM_FUNCTION]
as

select 
[Col1],
[Col2]
/*etc*/
from dbo.fn_YOUR_TABLE_VALUED_FUNCTION()

GO

I am getting the error while creating new Data type

Did you try to follow the advised steps in the error message?

Please create primary key for a column for example, create a new column with name ID and makeit primary key.

Please create primary key for a column for example, create a new column with name ID and makeit primary key.

okay thanks.

Additionally, you do not have to create an entire new column to add a unique column into a view.  For example in MSSQL, you can add a unique column on the fly with the ROW_NUMBER() function, for example:

SELECT TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY id DESC) AS Row, 
step,requestNumber,requestStartTime
from tblCOE_SAMPLE_TABLE

okay thanks for your reply chris, I am facing difficulties while doing the developement that I will be sharing my code here

a!gridField(
  labelPosition: "COLLAPSED",
  spacing: "DENSE",
  
  data: ri!ReportCountsViewData_cdt,
  columns: {
    a!gridColumn(
      label: "Summary",
      value: fv!row.reportName_txt
    ),
    a!gridColumn(
      align: "CENTER",
      label: "Due in 60 days",
    
      value: a!richTextDisplayField(
        value: a!richTextItem(
          text: fv!row.due60Days_int,
          linkStyle: "STANDALONE",
          link: a!dynamicLink(
            label: fv!row.due60Days_int,
            value: fv!row.due60Days_int,
            saveInto: {
              a!save(
                ri!selectedItemType_txt,
                "Due in 60 days"
              ),
              a!save(
                ri!selectedItemReportType_txt,
                fv!row.reportName_txt
              )
            },
            showWhen: and(
              not(
                isnull(
                  tointeger(
                    fv!row.due60Days_int
                  )
                )
              ),
              tointeger(
                fv!row.due60Days_int
              ) <> 0
            )
          )
        )
      )
    ),
    a!gridColumn(
      align: "CENTER",
      label: "Due in 30 days",
     
      value: a!richTextDisplayField(
        value: a!richTextItem(
          text: fv!row.due30Days_int,
          linkStyle: "STANDALONE",
          link: a!dynamicLink(
            label: fv!row.due30Days_int,
            value: fv!row.due30Days_int,
            saveInto: {
              a!save(
                ri!selectedItemType_txt,
                "Due in 30 days"
              ),
              a!save(
                ri!selectedItemReportType_txt,
                fv!row.reportName_txt
              )
            },
            showWhen: and(
              not(
                isnull(
                  tointeger(
                    fv!row.due30Days_int
                  )
                )
              ),
              tointeger(
                fv!row.due30Days_int
              ) <> 0
            )
          )
        )
      )
    ),
    a!gridColumn(
      align: "CENTER",
      label: "Past Due in 1-30 days",
      
      value: a!richTextDisplayField(
        value: a!richTextItem(
          text: fv!row.pastdue30Days_int,
          linkStyle: "STANDALONE",
          link: a!dynamicLink(
            label: fv!row.pastdue30Days_int,
            value: fv!row.pastdue30Days_int,
            saveInto: {
              a!save(
                ri!selectedItemType_txt,
                "Past Due in 1-30 days"
              ),
              a!save(
                ri!selectedItemReportType_txt,
                fv!row.reportName_txt
              )
            },
            showWhen: and(
              not(
                isnull(
                  tointeger(
                    fv!row.pastdue30Days_int
                  )
                )
              ),
              tointeger(
                fv!row.pastdue30Days_int
              ) <> 0
            )
          )
        )
      )
    ),
    a!gridColumn(
      align: "CENTER",
      label: "Past Due in 30+ days",
     
      value: a!richTextDisplayField(
        value: a!richTextItem(
          text: fv!row.pastdueOver30Days_int,
          linkStyle: "STANDALONE",
          link: a!dynamicLink(
            label: fv!row.pastdueOver30Days_int,
            value: fv!row.pastdueOver30Days_int,
            saveInto: {
              a!save(
                ri!selectedItemType_txt,
                "Past Due in 30+ days"
              ),
              a!save(
                ri!selectedItemReportType_txt,
                fv!row.reportName_txt
              )
            },
            showWhen: and(
              not(
                isnull(
                  tointeger(
                    fv!row.pastdueOver30Days_int
                  )
                )
              ),
              tointeger(
                fv!row.pastdueOver30Days_int
              ) <> 0
            )
          )
        )
      )
    )
  }
)

I need to do same summary report at cust level(view is created at unique cust level and taking care of the counts that is mentioned in the code).How will I modify the code at unique cust level and on click of counts the new grid (grid is different based on some conditions)has to be display. can anyone help on this?

I think you can use your condition based on your selectedItemType_txt variable. Please use apply when functionality into query filters of querylogicalexpression.

a!localVariables(
  local!data: a!queryEntity(
    entity: cons!CDU_TRACK_ITEMS_DSE,
    query: a!query(
      pagingInfo: a!pagingInfo(1, - 1),
      logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "id",
            operator: "=",
            value: 1,
            applyWhen: ri!selectedItemType_txt = "1"
          ),
          a!queryFilter(
            field: "id",
            operator: "=",
            value: 2,
            applyWhen: ri!selectedItemType_txt = "2"
          ),
          a!queryFilter(
            field: "id",
            operator: "=",
            value: 3,
            applyWhen: ri!selectedItemType_txt = "3"
          ),
          a!queryFilter(
            field: "id",
            operator: "=",
            value: 4,
            applyWhen: ri!selectedItemType_txt = "4"
          )
        }
      )
    )
  ).data,
  local!data
)

In the above code, i fetch the data based on the variable. 

Hi yes deepakg1538 I have used the same query rule and but my requirement is to get the same grid at cust level in that how would I modify the above shared code ? and on click of dynamic link counts as i said need to display the other grid which data is coming from the other view.