SQL view generated dinamically

matias_jaure
matias_jaure Member Posts: 157
edited 2008-02-06 in NAV/Navision Classic Client
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]

Comments

  • garak
    garak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • matias_jaure
    matias_jaure Member Posts: 157
    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
    ara3n Member Posts: 9,258
    You can also create a SQL report instead of Navision report.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • matias_jaure
    matias_jaure Member Posts: 157
    ara3n wrote:
    You can also create a SQL report instead of Navision report.

    And call it from Navision?... sounds good, but how?
  • ara3n
    ara3n Member Posts: 9,258
    What version of SQL are you on?

    Hopefully 2005.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • matias_jaure
    matias_jaure Member Posts: 157
    ara3n wrote:
    What version of SQL are you on?

    Hopefully 2005.
    2000 :-s
  • garak
    garak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • bbrown
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • garak
    garak Member Posts: 3,263
    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.
    Do you make it right, it works too!

Categories