Need help ASAP with calendar datetime popup

I have a report created in ssrs. In the sql t script I used the between @.startdate and @.enddate statement for a parameter. In my report parameters dropdown I selected the timedate as my datatype. The report works fine if view it from my developement server the calander loads ok. When I view it from any other system the calender Icon is present but with not work. Any suggestions. Is there anything that needs to be put into the webconfig file to make this work. Javascript??

Hi,

Post your code. Someone will have a look at it.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Need help ASAP 3414 error SQL 2005 Developer Edition

Hello ,

This morning I was running a report in my ssrs and I had a power failure. My Machine rebooted and I can not connect to my database below is the error log can some one tell me what I need to do to recover I really do not want to have to reinstall everything if possible.

2007-06-15 07:59:49.88 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-06-15 07:59:49.88 Server (c) 2005 Microsoft Corporation.
2007-06-15 07:59:49.88 Server All rights reserved.
2007-06-15 07:59:49.88 Server Server process ID is 1600.
2007-06-15 07:59:49.88 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-06-15 07:59:49.90 Server This instance of SQL Server last reported using a process ID of 880 at 6/14/2007 5:36:25 PM (local) 6/14/2007 9:36:25 PM (UTC). This is an informational message only; no user action is required.
2007-06-15 07:59:49.90 Server Registry startup parameters:
2007-06-15 07:59:49.90 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-06-15 07:59:49.90 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-06-15 07:59:49.90 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-06-15 07:59:50.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-06-15 07:59:50.01 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-06-15 07:59:50.36 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-06-15 07:59:50.46 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-06-15 07:59:51.16 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2007-06-15 07:59:51.25 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-06-15 07:59:51.29 spid5s Starting up database 'master'.
2007-06-15 07:59:51.70 spid5s WARNING: did not see LOP_CKPT_END.
2007-06-15 07:59:51.70 spid5s Error: 3414, Severity: 21, State: 2.
2007-06-15 07:59:51.70 spid5s An error occurred during recovery, preventing the database 'master' (database ID 1) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2007-06-15 07:59:51.70 spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

The error message pretty much tells you what to do. It tellsyou to look at the SQL Server books online, it gives you an error code to look up.

A quick search of Google gives a link to Microsoft's support page, which should lead you in the right direction:

http://support.microsoft.com/kb/306366

sql

Need expression help ASAP

I have a report in ssrs where it has a colum with dates in it I has and other colum with either a $0.00 amount or and amount paid. I need a colum which which calculates only the number of days from the date colum that when the other colum =0 it gives me the total number of days. VS.net SQL 2005 SSRS C#

example of what needed:

Invoice amount | Date of Invoice| Amount received | number days past due

$100.00 06/01/2007 0.00 18 days ( this is what I need)

can some one explain.

Here are my expressions

FormatCurrency(Fields!Grossamtbilled.value)

FormatDateTime(Fields!EXPR1.Value,2)

FormatCurrency(Fields!RCVAMOUNT.Value)

I'm not sure what expression you would use when creating a report, but it would be fairly easy to do it in the original SQL Statement (by using a mixture of the CASE and DateDiff functions).

sql

Need Date in Days Expression

I have an expression in a ssrs report that is a date can some one tell me how to formati the date to show days

example my expression =FormatDate Time(Fields!BillDate.Value,2)

This shows 02/02/2007 as the date

What i need it to say 33 days how can I format my exprsion in my ssrs report to reflect this..

HI,cpowers:

What is the meaning of 'What i need it to say 33 days how can I format my exprsion in my ssrs report to reflect this..'

Would you please clarify more about your requirement?

Thanks in advance!

sql

Need current date in ssrs expression

I have a report which has a date like 01/05/2007 for a bill date. I need to create and expression which will take the current date and subtract the number of days from todays date and give a total number of days. How do I get thew current date in the expression.

the billed date expression is =FormatDateTime(Today()) - FormatDateTime(Fields!BILLDATE.value,2) I can not get this to work

Any help would be appreciated

You can execute code within a Report. You may need to do this although I think the reporting expressions should support what you are trying to accomplish. This is a good resource for you anyway though:http://support.microsoft.com/kb/920769

Hope this helps!

Matt

|||

Thanks for the link but my expression isnt working in the report can you tell me what is wrong with it.

|||

HI,cpowers:

You can try this expression:

= DateDiff("d",Fields!ShippedDate.Value ,Today() )

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Rex Lin,

That works great thanks.. What I am tring to accomplish is a 1-30 31-60 61-90 days aging

I tried to di the following expression-

=DateDiff("d",Fields!ShippedDate.Value,Today()) < 30 for the 1-30 day

