adding a parameter @fyear to GL_Summary Report

Did you create a copy of the report and make it a local procedure? You should always do this when modifying canned reports. If you did this, have you also created a new infomaker report that ties to your local procedure? To get the parameters to show up on your infomaker report, you must rename your text fields to st_p1 for parameter 1, st_p2 for parameter 2, and so on. Not sure why you are only getting details as opposed to summary, but will need you to give some more info first.

Hi Christian,

yes I did create a new local stored procedure Lp_gl_summary_report

Code:

USE [impresario]

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER    PROCEDURE [dbo].[LP_GL_SUMMARY_REPORT_CUSTOM](

      @post_dt_start datetime,

      @post_dt_end datetime,

      @gl_account_start varchar(30),

      @gl_account_end varchar(30),

      @mode int,

      @batch_type_group int,

      @fyear int)

AS

SET NOCOUNT ON

select      a.gl_act_no ,         

      a.type ,           

      a.post_amt ,

      b.gl_description,

      a.post_no,

      a.post_dt,

      debit = convert(money,0),

      credit = convert(money,0),

      net = convert(money,0),

      mode = convert(int,0),

      fyear

into  #temp

FROM  T_GL_POSTING_HISTORY a 

      JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no

where   2=1

insert into #temp

SELECT  a.gl_act_no ,         

      a.type ,          

      a.post_amt ,

      gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),

      a.post_no,

      a.post_dt,

      debit = case when a.type = 'D' then a.post_amt else 0 end,

      credit = case when a.type = 'C' then a.post_amt else 0 end,

      net = convert(money,0),

      mode = @mode,

      a.fyear

FROM  T_GL_POSTING_HISTORY a 

      LEFT OUTER JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no 

      WHERE       a.post_dt >= @post_dt_start and

        a.post_dt <= @post_dt_end and

      a.gl_act_no >= @gl_account_start and

      a.gl_act_no <= @gl_account_end and

      exists (select * from t_batch where post_no = a.post_no)    -- this condition added CWR 9/16/2003 so that the report ignores orphaned rows here

      and (@batch_type_group is null and a.batch_type_group in (select id from vrs_batch_type_group)  -- parameter is null, then all you have access to

            OR

      (a.batch_type_group = @batch_type_group))  -- just the one you select

SELECT      gl_act_no ,         

      gl_description = max(gl_description),

      post_no,

      post_dt,

      debit = sum(debit),

      credit = sum(credit),

      net = sum(debit) - sum(credit),

      mode = max(mode),

      fyear

FROM    #temp

where fyear = @fyear

group by gl_act_no, fyear, post_no,post_dt

I have also created new infomaker object thats linked to newly created SP.

In the orginal report:

Date Range: St_date_range(2paramets)

gl_account Range:st_gl_account_range(2 parametrs)

group: st_group

mode: st_mode

st_pt1 --I will get a single parameter not a range I guess,I tried it didnot work.

Thank you very much for your quick reponce ...

I really appreciate it

Revanth

Hi Christain,

I have formatted the code in the post ,so its easy to have a look.

Thank you

Hi Christain,

I have formatted the code in the post ,so its easy to have a look.

Thank you

Revanth,

The stored procedure always returns the entire output (detail). So the mode parameter appears to be used for the infomaker report. The infomaker report must be created with a grouping on gl_account_no, and the detail group set to include post no,post_dt,debit,credit,etc...

So then if mode = 1, you do not show the detail and just display the gl_account_no group info. If mode = 2, then make the detail row visible.

In summary, you were having trouble because the mode parameter is used to toggle visibility on the grouping of the infomaker report rather than altering the result set from the stored procedure.

Hope that makes sense,

Christian

P.S. I believe that you can download the infomaker report for GL_SUMMARY_REPORT on TASK. This would be a good idea so you could see how it should be done.

Looking a little closer, the infomaker report may be a composite report and depending on what mode is sent through, it shows sub report 1 (summary) or sub report 2 (detail). Again, you should download the infomaker report from TASK and it will be very clear how they did it.

Thank you for all those who have replied, I am able to succesfully complete the report.

Revanth Kumar Anne..