Solved

Bookmark in ssrs

Posted on 2010-09-20
24
2,556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 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