• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

Constant value in crosstab in crystal reports 2011

I have a crosstab in crystal reports.  the rows are grouped by worker.  the columns are grouped by month.  right now, there is a field for the sum of the $ each person has billed under one type of work, a sum of the $ each person has billed for another type of work, and a total of these fields.

What I have been trying to figure out, is how to add a monthly cost to the company of each employee, which changes depending on the employee, and have these grand total in the column along with the other totals, and then produce a figure which is the $ each person is generating minus the cost to the company for each person.

the problems i have had so far include:
i can't enter a constant for a person for a month without having to do a summary on it.
the billed amount has no relationship to the cost of the employee per month.
the billed amount is based on how many billed hours the employee has entered on a work order...if i try to associate a time-based amount it produces a sum.
if i put in some kind of formula based on the person's name and choose a summary such as a median to just pick out one value of multiple values for that person, then it performs the same kind of summary in the total column.

I took a look at calculated members etc. but have never used these and i got lost.

Any help would be appreciated, Experts!!
0
Nero83
Asked:
Nero83
  • 11
  • 5
  • 4
1 Solution
 
vastoCommented:
You can use MIN or MAX for the column summary ( instead of SUM)
0
 
Nero83Author Commented:
Thanks.  I tried this, but then it also repeats the min or max for the total...and I need this to be a sum of the month columns.
0
 
mlmccCommented:
Do you know how many months there are?

Use a formula like
AmountForEmployee / NumMonths

Then it can be summed to get the correct amount

mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Nero83Author Commented:
Thanks mlmcc,

There are 13 months.
I have a monthly amount handy for each person, too.  I'm still confused tho.

                    total     jan     feb     mar
joe smith     6           3        2          1
stan blow    25          16     5          4

So what are you proposing as a summary type for each month?...i don't know how to enter a constant in a crosstab.  Whatever I have as the summary type will be xferred into the total column as well it seems.
0
 
mlmccCommented:
Are you saying you need a column like this that is NOT included in the totals?

                    total     jan     feb     mar     Adjustment
joe smith     6           3        2          1            3
stan blow    25          16     5          4             6

Are there potentially multiple records for a person in each month?

mlmcc
0
 
Nero83Author Commented:
Each person has 3 stacked figures per month plus a figure adding them.  Then each has to total in a column on the left.  The money generated figures come fom multiple records but the persond cost to the company is static per person.
0
 
Nero83Author Commented:
maybe this will help - or i can post something else.  i may not be describing it well.

                                                               total       jan       feb       mar
joe smith                                            
  $ generated from job type A            1,500      500      500      500     sum: many records
  $ generated from job type B            800         200      100      500     sum: many records
 $ cost to company                             -1,200    -400     -400     -400    same value monthly
  balance                                               1,100      300      200       600    sum of values

stan lee                                                etc..
  ""
joan baez                                             etc.
  ""
0
 
vastoCommented:
How do you prepare the data . I guess the easiest way to handle this is to unioun 2 recordsets : one with the data which will be shown in "$ generated from job ..." rows and another one which will contain the data for expences ( one row per each user and month).

Since the data will be not joined you will have just one value -400 for joe smith for jan and you can continue to use sum. However since you need UNION if you are using tables and views you will need to switch to a command , stored procedure or to create a union view and point the report to it.
0
 
mlmccCommented:
DO you have a table with the expenses?

Can you build such a table in the database?

mlmcc
0
 
Nero83Author Commented:
@vasto
you're getting a bit beyond what i know...i understand the concept of union recordsets, but don't know how to implement them in CR.
when you say i need to switch to a command, stored procedure or create a union view, do you have an example of something like this i could look at and try to figure out?
0
 
Nero83Author Commented:
@mlmcc
i could make a database to hold the expenses info - it's just in an excel sheet right now.  I can't attach it to the database with the $ generated data - it would have to be separate and i'd have to somehow relate them using the row names (person names) i guess.
0
 
mlmccCommented:
How do you plan to run the report in the future?

mlmcc
0
 
vastoCommented:
I guess you report source data has structure like this:

user                  source                                          month           value
joe smith         $ generated from job type A      jan                 $100

This is retrieved with a sql like this one
SELECT User,  Source, Month, Value From TableName

Open in new window


you can add your expences by using UNION:
SELECT User,  Source, Month, Value From TableName
UNION
SELECT User,  '$ cost to company' , Month, Value From ExpencesTable

Open in new window


I was expecting that you have your expences saved somewhere and you can just get the data . If you don't have a ExpenceTable then you can generate the data on the fly but the syntax will depend from the database type. What is your database ?
0
 
Nero83Author Commented:
Thanks vasto,

It's a Progress database that holds the $ generated info.  I only have read rights on this, so I can't create queries within it directly.

In Crystal, where do I create this SQL query?...I haven't done this within Crystal before, only in Access.
0
 
Nero83Author Commented:
Thanks mlmcc,
I'm not sure what you mean by 'how do i plan on running this report in the future'.
0
 
mlmccCommented:
Are you using an application or will they run from Crystal?

If you need to use a second data source for the expense records, most viewers won't support changing the second data source location.

In Crystal you can create a COMMAND when you select the data source.

mlmcc
0
 
Nero83Author Commented:
Thanks mlmcc,
This is all running from crystal - just on my machine.
I haven't figured out in the past how to pull data from multiple data sources in one report.  Will creating a Command allow me to do this?


Thanks vasto,
The source of $ generated is a sum of multiple records each with its own date and work order number.  Would this need some kind of outer join to make it so that I don't end up with tons of $ cost to company values?  I'm a bit out of my element here.

Is the best way to do this to create a separate database with only one table in Access containing the cost to employer $ ?

Thanks again for your continued help!!  Attached is the current report I have which does not contain any data for the cost to company for each person.
PRODUCERS-BY-NAME-V3.rpt
0
 
vastoCommented:
If you use 2 separate connections to retrieve the data it weill be processed inside Crystal reports and the report will be slow. To avoid using 2 connections try one of these:

1. I am not familiar with Progress database , but in most of the databases you can create a temp table within a script. This will allow you to create the expence table on the fly without using a separate connection.

2. The other option is to create a linked table(s) in Access ( which is pointing to your Progress database). Create also the expence table in Access and use UNION to combine the data. Then point your Crystal reports to Access (not to the Progress database). Access will be like a bridge and will retrieve the data from Progress and combine it with the expences.
0
 
Nero83Author Commented:
Interesting.  I don't know how to create a temp table within a script, but I know how to do #2 and will try that.  Thanks vasto!
0
 
Nero83Author Commented:
Didn't answer my question exactly, but gave me a non-CR alternative.  Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 11
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now