xtrout


Hello All,

I am trying to set parameter in reporting services that lets the user select "All Hours" or "Cour Hours" of the day in a given date range.

Core hours would filter the transactions (and aggregations) that happened between 6am and 10pm on certain days and another range of hours on some days. The days that the core hours exist will be static. i.e. every day the core hours are 6am to 10pm but on Mondays the core hours are Midnight to 2am and then 6am to 11:59:59.

I am trying to figure out what that code would look like. Any help is appreciated.

- Clint




Re: Time Parameter expression needed

LG1815


Hopefully not pointing out the obvious.

Without having the code in front of me, I would most likely try to do this within the dataset which means you have to check for day of the week and possibly parse out the time portion of your date field that you need to grab. Additionally, I would think that the parameter needs to drive an upper and lower bound set of parameters that then could be used in the dataset SQL. Or the value for the core hours parameter could be one huge iif like statement that is used to hide or display rows.






Re: Time Parameter expression needed

LG1815

Try this. Part of this may need to be modified based on your hour formatting , but....

These expressions are in the visibility property for each detail line:

If the conditions are met it returns a "True" to the hidden option.

Detail Line 1 has

=Parameters!Report_Parameter_0.Value=True

Detail Line 2 has

=(Parameters!Report_Parameter_0.Value=true

and ((weekday(Fields!time_idx.Value)<> 2

and datepart("h",Fields!time_idx.Value) < 6

or datepart("h",Fields!time_idx.Value) > 22)

or

(weekday(Fields!time_idx.Value) =2

and (datepart("h",Fields!time_idx.Value) > 2

and datepart("h",Fields!time_idx.Value) < 6))))

or Parameters!Report_Parameter_0.Value=false

The Parameter is show core hours, Yes or No (evaluates to true or false)

I did not do extensive testing, but a quick check indicated that it worked.






Re: Time Parameter expression needed

xtrout

Thanks LG. Ill give it a shot.



Re: Time Parameter expression needed

xtrout

Are those two different parameters expressions, or should they be in the same expression



Re: Time Parameter expression needed

LG1815

I used only one parameter that was yes or no (Show Core Hours Only)





Re: Time Parameter expression needed

xtrout

Hmmm not working for me. I get an error that says that fields cannot be used in a report parameter.



Re: Time Parameter expression needed

LG1815

All right, I think I misread your previous posting.

The parameter is a yes or no label with a value of true or false. True respresents show only core hours.

The report itself has two detail lines with the first detail line having the following code in the hidden property (I would just try it with the date/time field on each line of the table to start)

=Parameters!Report_Parameter_0.Value=True

And the other set of code is in the second detail line hidden property.

=(Parameters!Report_Parameter_0.Value=true

and ((weekday(Fields!time_idx.Value)<> 2

and datepart("h",Fields!time_idx.Value) < 6

or datepart("h",Fields!time_idx.Value) > 22)

or

(weekday(Fields!time_idx.Value) =2

and (datepart("h",Fields!time_idx.Value) > 2

and datepart("h",Fields!time_idx.Value) < 6))))

or Parameters!Report_Parameter_0.Value=false

If this does not work for you. Attach 5-10 examples of the date/time field and I will try to send you the rdl code within the next few days.





Re: Time Parameter expression needed

xtrout

Ok. I kind of see what you mean. I guess my main question is about the "Hidden Property"....what and where is that Does that go back in the data tab where you set your main query or is that part of the parameter screen

Thanks for your patience,

Clint





Re: Time Parameter expression needed

LG1815

It is part of the properties window for the detail line.

If you select the whole detail line, right click, look for properties at the bottom.





Re: Time Parameter expression needed

xtrout

Would that work on a group I only have one detail line that aggregates the data.

I have a total line and grouping on the datetime.





Re: Time Parameter expression needed

LG1815

Not sure. I think it should, but without the actual report in front of me I do not want to give you the wrong answer. You may have to create a second detail line to do this. The other option is to implement this logic with the T-SQL data grab using a case statement or pre-identifying the "Core" records with some type of flag and using the parameter/flag combination to get the right data. Frankly in the long run, that might be easier than messing with hidden records, etc.



Re: Time Parameter expression needed

xtrout

Ok. Ill play around with the details. The parameters look good when I preview the report.

Thanks so much for all your help! You got me at least 95% there.

--Clint





Re: Time Parameter expression needed

xtrout

Hate to bug you again on this one but I am revisting the core hours and am changing them to Sun - Fri 6am to 11pm and Saturday 9am to 11pm. I used the following in the hidden field...

Code Snippet

=(Parameters!Report_Parameter_0.Value=true

and ((weekday(Fields!TestDateTime.Value)<> 7

and datepart("h",Fields!TestDateTime.Value) < 6

or datepart("h",Fields!TestDateTime.Value) > 23)

or

(weekday(Fields!TestDateTime.Value) =7

and (datepart("h",Fields!TestDateTime.Value) > 9

and datepart("h",Fields!TestDateTime.Value) < 23))))

or Parameters!Report_Parameter_0.Value=false

And it does not appear to parameterize the report. Wondering if I am missing a step.

Thanks,

Clint





Re: Time Parameter expression needed

LG1815

In middle of some other things, but will get back to you.

Did you have two detail lines with both sets of code

What happened when you ran the code for each parameter setting