cursor to compare/report on the same fields in 2 tables

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...

I only want to see results if they are 1 for the same address field

this is what I have so far...

declare @.address1 char(61),@.city char(61)

declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd

OPEN address_cursor

fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursorhttp://sqlserver-puzzles.blogspot.c...albes-real.html
--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||SQLNewbie wrote:

Quote:

Originally Posted by

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
>
this is what I have so far...
>

[snip]

You refer to yourself as a "newbie". If you are new to SQL or to SQL
Server then do not even attempt to write cursors.

There are nearly always better alternatives to cursors. It's only when
you have a lot of experience that you can make an informed decision
about when a cursor makes sense. Meantime, if you can't think of
another way to do something it would be better to ask for help rather
than try to write a cursor. You'll learn good practices a LOT quicker
that way.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||SQLNewbie (tahnee.puckett@.bancsourceinc.com) writes:

Quote:

Originally Posted by

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field

If it's a copy, isn't it the same data then?

I was trying to understand what you really want to do, but I'm afraid I
don't.

I would suggest that you post:

o CREATE TABLE statements for your tables, preferably to show the
pertinent points.
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQLNewbie wrote:

Quote:

Originally Posted by

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
>
this is what I have so far...
>
>
declare @.address1 char(61),@.city char(61)
>
declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd
>
OPEN address_cursor
>
fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursor

See if this points you in the right direction:

select t.address1, t.city, count(*)
from test.dbo.testadd t
join production.dbo.testadd p on t.city = p.city
where p.address1 like '%' + t.address1 + '%'
group by t.address1, t.city
having count(*) 1

CurrentDate within a report?

