Sharepoint designer 2007

Hi,

I need to produce a report in Sharepoint Designer 2007 to display data from a Sharepoint Services 3.0 list.
In the list there is a date field which I use as the date range to filter the data being displayed.
This is working as intended, but the problem is that the date range for the report is embedded in the report, so when I need a different date range I have to modify the report in SPD.

I need the facility to have two fields (DateFrom: and DateTo) that the user can fill and a button or link that will run the report when the user clicks on it.
Any help or hints will be appreciated.
caratechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmitKBCommented:
Suppose your report url is: http://moss/report.aspx

Create a new page dateinput.aspx and have two textboxes for each date and a button called "Report". Use javascript for "Report" button click event, read those two date textboxes values and pass as querystring parameter to repot page. for example clicking on "Report" button will redirect to page http://moss/report.aspx?DateStart=1/1/2011&DateEnd=1/31/2011
(Not you need to replace "/" with "%2F" for url encoding.)
Now use the querystring parameter in your report to read those values. SharePoint Designer allowed you to use querystring as input parameter.

Amit
0
caratechAuthor Commented:
Thanks for your reply.

I understand your comment but I'm not sure how to implement it.
I created an aspx page and added the two date fields and a button, however, I can't figure out how to add javascript code to the button (or even how to construct the script).
Could you provide samples or perhaps a link to some example out there?
Many thanks.

Tony
0
AmitKBCommented:
Hi!
Here is the html code you need to put in date input page.
Input dates and click on "Show Report" button and it will redirect you to Report.aspx page.
Please remove the "Report.aspx" in your code with your actual page name or url.
<span>DateFrom</span><input id="txtDateFrom" type="text" /><br/>
	<span>DateUpto</span><input id="txtDateUpto" type="text" /><br/>
	<input id="btnReport"  type="button" value="Show Report" onclick="ShowReport();" />
	<script type="text/javascript">
		function ShowReport()
		{
			var ReportPageUrl;
			ReportPageUrl = location.href="Report.aspx" + "?DateFrom=" + 
					encodeURIComponent(document.getElementById('txtDateFrom').value) + 
					"&" +
					"DateUpto=" + 
					encodeURIComponent(document.getElementById('txtDateUpto').value);
			location.href = ReportPageUrl					
		}
	</script>

Open in new window

reportpage.png
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

caratechAuthor Commented:
Hi, Thanks for your prompt reply.

