Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Single Location:multiple values (Urgent Please See Post Reply Post New Topic
Author Message
acoolstud99
Newbie
Newbie


Joined: 27 Aug 2012
Online Status: Offline
Posts: 2
Quote acoolstud99 Replybullet Topic: Single Location:multiple values (Urgent Please See
    Posted: 27 Aug 2012 at 2:19am

There is a table PM (preventive maintenance) with columns PMNUM, LOCATION, NEXTDATE (MM/DD/YYYY), FREQUENCY....

suppose record is in this manner....

PMNUM LOCATION NEXTDATE FREQUENCY

PM8569 217.024 09/28/2012 3

PM7890 217.024 09/23/20113 12

PM4565 217.043 01/14/2012 3

PM7789 217.043 03/13/2012 3

PM8809 217.016 02/16/2012 6

Here frequency means that after every 3/6/12/1 months preventive maintenance needs to be done on that location.

i.e. if frequency is 3 then after every 3 months there should be preventive aintenance(PM) on that location. EG:: location is 217.024 and its month from date is 9th i.e. september and ffrequency is 3.Hence below september column in report Q must be printed and since frequency is 3 so after every three months Q must be printed below that colum too i.e below january,may.
The records must be printed in this format::

Page Header:

LOCATION JAN FEB MAR APR MAY JUN JUL AUG SEP

217.024     Q                          Q                      Q/Y

217.043     Q             Q           Q          Q           Q

217.016            S                                              S

I have showed column only till SEP...I am doing PM (primitive maintenance) forecasting...Please Help ASAP.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 27 Aug 2012 at 5:05am
Looking at this, I think you'll need a command to correctly pull this data.  A command is a SQL Select statement.  How good are your SQL skills?  The SQL would look something like this:
Select
  pm.LOCATION,
  month(pm.NEXTDATE) as STARTMONTH,
  'M' as JAN,
  'M' as FEB,
  'M' as MAR,
  'M' as APR,
  ...<etc>...
from PM
where pm.FREQUENCY = 1
 
UNION ALL
 
Select
  pm.LOCATION,
  month(pm.NEXTDATE) as STARTMONTH,
  Case when month(pm.NEXTDATE) = 1 then 'A' else ' ' end as JAN,
  Case when month(pm.NEXTDATE) = 2 then 'A' else ' ' end as FEB,
  Case when month(pm.NEXTDATE) = 3 then 'A' else ' ' end as MAR,
  ...<etc.>...
from PM
where pm.FREQUENCY = 12
 
UNION ALL
 
Select
  pm.LOCATION,
  month(pm.NEXTDATE) as STARTMONTH,
  case
    when month(pm.NEXTDATE) = 1 or month(pm.NEXTDATE) + 6 = 13 then 'S'
    else ' '
  end as JAN,
  case
    when month(pm.NEXTDATE) = 2 or month(pm.NEXTDATE) + 6 = 14 then 'S'
    else ' '
  end as FEB,
  ...<etc>...,
  case
    when month(pm.NEXTDATE) = 7 or month(pm.NEXTDATE) + 6 = 7 then 'S'
    else ' '
  end as JUL,
  ...<etc.>...
from PM
where pm.FREQUENCY = 6
 
UNION ALL
 
Select
  pm.LOCATION,
  month(pm.NEXTDATE) as STARTMONTH,
  case
    when month(pm.NEXTDATE) = 1 or
        month(pm.NEXTDATE) + 3 = 13 or
        month(pm.NEXTDATE) + 6 = 13 or
        month(pm.NEXTDATE) + 9 = 13 then 'Q'
    else ' '
  end as JAN,
  case
    when month(pm.NEXTDATE) = 2 or
        month(pm.NEXTDATE) + 3 = 14 or
        month(pm.NEXTDATE) + 6 = 14 or
        month(pm.NEXTDATE) + 9 = 14 then 'Q'
    else ' '
  end as FEB,
  ...<march>...
  case
    when month(pm.NEXTDATE) = 4 or
        month(pm.NEXTDATE) + 3 = 4 or
        month(pm.NEXTDATE) + 6 = 16 or
        month(pm.NEXTDATE) + 9 = 16 then 'Q'
    else ' '
  end as APR,
  ...<etc.>..
from PM
where pm.FREQUENCY = 3
 
Then, in your report, group by location and put the data  in the group FOOTER.  For each month you'll have a formula that looks something like this:
 
StringVar JANvals;
if PreviousIsNull({command.LOCATION}) or {command.LOCATION} <> previous({command.LOCATION}) then JANvals = '';
if {command.JAN} <> ' ' then JANvals = JANvals + {command.JAN} + '\';
left(JANvals, length(JANvals) - 1)
 
To get the underlines right, you'll create a formula for each month that looks something like this (I'll call this {@IsJAN}):
 
BooleanVar IsJAN;
if PreviousIsNull({command.LOCATION}) or {command.LOCATION} <> previous({command.LOCATION}) then IsJAN = '';
if {command.STARTMONTH} = 1 then IsJAN = true;
IsJAN
 
Then, for each of the value formulas that you put on the report, right-click and go to "Format Field...".  On the Font tab, click on the formula button to the right of Underline and, using the appropriate formula for that month, put the following:
 
{@IsJAN}
 
I realize this is a lot of coding, but it should get you at least close to what you're looking for.
 
-Dell


Edited by hilfy - 27 Aug 2012 at 5:08am
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
acoolstud99
Newbie
Newbie


Joined: 27 Aug 2012
Online Status: Offline
Posts: 2
Quote acoolstud99 Replybullet Posted: 28 Aug 2012 at 8:26pm

The query is giving me perfect results... and i displayed accordingly in crystal report..Clap....But the formula field is not giving proper results...it is showing a single character only but not in concatenated ('A/Q/M') form...

 
 
StringVar JANvals;
 
if PreviousIsNull({command.LOCATION}) or {command.LOCATION} <> previous({command.LOCATION}) then
JANvals := "";
 
if {command.JAN} <> "" then
JANvals := JANvals + {command.JAN} + '\';
 
if {command.JAN} <> "" then
left(JANvals, length(JANvals) - 1)
 
i placed all the formula fields for each month in group footer....
 
Before this method i had used resetting of variables after group change but it did not work...even here it was showing any one character for a particular location.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 29 Aug 2012 at 3:18am
Try adding WhileReadingRecords; to the beginning of the formula and see what happens.  If that doesn't work, try WhilePrintingRecords;.  If that still doesn't work, for debugging purposes, add the Location, JAN value (or several months' values), and the corresponding formula.  Run the report and see how the formula changes as the data is processed.  If that resolves the issue, leave the put each of the month formulas in the detail section and then suppress the section and see what happens.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.