Hi all, im having difficulty using dates within my report. Any advice
welcome.
Basically I want to restrict my query by date, however, I want my start and
enddate parameters to have universal generic values.
I.e.
startdate = CurrentDate - 31
enddate = CurrentDate
I cannot figure out how todo this within RS 2005, I have attempted to use
some of the .NET datetime functions but I cannot get them to work (i.e.
"Today() - 31" etc)
What should I be doing?
Kind regards
TazTry the following expressions:
=Today() -- for StartDate
=DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Hi all, im having difficulty using dates within my report. Any advice
> welcome.
> Basically I want to restrict my query by date, however, I want my start and
> enddate parameters to have universal generic values.
> I.e.
> startdate = CurrentDate - 31
> enddate = CurrentDate
> I cannot figure out how todo this within RS 2005, I have attempted to use
> some of the .NET datetime functions but I cannot get them to work (i.e.
> "Today() - 31" etc)
> What should I be doing?
> Kind regards
> Taz
>
>|||Many thanks for the reply, this has helped alot.
However, a new problem has surfaced. There is an inconsistency between the
date format in my data fields and the format within the database. I need to
force an American format, how can I do this?
Alternatively, is there a way to convert UK dates into American dates?
Currently, each time I run my report, within the parameter window, the
values are being alternated (which is VERY strange).
I.e.
12/01/2006 -> Press "View Report"
01/12/2006 -> Press "View Report"
12/01/2006 -> Press "View Report"
etc etc
Thanks for your help
Taz
"magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> Try the following expressions:
> =Today() -- for StartDate
> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>|||did you put that date formula into an expression or did you put it in the
data tab.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need
> to force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>|||I have put those formulas into the Default values within the "Report
Parameters" setup.
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||try this: =switch(Fields!YOURFIELD.Value = "US","M/d/yy")
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||I'm not sure where you need to force an American date format.
If you are referencing columns in your SQL Server database which have a
datatype of DATETIME then you shouldn't have a problem. If you are running
RS2000 and haven't applied Service Pack two you can get inconsistencies in
presentation of dates when entering report parameters - apply the service
pack.
Also, you can force a UK format in your reports by using the format property
of a given textbox, making it something like dd-MM-yy.
If none of these pointers helps you then please clarify what the exact
problem is with date formats.
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need to
> force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> > Try the following expressions:
> >
> > =Today() -- for StartDate
> >
> > =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> >
> > HTH
> >
> > --
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "Tarun Mistry" wrote:
> >
> >> Hi all, im having difficulty using dates within my report. Any advice
> >> welcome.
> >>
> >> Basically I want to restrict my query by date, however, I want my start
> >> and
> >> enddate parameters to have universal generic values.
> >>
> >> I.e.
> >>
> >> startdate = CurrentDate - 31
> >> enddate = CurrentDate
> >>
> >> I cannot figure out how todo this within RS 2005, I have attempted to use
> >> some of the .NET datetime functions but I cannot get them to work (i.e.
> >> "Today() - 31" etc)
> >>
> >> What should I be doing?
> >>
> >> Kind regards
> >> Taz
> >>
> >>
> >>
>
>|||and if my last post doesnt help then try changing up your formula some.
=dateadd("d",-31,today). leave off the () and use "d" instead of
dateinterval.day. I have never had my dates go crazy like that.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>I have put those formulas into the Default values within the "Report
>Parameters" setup.
> Taz
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in the
>> data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>|||Guys,
I really don't understand what was going on. The "flipping" date issue was
only happening within RS and not in the webservice. It seems to be fine now.
Thanks for all the help and the magic formulaes!!
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OdnS4Ko0GHA.3752@.TK2MSFTNGP02.phx.gbl...
> and if my last post doesnt help then try changing up your formula some.
> =dateadd("d",-31,today). leave off the () and use "d" instead of
> dateinterval.day. I have never had my dates go crazy like that.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>>I have put those formulas into the Default values within the "Report
>>Parameters" setup.
>> Taz
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in
>> the data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>>
>|||It seems i posted in success a little too early,
My default parameter is correctly appearing in UK format, however when I
pass it to another report, it is being sent in american format, or being
interpretted in american format. The chart that sends the data is set to UK,
as is the main report.
what could be afoot here? furter, my reports may be deployed on a number of
different systems, will this behaviour happen differently on different
servers depending on its language setting'
Thanks
Taz|||Tarun Mistry wrote:
> It seems i posted in success a little too early,
> My default parameter is correctly appearing in UK format, however when I
> pass it to another report, it is being sent in american format, or being
> interpretted in american format. The chart that sends the data is set to UK,
> as is the main report.
> what could be afoot here? furter, my reports may be deployed on a number of
> different systems, will this behaviour happen differently on different
> servers depending on its language setting'
> Thanks
> Taz
If you are running SQL 2005 make sure you have installed servicepack 1.
The thing you are describing looks a bit like this post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1|||> If you are running SQL 2005 make sure you have installed servicepack 1.
> The thing you are describing looks a bit like this post.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1
>
Many thanks Peter, it does indeed look like this is the problem.
I googled the issue but couldn't find anything, i really appreciate your
help.
Downloading SP1 now, fingers crossed.
Kind regards
Taz

currentdate and monthtodate help!

Hi,

I'm trying to make a report that calculates the sales for some products,
it should show the sales of the current date inaddition to the sales of month to date on one line. This seems easy but the problem is when I'm trying to put a formula for the current time the system is taking the first records' date only, and ignoring other invoices dates.
Well gurus, where could be the problem, I tried to change the linking with no use...!

thanks in advance,,,,,,,Can you explain this little bit more with some real data example

and from your question only thing I found is you need Sales group by Month Am I correct ??

If yes... use group by function for Monthly Sales

CURRENT_USER issue

I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-
Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:

> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>
|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-

CURRENT_USER issue

I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-

CURRENT_USER issue

I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:

> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the na
mes can be converted to
int. You can see what usernames exists in the database thought the sys.datab
ase_principals catalog
view. If my assumption is correct, they written a pretty crappy function, wh
ich need to be fixed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...[v
bcol=seagreen]
> I've inherited Database for a Web Application (with no support) that makes
heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I re
ceive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation
above.
> I understand what the above function is trying to do, but when I execute '
SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, whe
n this function is
> called from the Web Application, it returns a 'username' defined in the db
o.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the
UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how tha
t would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-[/vbcol]

