Hide data of collapsed matrix group

I have a matrix with date month on the rows axis. I want to hide all
but the most recent month. The data measures I am using are not
additive so I would like to hide the data for all collapsed groups.
Is this possible? I can't figure out how to reference a report item
to see if it is hidden or not.I got it...
--code to only show values in matrix where the group is visible.
=iif(
count(Fields!Num.Value) > 1
,""
, sum( Fields!Num.Value)
)
--code to set the most recent group to visible - default
=iif(format( Fields!Date.Value,"MMM yy") = format(
First(Fields!MaxDate.Value, "MaxDateForProject") ,"MMM yy")
, false,true)

Hidden parameters error (SP1)

I'm having problems with executing a report with a hidden parameter. For the
record, I've got SP1 installed.
I have a report with a date range that a user can modify:
From - defaults to previous business day
To - also defaults to previous business day
I also have a parameter called CurrentUserID (an integer) which is a user ID
that exists in a table called tblUser. I want this parameter to be hidden
from the user.
In Report Manager, I've gone into Properties, then Parameters, and set the
following for the CurrentUserID parameter:
Has Default - not ticked
Default Value - blank
Prompt User - ticked
Prompt String - blank
In the URL for the report, I add the following at the end:
&CurrentUserID=1083
where 1083 is a UserID I know should return some records in my test report.
However, I'm getting the following error:
This report requires a default or user-defined value for the report
parameter 'CurrentUserID'. To run or subscribe to this report, you must
provide a parameter value. (rsReportParameterValueNotSet)
I have tried the report again, this time with the CurrentUserID parameter
displayed, and entering the user ID 1083, and this time the report works
fine, so I know the stored procedure is not the problem.
Any suggestions to get this hidden parameter functionality to work would be
greatly appreciated.
Many thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)just give it a (non-used) default value fe -1
"Jadranka" wrote:
> I'm having problems with executing a report with a hidden parameter. For the
> record, I've got SP1 installed.
> I have a report with a date range that a user can modify:
> From - defaults to previous business day
> To - also defaults to previous business day
> I also have a parameter called CurrentUserID (an integer) which is a user ID
> that exists in a table called tblUser. I want this parameter to be hidden
> from the user.
> In Report Manager, I've gone into Properties, then Parameters, and set the
> following for the CurrentUserID parameter:
> Has Default - not ticked
> Default Value - blank
> Prompt User - ticked
> Prompt String - blank
> In the URL for the report, I add the following at the end:
> &CurrentUserID=1083
> where 1083 is a UserID I know should return some records in my test report.
> However, I'm getting the following error:
> This report requires a default or user-defined value for the report
> parameter 'CurrentUserID'. To run or subscribe to this report, you must
> provide a parameter value. (rsReportParameterValueNotSet)
> I have tried the report again, this time with the CurrentUserID parameter
> displayed, and entering the user ID 1083, and this time the report works
> fine, so I know the stored procedure is not the problem.
> Any suggestions to get this hidden parameter functionality to work would be
> greatly appreciated.
> Many thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)|||Along the same lines, I am creating an interface to an client application to
launch reports. I need a way to be able to set a parameter from within the
application AND allow the developer to configure this setting so the report
can be deployed with no Report Manager configuration necessary. If I clear
the prompt from the report designer and deploy, the parameter then becomes
'read-only' and I cannot set it from code.
The only work around that I can find is to go in to Report Manager and turn
ON the Prompt User, then clear the Prompt text and then adapt for that in
code. This works fine except that it means I have to go to each of the 100
client's ReportManagers and configure this report this way. With 50 reports
with hidden parameters at 100 clients, that is just not possible.
Any suggestions?
"Antoon" wrote:
> just give it a (non-used) default value fe -1
> "Jadranka" wrote:
> > I'm having problems with executing a report with a hidden parameter. For the
> > record, I've got SP1 installed.
> >
> > I have a report with a date range that a user can modify:
> > From - defaults to previous business day
> > To - also defaults to previous business day
> >
> > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > that exists in a table called tblUser. I want this parameter to be hidden
> > from the user.
> >
> > In Report Manager, I've gone into Properties, then Parameters, and set the
> > following for the CurrentUserID parameter:
> > Has Default - not ticked
> > Default Value - blank
> > Prompt User - ticked
> > Prompt String - blank
> >
> > In the URL for the report, I add the following at the end:
> > &CurrentUserID=1083
> > where 1083 is a UserID I know should return some records in my test report.
> >
> > However, I'm getting the following error:
> > This report requires a default or user-defined value for the report
> > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > provide a parameter value. (rsReportParameterValueNotSet)
> >
> > I have tried the report again, this time with the CurrentUserID parameter
> > displayed, and entering the user ID 1083, and this time the report works
> > fine, so I know the stored procedure is not the problem.
> >
> > Any suggestions to get this hidden parameter functionality to work would be
> > greatly appreciated.
> >
> > Many thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)|||Well, it's not clean but you could have a look at what exactly changes in the
reports (xml) when you change the settings in de report manager, and then
write a small program to make that change for your reports.
I hope they are going to fix this in RS2005
"rbowlin" wrote:
> Along the same lines, I am creating an interface to an client application to
> launch reports. I need a way to be able to set a parameter from within the
> application AND allow the developer to configure this setting so the report
> can be deployed with no Report Manager configuration necessary. If I clear
> the prompt from the report designer and deploy, the parameter then becomes
> 'read-only' and I cannot set it from code.
> The only work around that I can find is to go in to Report Manager and turn
> ON the Prompt User, then clear the Prompt text and then adapt for that in
> code. This works fine except that it means I have to go to each of the 100
> client's ReportManagers and configure this report this way. With 50 reports
> with hidden parameters at 100 clients, that is just not possible.
> Any suggestions?
> "Antoon" wrote:
> > just give it a (non-used) default value fe -1
> >
> > "Jadranka" wrote:
> >
> > > I'm having problems with executing a report with a hidden parameter. For the
> > > record, I've got SP1 installed.
> > >
> > > I have a report with a date range that a user can modify:
> > > From - defaults to previous business day
> > > To - also defaults to previous business day
> > >
> > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > that exists in a table called tblUser. I want this parameter to be hidden
> > > from the user.
> > >
> > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > following for the CurrentUserID parameter:
> > > Has Default - not ticked
> > > Default Value - blank
> > > Prompt User - ticked
> > > Prompt String - blank
> > >
> > > In the URL for the report, I add the following at the end:
> > > &CurrentUserID=1083
> > > where 1083 is a UserID I know should return some records in my test report.
> > >
> > > However, I'm getting the following error:
> > > This report requires a default or user-defined value for the report
> > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > provide a parameter value. (rsReportParameterValueNotSet)
> > >
> > > I have tried the report again, this time with the CurrentUserID parameter
> > > displayed, and entering the user ID 1083, and this time the report works
> > > fine, so I know the stored procedure is not the problem.
> > >
> > > Any suggestions to get this hidden parameter functionality to work would be
> > > greatly appreciated.
> > >
> > > Many thanks.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)|||Thanks for your response.
I've tried the suggestion to set a non-used default value. The report now
loads error-free, however, it comes back with no data even though I've added
&CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
return 3 records).
I've added a text box onto the report to show what's in the CurrentUserID
parameter when I run the report with &CurrentUserID=1083 in the URL. However,
it keeps showing the -1 I've set as the default. It doesn't appear to notice
the fact that I want to override the -1 default with my own user ID.
Any ideas?
Thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> Well, it's not clean but you could have a look at what exactly changes in the
> reports (xml) when you change the settings in de report manager, and then
> write a small program to make that change for your reports.
> I hope they are going to fix this in RS2005
> "rbowlin" wrote:
> > Along the same lines, I am creating an interface to an client application to
> > launch reports. I need a way to be able to set a parameter from within the
> > application AND allow the developer to configure this setting so the report
> > can be deployed with no Report Manager configuration necessary. If I clear
> > the prompt from the report designer and deploy, the parameter then becomes
> > 'read-only' and I cannot set it from code.
> >
> > The only work around that I can find is to go in to Report Manager and turn
> > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > code. This works fine except that it means I have to go to each of the 100
> > client's ReportManagers and configure this report this way. With 50 reports
> > with hidden parameters at 100 clients, that is just not possible.
> >
> > Any suggestions?
> >
> > "Antoon" wrote:
> >
> > > just give it a (non-used) default value fe -1
> > >
> > > "Jadranka" wrote:
> > >
> > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > record, I've got SP1 installed.
> > > >
> > > > I have a report with a date range that a user can modify:
> > > > From - defaults to previous business day
> > > > To - also defaults to previous business day
> > > >
> > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > from the user.
> > > >
> > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > following for the CurrentUserID parameter:
> > > > Has Default - not ticked
> > > > Default Value - blank
> > > > Prompt User - ticked
> > > > Prompt String - blank
> > > >
> > > > In the URL for the report, I add the following at the end:
> > > > &CurrentUserID=1083
> > > > where 1083 is a UserID I know should return some records in my test report.
> > > >
> > > > However, I'm getting the following error:
> > > > This report requires a default or user-defined value for the report
> > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > >
> > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > displayed, and entering the user ID 1083, and this time the report works
> > > > fine, so I know the stored procedure is not the problem.
> > > >
> > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > greatly appreciated.
> > > >
> > > > Many thanks.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)|||So you have: a default value, you've checked the box "prompt user" and have
left the prompt value blank?
If this is the case it should work.
"Jadranka" wrote:
> Thanks for your response.
> I've tried the suggestion to set a non-used default value. The report now
> loads error-free, however, it comes back with no data even though I've added
> &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> return 3 records).
> I've added a text box onto the report to show what's in the CurrentUserID
> parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> it keeps showing the -1 I've set as the default. It doesn't appear to notice
> the fact that I want to override the -1 default with my own user ID.
> Any ideas?
> Thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > Well, it's not clean but you could have a look at what exactly changes in the
> > reports (xml) when you change the settings in de report manager, and then
> > write a small program to make that change for your reports.
> > I hope they are going to fix this in RS2005
> >
> > "rbowlin" wrote:
> >
> > > Along the same lines, I am creating an interface to an client application to
> > > launch reports. I need a way to be able to set a parameter from within the
> > > application AND allow the developer to configure this setting so the report
> > > can be deployed with no Report Manager configuration necessary. If I clear
> > > the prompt from the report designer and deploy, the parameter then becomes
> > > 'read-only' and I cannot set it from code.
> > >
> > > The only work around that I can find is to go in to Report Manager and turn
> > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > code. This works fine except that it means I have to go to each of the 100
> > > client's ReportManagers and configure this report this way. With 50 reports
> > > with hidden parameters at 100 clients, that is just not possible.
> > >
> > > Any suggestions?
> > >
> > > "Antoon" wrote:
> > >
> > > > just give it a (non-used) default value fe -1
> > > >
> > > > "Jadranka" wrote:
> > > >
> > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > record, I've got SP1 installed.
> > > > >
> > > > > I have a report with a date range that a user can modify:
> > > > > From - defaults to previous business day
> > > > > To - also defaults to previous business day
> > > > >
> > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > from the user.
> > > > >
> > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > following for the CurrentUserID parameter:
> > > > > Has Default - not ticked
> > > > > Default Value - blank
> > > > > Prompt User - ticked
> > > > > Prompt String - blank
> > > > >
> > > > > In the URL for the report, I add the following at the end:
> > > > > &CurrentUserID=1083
> > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > >
> > > > > However, I'm getting the following error:
> > > > > This report requires a default or user-defined value for the report
> > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > >
> > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > fine, so I know the stored procedure is not the problem.
> > > > >
> > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > greatly appreciated.
> > > > >
> > > > > Many thanks.
> > > > > --
> > > > > Jadranka Krapic
> > > > > DBA
> > > > > Stargate Technologies
> > > > > (www.stargatetech.com.au)|||Yep, that's correct.
If I put my ID of 1083 in the default just to see if it works, my 3 records
come back fine. But when I put -1 as the default, and add the parameter to
the end of my URL (&CurrentUserID=1083), it doesn't.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> So you have: a default value, you've checked the box "prompt user" and have
> left the prompt value blank?
> If this is the case it should work.
> "Jadranka" wrote:
> > Thanks for your response.
> >
> > I've tried the suggestion to set a non-used default value. The report now
> > loads error-free, however, it comes back with no data even though I've added
> > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > return 3 records).
> >
> > I've added a text box onto the report to show what's in the CurrentUserID
> > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > the fact that I want to override the -1 default with my own user ID.
> >
> > Any ideas?
> >
> > Thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > Well, it's not clean but you could have a look at what exactly changes in the
> > > reports (xml) when you change the settings in de report manager, and then
> > > write a small program to make that change for your reports.
> > > I hope they are going to fix this in RS2005
> > >
> > > "rbowlin" wrote:
> > >
> > > > Along the same lines, I am creating an interface to an client application to
> > > > launch reports. I need a way to be able to set a parameter from within the
> > > > application AND allow the developer to configure this setting so the report
> > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > 'read-only' and I cannot set it from code.
> > > >
> > > > The only work around that I can find is to go in to Report Manager and turn
> > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > code. This works fine except that it means I have to go to each of the 100
> > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > with hidden parameters at 100 clients, that is just not possible.
> > > >
> > > > Any suggestions?
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > just give it a (non-used) default value fe -1
> > > > >
> > > > > "Jadranka" wrote:
> > > > >
> > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > record, I've got SP1 installed.
> > > > > >
> > > > > > I have a report with a date range that a user can modify:
> > > > > > From - defaults to previous business day
> > > > > > To - also defaults to previous business day
> > > > > >
> > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > from the user.
> > > > > >
> > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > following for the CurrentUserID parameter:
> > > > > > Has Default - not ticked
> > > > > > Default Value - blank
> > > > > > Prompt User - ticked
> > > > > > Prompt String - blank
> > > > > >
> > > > > > In the URL for the report, I add the following at the end:
> > > > > > &CurrentUserID=1083
> > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > >
> > > > > > However, I'm getting the following error:
> > > > > > This report requires a default or user-defined value for the report
> > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > >
> > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > fine, so I know the stored procedure is not the problem.
> > > > > >
> > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > greatly appreciated.
> > > > > >
> > > > > > Many thanks.
> > > > > > --
> > > > > > Jadranka Krapic
> > > > > > DBA
> > > > > > Stargate Technologies
> > > > > > (www.stargatetech.com.au)|||can you give the complete url
"Jadranka" wrote:
> Yep, that's correct.
> If I put my ID of 1083 in the default just to see if it works, my 3 records
> come back fine. But when I put -1 as the default, and add the parameter to
> the end of my URL (&CurrentUserID=1083), it doesn't.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > So you have: a default value, you've checked the box "prompt user" and have
> > left the prompt value blank?
> >
> > If this is the case it should work.
> >
> > "Jadranka" wrote:
> >
> > > Thanks for your response.
> > >
> > > I've tried the suggestion to set a non-used default value. The report now
> > > loads error-free, however, it comes back with no data even though I've added
> > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > return 3 records).
> > >
> > > I've added a text box onto the report to show what's in the CurrentUserID
> > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > the fact that I want to override the -1 default with my own user ID.
> > >
> > > Any ideas?
> > >
> > > Thanks.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > reports (xml) when you change the settings in de report manager, and then
> > > > write a small program to make that change for your reports.
> > > > I hope they are going to fix this in RS2005
> > > >
> > > > "rbowlin" wrote:
> > > >
> > > > > Along the same lines, I am creating an interface to an client application to
> > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > application AND allow the developer to configure this setting so the report
> > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > 'read-only' and I cannot set it from code.
> > > > >
> > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > >
> > > > > Any suggestions?
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > just give it a (non-used) default value fe -1
> > > > > >
> > > > > > "Jadranka" wrote:
> > > > > >
> > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > record, I've got SP1 installed.
> > > > > > >
> > > > > > > I have a report with a date range that a user can modify:
> > > > > > > From - defaults to previous business day
> > > > > > > To - also defaults to previous business day
> > > > > > >
> > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > from the user.
> > > > > > >
> > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > following for the CurrentUserID parameter:
> > > > > > > Has Default - not ticked
> > > > > > > Default Value - blank
> > > > > > > Prompt User - ticked
> > > > > > > Prompt String - blank
> > > > > > >
> > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > &CurrentUserID=1083
> > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > >
> > > > > > > However, I'm getting the following error:
> > > > > > > This report requires a default or user-defined value for the report
> > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > >
> > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > >
> > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > greatly appreciated.
> > > > > > >
> > > > > > > Many thanks.
> > > > > > > --
> > > > > > > Jadranka Krapic
> > > > > > > DBA
> > > > > > > Stargate Technologies
> > > > > > > (www.stargatetech.com.au)|||Sure...URL when viewing the report via Report Manager is as follows
http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
'&CurrentUserID=1083' is the user ID that, when used with the below
defaults, I know should return 3 records.
I have a number of parameters for this report (along with the troublesome
CurrentUserID) - all are displayed to the user and are changeable by them
except CurrentUserID:
Parameter Name Data Type Has Default Default Value
From DateTime Yes 1/03/2005 12:00:00 AM
To DateTime Yes 31/03/2005 12:00:00 AM
Channel Integer Yes 0
OrigState String Yes (ALL)
OriginatorID Integer Yes 0
SAM_FBM String Yes (ALL)
ReportAs String Yes Real-time
CurrentUserID Integer Yes -1
For CurrentUserID, Prompt User is ticked and Prompt String is blank.
Thanks.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> can you give the complete url
> "Jadranka" wrote:
> > Yep, that's correct.
> >
> > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > come back fine. But when I put -1 as the default, and add the parameter to
> > the end of my URL (&CurrentUserID=1083), it doesn't.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > So you have: a default value, you've checked the box "prompt user" and have
> > > left the prompt value blank?
> > >
> > > If this is the case it should work.
> > >
> > > "Jadranka" wrote:
> > >
> > > > Thanks for your response.
> > > >
> > > > I've tried the suggestion to set a non-used default value. The report now
> > > > loads error-free, however, it comes back with no data even though I've added
> > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > return 3 records).
> > > >
> > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > the fact that I want to override the -1 default with my own user ID.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > write a small program to make that change for your reports.
> > > > > I hope they are going to fix this in RS2005
> > > > >
> > > > > "rbowlin" wrote:
> > > > >
> > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > application AND allow the developer to configure this setting so the report
> > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > 'read-only' and I cannot set it from code.
> > > > > >
> > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > >
> > > > > > Any suggestions?
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > > > just give it a (non-used) default value fe -1
> > > > > > >
> > > > > > > "Jadranka" wrote:
> > > > > > >
> > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > record, I've got SP1 installed.
> > > > > > > >
> > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > From - defaults to previous business day
> > > > > > > > To - also defaults to previous business day
> > > > > > > >
> > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > from the user.
> > > > > > > >
> > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > Has Default - not ticked
> > > > > > > > Default Value - blank
> > > > > > > > Prompt User - ticked
> > > > > > > > Prompt String - blank
> > > > > > > >
> > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > &CurrentUserID=1083
> > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > >
> > > > > > > > However, I'm getting the following error:
> > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > >
> > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > >
> > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > greatly appreciated.
> > > > > > > >
> > > > > > > > Many thanks.
> > > > > > > > --
> > > > > > > > Jadranka Krapic
> > > > > > > > DBA
> > > > > > > > Stargate Technologies
> > > > > > > > (www.stargatetech.com.au)|||Perhaps you could try:
http://sqldev/Reports?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
"Jadranka" wrote:
> Sure...URL when viewing the report via Report Manager is as follows:
> http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> '&CurrentUserID=1083' is the user ID that, when used with the below
> defaults, I know should return 3 records.
> I have a number of parameters for this report (along with the troublesome
> CurrentUserID) - all are displayed to the user and are changeable by them
> except CurrentUserID:
> Parameter Name Data Type Has Default Default Value
> From DateTime Yes 1/03/2005 12:00:00 AM
> To DateTime Yes 31/03/2005 12:00:00 AM
> Channel Integer Yes 0
> OrigState String Yes (ALL)
> OriginatorID Integer Yes 0
> SAM_FBM String Yes (ALL)
> ReportAs String Yes Real-time
> CurrentUserID Integer Yes -1
> For CurrentUserID, Prompt User is ticked and Prompt String is blank.
> Thanks.
> --
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)
>
> "Antoon" wrote:
> > can you give the complete url
> >
> > "Jadranka" wrote:
> >
> > > Yep, that's correct.
> > >
> > > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > > come back fine. But when I put -1 as the default, and add the parameter to
> > > the end of my URL (&CurrentUserID=1083), it doesn't.
> > > --
> > > Jadranka Krapic
> > > DBA
> > > Stargate Technologies
> > > (www.stargatetech.com.au)
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > So you have: a default value, you've checked the box "prompt user" and have
> > > > left the prompt value blank?
> > > >
> > > > If this is the case it should work.
> > > >
> > > > "Jadranka" wrote:
> > > >
> > > > > Thanks for your response.
> > > > >
> > > > > I've tried the suggestion to set a non-used default value. The report now
> > > > > loads error-free, however, it comes back with no data even though I've added
> > > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > > return 3 records).
> > > > >
> > > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > > the fact that I want to override the -1 default with my own user ID.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jadranka Krapic
> > > > > DBA
> > > > > Stargate Technologies
> > > > > (www.stargatetech.com.au)
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > > write a small program to make that change for your reports.
> > > > > > I hope they are going to fix this in RS2005
> > > > > >
> > > > > > "rbowlin" wrote:
> > > > > >
> > > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > > application AND allow the developer to configure this setting so the report
> > > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > > 'read-only' and I cannot set it from code.
> > > > > > >
> > > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > > >
> > > > > > > Any suggestions?
> > > > > > >
> > > > > > > "Antoon" wrote:
> > > > > > >
> > > > > > > > just give it a (non-used) default value fe -1
> > > > > > > >
> > > > > > > > "Jadranka" wrote:
> > > > > > > >
> > > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > > record, I've got SP1 installed.
> > > > > > > > >
> > > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > > From - defaults to previous business day
> > > > > > > > > To - also defaults to previous business day
> > > > > > > > >
> > > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > > from the user.
> > > > > > > > >
> > > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > > Has Default - not ticked
> > > > > > > > > Default Value - blank
> > > > > > > > > Prompt User - ticked
> > > > > > > > > Prompt String - blank
> > > > > > > > >
> > > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > > &CurrentUserID=1083
> > > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > > >
> > > > > > > > > However, I'm getting the following error:
> > > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > > >
> > > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > > >
> > > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > > greatly appreciated.
> > > > > > > > >
> > > > > > > > > Many thanks.
> > > > > > > > > --
> > > > > > > > > Jadranka Krapic
> > > > > > > > > DBA
> > > > > > > > > Stargate Technologies
> > > > > > > > > (www.stargatetech.com.au)|||Hi Antoon,
I tried your suggested URL, but that always defaulted back to the Report
Manager Home page (http://sqldev/Reports/Pages/Folder.aspx).
However, I then tried going through Report Server
(http://sqldev/Reportserver?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083),
and hey presto - IT WORKED! - I get the 3 records I am expecting. The
CurrentUserID parameter is hidden, and my test text box I placed on the
report to check what was in CurrentUserID is now correctly showing my user ID
of 1083. I think that now I can work with this.
Many thanks for your assistance Antoon. I (and I'm sure the client of this
project) really appreciate you taking the time to help out.
Thank you again.
--
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)
"Antoon" wrote:
> Perhaps you could try:
> http://sqldev/Reports?%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> "Jadranka" wrote:
> > Sure...URL when viewing the report via Report Manager is as follows:
> > http://sqldev/Reports/Pages/Report.aspx?ItemPath=%2fRAMSReports%2frptFilesSentForSettlementRM_UserTest&CurrentUserID=1083
> >
> > '&CurrentUserID=1083' is the user ID that, when used with the below
> > defaults, I know should return 3 records.
> >
> > I have a number of parameters for this report (along with the troublesome
> > CurrentUserID) - all are displayed to the user and are changeable by them
> > except CurrentUserID:
> >
> > Parameter Name Data Type Has Default Default Value
> > From DateTime Yes 1/03/2005 12:00:00 AM
> > To DateTime Yes 31/03/2005 12:00:00 AM
> > Channel Integer Yes 0
> > OrigState String Yes (ALL)
> > OriginatorID Integer Yes 0
> > SAM_FBM String Yes (ALL)
> > ReportAs String Yes Real-time
> > CurrentUserID Integer Yes -1
> >
> > For CurrentUserID, Prompt User is ticked and Prompt String is blank.
> >
> > Thanks.
> > --
> > Jadranka Krapic
> > DBA
> > Stargate Technologies
> > (www.stargatetech.com.au)
> >
> >
> > "Antoon" wrote:
> >
> > > can you give the complete url
> > >
> > > "Jadranka" wrote:
> > >
> > > > Yep, that's correct.
> > > >
> > > > If I put my ID of 1083 in the default just to see if it works, my 3 records
> > > > come back fine. But when I put -1 as the default, and add the parameter to
> > > > the end of my URL (&CurrentUserID=1083), it doesn't.
> > > > --
> > > > Jadranka Krapic
> > > > DBA
> > > > Stargate Technologies
> > > > (www.stargatetech.com.au)
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > So you have: a default value, you've checked the box "prompt user" and have
> > > > > left the prompt value blank?
> > > > >
> > > > > If this is the case it should work.
> > > > >
> > > > > "Jadranka" wrote:
> > > > >
> > > > > > Thanks for your response.
> > > > > >
> > > > > > I've tried the suggestion to set a non-used default value. The report now
> > > > > > loads error-free, however, it comes back with no data even though I've added
> > > > > > &CurrentUserID=1083 to the end of the URL (1083 is a user ID that should
> > > > > > return 3 records).
> > > > > >
> > > > > > I've added a text box onto the report to show what's in the CurrentUserID
> > > > > > parameter when I run the report with &CurrentUserID=1083 in the URL. However,
> > > > > > it keeps showing the -1 I've set as the default. It doesn't appear to notice
> > > > > > the fact that I want to override the -1 default with my own user ID.
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jadranka Krapic
> > > > > > DBA
> > > > > > Stargate Technologies
> > > > > > (www.stargatetech.com.au)
> > > > > >
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > > > Well, it's not clean but you could have a look at what exactly changes in the
> > > > > > > reports (xml) when you change the settings in de report manager, and then
> > > > > > > write a small program to make that change for your reports.
> > > > > > > I hope they are going to fix this in RS2005
> > > > > > >
> > > > > > > "rbowlin" wrote:
> > > > > > >
> > > > > > > > Along the same lines, I am creating an interface to an client application to
> > > > > > > > launch reports. I need a way to be able to set a parameter from within the
> > > > > > > > application AND allow the developer to configure this setting so the report
> > > > > > > > can be deployed with no Report Manager configuration necessary. If I clear
> > > > > > > > the prompt from the report designer and deploy, the parameter then becomes
> > > > > > > > 'read-only' and I cannot set it from code.
> > > > > > > >
> > > > > > > > The only work around that I can find is to go in to Report Manager and turn
> > > > > > > > ON the Prompt User, then clear the Prompt text and then adapt for that in
> > > > > > > > code. This works fine except that it means I have to go to each of the 100
> > > > > > > > client's ReportManagers and configure this report this way. With 50 reports
> > > > > > > > with hidden parameters at 100 clients, that is just not possible.
> > > > > > > >
> > > > > > > > Any suggestions?
> > > > > > > >
> > > > > > > > "Antoon" wrote:
> > > > > > > >
> > > > > > > > > just give it a (non-used) default value fe -1
> > > > > > > > >
> > > > > > > > > "Jadranka" wrote:
> > > > > > > > >
> > > > > > > > > > I'm having problems with executing a report with a hidden parameter. For the
> > > > > > > > > > record, I've got SP1 installed.
> > > > > > > > > >
> > > > > > > > > > I have a report with a date range that a user can modify:
> > > > > > > > > > From - defaults to previous business day
> > > > > > > > > > To - also defaults to previous business day
> > > > > > > > > >
> > > > > > > > > > I also have a parameter called CurrentUserID (an integer) which is a user ID
> > > > > > > > > > that exists in a table called tblUser. I want this parameter to be hidden
> > > > > > > > > > from the user.
> > > > > > > > > >
> > > > > > > > > > In Report Manager, I've gone into Properties, then Parameters, and set the
> > > > > > > > > > following for the CurrentUserID parameter:
> > > > > > > > > > Has Default - not ticked
> > > > > > > > > > Default Value - blank
> > > > > > > > > > Prompt User - ticked
> > > > > > > > > > Prompt String - blank
> > > > > > > > > >
> > > > > > > > > > In the URL for the report, I add the following at the end:
> > > > > > > > > > &CurrentUserID=1083
> > > > > > > > > > where 1083 is a UserID I know should return some records in my test report.
> > > > > > > > > >
> > > > > > > > > > However, I'm getting the following error:
> > > > > > > > > > This report requires a default or user-defined value for the report
> > > > > > > > > > parameter 'CurrentUserID'. To run or subscribe to this report, you must
> > > > > > > > > > provide a parameter value. (rsReportParameterValueNotSet)
> > > > > > > > > >
> > > > > > > > > > I have tried the report again, this time with the CurrentUserID parameter
> > > > > > > > > > displayed, and entering the user ID 1083, and this time the report works
> > > > > > > > > > fine, so I know the stored procedure is not the problem.
> > > > > > > > > >
> > > > > > > > > > Any suggestions to get this hidden parameter functionality to work would be
> > > > > > > > > > greatly appreciated.
> > > > > > > > > >
> > > > > > > > > > Many thanks.
> > > > > > > > > > --
> > > > > > > > > > Jadranka Krapic
> > > > > > > > > > DBA
> > > > > > > > > > Stargate Technologies
> > > > > > > > > > (www.stargatetech.com.au)

Hidden expression with dates?

Hi! How to write the syntax when you try to compare the dates. In this case I try to check if the date is empty(then it's 1/1/1900) then the field should be hidden. This is what I tried but didn't succeed:

=iif(Fields!ModifiedDate.Value="1/1/1900",true,false)

You can convert your string value ('1/1/1900') to a date with the CDate() function. With this you can compare the to value's with each other.|||

Jan Pieter Posthuma wrote:

You can convert your string value ('1/1/1900') to a date with the CDate() function. With this you can compare the to value's with each other.

Thanks for the post but unfortunately I didn't get it. The main problem is syntax - I don't know where to use Cdate() -function. I tried this:

=iif(Cdate(Fields!modifiedDate).Value) > "1/1/1900",false,true)

the compiler didn't like it :)

Can you give me an example?

|||

It just the otherway round: =iif(Fields!modifiedDate.Value=CDate("1/1/1900"), false, true)

hi,need help.....

hi all...
i need help in writing a SQL query to display the date and time of the client last visited.

Quote:

Originally Posted by rocpraveen

hi all...
i need help in writing a SQL query to display the date and time of the client last visited.

Hi rocpraveen,

We are more than happy to help you with your query. But the only problem is we are not good at guessing other people's requirements with absolutely no information to go with. I might decide upon my own set of tables and columns and arrive at a suitable query but I guess it would not serve your purpose. So either you have not given enough information or we are just not good enough.

P.S: It's absolutely irritating when someone assumes that others will guess their requirements.|||Can u please post your table structure n what exactly u want to retrive.

And if possible post how u have tried to solve the problem .

It makes it easier for the experts in the forum in understanding your problem better.

Hi

I have a field that I would like to only display the date as mm/dd/yyyy. Current field shows mm/dd/yyyy hh:mm:ss AM.

I would most likely create a view that will create a field that will display the date only.

Answered there: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619822&SiteID=1

Here's the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.
Thanks again for the help.
create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)
insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')
insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')
insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')
insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount
1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100
======== Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========> Result => 2100
>
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> >
> > Thanks again for the help.
> >
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> >
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> >
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> >
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> >
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> >
> >
> > customerid lastpayment amount
> >
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> >
> > ========> > Result => 2100
> >
> >
> >
> >
>|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.
You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:
CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > >
> > > Thanks again for the help.
> > >
> > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > >
> > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > >
> > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > >
> > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > >
> > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > >
> > >
> > > customerid lastpayment amount
> > >
> > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > >
> > > ========> > > Result => 2100
> > >
> > >
> > >
> > >
> >
> >
>

