SSRS Helper 1.0 (CTP 1)

Administrator · **Posted:** Mon May 11, 2009 1:23 pm

SSRS Helper 1.0 (CTP 1)
When querying data from NAV/SQL tables - e.g. with "SQL Server Reporting Services (SSRS)" - there are basically two somewhat annoying issues:
1. The content of Option Fields is only shown by Value, not by Text
2. FlowFields are not available

This little tool converts the OptionString and CalcFormula of a NAV table-field into a [T]SQL Subquery (!) which could be used with dynamic SQL in SSRS, SSAS or any other Non-NAV application to select the data!

The current release is NOT a final version, but IMHO a good start; and you all are invited to share your ideas to improve this little thing further on. Meet you online ;c)
Please refer to the forum discussion about further details.

http://www.mibuso.com/dlinfo.asp?FileID=1101

Discuss this download here.

stryk · **Posted:** Mon May 11, 2009 3:54 pm

Hi all,

as announced, here some further info:

First of all, I have to admit, that this little utility was just created as some sort of "experiment", thus the code has not been cleaned up (several things might be programmed better) and there are some limitations. I just stopped development as this thingy actually fulfilled MY requirements, but I decided to publish it here to give YOU also a basis to start with $\:D/$

So I would like to invite YOU to further improve this "SSRS Helper", please share your comments, advices, fixes, enhancements etc.. As we should not "flood" MIBUSO by posting dozens of different variants of this tool, I kindly ask you to publish your proposals HERE or to send me your code via e-mail: [url]contact@stryk.info[/url] I'll update the "SSRS Helper" then and publish the new versions here (of course, with appropriate acknowledgement of all who participated!)

--------------------------------------------------

OK, so here - briefly - what "SSRS Helper" does:

1. Option to SQL

The Problem: If you query an option field vie SQL you only retrieve the Option Value.
For example ...

Code: Select all

SELECT [Document Type], [Document No_], [Line No_] FROM [dbo].[Cronus$Sales Line]

... will show a result like:

Code: Select all

[Document Type], [Document No_], [Line No_]
1              , 12345         , 10000

Here, "SSRS Helper" is generating the CASE subquery which could be used to fetch the Option Text e.g. of [Document Type]:
Option String:

Code: Select all

Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order

Code: Select all

CASE [Document Type] WHEN 0 THEN 'Quote' WHEN 1 THEN 'Order' WHEN 2 THEN 'Invoice' WHEN 3 THEN 'Credit Memo' WHEN 4 THEN 'Blanket Order' WHEN 5 THEN 'Return Order' END AS [Document Type]

Hence, when including this CASE in the SELECT statement ...

Code: Select all

]SELECT
CASE [Document Type] 
  WHEN 0 THEN 'Quote' 
  WHEN 1 THEN 'Order' 
  WHEN 2 THEN 'Invoice' 
  WHEN 3 THEN 'Credit Memo' 
  WHEN 4 THEN 'Blanket Order' 
  WHEN 5 THEN 'Return Order' 
END AS [Document Type],
[Document No_], [Line No_] FROM [dbo].[Cronus$Sales Line]

... will give this:

Code: Select all

[Document Type], [Document No_], [Line No_]
Order          , 12345         , 10000

Again, "SSRS Helper" creates this CASE part, which could be used in those queries, either by "copy & paste" or via "dynamic SQL" (the download package contains some examples).

--------------------------------------------------

2. FlowField to SQL

"FlowFields" are not really fields within the table, at least not on the SQL server site - "FlowFields" simply do not exist there, hence they cannot be selected.
Actually a FF displays data from a sub-sequent table, thus a subquery is required to get this info.
The "SSRS Helper" reads the CalcFormula of FlowFields from TXT file into table and parses it to generate this subquery.
For example, FlowField [Inventory] of table [Item]:
The CalcFormula ...