Currency Symbol Problem

I am currently writing a report in SQL 2005 RS and have a problem with the format of a currency field. I am creating the report through the 'SQL Server Business Intelligence Development Studio'

In the report preview (and live) it shows the prefix for the field to be $, and yet when I look at the custom format for the field it shows it as being Format Currency and the sample shows the output will be ￡1,234.00.

If I change the format to percentage or whatever it does exactly what you would expect it to do.

I have checked all the regional settings on my machine to UK in the various places you can and still it wants the result to be dollars.

Anybody, help please!!!!

Try setting the Report.Language property explicitly to en-UK.

Alternatively, set the Report.Language based on the user's language setting: =User!Language

Note: format code (such as currency) only have an impact if the textbox value is a numeric datatype and not already a string.

-- Robert

|||

I could not find the reference to which you referred, however following along the lines that you suggested I noticed that in the properties for the textbox, under 'International' there was a setting for 'Language', so I selected all of the offending boxes and changed their International properties from Default (U.S. I guess) to English (United Kingdom), and bingo, problem solved.

Thanks for pointing me in the right direction.

Regards

|||

The Report.Language is available through the VS properties window - which contains a drop down list with all items contained in the report. Select "Report" from the drop down.

The Report.Language property is a global setting for the entire report, while the Textbox.Language property will only impact that particular item.

-- Robert

Currency symbol

Hi,
I would like to get the symbol for the currency under the environment the
report is being run... Is there a way to do it?
For example, in the US it would display a $
in UK, it would display either the Euro or the pound symbol
etc...
ThanksTypically, you would use the C formatcode for currency formatting. You could
for instance set the format property on the textbox. Alternatively, you
could use the Format(...) function call.
More information about numeric format codes is available on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:BDDF44A0-3419-4D66-9151-29D48B379497@.microsoft.com...
> Hi,
> I would like to get the symbol for the currency under the environment the
> report is being run... Is there a way to do it?
> For example, in the US it would display a $
> in UK, it would display either the Euro or the pound symbol
> etc...
> Thanks

Currency in Analysis Services

Hi,

All the reports with comes out as $ (ex.) ( $123,437,00) I am working for the requirement that the same report has to show in multiple currency (ex.) ($,Yen,Pound,AUS$ ).As i am in Analysis Side can anyone give good Suggestion to get the output. Do any one faced the same?

Take a look at the following entry on Mosha's Blogsite:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

HTH,

Steve

Currency Formatting