Heres the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')

customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========
> Result => 2100
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.

"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> > Thanks again for the help.
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> > customerid lastpayment amount
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> > ========
> > Result => 2100|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.

You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:

CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > > > Thanks again for the help.
> > > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > > > > customerid lastpayment amount
> > > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > > > ========
> > > Result => 2100
> > > >

HelpNeed to convert number to DateTime

Hi! I'm not very technical, so please excuse me if I don't make sense...

I'm pulling Date/Time data from an Oracle database using Crystal 10, however, instead of getting a number in DateTime format, I'm getting a really long number that does not convert when using the DateTime function (I get an error stating that the year is greater than 9999.)

The number is 1123876247 (the actual date should be 8/12/2005 12:50). Sound familiar? Does anyone have any ideas on how to convert this number?

Any help is greatly appreciated!! (I'm about to pull my hair out!)How is 1123876247 related to 8/12/2005 12:50?|||1123876247 is 8/12/2005 represented in seconds since a certain date. Since my last post, I have since found that date to be 01/01/1970. So, the formula is:

DateAdd("s", (11238876247), #1970/01/01#)

This works to convert the seconds into Date/Time format.

help-how to retrive date from sql to array

i m new 4 asp.net

i retrive data from sql database.
now,
i want to put that retrived data into array.
how could i do this?

i am use vb.net

plz anyone give any idea.
it's urgent

thanks in advance.U shud be able to do that using a foreach loop for going thru each DataRow in all the existing rows and then add the field values to an ArrayList. something like below..its a very rough outline but I guess it wud help u get a direction. I may be able to help u better if u can revert back with what exactly u are trying to do.

ArrayList ar = new ArrayList();
foreach (DataRow dr in yourtable.Rows)
{
 ar.Add(dr.Columns["yourfield"]);
}

hth

Help: query to get total last 7 day sales for each day

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanksselect date,sum(sales) from table where date between (getdate()-7) and getdate()
group by date|||That will get you sales within the last 7x24 hours, because getdate returns the full date and time. Maybe that's what you want, but if you are looking at full sales days you should try the datediff function:

select convert(char(10), date, 120), sum(sales)
from table
where datediff(yourdate, getdate()) < 7 --(or 6, depending upon whether you want to count the current day)
group by convert(char(10), date, 120)