report in Crystal which runs from crystal but generates nothing from reports Menu

Sage 300.  I have written a report in Crystal which runs great from crystal  but generates nothing when I add it to the reports Menu in SAGE. I get a bit of an hourglass but no output.  It is setup for preview..  Any tips on how to troubleshoot this?

  • 0

    Which "reports Menu" are you referring to?  Do you mean the Sage desktop?

  • 0

    I assume we're talking about Sage 300 ERP?

    What Report Menu are you referring to?

  • 0 in reply to Dana B. Stidsen

    Sage 300 Report Manager. I tried moving my database location to 64 bit odbc but that didn't help.  tried reloading the report.  get a warning message in a Report Manager Popup Window that says "unable to load report"

  • 0 in reply to GRINGEL

    Don't use 64 bit ODBCs,  Stay with 32-bit.  The software is 32-bit.

    Is the database a Sage 300 Company database?  When a report is run from within Sage 300 the system manager forces it to use the company database of the company you're logged into.

  • 0 in reply to Dana B. Stidsen

    Ok Dana, I've switched to the 32bit ODBC.  I don't get "unable to load" message anymore.  When I attempt to run the report from the menu I get the hourglass but then no result.  almost as if it is printing to a printer but the settings are to Preview.  It is a report which runs against the company that I am logged into using a view that I built against tables within the database instance for that company.

  • 0 in reply to GRINGEL

    Are you passing any date parameters to the report from Report Manager?  Date parameters need special handling.  Try setting the report to use some default parameters in Crystal and see if it works.  In other words, remove all the parameters sent by Report Manager.

  • 0 in reply to Dana B. Stidsen

    There was a character year YYYY format parameter that I was passing in but I've removed the parameter and hardcoded the value in a report variable.  The report still doesn't generate a listing from within Report Manager but still generates from within Crystal.

  • 0 in reply to GRINGEL

    Are the SQL Views the only 'tables' being used in the report? 

  • 0 in reply to Dana B. Stidsen

    A single SQL View. Yes it is the only table.  I could modify the view to add AUDTORG if you want me to join it to CSCOM if you think that might help...?

  • 0 in reply to GRINGEL

    That's the issue.  SQL Views by themselves must be in a sub-report.  If you add the CSCOM table and link it to the view it should be Okay.

  • 0 in reply to Dana B. Stidsen

    Hi Dana, I modified the view to include AUDTORG and joined it to CSCOM and brought in company name. This still doesn't solve the problem unfortunately.  Anything else I can try?  Is there a way to log what is actually happening within the call from the Report Manager?  

    Aside I have constructed many reports using views and have never yet had this issue.

  • 0 in reply to GRINGEL

    You can try adding the report to the Sage desktop outside of report manager and see if it runs.  

  • 0 in reply to Dana B. Stidsen

    I have added it to the order reports menu and it doesn't produce visible output from there either

  • 0 in reply to GRINGEL

    So report manager is not the issue.  You just set it up to run right from the desktop right?

    Does the SQL View include any data from any non-standard Sage 300 table?

    You could post the view design here and maybe I'll see something obvious.  I'm about to pack it in for the day so it will be tomorrow before I look at it.

  • 0 in reply to Dana B. Stidsen

    Hi Dana, Below is the SQL View but I can't imagine that is the problem as I've done this kind of report against views many times with great success.  

    create VIEW  SalesBudgetThisYearLastYear

     

    as

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '01' AS PERIODCHAR,

     MIN(-B.NETPERD1) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='1') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='1') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

      AUDTORG,  ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

     

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '02' AS PERIODCHAR,

     MIN(-B.NETPERD2) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='2') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='2') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

     

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '03' AS PERIODCHAR,

     min(-B.NETPERD3) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='3') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='3') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

      

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '04' AS PERIODCHAR,

     min(-B.NETPERD4) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='4') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='4') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

      

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '05' AS PERIODCHAR,

     min(-B.NETPERD5) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='5') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='5') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

      

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '06' AS PERIODCHAR,

     min(-B.NETPERD6) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='6') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='6') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

      

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '07' AS PERIODCHAR,

     min(-B.NETPERD7) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='7') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='7') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

         

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '08' AS PERIODCHAR,

     min(-B.NETPERD8) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='8') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='8') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

      

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '09' AS PERIODCHAR,

     min(-B.NETPERD9) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='9') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='9') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

         

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '10' AS PERIODCHAR,

     min(-B.NETPERD10) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='10') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='10') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

         

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '11' AS PERIODCHAR,

     min(-B.NETPERD11) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='11') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='11') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

         

     

    UNION

     

    select

     AUDTORG, B.FSCSYR, B.FSCSYR-1 AS PRIORYEAR, B.FSCSDSG, B.ACCTID,

     '12' AS PERIODCHAR,

     min(-B.NETPERD12) AS MTHLY_BUDGET,

    (SELECT  coalesce(SUM(S1.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S1

         WHERE B.FSCSYR=S1.TRNFISCYR and S1.MISCCHARGE<>'TAX' and  S1.TRNFISCPER='12') AS TY_GROSS_SALES,

    (SELECT  coalesce(SUM(S2.EXTTRNAMT),0)

         FROM dbo.SalesAndCreditsDataTempGeo S2

         WHERE B.FSCSYR-1=S2.TRNFISCYR and S2.MISCCHARGE<>'TAX' and S2.TRNFISCPER='12') AS LY_GROSS_SALES

    from GLAFS B

    GROUP BY

       AUDTORG, ACCTID, FSCSYR, FSCSDSG, FSCSCURN, CURNTYPE

    HAVING

       ACCTID='410000'  and FSCSDSG = '1' and FSCSCURN='USD' and CURNTYPE='F'

  • 0 in reply to GRINGEL

    When added the CSCOM table to the database selection in the report did you add a field from CSCOM on the report? Like the company name?

  • 0 in reply to Dana B. Stidsen

    precisely what I did

  • 0 in reply to GRINGEL

    Is this  'dbo.SalesAndCreditsDataTempGeo S1' a Sage 300 Database?

  • 0 in reply to Dana B. Stidsen

    It is also a view against Sage database.

  • 0 in reply to GRINGEL

    Every standard report in Sage 300 is designed to read data from the database that holds the company data of the company you're logged into when you run the report.  The system manager run's the report that was designed using a typical Sage 300 database 'SAMINC' by replacing the database used by the report with the database of the company you're logged into.  This allows the same report to be used across multiple Sage 300 companies.  If you build a report with Sage 300 company data and you run the report from within a different company it will try to change the database.  The only way I've ever been able to report from a view is the view has to be within the database of the company data and the view contains only data from that database. 

    Views can be reported as part of a sub-report too. 

    That's all I got.  Good luck.

  • 0 in reply to Dana B. Stidsen

    Hi Dana, one more question? Is there a way for me to turn on some kind of logging which would provide me with a real error of some kind?

  • 0 in reply to GRINGEL

    None that I know of.  It would have to work with Crystal Reports and I don't now of any.

  • 0 in reply to GRINGEL

    Search the forums for REPCMD - there is a registry entry that you can toggle on to see how Sage is interacting with Crystal.  It might help.