I have a report that uses the following xml dataset via a DPE I got from Teo
Lachev:
<dsDisplay>
<Table1>
<AreaGroup>Orange County </AreaGroup>
<Col1>96090.8100</Col1>
<Col2>310241.9500</Col2>
<Col3>95732.6400</Col3>
<Col4>123224.3500</Col4>
<ShowFilter>show</ShowFilter>
<V1>-214151.1400</V1>
<V2>-27491.7100</V2>
</Table1>. . . . . . . .
I am specifying Currency formatting for the textboxes having numeric amounts
but it doesn't format - they still appear as they are in the dataset.
Why would this not work?
Thanks,
TI suspect that your report data is being treated as a string. If that is
correct the currency formatting will affect how your report data is
displayed.
What you need to do is to convert the data to a numeric format and then
apply the currency formatting. The textbox value expression you will need
should be similar to this:
=CDbl(Fields!<SomeFieldName>.Value)
Please see the VB Type Conversion discussion at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tina" <tinamseaburn@.removespamexcite.com> wrote in message
news:OegSX3shEHA.1156@.TK2MSFTNGP10.phx.gbl...
> I have a report that uses the following xml dataset via a DPE I got from
Teo
> Lachev:
> <dsDisplay>
> <Table1>
> <AreaGroup>Orange County </AreaGroup>
> <Col1>96090.8100</Col1>
> <Col2>310241.9500</Col2>
> <Col3>95732.6400</Col3>
> <Col4>123224.3500</Col4>
> <ShowFilter>show</ShowFilter>
> <V1>-214151.1400</V1>
> <V2>-27491.7100</V2>
> </Table1>. . . . . . . .
> I am specifying Currency formatting for the textboxes having numeric
amounts
> but it doesn't format - they still appear as they are in the dataset.
> Why would this not work?
> Thanks,
> T
>|||I forgot to include the schema info in the xml file. Once I did that it
worked just fine.
As Anna Roseanadana would say, uh, never mind :)
T
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:OeHsUMthEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I suspect that your report data is being treated as a string. If that is
> correct the currency formatting will affect how your report data is
> displayed.
> What you need to do is to convert the data to a numeric format and then
> apply the currency formatting. The textbox value expression you will need
> should be similar to this:
> =CDbl(Fields!<SomeFieldName>.Value)
> Please see the VB Type Conversion discussion at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tina" <tinamseaburn@.removespamexcite.com> wrote in message
> news:OegSX3shEHA.1156@.TK2MSFTNGP10.phx.gbl...
> > I have a report that uses the following xml dataset via a DPE I got from
> Teo
> > Lachev:
> > <dsDisplay>
> > <Table1>
> > <AreaGroup>Orange County </AreaGroup>
> > <Col1>96090.8100</Col1>
> > <Col2>310241.9500</Col2>
> > <Col3>95732.6400</Col3>
> > <Col4>123224.3500</Col4>
> > <ShowFilter>show</ShowFilter>
> > <V1>-214151.1400</V1>
> > <V2>-27491.7100</V2>
> > </Table1>. . . . . . . .
> >
> > I am specifying Currency formatting for the textboxes having numeric
> amounts
> > but it doesn't format - they still appear as they are in the dataset.
> >
> > Why would this not work?
> > Thanks,
> > T
> >
> >
>

Currency format in reporting services

I have what is likely a simple question but I have had no luck in tracking
down an answer so far. I have a report that I've created in RS that is for an
English company and I want to display the output in pounds as opposed to $.
When I go to a given text-box and click the format/currency option all I get
is $. I realize that RS tries to default to the currency setting on the
location the report is run but since the report will be hosted in the states
that's not going to help. All I need to do is format a text box as pounds!
Anyone have any ideas?
Thanks in advance.I noticed that if you click on the text box and look at the properties for it
that there is a language property. Change it from default to English (United
Kingdom) and that will change the currency symbol to the other symbol. I had
something formatted as currency and tried this and it seemed to work.
"littleren" wrote:
> I have what is likely a simple question but I have had no luck in tracking
> down an answer so far. I have a report that I've created in RS that is for an
> English company and I want to display the output in pounds as opposed to $.
> When I go to a given text-box and click the format/currency option all I get
> is $. I realize that RS tries to default to the currency setting on the
> location the report is run but since the report will be hosted in the states
> that's not going to help. All I need to do is format a text box as pounds!
> Anyone have any ideas?
> Thanks in advance.|||Thank You Kim! It worked perfectly.
"KimB" wrote:
> I noticed that if you click on the text box and look at the properties for it
> that there is a language property. Change it from default to English (United
> Kingdom) and that will change the currency symbol to the other symbol. I had
> something formatted as currency and tried this and it seemed to work.
> "littleren" wrote:
> > I have what is likely a simple question but I have had no luck in tracking
> > down an answer so far. I have a report that I've created in RS that is for an
> > English company and I want to display the output in pounds as opposed to $.
> > When I go to a given text-box and click the format/currency option all I get
> > is $. I realize that RS tries to default to the currency setting on the
> > location the report is run but since the report will be hosted in the states
> > that's not going to help. All I need to do is format a text box as pounds!
> >
> > Anyone have any ideas?
> >
> > Thanks in advance.|||I'm glad it helped!
"littleren" wrote:
> Thank You Kim! It worked perfectly.
>
> "KimB" wrote:
> > I noticed that if you click on the text box and look at the properties for it
> > that there is a language property. Change it from default to English (United
> > Kingdom) and that will change the currency symbol to the other symbol. I had
> > something formatted as currency and tried this and it seemed to work.
> >
> > "littleren" wrote:
> >
> > > I have what is likely a simple question but I have had no luck in tracking
> > > down an answer so far. I have a report that I've created in RS that is for an
> > > English company and I want to display the output in pounds as opposed to $.
> > > When I go to a given text-box and click the format/currency option all I get
> > > is $. I realize that RS tries to default to the currency setting on the
> > > location the report is run but since the report will be hosted in the states
> > > that's not going to help. All I need to do is format a text box as pounds!
> > >
> > > Anyone have any ideas?
> > >
> > > Thanks in advance.|||Hi,
Is it possible to apply your machine's Regional Settings to the currency
symbol Meaning, when I change my Windows' Regional Settings to English/UK,
currency will display with a pound symbol, if South Africa, with an R, or if
US. with a $?
Regards,
PM
"KimB" wrote:
> I'm glad it helped!
> "littleren" wrote:
> > Thank You Kim! It worked perfectly.
> >
> >
> > "KimB" wrote:
> >
> > > I noticed that if you click on the text box and look at the properties for it
> > > that there is a language property. Change it from default to English (United
> > > Kingdom) and that will change the currency symbol to the other symbol. I had
> > > something formatted as currency and tried this and it seemed to work.
> > >
> > > "littleren" wrote:
> > >
> > > > I have what is likely a simple question but I have had no luck in tracking
> > > > down an answer so far. I have a report that I've created in RS that is for an
> > > > English company and I want to display the output in pounds as opposed to $.
> > > > When I go to a given text-box and click the format/currency option all I get
> > > > is $. I realize that RS tries to default to the currency setting on the
> > > > location the report is run but since the report will be hosted in the states
> > > > that's not going to help. All I need to do is format a text box as pounds!
> > > >
> > > > Anyone have any ideas?
> > > >
> > > > Thanks in advance.

