passing parameters dynamcailly to a report and calling from SLX web.. Help Needed very urgent

[jack4slx]

Dear All,

I just need help regarding following scenario.

I am using SLX 7.5.4 Web. I need to call a report on web on a button click of account detail screen by dynamically passing account id as a parameter.

The Report is “Custom Account Detail Report” which aspects one parameter which is accountid

In Architect report settings are  main table is Account table and Masteruserid is accountid

Externally my report works fine when I manually pass any accountid as parameter.

I am using below JS function for calling report.

function ShowReportByName(ReportName) {
    if (GLOBAL_REPORTING_URL == "") {
        alert(MSGID_THE_REPORTING_SERVER_HAS_NOT_BEEN);
        return;
    }
    if (ReportName == null || ReportName == "") {
        alert(MSGID_THE_REPORTNAME_HAS_NOT_BEEN_DEFINED);
        return;
    }
    if (Sage.Services.hasService("ClientEntityContext")) {
        var contextSvc = Sage.Services.getService("ClientEntityContext");
        var context = contextSvc.getContext();
        var strTableName = context.EntityTableName.toUpperCase();
        var strEntityId = context.EntityId;
        if (strEntityId != "") {
            var strReportId = GetReportId(ReportName);
            if (strReportId != null && strReportId != "") {
                PopulateGlobals(strReportId, strTableName, strEntityId);
                var url = "ShowReport.aspx";
                window.open(url, "ShowReportViewer", "location=no,menubar=no,resizable=yes,scrollbars=yes,status=no,titlebar=no,toolbar=no,width=800,height=630");
            }
            else {
                alert(MSGID_THE_REPORT_COULD_NOT_BE_LOCATED_FOR)
            }
        }
        else {
            alert(MSGID_THE_CURRENT_ENTITY_IS_UNDEFINED);
        }
    }
    else {
        alert(MSGID_UNABLE_TO_SHOW_THE_DETAIL_REPORT);
    }
}

I am giving the report name as parameter at client click. But still it is asking for the parameter.

Could you please help me with this..

I have also tried below JS function which is in  reportutil.js of slxlclient portal  file but it is not working it is still asking for parameter to pass manually.

function ShowReport(ReportNameOrId, EntityTableName, EntityId) {
    if (GLOBAL_REPORTING_URL == "") {
        alert(MSGID_THE_REPORTING_SERVER_HAS_NOT_BEEN);
        return;
    }
    if (ReportNameOrId == null || ReportNameOrId == "") {
        alert(MSGID_THE_REPORTNAMEORID_IS_UNDEFINED_IN);
        return;
    }
    if (EntityTableName == null || EntityTableName == "") {
        alert(MSGID_THE_ENTITYTABLENAME_IS_UNDEFINED_IN);
        return;
    }
    if (EntityId == null || EntityId == "") {
        alert(MSGID_THE_ENTITYID_IS_UNDEFINED_IN);
        return;
    }   
    var strReportId = GetReportId(ReportNameOrId);
    if (strReportId != null && strReportId != "") {
        var strTableName = EntityTableName.toUpperCase();
        var strEntityId = EntityId;
        if (strEntityId != "") {
            PopulateGlobals(strReportId, strTableName, strEntityId);
            var url = "ShowReport.aspx";
            window.open(url, "ShowReportViewer", "location=no,menubar=no,resizable=yes,scrollbars=yes,status=no,titlebar=no,toolbar=no,width=800,height=630");
        }
        else {
            alert(MSGID_THE_CURRENT_ENTITY_IS_UNDEFINED);
        }
    }
    else {
        alert(MSGID_THE_REPORT_COULD_NOT_BE_LOCATED_FOR)
    }
}

Thanks in advance

[mcessna]

There is no way to keep parameter fields from prompting the user. The ShowReport code just builds a WHERE SQL that is appended to the report query as either a RecordSelectionFormula or integrated into the report SQL.

Thanks

Mike

[jack4slx]

hi Mike,

Does it mean that we cannot pass paramaters from c# code or java script dynamically on a button click. Let's say a report needs to be generated based on some four parameters that should have to pass from code. Is it possible? or not?

[mcessna]

If you are referring to report parameter fields, where you would call the Crystal API Report.SetParameterValue(), it would have to be a complete custom solution. We only support SQL WHERE conditions that are parsed into the report's RecordSelectionFormula.

Thanks

Mike

[jack4slx]

So we can still write a custom c# code to call crystal report viewer to pass report parameters from c# code in SLX web?

[mcessna]

Yes, but you would have to create your own report viewer, etc.

The following post should get you started on generating a report on the server-side: http://community.sagesaleslogix.com/t5/Sage-SalesLogix-Developer/7-5-2-Web-Export-Crystal-Reports-to....

Thanks

Mike

[jack4slx]

Hai Mike,

I dont have sufficient previllages to see the link you have posted above. Could you please paste the link content here as it would be very helpful for me.

Expecting positive Reply.

Jack

[mcessna]

There were a series of posts:

<Quote>

So I did some investigation on my own and came up with the following solution where a report is generated, exported to disk and added as an attachment all in code:

