Create A checklist type report 1

[ciscowiz]

I am using CR9 with MS SQL Server 2000. I have 2 views that pull back the correct data. The first view pulls back the Number of pages and the spreads they belong to, and it is always the same result set.
v_view1 Result Set:
Page Spread
B01 F01_B01
B02 B02_B03
B03 B02_B03
B04 B04_B05
B05 B04_B05
B06 B06_B07
B07 B06_B07
F01 F01_B01
F02 F02_F03
F03 F02_F03
F04 F04_F05
F05 F04_F05
F06 F06_F07
F07 F06_F07

The second view, v_view2 pulls back more specific data including if any of the above listed pages have ads placed on them, what category the ads belong to, ad size, etc. v_view2 pulls back the pages used which will correspond to one of the pages from v_view1's result set. However, it will not pull back every page unless all the pages have been used, more likely it only pulls back a couple of the 14 pages listed above.

The report I want to create will list out the result set from v_view1 such as below

GH1 F01_B01
F01
B01
F02_F03
F02
F03
F04_F05
F04
F05
etc
etc
With the spread being the group and the two pages that belong to each spread listed. I want this data to show up no matter what, whether there is an ad for that page or not. Then I want v_view2 to populate the report with its data and fill in the pages where appropriate so it will look like this when finished:
Page Category FullPage 1/2 1/4
GH1 F01_B01
D F01 Construction 1
B01 Automotive 1
F02_F03
F02
F03 Lawyer, Pizza 2
F04_F05
F04
F05
F06_F07
F06
F07
B02_B03
B02
B03 Automotive 1
etc.
etc.
Basically the User will run the report in the morning, it will populate the pages that have ads while showing which pages are open so they can fill it in themselves as the day goes. I have tried creating this report but with unpredictable results - I would be getting hundreds of records when it should have been around 41. If anyone has any ideas on how to get this done I would appreciate it.

Thanks,
bryan

 

[IdoMillet]

Use an Outer Join from the Page info to the Ad info.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ciscowiz]

Ok I did that already as well, my result set had the ad info listed and the pageinfo as nulls all the way down until my adinfo was done and it had nulls and my 14 rows of pages were listed. I guess the problem is that if you select an adinfo row, it has null page data and if you select a page row it has null adinfo data.

 

[ciscowiz]

Once I start filtering the AdInfo Data with a parameter, I lose what is supposed to be my "Static" data of the pages, only the pages and spreads in the adinfo show up.

I need one view to always show all of its data, no matter what the parameters. The parameters don't involve that view, they involve the adinfo view which needs to be filtered.

Is there any way to use the satic view and not have its rows be affected by another view with parameters? I would like to use the static view as the "backbone" of the report. It lists out all the pages possibly available then the other view will populate the pages it has and leave nulls elsewhere.

 

[lbass]

You should have a left join FROM the Pages table TO the Adinfo table. Then instead of using a record selection formula, create a formula like this to display the adinfo data:

if isnull({adinfo.page}) or
{table.fieldtodisplay} <> {?parm} then "" else
{table.fieldtodisplay}

-LB

 

[ciscowiz]

I guess I dont understand why you would check if {table.fieldtodisplay} is <> to the parameter. My parameters are for the IssueDate (DateTime) and the Distribution (String). The field I want to display is a Category field (string) and then have a few running totals based on an adsize field(string). This data is all from the adinfo view and should flow into the Pages view on the report, which would list out the 14 pages grouped by the 7 spreads.

The category will never be equal to the IssueDate or the Distribution. Wouldn't I need a record selection formula for the user to pick the particular IssueDate and Distribution?

 

[lbass]

I was attempting to show you a technique, not the specifics of it. Try something like the following:

if isnull({adinfo.linkingfield}) or//I assumed this was page
not({table.issuedate} in {?datetimerange}) or
{table.distribution} <> {?distribution}
then "" else
{table.category}

The issue is that if you use a select on the second table, you will limit records from the first. So instead, use the parameters in conditional formulas to allow all records from the first table to populate the report. It might also make sense to group on the linking field from the first table, and then insert a maximum on the conditional formula and suppress the details, but I don't really have enough information to for sure.

-LB

 

[ciscowiz]

OK now it is pulling back all my records, about 250, and even with the parameters set, all the pages show up, which is what I wanted. However, there is a record that shows up in the report in the details section for all 250, most are just blanks textboxes.

 

[ciscowiz]

Im sorry I am such a noob, I have tried suppressing the detail section with isnull(Table.field) etc. but how can I roll up the blank records in the details section. The report has all the correct pages and correct adinfo, its just got a ton of whitespace now.

 

[lbass]

In the section expert, format the detail section to "suppress blank section".

-LB

 

[ciscowiz]

Lol figured it was something simple like that. Thanks again man, you always come through.

bryan

 

[ciscowiz]

I am having trouble getting the proper count and for some reason a blank field is being displayed. My report looks like this:

"BLANKBOX" is a blank textbox you can highlight when viewed in the designer.
Page Code AdSize Count
__________________________________________________________
GH1 -- F01_B01
D -- B01 BLANKBOX BLANKBOX
D -- "Wireless" FullPage [1]
D -- F01 BLANKBOX BLANKBOX
GF1 -- (This section currently has no fields)
GH1 -- F02_F03
D -- F02 Automotive 1/2 Page
D -- Construction 1/2 Page [2]
D -- F03 Real Estate FullPage [1]
GH1 --
etc. all the way down to
GH1 -- B06_B07
D -- B06 BLANKBOX BLANKBOX
D -- B07 BLANKBOX BLANKBOX
GF1 --

I want the count per page of the number of ads, so for page "F02" I do a running total on on "adsize" where it is = to the adsize I am counting for. Now i have tried adding the parameters into the RT formula but the RT still ends up insertting itself on all the suppressed records i am hiding so the report ends up having 250 records again with a bunch of blanks and my RT field. I just want it to count what is showing and not bother with all the suppressed records. The thing is when you run the report it still says its pulling back 250+ records but only the ones I want are showing up yet it seems like the RT is still counting all of them.
Also, this isn't too big of a deal but for some of the pages, like page "B01" in this example, The first record listed next to the page is just a blank box and then on the next detail line it lists the "Code" and the "AdSize". It should just be on the top line with nothing underneath it. The Page "F01" is fine in listing the two BLANKBOX fields, thats how I want it. Anyway the count is the most important thing to me right now the rest is aesthetics. Let me know if you need anymore info. Thanks again - Bryan

 

[lbass]

In the running total, in the evaluation section, choose "use a formula"->x+2 and enter:

{@myformula} <> ""

...where my formula is the suggested formula I gave you.

Where are these text boxes coming from? What are they for?

-LB

 

[ciscowiz]

Thanks for the formula tip, working fine now.
As for the BLANK textboxes, they are just record entries, I can't really explain it. I modified it to display the "Code" the way you told me to from a different thread, where you create 3 formulas and display it on the group footer to basically concatenate two "Codes", I dont know if you remember that, and now it is showing up exactly how I want - the Blank Boxes are gone and only shwoing up where appropriate, ie. when there is no ad.
I think it was displaying the blanks because if I uncheck the "DATABASE" -> "Select Distinct Records" option, there are multiple records listing for the page:
GH1 F01_B01
D B01
D B01
D F01
D B01
D F01
D F01
GF1
GH1 F02_B02
D F02
D F02
etc.etc.
whereas with "Database -> "Select Distinct Records" checked, I get the listing I want of two distint pages per group. There is still something screwy with the data, I guess, seems to be pulling a record For the page with no other fields that have data. Thanks again LB!
Bryan