Currency Column in Report Problem

I have a column in a report which displays an amount of currency. The currency is stored in sqlserver databse as 'Money' data type.

My problem is when I run the report it rounds off the value and does not disply to any decimal places. I want it to display the currency as 2 decimal places as this is how it is input and stored in the database.

anyone know how to do this?

macca

You could format the textbox like this:

http://msdn2.microsoft.com/en-us/library/ms251684(VS.80).aspx

Find more info here:

http://msdn2.microsoft.com/en-us/library/ms252073(VS.80).aspx

Currency being rounded up in Report

I am displaying an amount of money to two decimal places in a field in a report.

If I run the stored procedure in sql server the amounts are displayed to two decimal places like so €31.75.

But when I run the report the amount is returned as so €32.00, it is being rounded up to the nearest euro.

Anyone any ideas how to solve this?

macca

Figured this out myself.

cumulative totals in chart : Report Builder

Hi,

I am trying to display a line chart with cumulative totals over period of 12 months in a fiscal year. I know this can be achieveable in report designer using "Running value function".

Any idea how to achieve the same in Report builder ?

thanks in advance.

Here is the data :

Month Count

July 2

Aug 3

Sept 2

Expected output should be

Month Count

July 2

Aug 5(July count + Aug.Count)

Sept 7(July count + Aug count + Sept Count)

Regards,

bala

Hi guys,

Any idea how to achieve the above functionality in Report Builder ?

Cumalative %

Hi there, i am having some problems with a Crystal Report. I have created a report showing the duration of calls within a HEAT System. The calls are grouped into the below areas:

Under 4 Hours
Under 8 Hours
Under 2 Days
Under 3 Days

What is required is the following:

Duration of Call(Group) Number of Calls Cumalative %

Under 4 Hours 300 30% Calc(300/1000)
Under 8 Hours 300 60% Calc(600/1000)
Under 2 Days 100 70% Calc(700/1000)
Under 3 Days 300 100% Calc(1000/1000)

Total Calls 1000

I don't know how to achieve this in a Grouped report in Crystal - what would i use in a formula to do this?

Any help would be greatly received.

