How to display flow fields in Reporting Services

GopinathGuthi

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

GopinathGuthi

Please help me out.

Alisha

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.

GopinathGuthi

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.

Alisha

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..

GopinathGuthi

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_]

GopinathGuthi

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.

Alisha

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...

GopinathGuthi

But if i take out Posting Date from group, it throws an error says 'At least one aggreagate function should be used '

Alisha

you need to take it out both from the group and the select, the same fields must appear in both places.

David_Singleton

GopinathGuthi wrote:

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.

](*,)