display data based on the parameter 1

[ridhirao22]

Using CR2008
I have a report by date shows orders for US. I need to change report to show by state. So, I have added state as optional parameter and default to Total US if the optional {?}, is null/blank
I have summary the data in the temp table with states data and US data (summary)

How can I display the report data to show US data? When the ?State is left empty.
Report is group by date (daily) and its display only the first state value (as the fields are placed in the GH)


TIA,
RR

 

[jlopeman]

Create a cascading parameter. First parameter being Country and then State. If you want to see a particular state's sales, you can select just that state. If you want to see all of US's sales, select all states with the >> button.

 

[ridhirao22]

I can't use dynamic parameter. Some reason they don't work thru CE
Any other alternative?

 

[lbass]

Please show how the data displays in the detail section of the report if both state and US are placed there (without using the parameter). Also, does the {?State} parameter allow you to choose specific states? Or is it a boolean saying show by state or not?

-LB

 

[ridhirao22]

DATE Loc Orders

01/30/2011 TX 117
01/30/2011 VA 0
01/30/2011 MI 17
01/30/2011 AZ 5,648
01/30/2011 US 5,782

?state is editable. when ?state is left blank I need to see
01/30/2011 US 5,782
other wise any state input for ?state ex: ?state= AZ
01/30/2011 AZ 5,648

TIA,

 

[lbass]

Try something like this for your record selection formula:

(
(
not hasvalue({?state}) and
{table.loc} = "US"
) or
{table.loc} = {?state}
)

-LB

 

[ridhirao22]

I try this and when refresh the report it takes me to the record selection formula and say parameter has no value and highlights the {table.loc} = {?state}

 

[ridhirao22]

Sorry I change the formula to IF then else. It worked.

Thanks a lot LB!

 

[lbass]

You shouldn't have had to change it to if/then/else. When you tried my suggestion, what did you enter?

-LB

 

[ridhirao22]

copy paste and chage the table field
save with no errors, but when refreshed therecord selection pops up and alters no parameter value highlighting the "OR" part.

 

[lbass]

You are using CR 2008, right? I can't test this right now, but what if you changed it to:

(
(
not hasvalue({?state}) and
{table.loc} = "US"
) or
(
hasvalue({?state}) and
{table.loc} = {?state}
)
)

-LB

 

[ridhirao22]

This worked

 

[ridhirao22]

LB,

Your suggestion to change the record selection has worked but just curious to know how would the report be effected if I used IF-then-else (which also worked).

In case, I have a custom value to be passed for the {?state}...
for ex:

Passing EST then I will need look for states in the eastern US

if {?state} = 'EST' then
{table.loc} in [NY,CT,PA,MA..]

TIA,
RR

 

[lbass]

You can use if/then or and/or. The key thing is to check whether all selection criteria are passing to the SQL query (database->show SQL query). To write your if/then in and/or language:

(
{?state} = 'EST' and
{table.loc} in ['NY','CT','PA','MA']
) or
(
{?state} = 'WEST' and
{table.loc} in ['CA','OR', 'MT']
) or
//etc.

-LB