Code: Select all

Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Location Code=FIELD(Location Filter),Drop Shipment=FIELD(Drop Shipment Filter),Variant Code=FIELD(Variant Filter),Lot No.=FIELD(Lot No. Filter),Serial No.=FIELD(Serial No. Filter)));

... is "translated" into this SQL query:

Code: Select all

(SELECT ISNULL(SUM([Quantity]), 0) FROM [Cronus$Item Ledger Entry] WHERE ([Item No_] = [CRONUS 403$Item].[No_]) AND ([Global Dimension 1 Code] IS NOT NULL) AND ([Global Dimension 2 Code] IS NOT NULL) AND ([Location Code] IS NOT NULL) AND ([Drop Shipment] IS NOT NULL) AND ([Variant Code] IS NOT NULL) AND ([Lot No_] IS NOT NULL) AND ([Serial No_] IS NOT NULL)) AS [Inventory]

This could be included into the "main" query.

--------------------------------------------------

Limitations & Known Issues:

- The NAV OptionStrings, CalcFormulas and the generated TSQL frequently exceeds the max. field length of 250 characters in NAV. I decided NOT to save everything in binary fields, but to split up the strings in up to 4 table fields (like TSQL1, TSQL2, TSQL3, TSQL4). This gives a max. length of 1000 characters, but even that is exceeded in NAV 2009. If this happens, the query contains a '<...>' at the end.
- Some CalcFormulas are really tricky to parse. I didn't care, I just left them incomplete - feel free to enhance.
- FlowFilters are included as "IS NOT NULL" filters; these filters have to be set manually.

So, I hope it helps you with SQL reporting etc.! Enjoy!

Looking forward to getting your most appreciated feedback.

Best regards,
Jörg

MarHan · **Joined:** Wed May 20, 2009 1:22 pm **Posts:** 0

Hello Jörg,

first of all: You did a great job! Thanks a lot!!!

I tried your code within a clean Cronus 5 and it worked for me without any errors.

But when I ran it within our solution, I recognized that NAV does not export the text-files
properly as you expected in your code.

After having found a line containing "CalcFormula="
you loop over the next ImportLines
as long as they don't contain a semikolon

Code: Select all

WHILE STRPOS(ImportLine, ';') = 0 DO BEGIN

In my exported text-objects I have found the following (several times!):

Code: Select all

CalcFormula=Lookup("Bank Document".Orderer WHERE (Entry No.=FIELD(Import Journal Entry No.))) }

or

Code: Select all

CalcFormula=Lookup(Customer.Name WHERE (No.=FIELD(Bal. Account No.))) }

(not only at Lookup, also at Sums, ...)
NAV "forgets" writing a semikolon at the end, so your code does not recognize the end of the calcformula properly.

Btw: Client is 4.0 SP 3.

Again thanks a lot!

//Edit+
Running a search and replace on my exported text-file which replaces ))) } with ))); } reduces the errors!
//Edit-

~Markus

stryk · **Posted:** Wed May 20, 2009 8:16 pm

Hi Markus,

thank you very much for your reply! OK, this error did not occur in my tests ... Which NAV version (DB and C/SIDE) were you using? Or could you post here a complete table object (as TXT) which is affected by this? So I could try to fix this ...

Again, thanks a bunch for your support!
Best regards,
Jörg

MarHan · **Joined:** Wed May 20, 2009 1:22 pm **Posts:** 0

Hi Jörg,

Version is 4.0 SP 3

I'll try to post more code asap, no time at the moment, sorry.

As already told: Running a search-and-replace from "))) }" to ")));}" fixes 90% of the errors. The remaining errors
have to be fixed manually, but that's ok.

Again: Not your code is faulty! The client exports some objects wrong!

Have a nice week!

Markus

stryk · **Posted:** Sun Nov 15, 2009 12:32 pm

Hi all,

there is a small :bug:

in Report 80000 "SSRS Helper: Import FlowFields"; Function "ReadFile()".
Please replace the Line

FlowFieldToTSQL.INSERT;

with

IF FlowFieldToTSQL.INSERT THEN;

