Formula to determine out put of parameters

[robercr12]

Hi,

I am using Crystal v10.

I have seen this thread in the FAQ's

http://www.tek-tips.com/faqs.cfm?fid=5684

(Displaying parameters chosen using a formula)- that is displaying whatever the user has chosen. This i am able to use.

Now I want to display the output for the chosen parameter. -- Let me explain what exactly that means:

Building: 1600 Pennsylvania ave
Floor: 2,3,7,9
Rooms: 2A, 3B, 5C
Occupancy: YES
Date Range: 6/2009 to 10/2009

So the user has chosen the above parameters and specified what they are looking for. But not all rooms in all floors are occupied, so the report will show only some rooms etc but not all the rooms the user has specified ---> this is the output I am looking for.

Is there a formula to find out what that output is?

Thanks
bob

 

[elsenorjose]

Can you clarify a bit more? What exactly did the user enter and what is not being displayed?

Building: 1600 Pennsylvania ave
Floor: 2,3,7,9
Rooms: 2A, 3B, 5C
Occupancy: YES
Date Range: 6/2009 to 10/2009

Are the values 2A, 3B, 5C entered by the user? If so, what is it that you additionally want to display?

 

[robercr12]

2a,3B,5C is what the user choose from the drop down list of the parameter.

so say only 5C is the output given the combination of the date range,occupancy, building , floor etc

 

[elsenorjose]

So you only want to see 5C, not the actual parameters entered? If so, just display the field value. If however, you want to see the parameters selected, regardless of the data returned, you could try modifying this:

http://tek-tips.com/viewthread.cfm?qid=1575538&page=1

CoSpringsGuy helped me with that and it displays all the parameters selected even if there are no records displayed.

 

[lbass]

You could show those actually used by inserting a crosstab that uses the field as the row. Only those with records would show up. Thic could be placed in the report header or footer, with the grid removed and the summary field suppressed.

You could alternatively collect unique values in a variable to be displayed in the report footer.

-LB

 

[robercr12]

Lb,

Can you expand on this "----You could alternatively collect unique values in a variable to be displayed in the report footer."

Thanks
bob

 

[lbass]

Create a formula like this and place it in the detail section:

whileprintingrecords;
stringvar rooms;
if not({table.room} in rooms) then
rooms := rooms + {table.room} + ", ";

Then in the report footer use this formula:
whileprintingrecords;
stringvar rooms;
if len(rooms) > 2 then
left(rooms,len(rooms)-2)

-LB

 

[robercr12]

so I have to replace "rooms" and "table.room" with my field value -- right?

what about:

if len(rooms) > 2 then
left(rooms,len(rooms)-2)

what is "len" ??

sorry about my noob questions

THANKS
bob

 

[lbass]

rooms" is how I named the variable--it could be named "x". Leave that as is. Just replace {table.rooms} with the field that gives you the room info. You would create separate variables like this for each field that you limit with a parameter.

-LB

 

[robercr12]

I believe it's working. I can confirm better tomorrow

Thanks
bob

 

[lbass]

P.S. len = "length" and tests the length of the result and the final clause then removes the last comma and space.

-LB

 

[robercr12]

LB,

regarding what you said earlier

"---Then in the report footer use this formula:
whileprintingrecords;
stringvar rooms;
if len(rooms) > 2 then
left(rooms,len(rooms)-2)"

Instead of report footer i put this in page footer -- it works fine.

Say i wanted to display this info in the GH etc -- i actually tried that -- but it did not work.
Is there any other way to do achieve this?

thanks
bob

 

[lbass]

If you place the display formula in the page footer, it will only show the values up to the end of that page, but maybe that works for you.

Notice that the first formula is accumulating data with: rooms := rooms + //etc.

Because of this, the results are only accurate in footer sections. If you want to display the same info, but in a report header section, you would need to insert a crosstab as mentioned earlier. It doesn't make sense to place a crosstab in a group header section in this case as the results would only reflect those values represented in the group--not the entire report.

-LB

 

[robercr12]

This report is a drill down report. When i put the formula in the report footer --- the values change according to what is in that particular page.

How can the formula be modified to show the * same output for the report in *** ALL the drill down pages (when placed in the page footer)?

thanks for your patience in replying to all my questions

 

[lbass]

If you put a crosstab in the report footer of the main report, and then go to file->report options->check: show all group headers on drilldown, then the correct results will appear in the crosstab upon drilldown. Do not use the page footer. If you want the crosstab to only show at a certain level of drilldown, you can suppress the report footer using a formula like this:

drilldowngrouplevel < 2 //or whatever levels you want suppressed

-LB

 

[robercr12]

how do i suppress cross - tab summary? It seems it cannot be done from within the cross tab itself

how else?

bob

 

[robercr12]

i see it grand totals can be suppressed from Customize tab.

But when i suppress row grand totals -- the cross tab does not show at all

 

[lbass]

In preview mode, select the summary->right click->format field->check suppress. Also go into customize style tab->format gridlines and uncheck "show gridlines"--if you want to eliminate the grid.

-LB

 

[robercr12]

Let me know if this is possible (or makes sense)

I want the output to represent the output for the entire report BUT at the same time i want to show it at the bottom of every page (page footer? -- 'cause report footer cannot be shown on every page) -- is this possible? (you might have answered it in an other way but i wanted to be sure)

thanks

 

[lbass]

No, crosstabs cannot be inserted in page footers. The other alternative, using variables, will not work in drilldown as far as I know.

-LB