Regards

TimI think you want the PercentOfCount function, e.g.

PercentOfSum (call, inner, outer)

So if your report were grouped on, say,
1) Call centre
2) Duration

You would want: PercentOfSum(call, duration, centre)

Anyway, read the help on the PercentOf... functions.|||Sorry, I kept writing PercentOfSum instead of PercentOfCount

Culture problem with datepicker in report parameters

Hi,
on the server of a customer I determined the following problem: The customer
installed an English operating system (Windows server 2003) with English SQL
server 2005. I go from a German Client on a report about http://
<servername>/reports (report managers) to calendars in the report parameters
with German format of date as expected there. So far, so well. I turn into
however http:// more <servername>/reportserver on report, are indicated now
everything correctly in the report parameters on German represented indicate
(Tooltips, "Bericht anzeigen" (show report). etc.), only the calendar
strangely enough is in the English format of date. I have tested the same
configuration (likewise with English operating system and English Sql
server; german Client) on a virtual server, and can not reproduce the
problem here. Someone an idea, why I do not get the calendar at the customer
in accordance with the language settings of the client as expected?
Thanks and regards,
StefanHello Stefan,
I would like to know what's the server Regional Setting.
Also, please applied the latest service pack to your server and let me know
the result.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hello Wei Lu,
the Regional Setting of the Server is German.
I will ask the customer, if they have installed the latest service pack. As
far as I know, they have installed SP2.
Thanks and regards,
Stefan Dirks
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schrieb im Newsbeitrag
news:PsMLICbnHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
> Hello Stefan,
> I would like to know what's the server Regional Setting.
> Also, please applied the latest service pack to your server and let me
> know
> the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hi Wei Lu,
the customer has installed SQL Reporting Services Version 9.00.3042.00.
It's strange that every reporting parameter (e.g. Tooltip) is in german
except for the calendar. Is there a possibility to set the
calendar-parameter explicit to german?
I tried to set the default language of the Reports to german/User!Language
but with no effect.
Thanks Stefan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schrieb im Newsbeitrag
news:6tvedcQoHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Stefan,
This is a known issue in the reporting Services SP2. Product team is still
working on it. I will inform you the update if any progress.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hi Wei Lu,
thanks for this information. I use in the meantime a ReportViewer on a
aspx-Site as a workaround.
Thanks,
Stefan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schrieb im Newsbeitrag
news:39vfU9ooHHA.1428@.TK2MSFTNGHUB02.phx.gbl...
> Hello Stefan,
> This is a known issue in the reporting Services SP2. Product team is still
> working on it. I will inform you the update if any progress.
> Thank you!
>
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>

Culture overriding custom format

Hi, I've been having a weird problem with a report I'm making. I've done douzan of em, and the problem only appeared in one. Let say I have a text field, and I give it a format property (for the sake of the example, imagine its a date, and I set it as yyyy/MM/dd. Same result if I would use an expression with the Format function).

