Wise Owl Answers - How do I create an optional end date parameter in Report Builder?

[@pateldh23]

Thanks Thanks thanks lot sir, Really I appreciate you sir

[@WiseOwlTutorials]

You're very welcome Dharamrajkumar, I hope it was useful!

[@pateldh23]

@@WiseOwlTutorials Very useful... Thanks lot

[@lakshmireddy7979]

Hi Sir, Can you please let me know how do we modify the above query to retrieve all the data instead of start date when nothing is given in the end date field

[@caminixam]

How can we create and handle month_end date from parameter date ? i am using Text query in rpeort builder instead of table/procedure. i am making previous month_end date from parameter date manually entered. but how can i handle it on report builder ?

[@pongpanitwannasutthiwat111]

Hi Ask for help ,I want a way to extract page numbers. Use it to check within the group. What is your duty now? ,Normally, page numbers can be used in the header and footer. But my work needs to be used with in the group.

[@Swanny74]

Thanks for this video! Super helpful as I build out my first report. I do have a question though. I have two column, one for Created Date and the other for Completed Date. Is there a way to create a new column that shows the days, hours, and minutes it took between the Created and Completed Column?

[@WiseOwlTutorials]

Hi Matt! You might find Part 7.7 in this playlist useful kzbin.info/aero/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU It explains how to use the DateDiff function in Reporting Services. I hope it helps!

[@corneliuzabolotnic]

Hello WiseOwls, thanks for the simple and clear videos. How can you create a listbox that filters accordiantly to what you type in a cell (or a text box in a form, it may be easier, I guess)? It's like when you type in the Google and its shows a list box.

[@WiseOwlTutorials]

Hi Corneliu! There's a nice solution using a Dictionary from the Scripting Runtime library shown here stackoverflow.com/questions/44076069/searchable-combo-box-with-a-list-of-sugggestion-on-a-userform? I hope it helps!

[@joanietyson2051]

Hello Can you tell me how I could add the enddate as GETDATE() ? Thanks

[@WiseOwlTutorials]

