There is a NEW query in an existing package that has been created to use a different datasource(SQL SERVER) through double clicking [SQL] IN QUERY EXPLOYER without access to Admin contents or Framewkmgr. I have added the SQL statement select * from testdb and changed the DataSource to Mart1
(https://p06bxg-dm2305.files.1drv.com/y4mgUtz_mUBQeW_ljaus_nibsXU1YgEUDWM39jXJoRUe8PW2g6VM6keWGwZ5BGNGYxl-ClGId7ucVT84IthErlxOyuU7qHNyKYRcQvtoBMuRkNF4DHqjTHZRcvuV8h5SVSpKwYIBI0fpswXRZE5jg45hd-4m-XEcF66PZsj-QDGXdQ1iwDe2qBl5ynso3CnDbV9PNudg1blWBt02wBATBoO4w?width=542&height=273&cropmode=none)
I added [Invoice_COUNT]
Count([InvoiceNo]) as INVOICE_COUNT
I attempted to add the following DataItems but encountered an error
AVG_PER_DAY:
[INVOICE_COUNT] / (_days_between (_add_days (maximum ([Date]),1), minimum ([Date])))
the error returned:
The InvoiceNo is unable to be found
Quote from: ackrite55 on 06 Feb 2018 03:57:06 PM
There is a query in an existing package that needs to be switched to a different datasource without access to Admin contents or Framewkmgr. Within Qry Explorer, I have tried oldQ <---- newQ where like columns become the Data Items. But I try :
AVG_PER_DAY:
[INVOICE_COUNT] / (_days_between (_add_days (maximum ([Date]),1), minimum ([Date])))
[Invoice_COUNT]
Count([InvoiceNo])
the error returned:
The InvoiceNo is unable to be found
Hi,
"The InvoiceNo is unable to be found" doesn't look like a real error message. Can you give us the error you see? Does your query have an item in it called InvoiceNo?
MF.
Thank you. InvoiceNo does exist.
(https://p06yxg-dm2305.files.1drv.com/y4m6xWFmOXJPv4NOVk17EMyx3bhyPWes3bjLUc8Qgm0tqa8Crv-Ibt4ibTIoH3HQWGOrAOURJ2hzAzy7D2A6uLztJuWeNTXoR__1SUz1lvlrXmi3YhU34c6rBz5qdRN60feHGsZ-CkHtv3-Q7XMbDVDpOQV9tgQCAZz3W25K4eUDlzlPRnd77Uq-2nOpIJmLDkfRHDiXkj5UL-Dbgs_CaHfWg?width=521&height=294&cropmode=none)
Quote from: ackrite55 on 06 Feb 2018 03:57:06 PM
There is a query in an existing package that needs to be switched to a different datasource without access to Admin contents or Framewkmgr.
I am not aware of any way to change a data source within a report.
Quote from: Lynn on 07 Feb 2018 09:57:23 AM
I am not aware of any way to change a data source within a report.
I have queries within the package that uses the new datasource, but creating the DataItem, AvgPerDay in the new query causes an error so I'm attempting to use the original query <-- new query
(https://p06zxg-dm2305.files.1drv.com/y4mdNL1Sg4PbMmKMZL7pdvsP0WU6jYUjYb7DyGXpoEASooi8yLWuj2qXH5kSlkvitXzMnetf1K2BnieZov9h4w6ZPyRCkH7mCk8ysd6xjrCuEkKt3Jja8EWGJPMOFH3pg8ni_N0WRYbU9_3qWd5s-S6TKquC4pAaeWko0DcJZ8rFdJtdjIWO3REed8b6ZPBi8uzucvvXregG2VZw9KtrsQYMA?width=471&height=169&cropmode=none)
I'm struggling to understand what you are doing in your report. Can you emulate it using one of the sample packages and post up the report spec so we can see what you are doing?
MF.
Quote from: MFGF on 07 Feb 2018 11:18:11 AM
I'm struggling to understand what you are doing in your report. Can you emulate it using one of the sample packages and post up the report spec so we can see what you are doing?
MF.
(https://p06bxg-dm2305.files.1drv.com/y4mgUtz_mUBQeW_ljaus_nibsXU1YgEUDWM39jXJoRUe8PW2g6VM6keWGwZ5BGNGYxl-ClGId7ucVT84IthErlxOyuU7qHNyKYRcQvtoBMuRkNF4DHqjTHZRcvuV8h5SVSpKwYIBI0fpswXRZE5jg45hd-4m-XEcF66PZsj-QDGXdQ1iwDe2qBl5ynso3CnDbV9PNudg1blWBt02wBATBoO4w?width=542&height=273&cropmode=none)
I can load the query, but I can execute any statements on the query for the report.
Quote from: ackrite55 on 08 Feb 2018 01:48:31 PM
(https://p06bxg-dm2305.files.1drv.com/y4mgUtz_mUBQeW_ljaus_nibsXU1YgEUDWM39jXJoRUe8PW2g6VM6keWGwZ5BGNGYxl-ClGId7ucVT84IthErlxOyuU7qHNyKYRcQvtoBMuRkNF4DHqjTHZRcvuV8h5SVSpKwYIBI0fpswXRZE5jg45hd-4m-XEcF66PZsj-QDGXdQ1iwDe2qBl5ynso3CnDbV9PNudg1blWBt02wBATBoO4w?width=542&height=273&cropmode=none)
I can load the query, but I can execute any statements on the query for the report.
So you have a SQL object feeding into a query? What query items exist in the query if you drill into it in Query Explorer?
MF.
I'm not certain, but I seem to recall you cannot use "select * from ..." syntax in a SQL query object. I think you need to explicitly list each column. Try replacing the asterisk with all the columns referenced.
Of course the standard advice to consider is that you'll be better off by properly modelling in Framework Manager rather than using SQL queries in reports.
Quote from: Lynn on 09 Feb 2018 05:47:25 AM
I'm not certain, but I seem to recall you cannot use "select * from ..." syntax in a SQL query object. I think you need to explicitly list each column. Try replacing the asterisk with all the columns referenced.
Of course the standard advice to consider is that you'll be better off by properly modelling in Framework Manager rather than using SQL queries in reports.
It's being seen as an IT function. Therefore, it's not available for now.