=DateDiff('d".Fields!ShippedDate.Value, Today()) >30<61 for the 31-60 day I am getting a true or false instead of the days is there away to modify the expression to give only the number of days that fall within the range

|||

HI,cpowers:

Hope this will meet your requirement:

=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range")

|||I am tring to get a total for the amount owed that is over 30days but under 60days. My expression just gives me the word True does not give me the total MASRCVAMOUNT $$ in that date range. Can come one explain or help with expression...

=FormatCurrency(Sum(Fields!MASRCVAMOUNT.Value))=DateDiff("d",Fields!Expr1.Value ,Today())>31<60

I also need to no how to get total MASRCVAMOUNT over 120 days

=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range") How would I sum the MASCVAMOUNT in this date range and how can I select a between 61-91days these are my two real issues. I have the dates working.

Thanks for all the help I do appreciate it

|||

HI，cpowers：

It seems that the goal you need to achieve is not easily for expression. How about deal with it in the sql statement?

|||

Thanks for the help Rex Lin,

Ok I added the daging date to my sql script. Every thing saves ok and in my parameters there is now an new entry filter. Before I only had a StartDate, Enddate and Company. Now I have the Startdate , Enddate Company and dAging. I have tried to enter a date in to the dAging but it keeps giving me and error.This script was added in the creation of the ssrs report in the ssrs query builder. Can you explain to me what I may be missing or need to do to get my 1-30 , 31 60, 61-90 and 91 and over amounts.... The field I am collecting the amounts from is MASRECEIVL.MASRCVAMOUNT I am really stuck with this and need any help availible. Thanks

SELECT ' Language Detail by Company' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,PAYER.PAY_GROUPNAME,PAYER.PAY_COMPANY,PAYER.PAY_CITY,PAYER.PAY_STATE,PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,JOB.PATIENTID,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,INVOICE_AR.INVOICE_NO,INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,PATIENT.LASTNAME,PATIENT.FIRSTNAME,PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,PAT_SERVICES_HISTORY.LANG_TYPE,MASRECEIVL.MASRVDATE,MASRECEIVL.MASRCVAMOUNT,REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,REFERRAL_SOURCE.REF_PHONE,REFERRAL_SOURCE.REF_PHONE_EXT,REFERRAL_SOURCE.REF_FAX,REFERRAL_SOURCE.REF_EMAIL,JOB.INJURYDATE,JOB.APPT_DATE,

SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems',

SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-30 and @.dAgingDate THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age30,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-60 and @.dAgingDate-31 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age60,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-90 and @.dAgingDate-61 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age90,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-120 and @.dAgingDate-91 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age120

FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN MASRECEIVL ON MASRECEIVL.INVOICE_NO = INVOICE_AR.INVOICE_NO
LEFT OUTER JOIN REFERRAL_SOURCE ON REFERRAL_SOURCE.REFERRAL_ID = JOB.ADJUSTERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE
(INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_Company like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%L') AND (MASRECEIVL.MASRCVAMOUNT > 0)
Group By
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.LANG_TYPE,
MASRECEIVL.MASRVDATE,
MASRECEIVL.MASRCVAMOUNT,
REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,
REFERRAL_SOURCE.REF_PHONE,
REFERRAL_SOURCE.REF_PHONE_EXT,
REFERRAL_SOURCE.REF_FAX,
REFERRAL_SOURCE.REF_EMAIL,
JOB.INJURYDATE,
JOB.APPT_DATE

Order By 'QTR' asc

|||

HI,cpowers:

Your sql statement is quite complicated than i expected. I have created a simple one by using Orders table in the Northwind. It brings out the sum value of 30-60 and 60-90 date intervals

select
sum(case when timespan between 0 and 29 then shipvia end )as zeroToThirty,
sum(case when timespan between 30 and 59 then shipvia end )as ThirtyToSixty
from
(select (datediff(dd,getdate(),orderdate)+4000)as timespan,*
from dbo.orders) as newtable

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Rex Lin,

I am having similar problems, except all I want is to substract current date from the promised date (date that is was suppose to ship) and be able to view only the negetive data. I tried =GETDATE()-Fields!PROMISE.Value

but it would give me an error, then I tried the format you gave cpowers

=DateDiff("d",fields!Promise.Value, Today()) but all it did was give me one number. Am I doing it wrong? What do you recomend? Please advice

Thanks

Need code examples of how to call SSRS reports from winforms app

I'm writing a vb.net winforms app and need to call up reports from SSRS. I
also need to pass in secure parameters into the report and I don't want to
pass the credentials if I don't have to. I was hoping I could call my own
web service where I can pass encrypted data to my web service which would
contain the parameters. then a business object on the server could generate
the credential needed by SSRS and pass both the credentials and decrypted
parameters into the report. Problem is I don't know how to get the report
back to the client. One idea was to render the report on the server side
and then convert its html into a byte stream and pass the byte stream as a
return value for my web service. Then my winform client could convert the
byte stream into an html document. However, I have no idea if this is
possible and need examples for the various parts.
can someone please give me advise (and preferable links to code examples) on
how to solve this problem?
Thanks.
--
moondaddy@.nospam.nospamHello,
The following link might be helpful for you:
http://www.codeproject.com/useritems/SQLRSViewer.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "moondaddy" <moondaddy@.nospam.nospam>
| Subject: Need code examples of how to call SSRS reports from winforms app
| Date: Fri, 29 Apr 2005 00:29:02 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <ujCUetHTFHA.2756@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: adsl-70-240-196-183.dsl.hstntx.swbell.net
70.240.196.183
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42254
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm writing a vb.net winforms app and need to call up reports from SSRS.
I
| also need to pass in secure parameters into the report and I don't want
to
| pass the credentials if I don't have to. I was hoping I could call my
own
| web service where I can pass encrypted data to my web service which would
| contain the parameters. then a business object on the server could
generate
| the credential needed by SSRS and pass both the credentials and decrypted
| parameters into the report. Problem is I don't know how to get the
report
| back to the client. One idea was to render the report on the server side
| and then convert its html into a byte stream and pass the byte stream as
a
| return value for my web service. Then my winform client could convert the
| byte stream into an html document. However, I have no idea if this is
| possible and need examples for the various parts.
|
| can someone please give me advise (and preferable links to code examples)
on
| how to solve this problem?
|
| Thanks.
|
| --
| moondaddy@.nospam.nospam
|
|
|sql

Neebie needing help asap restoring sql

I am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.

You dont have to reinstall SQL Server just for a power failure? Try restarting all the services - SQL server as well as IIS.