SQL view generated dinamically

matias_jaure

Hi people!!.. I need to make a report, I created it using Navision and it was unusable.. very very slow.. because I have millions of records...
I have the Report using a SQL query, I was wondering if is any way to create a view with this query dinamically, passing 2 parameters using Navision. And then use the view to create the report. Below is the SQL query and the parameters I want to pass in bold.
Thank u for your time!!




select year(s.[Posting Date]) as [PYear],month(s.[Posting Date]) as [PMonth],d.[Recognition Year],d.[Recognition Month],sum([Amount To Be Recognized])as Amount
into DeferralReport_Partial_invoices
from
[US Trend Micro$Deferral Detail] d,
[US Trend Micro$Sales Invoice Header] s
where
d.[Document No_]=s.[No_]
and s.[Posting Date] between PARAMETER1 and PARAMETER2
group by
year(s.[Posting Date]),month(s.[Posting Date]),d.[Recognition Year],d.[Recognition Month]

garak

if you often need this table, why you not create an view on SQL / Navision.
Than you can filter in navision on the viewfield.

if you not need this table and eed only he reult set by for example 10 recs, you can also create an scalar function with parameters and call this function via ADO from C/AL

Regards

matias_jaure

garak wrote:

if you often need this table, why you not create an view on SQL / Navision.
Than you can filter in navision on the viewfield.

if you not need this table and eed only he reult set by for example 10 recs, you can also create an scalar function with parameters and call this function via ADO from C/AL

Regards

The thing is that I need to create an excel report with the table, not just see it...

Do you have any example of the scalar function you told?..

ara3n

You can also create a SQL report instead of Navision report.

matias_jaure

ara3n wrote:

You can also create a SQL report instead of Navision report.

And call it from Navision?... sounds good, but how?

ara3n

What version of SQL are you on?

Hopefully 2005.

matias_jaure

ara3n wrote:

What version of SQL are you on?

Hopefully 2005.

2000 :-s

garak

mhm 2000 :-( there is no reporting service.
But, you say, you will create an excel document. Why you not make an ODBC Connection from excel to the SQL Server?

Here an very simple example for an SQL function that you can call from Navision with using ADO (search the forum)

This SQL Function returns the document No from Cust_ Ledger Entry. It req. an Parameter -> Customer No. and an Parameter 2 -> Posting date

SQL Function

Create Function [YourCompanyName_MyFunction]
  (@ParaCustNo varchar(20), @ParaDate datetime) 
  returns decimal(38,20) as Begin Declare @DocumentNo varchar(20)

Select Top 1 @DocumentNo = [Document No_] from [YourCompanyName$Cust_ Ledger Entry] 
where [Customer No_] = @ParaCustNo and [Posting Date] <= @ParaDate
order by [Document No_] desc
if @DocumentNo is NULL select @DocumentNo = 'nothing' Return @DocumentNo End 

Now you must call this from Navision per ADO

But other question. What is the problem with an view :?:
(Create View ......)

Regards

bbrown

garak wrote:

mhm 2000 :-( there is no reporting service.

Actually there was. It just didn't ship in the SQL box for "free". It was part of the development products. Reporting Services is not a new product. It is a repackaging of an existing feature along with a sizable feature upgrade.

You could setup a 2005 Reporting Services environment and use it to report from the SQL 2000 database. The data sources do not need to be SQL 2005.

garak

yes for 2000 it is an part of the development products and not for free.

That's right, its an better way to install the Reporting Services environment and use this with 2000 database.