Hi! You could change the query for the dataset so that it looks like this: SELECT Film.Title ,Film.ReleaseDate ,Film.RunTimeMinutes FROM Film WHERE (Film.ReleaseDate >= @StartDate) AND (Film.ReleaseDate

[@twiddle7125]

I have a table whose rows correspond to reservations, meaning one date can have many rows associated with it. I have a pivot table based on this table. The reservation table vlookups values from another table of Events. That way I can have a slicer for Events for the Pivot Table. The problem is one date may have multiple events associated with it, so I'm left combining events in only row of the Events table like this, "Event1/Event2" but then the slicer becomes ineffective because I won't be able to select one specific event without selecting another. I've tried looking up so many different ways of coming up with a bridge table to convert the M:M to a M:1 relationship, but I haven't been successful. Do you know how/if this could be accomplished?

[@WiseOwlTutorials]

Hi there! We're talking about Excel here, right? It sounds like the best approach to model this would be to make sure that each event has a separate row in the Event table. This will mean that you have duplicate dates in the EventDate column. Load this Event table into a Power Pivot data model. You can then create a calendar or date table in your data model. The key column of this table would contain a continuous, unbroken list of unique dates. You can then create a relationship between the DateKey column of the calendar table and the EventDate column of your Event table - 1 date : Many events. Here's Microsoft's documentation on date tables, I hope it helps! support.microsoft.com/en-us/office/understand-and-create-date-tables-in-power-pivot-in-excel-1c8b072e-9108-442f-8115-c72ba796d31e?ui=en-US&rs=en-US&ad=US

[@twiddle7125]

@@WiseOwlTutorials Yes for Excel, sorry about the confusion. These pictures with captions explain it much easier. imgur.com/a/IurpzX9 So, I have previously tried what you're suggesting. The thing is it works if I want to place the Event field in the Values section of the Pivot Table which defaults to the Count of the Event. What I would like to do is place the Event in the Filter section of the Pivot Table. Despite having the bridge date table of continuous dates with the correct relationships setup, the filter is not applied correctly, so the values from the reservation table are unaffected no matter which Event is selected in the filter. I looked up the documentation and they do have a section about Holidays which is very similar to what I'd like to do, but their Holiday table doesn't have any dates with multiple holidays, so when I tried to use their lookup formula it resulted in an error due to duplicate dates.

[@WiseOwlTutorials]

@@twiddle7125 Agreed, it's difficult to describe things like this without pictures. Even with the pictures it's not entirely clear! First things first - the reason clicking an event doesn't do anything to the values is because relationships in PowerPivot only filter in one direction as indicated by the arrow on the relationship line. So, you can click a date in a slicer/filter and see all the values associated with that date and/or all the events associated with that date but using the event or the value in a slicer/filter wouldn't work because the relationship doesn't filter in that direction. If you were using PowerBI rather than PowerPivot you could change a property of the relationship to filter in both directions. You get a tantalising hint that this is possible even in PowerPivot if you choose Design | Manage Relationships but unfortunately you can't change the Filter Direction that is shown in the dialog box. Next things next - even if you could change the filter direction in PowerPivot I don't think your model would show what you want but it's difficult to tell based on the images. Event A is listed as happening on 1/1/2021 and 1/3/2021. Event B also happens on 1/3/2021. The Values table has three rows with the date 1/3/2021. How does the values table know which event, A or B the value is associated with?

[@twiddle7125]

​@@WiseOwlTutorials I inherited a workbook that essentially looks like this, where column C is a vlookup of the 2nd table. imgur.com/a/1jQqCw3 The problem with how it was setup is that in the slicer, you can't select Event A by itself, you'd have to select both "Event A" and "Event A, Event B." The problem is there are many events in that field, so you'd have to check through all slicer items to ensure you've selected all occurrences of the event you're looking for. I thought I could change the structure of the Event table to have a row for each event, but that brings me to the problem of not being able to get the filter to work in the opposite direction of the relationship, like you mentioned.

[@WiseOwlTutorials]

@@twiddle7125 Indeed, inheriting something like this is almost always worse than just building it yourself from scratch! I'm still not entirely clear on what the value table is recording. The first row has a Date of 1/4/2021, a Value of 21 and an Event of Event B, Event C. Is the value 21 related to each event separately? Or is half allocated to Event B and half to C? Or is it more complicated than that?

[@tejamarneni]

Hi, Andrew. Somehow my comment got deleted. Do you know any databases that we can download to practice SQL? Thanks for amazing videos every day.

[@WiseOwlTutorials]

Thanks Ravi! KZbin's spam filter does seem to be very aggressive recently. Several viewers have added comments that have disappeared completely - they don't appear in the spam comments section in KZbin Studio so I don't have a way to recover them. I've contacted KZbin support but they've investigated and said that there's nothing wrong. Anyway, for sample database you have the Wise Owl Movies database of course! If you'd like larger datasets then Microsoft provide AdventureWorks and Contoso sample datasets: docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms www.microsoft.com/en-gb/download/details.aspx?id=18279 I hope that helps!

[@WiseOwlTutorials]

I found the email notification of your comment but there's no sign of the comment itself anywhere in KZbin Studio. But I did follow the link to the pitch meeting for Avatar 😀 excellent, thanks!

[@tejamarneni]

@@WiseOwlTutorials I have been practicing on those databases. I also solved problems from Leetcode and Hackerrank. I was able to solve those because of the concepts you taught me. Thank you very much, Andrew.

[@WiseOwlTutorials]

@@tejamarneni That's awesome Ravi, the student has become the master 😀

[@vigneshe6329]

Hi I have multiple inbox in outlook so when I try to extract the emails it only show default inbox emails in excel can u help me to change the path to other inbox

[@vigneshe6329]

Using vba

[@vigneshe6329]

Kindly help please

[@vigneshe6329]

I created using your vba code only but when i try to change the path it giving me error only thanks in advance

[@WiseOwlTutorials]

Hi Vignesh! We have a playlist on working with Outlook using Excel VBA and video 29.10 shows you how to use another inbox kzbin.info/aero/PLNIs-AWhQzcleylKSN4MS-tJloReq0XcK I hope it helps!

[@vigneshe6329]

@@WiseOwlTutorials thank you it worked and one more help when the inbox contain recall mails it is showing debug and code to fix it

[@edbanz2012]

how to open,edit and save CSV Files vb.net?

[@WiseOwlTutorials]

Hi Ed! Microsoft have some documentation for this which you can see at the following links: docs.microsoft.com/en-us/dotnet/visual-basic/developing-apps/programming/drives-directories-files/how-to-read-from-comma-delimited-text-files docs.microsoft.com/en-us/dotnet/visual-basic/developing-apps/programming/drives-directories-files/writing-to-files I hope it helps!