Solved

Bookmark in ssrs

Posted on 2010-09-20
24
2,477 Views
Last Modified: 2012-05-10
Hi,

I need to display bookmark for date fields which are having data for the upcoming month.

Can you please help me regarding this.

Please let me know if you have any questions.

Its urgent!!
0
Comment
Question by:Mounikaa
  • 14
  • 9
24 Comments
 

Author Comment

by:Mounikaa
ID: 33718489
Hello..

Any body is there to help me!!

Every time I post a question I am not getting any response.

Please help me..
0
 
LVL 2

Expert Comment

by:JimyLee
ID: 33718622
I don't understand what you want to display.  Are you listing a bunch of dates and want to change the appearance of ones that have data to for that date?
0
 

Author Comment

by:Mounikaa
ID: 33718748
Can you tell me bookmark syntax?
and how to use that
I want to display the bookmark for dates. i.e. listing bunch of dates that have values in report
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Expert Comment

by:aelliso3
ID: 33718853
Try this page and see if it helps out ... I assume that you are speaking of the Bookmark property
http://www.exforsys.com/tutorials/ssrs/create-bookmark.html
 
0
 

Author Comment

by:Mounikaa
ID: 33719880
I have to create a bookmark as per the link.

But, have you worked on ssrs 2008?
I am not seeing bookmark id where I have to give the expression to jump to specific portion of the report.
please help me!!

LET ME KNOW IF U HAVE ANY QUESTIONS..
0
 

Author Comment

by:Mounikaa
ID: 33719948
DO u have any idea about displaying distinct fields in ssrs?
0
 

Author Comment

by:Mounikaa
ID: 33719953
Please reply...
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33719994
Somewhere on the report there has to be a place to select the date that you want to jump to. Where is this place going to be ... In the parameter bar at the top?
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33720112
Distinct fields in SSRS are pretty simple ... create a dataset
SELECT DISTINCT Column1 FROM Table1
just put it in a table by itself, or assign to a parameter for user selection
0
 

Author Comment

by:Mounikaa
ID: 33720451
Hi aelliso3,

Thanks for the solution for the distinct.

That workes for me.


For the bookmark one:

I have created 2 different list reports in the same report.
One is for only distinct dates
other one is for the report with all the data.
I have to create a bookmark so that I can jump from distinct dates report to another report according to the dates.
i.e. if I click a date in one report, I have to go to the specific location in the second list report.

Hope you understood.

Please help regarding this.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33720653
I believe that I know what you are trying to do, but you kind of have to trick the system a bit. I do not believe that you can put bookmarks on a datetime datatype field. However, you can convert that to a varchar field in your SQL query. On the list that you would select the date from, Right click and go to TextBox Properties --> Action and select Go To Bookmark then select the bookmark field to use. On the list that has the date that you want to jump to, select the box that has the field in question, and in the properties window, you'll see an option for Bookmark. In there, select the Date field that you want to use.
0
 

Author Comment

by:Mounikaa
ID: 33720768
what is the syntax for setting bookmark?

"Bookmark" & field.value

Was the above one correct?

I think I need to give sam esyntax at both places.

Please correct me if I am wrong.

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33721074
you should be able to select it all in the drop down list ... but it would be
=Fields!DateField.Value
0
 

Author Comment

by:Mounikaa
ID: 33721089
Is that the same for both fields?

Infact I tried that too.
0
 

Author Comment

by:Mounikaa
ID: 33721102
[rsInvalidExpressionDataType] The Bookmark expression used in text box ‘FROM_EPOCH_TIME_CHANGE_BEGINS_2’ returned a data type that is not valid.

I am getting the above error
0
 

Author Comment

by:Mounikaa
ID: 33721135
Can we apply bookamark on group?
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33721282
Well ... like I said before ... you cannot use a date as a bookmark. It's one of those strange quirks about SSRS, so you'll have to handle that in the query ...
example:
convert(varchar(10), DateField)
 
This goes into the stored procedure, view, or query that is in your dataset
0
 

Author Comment

by:Mounikaa
ID: 33721337
can you give me the exact syntax to convert the date fields?

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33721383
if you can supply the query that drives the report, then that shouldn't be a problem.
0
 

Author Comment

by:Mounikaa
ID: 33721398
CREATE OR REPLACE
PROCEDURE DISTINCTDATE

(OUT_CURSOR OUT SYS_REFCURSOR )
as

 CurrentDate DATE;
StartDate DATE;
 EndDate DATE;
 
 BEGIN
 
 CurrentDate := TRUNC(SYSDATE);
 StartDate := CurrentDate +1;
 EndDate := CurrentDate +31;
 
  OPEN OUT_CURSOR FOR
 SELECT DISTINCT FROM_EPOCH(Time_Change_Begins)
      FROM TABLE
      WHERE  FROM_EPOCH(Time_Change_Begins) >= StartDate
 AND FROM_EPOCH(Time_Change_Begins) < EndDate;
 END DISTINCTDATE;
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33721435
Sorry ... I forgot that your datasources are Oracle and I do not know that syntax ... The best advice that I can give is to post that code to an Oracle thread and see if someone knows how to convert the date to varchar. Judging by the error that you are getting, I think you got the SSRS part correct.
0
 

Author Comment

by:Mounikaa
ID: 33721467
I have one more doubt.

Will my report work if I convert date to varchar?

Coz, I need to get data for upcoming month. So I 've used date fields here.

Will my report going to give data if I convert them to varchar?

Please let me know...
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33721486
it will show the date, and the only concern that I can see is that it may cause some sorting issues.  Reason:  in varchar,  1/1/2010 comes before 3/2/2009 because it's going to look at the first character to do the sorting.
The best way to handle this is to have the procedure return 2 date columns. One is the date in varchar, and one is the date in the same datatype that you have it now.
you can hide the one that is returned in varchar, but it will still be used for the bookmark.
 
0
 

Author Comment

by:Mounikaa
ID: 33721491
Hi,

Thanks for spending time for me.

I got the solution without converting them to varchar

Anyways,

Thanks very much..
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question