Changing Datasource within Package in 10.2

[ackrite55]

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


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

[MFGF]

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.

[ackrite55]

Thank you. InvoiceNo does exist.

[Lynn]

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.

[ackrite55]

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

[MFGF]

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.

[ackrite55]

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.

I can load the query, but I can execute any statements on the query for the report.

[MFGF]

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.

[Lynn]

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.

[ackrite55]

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.