It redirects to the report page as intended. However, I cant figure out how to pass the date parameters to the report page. I attach an image of the report page that shows how I do the filtering at the moment.
Guess I need to replace the two DATE fields values with the dates passed by the redirecting page, but I can't figure out how to do it, sorry, I'm not a programmer :(
I'm almost there and any help will be really appreciated...!

Many thanks

Tony
sample.png
0
AmitKBCommented:
Hi!
I have did a screen recording for you, please download this here http://amitbhagat.name/temp/SharePointFilterUsingQuerystring.wmv

Thanks,
Amit
0
AmitKBCommented:
Here is screencast 1
AmitKB-439734.flv
0
AmitKBCommented:
Please ignore the screencast. I am requesting to delete that.
0
caratechAuthor Commented:
Hi Amit,

That is great! thank you very much.
I notice that you used the Title field in your example, presumably it will work with a date field also?
Using your method, could I replace all the clauses in my original filter with string variables and pass those from the redirecting page?

Many thanks for all your help, I wasnt exptecting a video but I really appreciate it!

Tony
0
AmitKBCommented:
First of all you don't have to use XSLT filtering, I have mistakly did so. In next video I am showing how to do in better way.
Second thing is that with Date filtering it is not working. I am checking on how to do that.
You can pass your all the string variables to the Report page in querystring but you havet to url encode it to pass properly.

I am working on your question and back with some solution.
0
AmitKBCommented:
Hi!
Ok, here is the final one.
First of all filtering by date issue has been fixed. Acutally we need to pass the date in "YYYY-MM-DD" format. So I used javascript to read the date input (in US format mm/dd/yyy) and then covert it to "YYYY-MM-DD" format and then pass in querystring.

Second thing is that in this case I have not used XSLT filtering which is slow.

See the video and let me know your comments.

Thanks,
Amit
SharePointFilterUsingQuerystring.flv
0
caratechAuthor Commented:
Hi,

OK, I have downloaded the video and watcched it, thank you. You really are being VERY helpful.

Still does not work for me, I noticed that the javascript you are using in your latest video is different to the one you gave me earlier.
I have modified mine to match yours, the only difference is the Date fields names, which I have changed to mathc the names in my SharePoint List.

When I run ReportDateInput.aspx and enter date range the Report.aspx page opens, but it doesn't display any records.
I guess that ther is something wrong either with my code or with the way the date is formatted in my sharepoint list.


<span>DateFrom </span><input id="txtDateFrom" type="text" /><br/>
	<span>DateUpto&nbsp; </span><input id="txtDateUpto" type="text" /><br/>
	<input id="btnReport"  type="button" value="Show Report" onclick="ShowReport();" />
	<script type="text/javascript">
		function ShowReport()
		{
			GetFormattedDate(document.getElementById('txtDateFrom').value);
			var ReportPageUrl;
			ReportPageUrl = location.href="Report.aspx" + "?StartDate=" + 
			        GetFormattedDate(document.getElementById('txtDateFrom').value)+
					encodeURIComponent(document.getElementById('txtDateFrom').value) + 
					"&" +
					"DueDate=" + 
					GetFormattedDate(document.getElementById('txtDateUpto').value);
			location.href = ReportPageUrl					
		}
		
		function GetFormattedDate(value)
		{
			var y,m,d;
			y = value.split("/")[2];
			m = value.split("/")[0];
			d = value.split("/")[1];
			
			return (y+"-"+m+"-"+d);
		}
	</script>

Open in new window

I enter this on the date fields:
1/1/2010
1/7/2010

This is what I can see on the browser address when I click on Show Report button:
http://companyweb/contracts/Report.aspx?StartDate=2010-1-11%2F1%2F2010&DueDate=2010-7-31

Open in new window

0
caratechAuthor Commented:
Hi,

I have found an error in the JavaScrit I copied.
Removed and tried again, still doesn't return any records.
When I look at the web browser address bar, the date variables seem to be formatted ok, see attached code.
It must be something to do with the way the date fields are stored in SharePoint.

Any ideas?

Thanks again for all your help so far.
http://companyweb/contracts/Report.aspx?StartDate=2010-1-1&DueDate=2010-12-31

Open in new window

0
AmitKBCommented:
Sorry, I forgot to attache the code, so here it is.
I don't know why that is not working for you.
Have you set parameter well? Can you do the screen record and upload there?

Amit
<span>DateFrom</span><input id="txtDateFrom" type="text" /><br/>
	<span>DateUpto</span><input id="txtDateUpto" type="text" /><br/>
	<input id="btnReport"  type="button" value="Show Report" onclick="ShowReport();" />
	<script type="text/javascript">
		function ShowReport()
		{
			GetFormattedDate(document.getElementById('txtDateFrom').value);
			var ReportPageUrl;
			ReportPageUrl = location.href="Report.aspx" + "?DateFrom=" + 
					GetFormattedDate(document.getElementById('txtDateFrom').value)+ 
					"&" +
					"DateUpto=" + 
					GetFormattedDate(document.getElementById('txtDateUpto').value);
			location.href = ReportPageUrl					
		}
		
		function GetFormattedDate(value)
		{
			var y,m,d;
			y = value.split("/")[2];
			m = value.split("/")[0];
			d = value.split("/")[1];
			
			return (y+"-"+m+"-"+d);			
		}
	</script>

Open in new window

0
caratechAuthor Commented:
Thanks,

Code is the same to what I have now...
What do you use to record?
0
AmitKBCommented:
I use Camtesia but you can use free one http://camstudio.org/.
Other way is that you share you screen with me using TeamViewer (TeamViewer.com). Install it and run it and pass me ID and Pwd and I can see your screen.

Amit
0
caratechAuthor Commented:
TeamViewer
Session ID: 577 158 921
Password: 1074

Thanks
0
AmitKBCommented:
ok, here is the final solution you can check.
The code can be used to input date range.
Amit
<span>DateFrom</span><input id="txtDateFrom" type="text" /><br/>
	<span>DateUpto</span><input id="txtDateUpto" type="text" /><br/>
	<input id="btnReport"  type="button" value="Show Report" onclick="ShowReport();" />
	<script type="text/javascript">
		function ShowReport()
		{
			GetFormattedDate(document.getElementById('txtDateFrom').value);
			var ReportPageUrl;
			ReportPageUrl = location.href="Report.aspx" + "?DateFrom=" + 
					GetFormattedDate(document.getElementById('txtDateFrom').value)+ 
					"&" +
					"DateUpto=" + 
					GetFormattedDate(document.getElementById('txtDateUpto').value);
			location.href = ReportPageUrl					
		}
		
		function GetFormattedDate(value)
		{
			var y,m,d;
			y = value.split("/")[2];
			m = value.split("/")[0];
			d = value.split("/")[1];
			
			return (y+"-"+m+"-"+d);			
		}
	</script>

Open in new window

SharePointFilterUsingQuerystring.flv
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
caratechAuthor Commented:
One more question:

It is working now, but it only returns 10 rows in the report, I know that there are more records than that matching the criteria.
Any ideas where I nee to look?

Thanks again...
0
AmitKBCommented:
Are you using Paging?
Make sure as in attached screenshot.
Amit
dvpaging.png
0
caratechAuthor Commented:
Yes, I have already checked that, weird...
0
caratechAuthor Commented:
I have fiugured out...

It has to do with the default size of the screen area on the report.
The records are there, but they are not showing.
Made the screen area bigger and now I get a scroll bar.
Need to figure out how to make the screen size so that it is maximized when the report opens.
0
caratechAuthor Commented:
OK, I have sorted it now.
Created new report from an existing working report.
Working as required.
You have been extremely helpful and really deserve the 500 points.
Thank you.
0
caratechAuthor Commented:
You have been the most helpful expert I have had helping me so far.
THANK YOU
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.