PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 04/29/2015 10:43 AM by  Randy
Crystal-Financial Statements-Selecting the month
 1 Replies
Sort:
You are not authorized to post a reply.
Author Messages
dcaiani
Private
Private
Veteran Member
(127 points)
Veteran Member
Posts:49


Send Message:

--
04/29/2015 9:28 AM
    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
    Systems Administrator
    Rochester Regional Health
    Veteran Member
    (113 points)
    Veteran Member
    Posts:41


    Send Message:

    --
    04/29/2015 10:43 AM
    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'

    You are not authorized to post a reply.