Now, depending on culture, the slashes gets overrided. Everything else work (so if I put yyyy/MM, the format is recognised and the day won't show...but it will use dashes instead of slashes, for example, with us culture).

How do I get it to always respect my format? I have, like I said, douzans of other reports and it works fine, the format string gets applied exactly: I don't know what the hell I could have changed. All the culture settings are at default, like in the reports that do work... I've tried diff-ing the source, can't see anything weird, but I'm sure I'm just being blind.

Any ideas?

Thank you.

The DateSeparator is defined on the DateTimeFomatInfo objects and the slash is being "translated" to the current culture.

If there's not an option to have the slash in the format string not interpreted as a DateSeparator, the only thing I can think of is to add a .Replace(CultureInfo.CurrentCulture.DateTimeFormat.DateSeparator, "/") to the end of your Format statement.

|||

Just found this stumbling around on something else. Try "yyyy'/'mm'/'dd" as your format string. Found ithere, so you can fuss at Microsoft if it doesn't work.

|||

Makes sense. Yeah, escaping the slashes (let say, yyyy\/MM\/dd ) worked too.

But then, what do I do with numeric formats? (since the date was just an example). #,##0.00
Works fine in most of my reports, but in that particular one, no matter what format I put, the number gets translated with the current culture. What mostly gets me, is that it works in all the reports! Just not -that- one...ugh.|||

Those separators are also defined in a culture-specific way. I'd recheck that one report and make sure that you haven't accidentally specified a culture or that you did specify a culture on the other reports.

|||

Yup, you're right... I'm baffled, I tested the other reports, and not only did they have cultures set, but the formats never worked (I had a formula that switched the Language setting depending on a parameter set by the user, and the culture I always ended up printing my report coincidently had the correct separators).

My apologies for misleading you, I know it can be annoying when you're trying to help someone and they give you the wrong info :)

At least now I know I can backslash escape the magical separators... That is quite awkward that it does that, especially since its a bit different from the behavior of .NET formatters... Oh well!

Cubes in Report manager?

Can OLAP cubes be stored on report manager aswel as reporting services reports?
Many thanks
GregNot sure what you mean. Cubes can be data sources for reporting services.
You can have a shared data connection stored in reporting services, and that
data source can be analysis services. Is that what you're asking?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
> Can OLAP cubes be stored on report manager aswel as reporting services
> reports?
> Many thanks
>
> Greg|||He might be asking about local cubes. You could store the .cub file in RS
but the server wouldn't know anything special about it. I don't think the AS
engine would be able to load it either.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u5rQ3%23F5EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Not sure what you mean. Cubes can be data sources for reporting services.
> You can have a shared data connection stored in reporting services, and
> that data source can be analysis services. Is that what you're asking?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
>> Can OLAP cubes be stored on report manager aswel as reporting services
>> reports?
>> Many thanks
>>
>> Greg
>

Cube usage by users

Hi,

What would be the correct way to generate some kind of log or report that will tell me which user used which cube in which database along with the number of times the user connected to each cube each day? The number of queries sent by each user to each cube by day would be ok. I am not interested by the query content for this exercise. I just want Userid, DB name, Cube name and Hits.

Then I can take it from there and create a report for our security/sox guy with User name, Cube name, Week of year and number of hits by week, meaning if the users send 36 queries to Cube A on Monday and 2 queries on Friday, it counts for 2 cube accesses for that week.

This assume that I use only TCP/IP access but if the same mechanism would work for HTTP access that would be fine. I try not to use HTTP but I may be forced to do it.

Regardless of network transport, the only logins allowed are though Windows Domain Global Group Membership.

Seems simple but I do not find where to start.

Thanks,

Philippe

Hello,

I have found a solution to this issue.

In the Server property box, you will enable query loging to a table and set the interval to 1 instead of 10.

Stop and Start the AS Server

Create some query or cube to access your log data, you are done.

I cannot really provide a query example since it depends on your situation. Mine is linked to a Calendar and to a Users tables. I convert the query time to keep only the day and the username to keep only the userid, then I build some Cube and/or Report that joins all that stuff and do some DistinctCount on the daily access by Cube by User and voila. I keep my infosec guy happy and I know what are the cubes I can retire for cause of no use.

You know, sometimes they ask you to build a cube real quick, they use it once and then they forget about it (and do not tell you). So I am not maintaining useless cubes :-)

Regards,

Philippe

Cube not refreshing?

AS and RS 2005

My cube is built on views both for dimensions and the fact table.

I am using reporting services and the report i have designed works fine except it does not update when i change the data. I can see the change in a select from the view so it is definitly there. It does refresh if i reprocess the cube. This happens in rolap as well as molap.

Any Ideas?

Thanks,

Jules

Moving to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

why have you moved this?

does that mean it is reporting services that is caching the data?

It looks more like a problem with analysis services to me given that the report displays the correct data if a reprocess the cube.

Whats you thinking here.

Please help

Thanks,

Jules

|||

Most chances it is Reporting services problem. You can try and browse your AS cube using cube browser built-in in SQL Management studio to see if new numbers are avaliable.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.