public static IAttachment ExportReport(this IOpportunity opportuntiy)
{
    IAttachment attachment = null;          
    string pluginid = string.Empty;
            
    //if (ReportsHelper.GetPluginId("Opportunity:Opportunit​y Detail", out pluginid))
    if (ReportsHelper.GetPluginId("Opportunity:Open Opportunities Summary", out pluginid))
    {
        WebReportingClass reporting = new WebReportingClass();
        IDataService datasvc = MySlx.Data.CurrentConnection;
        User user = MySlx.Security.CurrentSalesLogixUser;

        string tempPath = Rules.GetTempAttachmentPath();
        string report = reporting.GenerateReport(datasvc.GetConnectionStri​ng(), 
                                                 datasvc.Server, 
                                                 DatabaseServer.dsMSSQL, 
                                                 tempPath, 
                                                 false, 
                                                 false, 
                                                 "OPPORTUNITY.OPPORTUNITYID", 
                                                 "", 
                                                 pluginid, 
                                                 "", 
                                                 "", 
                                                 "",
                                                 "SELECT OPPORTUNITY.OPPORTUNITYID FROM OPPORTUNITY", 
                                                 "",//string.Format("(OPPORTUNITY.OPPORTUNITYID = '{0}')", opportuntiy.Id), 
                                                 user.Id, 
                                                 user.UserName);

                ReportDocument doc = new ReportDocument();
                
                report = string.Format("{0}run\\{1}", tempPath, report);
                doc.Load(report);
                //doc.SetDatabaseLogon("ADMIN","");
                string filename = string.Format("{0}\\{1}_v{2}.pdf", Rules.GetAttachmentPath(), opportuntiy.Description.Replace(" ", "_"), 1);

        doc.ExportToDisk(ExportFormatType.PortableDocForma​t, filename);
        doc.Close();
                
        attachment = EntityFactory.Create<IAttachment>();
        attachment.Description = string.Format("{0} v{1}", opportuntiy.Description, 1);
        attachment.InsertFileAttachment(filename);
        attachment.Save();

        File.Delete(report);
    }

    return attachment;            
}

I'm not sure if this is the best way to go... but it works!

</Quote>

<Quote>

Hi Alexander,

These are the usings

using System.IO;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using Interop.SLXWR;
using Sage.Entity.Interfaces;
using Sage.Platform;
using Sage.Platform.Data;
using Sage.SalesLogix.API;
using Sage.SalesLogix.Attachment;
using Sage.SalesLogix.Client.Reports.Helper;
using Sage.SalesLogix.Security;

The :ReportDocument class is from the CrystalDecisions.CrystalReports.Engine.dll which is part of the Crystal Reports .Net SDK. As far as I know the dll's from this SDK will be added to the global assembly cache when installing the Web Reporting Component.

The WebReportingClass is from the Interop.SLXWR.dll. This dll should be available in your Web Reporting Portal.

</Quote>

<Quote>

Just a view additions on the posted code, this was based on an User with an empty password. I've had to add the following to use a password.

string pwd = string.Empty;

if (!string.IsNullOrEmpty(user.UserPassword))
{
    pwd = Sage.SalesLogix.SLXEncryption.Decrypt(user.UserPas​sword, userid).Trim();
}

The pwd variable should then be used in the GenerateReport function.

Furthermore I had to add the following before the call to the ExportToDisk function:

doc.SetDatabaseLogon(username, pwd);

Still not sure if this is the recommended way to do it but it works

</Quote>

Thanks

Mike

[mcessna]

Hi Alexander,

These are the usings

using System.IO;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using Interop.SLXWR;
using Sage.Entity.Interfaces;
using Sage.Platform;
using Sage.Platform.Data;
using Sage.SalesLogix.API;
using Sage.SalesLogix.Attachment;
using Sage.SalesLogix.Client.Reports.Helper;
using Sage.SalesLogix.Security;

The :ReportDocument class is from the CrystalDecisions.CrystalReports.Engine.dll which is part of the Crystal Reports .Net SDK. As far as I know the dll's from this SDK will be added to the global assembly cache when installing the Web Reporting Component.

The WebReportingClass is from the Interop.SLXWR.dll. This dll should be available in your Web Reporting Portal.

[mcessna]

Just a view additions on the posted code, this was based on an User with an empty password. I've had to add the following to use a password.

string pwd = string.Empty;

if (!string.IsNullOrEmpty(user.UserPassword))
{
    pwd = Sage.SalesLogix.SLXEncryption.Decrypt(user.UserPas​sword, userid).Trim();
}

The pwd variable should then be used in the GenerateReport function.

Furthermore I had to add the following before the call to the ExportToDisk function:

doc.SetDatabaseLogon(username, pwd);

Still not sure if this is the recommended way to do it but it works

[jack4slx]

Hi Mike,

string report = reporting.GenerateReport(datasvc.GetConnectionStri​ng(),
                                                 datasvc.Server,
                                                 DatabaseServer.dsMSSQL,
                                                 tempPath,
                                                 false,
                                                 false,
                                                 "OPPORTUNITY.OPPORTUNITYID",
                                                 "",
                                                 pluginid,
                                                 "",
                                                 "",
                                                 "",
                                                 "SELECT OPPORTUNITY.OPPORTUNITYID FROM OPPORTUNITY",
                                                 "",//string.Format("(OPPORTUNITY.OPPORTUNITYID = '{0}')", opportuntiy.Id),
                                                 user.Id,
                                                 user.UserName);

In the above statement, when i used it in code it is showing an error that  "error in web reporting path". How can we detect a web reporting path for slxclient site through code. Because in my case(in production) web reporting portal is installed in seperate  server.

PLease Suggest.

Thanks,

Jack

[mcessna]

The default path is the C:\inetpub\wwwroot\slxwebrpt\run location. There are other paths that are used as well, such as %TEMP% and SomeProfile\My Documents\SalesLogix\Reports\w3wp. You will need to make sure that the app pool user has full control of these locations; in addition, if running a RAS server the RAS service user must have full control of these locations as well (the locations are configured in the Business Objects Central Configuration Manager).

The Application event log may give details about the location.

Thanks

Mike