Crystal Reports

Message Bookmarked

Bookmark Removed

I don't think there's a thread for this... I am sure I will have more queries as I have to use this thing.

My specific question this time:

OK, what I want to do is this - for each record in my query, there's a field with a Boolean true/false. In the Group Footer of my report, I want to have a count of just the FALSE records. How the heck do I do this?

I can't seem to attach an IF to a COUNT. I can't see to attach a WHERE to a COUNT.

The only things it will let me do are these:

Count (fld, condFld, cond)

where fld is the field, condFld is what you group it by, and cond another thing you group it by. (This is making no sense to me - it's like subtotal in Excel, telling the thing where to put the subtotals in.)

Anyone have any ideas? I've tried every bit of SQL code I know (and some others have suggested) in the function and formula workshop, but can't seem to do it.

― MIS Information (kate), Thursday, 14 July 2005 15:54 (twenty years ago)

That is mad as a bat.

select stuff, count (stuff)
from table
group by stuff
having stuff=false?

I was doing this not half an hour ago - this works shurly?

What errors does it tell you?

― Come Back Johnny B (Johnney B), Thursday, 14 July 2005 16:18 (twenty years ago)

That's a query.

I can't do it as a query, I have to do it as a subtotal for a group.

I'm trying right now to convert my TRUE/FALSE into 0/1 and then do a SUM which is what I ended up doing in Excel.

― MIS Information (kate), Thursday, 14 July 2005 16:21 (twenty years ago)

if you do a count and group by the boolean field name you'd end up with two values, a count of the true values and a count of the false ones. just ignore the count for true. would that work?

― koogs (koogs), Thursday, 14 July 2005 16:21 (twenty years ago)

I'm trying right now to convert my TRUE/FALSE into 0/1

Good idea.

― Jon, remind me again why you haven't drowned in your own vomit (ex machina), Thursday, 14 July 2005 16:23 (twenty years ago)

OMG Kate are you me?

SQL, Crystal...this is my daily bread and butter. Dull, isn't it?

― Felix Leiter (nordicskilla), Thursday, 14 July 2005 16:23 (twenty years ago)

I used to have to use ODBC to a legacy DB to get marketing data. I would then use ACCESS to crunch it. Queries would take over an hour to run sometimes.

― Jon, remind me again why you haven't drowned in your own vomit (ex machina), Thursday, 14 July 2005 16:26 (twenty years ago)

Adam, I don't find it dull at all. I actually find it really challenging and interesting. And I feel so... CLEVER when I finally get it working! I've got that kind of mind that likes to puzzle it out.

― MIS Information (kate), Thursday, 14 July 2005 16:26 (twenty years ago)

Bbbbbut imagine what you could do with a REAL database!!!

― Jon, remind me again why you haven't drowned in your own vomit (ex machina), Thursday, 14 July 2005 16:27 (twenty years ago)

Oh...I'm really just doing this to pay some bills. I guess I should try and get into it more.

I took Crystal training and they showed as all of these sexy features...none of which I ever need to use on my job. We mostly just run canned reports with a few minor SQL modifications.

Is there...much call for this kind of work in London? Just curious.

― Felix Leiter (nordicskilla), Thursday, 14 July 2005 16:29 (twenty years ago)

Yes!!! It worked! Now if only I could figure out a way to display the records as yes/no still so the bloody banker types upstairs don't get confused.

There is tons of call for this in London, Adam. I'm just glad I've got a job where I am able to learn Oracle, Crystal Reports, etc. because if you know it, there's so much work.

I was trying to find work earlier with just Access, ACT and bits of SQL and had diminishing returns. Learn Crystal and you are the mang as far as recruiters are concerned.

― MIS Information (kate), Thursday, 14 July 2005 16:31 (twenty years ago)

hmmm...

― Felix Leiter (nordicskilla), Thursday, 14 July 2005 16:32 (twenty years ago)

hmmmmmm................

― mark p (Mark P), Thursday, 14 July 2005 16:36 (twenty years ago)

hmmmmmm . . . .

― Come Back Johnny B (Johnney B), Thursday, 14 July 2005 16:56 (twenty years ago)

Shh you mark, there are plenty of jobs in Toronto for this too.

― Rufus 3000 (Mr Noodles), Thursday, 14 July 2005 17:12 (twenty years ago)

I warned you Crystal was evil

[evil laugh]

― Forest Pines (ForestPines), Thursday, 14 July 2005 17:44 (twenty years ago)

More hell awaits me today... now I've generated the count field, I have to perform functions and percentage based calculations upon it. Oh joy.

(However, while falling asleep last night I thought of a more... elegant way of calculating the count.)

― MIS Information (kate), Friday, 15 July 2005 06:34 (twenty years ago)

You're working some long hours at the moment.

― Ed (dali), Friday, 15 July 2005 06:43 (twenty years ago)

Such is the way of banking. Sigh.

― MIS Information (kate), Friday, 15 July 2005 06:47 (twenty years ago)

one month passes...

Oh boy, I've got a new dilemma now. I'm trying to extract the number of working days in the current month, and from that, the working day number of the current day.

Somewhere in my old Oracle database it does this... before I go insane trying to dig it out, has anyone else done this in Crystal?

Forest? Adam?

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 13:47 (twenty years ago)

Oh god, I've got to translate this into Crystal Syntax:

PROCEDURE clear_production_report (chosen_date IN DATE DEFAULT SYSDATE)
IS
BEGIN
EXECUTE IMMEDIATE ('TRUNCATE TABLE PRODUCTION_REPORT REUSE STORAGE');
INSERT INTO PRODUCTION_REPORT (TEAM, DATA_GROUP, VALUE_1, VALUE_2, VALUE_3, VALUE_4, COUNT, UPDATED)
SELECT 'DATE' TEAM, TO_CHAR(TRUNC(CHOSEN_DATE,'DD'),'DD/MM/YY') DATA_GROUP, COUNT(*) WORKING_DAYS,
TO_CHAR(ADD_MONTHS(TRUNC(chosen_date,'Q'),0),'MM'),
TO_CHAR(ADD_MONTHS(TRUNC(chosen_date,'Q'),1),'MM'),
TO_CHAR(ADD_MONTHS(TRUNC(chosen_date,'Q'),2),'MM'),
SUM(DECODE(ISGREATER(DATES.SELECTED_DATE-TRUNC(CHOSEN_DATE,'DD'),0),1,0,1)) Day,
SYSDATE UPDATED
FROM SYSADMIN.DATES
WHERE DATES.SELECTED_DATE BETWEEN TRUNC(chosen_date,'MM')
AND LAST_DAY(TRUNC(chosen_date,'MM'))
AND WEEKDAY NOT IN ('Saturday','Sunday') AND BANK_HOLIDAY = 'N';
Insert_Proc ('CLEAR_PRODUCTION_REPORT',chosen_date,'SUCCESS',SQLCODE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Insert_Proc ('CLEAR_PRODUCTION_REPORT',chosen_date,'FAILURE',SQLCODE);
END clear_production_report;

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 13:52 (twenty years ago)

Oh blimey - that's cheating. There's an actual table in the old db which has listed every date since 1988 with bank holidays and weekends picked out. There has GOT to be a simpler way to do this. Without actually editing the new SQL database that I can't touch.

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 13:55 (twenty years ago)

How come there's a LastFullMonth function, but not a CurrentFullMonth function? Or will I need to fool the thing into thinking it's next month?

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 14:15 (twenty years ago)

Dude, Oracle will gladly do this. Leap Years it can handle, Holidays will require a little bit of DECODE.

Sum the number of days in the month where the day in the week was 1-5 (or is it 2-6 I forget). Subtract holidays where appropriate with decode/if setting a value = -1.

― Rufus 3000 (Mr Noodles), Wednesday, 14 September 2005 14:17 (twenty years ago)

I know that Oracle can do it, it's already being done in the old Oracle database. I need to do it in *Crystal*. Which is a different beast. :-(

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 14:19 (twenty years ago)

Then the trick is to find a way for Crystal to let you do it in Oracle.

― Rufus 3000 (Mr Noodles), Wednesday, 14 September 2005 14:21 (twenty years ago)

I cannot touch the database, this is more than half of my problem with this. I can only interrogate it with Crystal.

― Ancients of LAUTRO (kate), Wednesday, 14 September 2005 14:24 (twenty years ago)

Forest?

I feel slightly bad as I've been namechecked, but I have absolutely no idea.

― Forest Pines (ForestPines), Wednesday, 14 September 2005 14:34 (twenty years ago)

one month passes...

Hello... hello... again with the stupidly ridiculous questions, but I can't seem to find this anywhere.

How can I set up a freeform sized report? i.e. one that is not bound to the printer page sizes for its width. I'm designing a report for export only, which will never be printed, so it doesn't matter WHAT the page size is. But still, it won't let me go over the boundaries of what the printer setup is.

Is this even possible?

― Paranoid Spice (kate), Tuesday, 18 October 2005 10:41 (twenty years ago)

Never mind, I cracked it as soon as I posted that. You just change the printer to MS Office Image Writer and set up a custom page size as big as you like.

― Paranoid Spice (kate), Tuesday, 18 October 2005 10:55 (twenty years ago)

Sniff I think I was just insulted by the server. It said "you poxy fule." Sniff this probably isn't the right space but it was sad and upsetting and traumatising :(

― salexander / sophie (salexander), Tuesday, 18 October 2005 11:09 (twenty years ago)

You've received your first poxy-fuling? It's a rite of passage, you know.

― Forest Pines (ForestPines), Tuesday, 18 October 2005 11:10 (twenty years ago)

As far as I know, the ILX server doesn't use Crystal Reports. Wouldn't it be hillarious if it did, though? I could produce usage stats and give them pretty colours! That would certainly liven up the Statscock!

― Paranoid Spice (kate), Tuesday, 18 October 2005 11:11 (twenty years ago)

Well, I'm sure you *could* do it, if there was a Windows machine that could access the database using the MySQL OBDC driver.

― Forest Pines (ForestPines), Tuesday, 18 October 2005 11:13 (twenty years ago)

Using Crystal Reports is about the worst thing ever. :(

― Jonothong Williamsmang (ex machina), Tuesday, 18 October 2005 11:21 (twenty years ago)

I like it

― Munki (nordicskilla), Tuesday, 18 October 2005 21:40 (twenty years ago)

I remember these! Wow, flashback...

― gygax! (gygax!), Tuesday, 18 October 2005 22:33 (twenty years ago)

This should be your new screenname Adam.

― gygax! (gygax!), Tuesday, 18 October 2005 22:34 (twenty years ago)

I did it, but I've totally fuX0red my printer settings for my laptop. Not sure how... Ah well. Not important as I never print.

On the whole, I've really started to appreciate Crystal. Now I know its foibles, it's a really versatile tool. Would still like to be able to script my own queries, but still.

― Paranoid Spice (kate), Wednesday, 19 October 2005 07:20 (twenty years ago)

three weeks pass...

Does anyone know if you can hook Crystal up to an Access database?

I'm going to have to link my SQL database to an Access database in order to build more advanced queries and archive datacube data - but Access report builder is such a sucky tool.

Surely I should be able to hook up a pretty pretty Crystal report to it? I hope?

― Streatham's Paisley Princess (kate), Wednesday, 9 November 2005 16:08 (twenty years ago)

There's this website called "Google" you ought to check out.

― Jdubz (ex machina), Wednesday, 9 November 2005 16:16 (twenty years ago)

Well, after some experimentation, it turns out that you can. That's a good thing to know. However, I wonder if you can re-import the report into Access as I don't know if my users will have it as a standalone.

― Streatham's Paisley Princess (kate), Wednesday, 9 November 2005 16:35 (twenty years ago)

Eep! There's some kind of Crystal Wizard attached to Access. Well, this is fun. I never knew any of this stuff was in here.

― Streatham's Paisley Princess (kate), Wednesday, 9 November 2005 16:37 (twenty years ago)

Is this.... ODBC?

― Jdubz (ex machina), Wednesday, 9 November 2005 16:38 (twenty years ago)

I thought this was about drugs!

― dog latin (dog latin), Wednesday, 9 November 2005 16:39 (twenty years ago)

If you don't have any helpful answers to contribute to this thread, then please don't post. This is a technical thread for users of Crystal, not a troll playground. Get your attention elsewhere please.

― Streatham's Paisley Princess (kate), Wednesday, 9 November 2005 16:40 (twenty years ago)

one year passes...

So, after months of not contacting me, an agency found a job to put me forward for... and it's a Crystal Reports job. I have a few years' experience of Access databases (not that anyone wants that any more) but no Crystal Reports, so I thought I'd bump this thread and see if people were still gently positive about it, hated it and wished they'd never worked with it, etc.

(Worse is that even though the agency said "oh, we know you don't have Crystal experience but that's fine, it's been relisted as a general database job" apparently there's going to be a practical test at the interview. Mew. So any general advice on how to look half-competent at it would be appreciated as I leaf through a book about it. Uh-oh. Thanks!)

― a passing spacecadet, Friday, 9 March 2007 15:50 (eighteen years ago)

You must be logged in to post. Please either login here, or if you are not registered, you may register here.