How to display flow fields in Reporting Services

GopinathGuthiGopinathGuthi Member Posts: 15
edited 2009-06-07 in NAV Three Tier
Hi All,

Can any body tell me how to display flow fields in reporting services in SQL Server 2005.

I want to generate Vendor Ledger Report , which should show only open transactions.

Amount (LCY) - is flow field. It is not showing in SQL Table.

Please suggest me what to do.
It is urgent requirement.

Thanks in Advance,
Gopinath

Comments

  • GopinathGuthiGopinathGuthi Member Posts: 15
    Please help me out.
  • AlishaAlisha Member Posts: 217
    Are you trying to create the report from Navision , using "Generate layout", or from scratch on reporting services?

    If it's from scratch, you can't use flowfields, you need to generate the report using the table that has the values, in this case Detailed Vendor ledger entry, and they use groups or Group by clauses to organise the info.

    If you are using "Generate layout", the flowfiels should appear as the contents in this case do not come from the tables, but from a sort of buffer generated from the report output.
  • GopinathGuthiGopinathGuthi Member Posts: 15
    Hi,

    Yes, i have used detailed vendor ledger entry table. But client wants to see only open transactions. In Detailed vendor ledger entry 'Open' field is not available.
    how can i achieve this.

    thanks.
  • AlishaAlisha Member Posts: 217
    You will need to find another way of filtering the entries you want. If you group by "Vendor ledger entry no.", the totals <> 0 should give you the open entries..
  • GopinathGuthiGopinathGuthi Member Posts: 15
    I grouped by Vendor Ledger and here is my code
    but still i am getting the result.

    Please look at this correct me if iam wrong.

    SELECT [company1$Detailed Vendor Ledg_ Entry].[Vendor No_],
    [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date],
    [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) AS amt,
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    FROM [company1$Detailed Vendor Ledg_ Entry]
    INNER JOIN
    [company1$Vendor] ON [company1$Detailed Vendor Ledg_ Entry].[Vendor No_] = [company1$Vendor].No_

    GROUP BY [company1$Detailed Vendor Ledg_ Entry].[Vendor No_], [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    HAVING (SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) <> '0')

    ORDER BY [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_]
  • GopinathGuthiGopinathGuthi Member Posts: 15
    I grouped by Vendor Ledger and here is my code
    but still i am not getting the result.

    Please look at this correct me if iam wrong.

    SELECT [company1$Detailed Vendor Ledg_ Entry].[Vendor No_],
    [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date],
    [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) AS amt,
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    FROM [company1$Detailed Vendor Ledg_ Entry]
    INNER JOIN
    [company1$Vendor] ON [company1$Detailed Vendor Ledg_ Entry].[Vendor No_] = [company1$Vendor].No_

    GROUP BY [company1$Detailed Vendor Ledg_ Entry].[Vendor No_], [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    HAVING (SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) <> '0')

    ORDER BY [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_]
    I grouped by Vendor Ledger and here is my code
    but still i am getting the result.

    Please look at this correct me if iam wrong.

    SELECT [company1$Detailed Vendor Ledg_ Entry].[Vendor No_],
    [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date],
    [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) AS amt,
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    FROM [company1$Detailed Vendor Ledg_ Entry]
    INNER JOIN
    [company1$Vendor] ON [company1$Detailed Vendor Ledg_ Entry].[Vendor No_] = [company1$Vendor].No_

    GROUP BY [company1$Detailed Vendor Ledg_ Entry].[Vendor No_], [company1$Vendor].Name,
    [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_],
    [company1$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]

    HAVING (SUM([company1$Detailed Vendor Ledg_ Entry].[Amount (LCY)]) <> '0')

    ORDER BY [company1$Detailed Vendor Ledg_ Entry].[Posting Date], [company1$Detailed Vendor Ledg_ Entry].[Document No_]


    thanks.
  • AlishaAlisha Member Posts: 217
    i don't think you should use "Posting date" on the grouping, the detailed vendor ledger entries will have different posting dates, and this will create a break where you don't want to...
  • GopinathGuthiGopinathGuthi Member Posts: 15
    But if i take out Posting Date from group, it throws an error says 'At least one aggreagate function should be used '
  • AlishaAlisha Member Posts: 217
    you need to take it out both from the group and the select, the same fields must appear in both places.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Hi All,

    Can any body tell me how to display flow fields in reporting services in SQL Server 2005.

    I want to generate Vendor Ledger Report , which should show only open transactions.

    Amount (LCY) - is flow field. It is not showing in SQL Table.

    Please suggest me what to do.
    It is urgent requirement.

    Thanks in Advance,
    Gopinath

    Since this is Urgent, you need to log a support issue with Microsoft where you can pay for priority service. The forum is made up of volunteers, and we don't get paid, so there is no reason to drop everything.

    ](*,)
    David Singleton
Sign In or Register to comment.