Here the complete function:

Code: Select all

ReadFile()
Window.OPEN('Importing FlowFields ...            \' +
            'Line  #1############################\' +
            'Table #2######  #3##################\' +
            'Field #4######  #5##################\' +
            '@6@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');

c :=  ImportFile.LEN;
WHILE ImportFile.POS < c DO BEGIN
  ImportLine := '';
  ImportFile.READ(ImportLine);
  Window.UPDATE(1, ImportLine);
  Window.UPDATE(6, ROUND(10000 / c * ImportFile.POS, 1));

  IF COPYSTR(ImportLine, 1, 12) = 'OBJECT Table' THEN BEGIN
    NewTable := TRUE;
    EVALUATE(TableNo, DELSTR(COPYSTR(ImportLine, 14), STRPOS(COPYSTR(ImportLine, 14), ' ')));
    Window.UPDATE(2, TableNo);
    Fields.SETRANGE(TableNo, TableNo);
    IF Fields.FINDFIRST THEN
      Window.UPDATE(3, Fields.TableName);
  END;
  IF NewTable AND (COPYSTR(ImportLine, 1, 8) = '  FIELDS') THEN
    NewFields := TRUE;
  IF NewTable AND NewFields AND (COPYSTR(ImportLine, 1, 5) = '    {') THEN BEGIN
    EVALUATE(FieldNo, DELSTR(COPYSTR(ImportLine, 7), STRPOS(COPYSTR(ImportLine, 7), ';')));
    Window.UPDATE(4, FieldNo);
    Fields.SETRANGE(TableNo, TableNo);
    Fields.SETRANGE("No.", FieldNo);
    IF Fields.FINDFIRST THEN
      Window.UPDATE(5, Fields.FieldName);
    IsFlowField := Fields.Class = Fields.Class::FlowField;
  END;
  IF NewTable AND NewFields AND IsFlowField AND (COPYSTR(ImportLine, 52, 12) = 'CalcFormula=') THEN BEGIN
    CalcFormula := COPYSTR(ImportLine, 64);
    WHILE STRPOS(ImportLine, ';') = 0 DO BEGIN
      ImportFile.READ(ImportLine);
      i := 0;
      REPEAT
        i := i + 1;
      UNTIL ImportLine[i] <> ' ';
      CalcFormula := CalcFormula + COPYSTR(ImportLine, i);
    END;

    FlowFieldToTSQL.INIT;
    FlowFieldToTSQL."Table ID" := Fields.TableNo;
    FlowFieldToTSQL."Table Name" := Fields.TableName;
    FlowFieldToTSQL."Data Per Company" := TableInfo.GET(COMPANYNAME, Fields.TableNo);
    FlowFieldToTSQL."Field ID" := Fields."No.";
    FlowFieldToTSQL."Field Name" := Fields.FieldName;
    FlowFieldToTSQL."CalcFormula 1" := COPYSTR(CalcFormula, 1, 250);
    IF STRLEN(CalcFormula) > 250 THEN
      FlowFieldToTSQL."CalcFormula 2" := COPYSTR(CalcFormula, 251, 250);
    IF STRLEN(CalcFormula) > 250 THEN
      FlowFieldToTSQL."CalcFormula 3" := COPYSTR(CalcFormula, 501, 250);
    IF STRLEN(CalcFormula) > 250 THEN
      FlowFieldToTSQL."CalcFormula 4" := COPYSTR(CalcFormula, 751, 250);
    IF FlowFieldToTSQL.INSERT THEN;

  END;
  IF NewTable AND NewFields AND (COPYSTR(ImportLine, 1, 3) = '  }') THEN
    NewFields := FALSE;
  IF NewTable AND (COPYSTR(ImportLine, 1, 1) = '}') THEN
    NewTable := FALSE;
END;
Window.CLOSE;

Kind regards,
Jörg

mibuso.com

SSRS Helper 1.0 (CTP 1)

Who is online