Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Crystal-Financial Statements-Selecting the month

 1 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
dcaiani
Veteran Member
Posts: 52
Veteran Member
4/29/2015 1:28 PM
    So I have a Crystal Report built where I am showing financial data Primarily from the GLAMOUNTS and FBDETAIL tables. The report basically looks like this:
    Company | Account | MTD Actual | MTD Budget

    Its subtotaled at the company group level.

    So for one month its working correctly.What I want to do is make it so that the end user is picking the month they want to see from a drop down list rather than creating a report per month or a report that lists all months.

    The problem I'm having is that GLAMOUNTS and FBDETAILhave one field for each month (Actually 2, one for debits and one for credits which I have combined in the command to be one field). Parameter field options seem to only want to work with the data inside one specific field.

    * What do I need to do to allow the user to select one month?
    * Is it posible to have the user select one month and it gets the correct field for both the actual and budget or do they have to select twice, once for budget and once for actual?

    Thanks

    Dave
    Randy
    Veteran Member
    Posts: 50
    Veteran Member
    4/29/2015 2:43 PM
      I've created a view that combines the credit/debit amounts and adds in all the names/descriptions of COMPANY, AU,ACCOUNT and SUB_ACCT. In the SQL of the crystal report I have the following

      -- ------------------------------------------------------------------------
      Declare @PER smallint = {Period} <-- Param Here

      Select
      GLA.COMPANY
      ,GLA.COMPANY_NAME
      ,GLA.ACCT_UNIT
      ,GLA.ACCT_UNIT_NAME
      ,GLA.ACCOUNT
      ,GLA.SUB_ACCOUNT
      ,GLA.ACCOUNT_DESCRIPTION

      ,Case @PER
      When 1 then GLA.JAN
      When 2 then GLA.FEB
      When 3 then GLA.MAR
      When 4 then GLA.APR
      When 5 then GLA.MAY
      When 6 then GLA.JUN
      When 7 then GLA.JUL
      When 8 then GLA.AUG
      When 9 then GLA.SEP
      When 10 then GLA.OCT
      When 11 then GLA.NOV
      When 12 then GLA.DEC
      end [AMT]

      from dbo.C_GLAMOUNTS_NAMES_V GLA
      Where GLA.FISCAL_YEAR = 2015
      AND ACCOUNT Between '50000' and '69999'