Formulae for SQL Server table cells

Hi,

I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:

I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.

Heres an example:

TABLE(A, B, C, D)

Total Records = 9;

A

BCDA1B1C1 = A1+ B1D1 = A1A2B2C2 = A2+ B2D2 = A2A3B3C3 = A3+ B3D3 = A3A4B4C4 = A4+ B4D4 = A4A5B5C5 = A5+ B5D5 = A5 + B5A6B6C6 = A6+ B6D6 = A6 + B6A7B7C7 = A7+ B7D7 = A7 + B7A8B8C8 = A8+ B8D8 = A8 + B8A9B9C9 = A9+ B9D9 = A9 + B9

If you notice, for Column D, some of the cells have a different formula than others. Is this doable?

Thanks...

One possible way would be there was an Id column with value 1-9, once the table was populated to make an update stored procedure calculate column unconditionally and column D according to a formula.

|||

Please let me know if I am going off-track here but this is what I understood from what you wrote.

Have an ID column (say autoincrement integers), then when all the values are being populated, I run an UPDATE stored procedure which whould check if ID is from 1-4, then assign the same value otherwise if ID = 5 to 9, perform calculation.

|||

Instead of an Identity column you could just populate it manually.

Incidentally as an alternative you could drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select.

|||

TATWORTH:

drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select

Does that mean I won't have columns C and D in the schema but will be generated dynamically when SELECTed (they won't ever be in the schema).

|||

>Does that mean I won't have columns C and D in the schema but will begenerated dynamically when SELECTed (they won't ever be in the schema).
Yes!

Formula Help for a new user

I am brand new to CR... i have version 10. My work jsut threw this on me and i am about clueless. I have a variable that is giving me multiple occurances of the same data. I am pulling in account numbers in (with other fields too) in a certain date range. What would be a good formula to tell it to ONLY show me each account # once?If your variable is in a field, you can set the field's 'SuppressIfDuplicate' to True. Or in a formula, you can compare the field in the current record to the previous or next record by doing:

If {FieldName} = Next ({FieldName} Then
//Code for duplicate values
Else
//Code for non-duplicate values

Just replace 'Next' with 'Previous' if you want to look at the previous record.

(I'm running CR 8.5, so the syntax may be a little different than in 10)|||Thanks for your help... i'll try the suppress first... but i'll hold ont to that Formula for future reference also.

_________________________________________________________________

If your having a bad day dont worry it'll change; if your having a good day dont worry it'll change too.

forms based queries

please help i am new to sql and my old access 2000 program used forms based queries.

the user simply input the patients last name and first name and the form would open up every record that had a matching combo to edit and if the combo didnt exist the form was ready for new input.

im under the understanding that sql doesnt support forms based queries and no i would like to know how to open my access form but first prompt users for what last name and first name they are looking for then fill the recordset with just those patients.

i have upsized my access 2000. mdb file to an access project now adp.
so im not using linked tables anymore or this would not be a problem.

this is very urgent for me to fix . i have already fixed all my vb code to make my chart number automatically. now im stuck on getting a prompt for a user to enter info to fill a form.Try using a separe form that is Modal = yes. This forces them to close the form before opening the next form. Then on event Form.Close check to make sure variables (names) are set properly (not empty).

forms authentication RSAccessdenied

Hello,
I try to get the sample for the forms authentication running.
I registered the first user as described in the instracutions and the user
was added to the database.
If I try to logon with this useraccount I receive the following error:
The permissions granted to user 'UserAdmin' are insufficient for performing
this operation. (rsAccessDenied)
UserAdmin is the username I created.
Have somebody expirienced this problem or have somebody a hint how I can
resolve this?
Thanks!I solved this in the following way:
I used the C Sharp instade of the vb sample. And with C# Sample the Login
seems to work.
The Report manager still doesnt work. I receive allways:
Object reference not set to an instance of an object.
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an
object.]
Microsoft.ReportingServices.UI.GlobalApp.Application_AuthenticateRequest(Object sender, EventArgs e) +44
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously) +64
Can somebody help on this subject?
"Dev Main" wrote:
> Hello,
> I try to get the sample for the forms authentication running.
> I registered the first user as described in the instracutions and the user
> was added to the database.
> If I try to logon with this useraccount I receive the following error:
> The permissions granted to user 'UserAdmin' are insufficient for performing
> this operation. (rsAccessDenied)
> UserAdmin is the username I created.
> Have somebody expirienced this problem or have somebody a hint how I can
> resolve this?
> Thanks!

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms Authentication Finally Working!!

I finally got forms authentication to work, but when I create a new user and
log in, I don't get any menus. My question is how do I assign the user I
created access to the appropriate menus (e.g., view report list,
subscriptions, etc.)?
The sample dB is different then the ReportServer dB.Ok I am an idiot and forgot to go into the RSReportServer.config file to
enter the name of the user i created. For all of you that had this problem,
this is what you needed to do!
"Neo" wrote:
> I finally got forms authentication to work, but when I create a new user and
> log in, I don't get any menus. My question is how do I assign the user I
> created access to the appropriate menus (e.g., view report list,
> subscriptions, etc.)?
> The sample dB is different then the ReportServer dB.|||Hi Neo
Where do I include this user in RSReportServer.config. The
<AdminConfiguration> portion in for the admin user I guess....
thanks
Angelo
"Neo" wrote:
> Ok I am an idiot and forgot to go into the RSReportServer.config file to
> enter the name of the user i created. For all of you that had this problem,
> this is what you needed to do!
> "Neo" wrote:
> > I finally got forms authentication to work, but when I create a new user and
> > log in, I don't get any menus. My question is how do I assign the user I
> > created access to the appropriate menus (e.g., view report list,
> > subscriptions, etc.)?
> >
> > The sample dB is different then the ReportServer dB.|||Hi Angelo,
You need to find the <security> element and change the "username." Please
see the sample below:
<Security>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,
Microsoft.Samples.ReportingServices.CustomSecurity" >
<Configuration>
<AdminConfiguration>
<UserName>changethistoyourusername</UserName>
</AdminConfiguration>
</Configuration>
</Extension>
</Security>
Sorry about the word wrap. HTH.
"Mathi" wrote:
> Hi Neo
> Where do I include this user in RSReportServer.config. The
> <AdminConfiguration> portion in for the admin user I guess....
> thanks
> Angelo
> "Neo" wrote:
> > Ok I am an idiot and forgot to go into the RSReportServer.config file to
> > enter the name of the user i created. For all of you that had this problem,
> > this is what you needed to do!
> >
> > "Neo" wrote:
> >
> > > I finally got forms authentication to work, but when I create a new user and
> > > log in, I don't get any menus. My question is how do I assign the user I
> > > created access to the appropriate menus (e.g., view report list,
> > > subscriptions, etc.)?
> > >
> > > The sample dB is different then the ReportServer dB.

Formatting a textbox

I need to format a phone number parameter the user enters. Currently
when I print the parameter entered it displays like this ##########. I
want to format it to look like ###-###-####. Does anyone have any
ideas for making it look like that?E,
RS has a Custom Formatting built in where you can type in ###-###-####
for the Format.
But for phone Numbers the DB probably returns it as a string. You must
first convert it to a Number... a LONG in this case.
So go to report Properties go to Custom Code and put this in there:
Public Function convertToIntegerNow(tempVar2 As String)
convertToIntegerNow= CLng(tempVar2)
End Function
in the cell type in
=Code.convertToIntegerNow(Fields!Phone_Field_name.Value)
Then right click on the cell go to properties, change the format of the
cell to ###-###-####
Hope this helps
regards,
Stas K.

Formatting a Text box

I need to format a phone number parameter the user enters. Currently
when I print the parameter it displays like this ##########.
I want to format it to look like ###-###-####.
It can't be done with SQL, it must be done in the VB of that textbox.
Does anyone have any ideas' It would save me from pulling my last
hair out!!Down and Dirty, but:
=Mid(Fields!homephone.Value,1,3) & "-" & Mid(Fields!homephone.Value,4,3) &
"-" & Mid(Fields!homephone.Value,7,4)
You can substitute the Parameter value as Parameters!PHNumber.Value in place
of Fields!homephone.Value if they are entering the phone number in the
parameter box.
Rodney Landrum
"E" <ericfreiman@.mtgsi.com> wrote in message
news:1144097404.116802.178020@.v46g2000cwv.googlegroups.com...
>I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>|||Hi,
Just select the cell you want to display with format right click and click
properties and select Format tab on the format field select "..." and click
custom and type "###-####"
Amarnath
"E" wrote:
> I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>

Formatted text in report parameter

I want the user to add his/her own title and some text to the report which can be bold, different color etc. Can this be done via a single report parameter and are there any ways to do it?

Not out of the box. If you use SRS 2005, the ReportViewer controls (part of VS 2005) will allow you to show reports in any ASP.NET or Winforms application. Using the controls, you can hide the report parameters page and replace it with your own prompting logic. See www.gotreportviewer.com for more details.

If you use SRS 2000, you can achieve most of this by putting an iframe to the report server in your web page and include rc:Parameters=False to hide the parameters area. You web page would be responsible for parameters prompting and for feeding the values to the report server iframe. For ex: http://server/reportserver/?PathToTheReport&rc:Parameters=false&Param1=value1&Param2=Value2

Formating Date Display

How to I set the =User!Language on a Reporting Services report to display
based on the locale of the user. I'd like to make sure date fields are
displayed using the correct format.On Nov 19, 1:48 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> How to I set the =User!Language on a Reporting Services report to display
> based on the locale of the user. I'd like to make sure date fields are
> displayed using the correct format.
I found an example online that shows it should be something like:
lblTime.Text = String.Format("{0:T}", rightNow);
lblDate.Text = String.Format("{0:d}", rightNow);
-- Scott

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

|||You can report the same in connect site by referring to Http://connect.microsoft.com.
FYI

Format function in SQL

Hi ... i'm sorry to hassle this user group but i have an urgent need
for some code - i have tried and tried to find a solutionn elsewhere -
but the problem is i don't really know what i am looking for.

What is the equivalent SLQ code for the below statement which works in
MS Access?

Format(7,"00")

Result: 07

or alternatively ...

what i am actually trying to do is return the string yyyymm based on
the current date. eg 200506 (June 2006)

Is there a simple way of doing this?

The code i am currently using is

CONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,
GETDATE()))

Result: 2005 !!!!

At least i thought i would get 20056. But obv what i am aiming for is
200506.

Thanks in advance.

TCSee the various formats under CONVERT in Books Online:

select convert(char(6), current_timestamp, 112)

But in general, it's better to format output in the front end, not in
the database.

Simon|||To format a number as text with a leading zero, you can use the
following trick:

SELECT RIGHT('0'+CONVERT(varchar(2),YourNumber),2)

To get the year and month of a date, formatted as "yyyymm" you can use:

SELECT CONVERT(varchar(4),YEAR(GETDATE()))
+RIGHT('0'+CONVERT(varchar(2),MONTH(GETDATE())),2)

or:

SELECT CONVERT(varchar(6),GETDATE(),112)

By the way, your code doesn't return '2005', as you think; it returns:
'2005 6 '
This is because the char data type has a fixed length (it doesn't trim
trailing blanks) and because the default size of a char is 30.

For more informations about datetime data types, see:
http://www.karaszi.com/SQLServer/info_datetime.asp

Razvan|||thanks heaps - the SELECT CONVERT(varchar(6),GETDATE(),1*12) worked a
treat!

exactly what i needed

cheers,

TC|||(tcumming@.smorgonsteel.com.au) writes:
> Hi ... i'm sorry to hassle this user group but i have an urgent need
> for some code - i have tried and tried to find a solutionn elsewhere -
> but the problem is i don't really know what i am looking for.
> What is the equivalent SLQ code for the below statement which works in
> MS Access?
> Format(7,"00")

While you already have gotten help with your urgent needs, permit me
to point out that Functions->String Functions in the T-SQL Reference
of Books Online is a good place to start. That and the CAST and
CONVERT topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Format Date Parameter in a Report

Hi,
Can I get a Date Parameter that the user filled and format it to
'yyyy-mm-dd' format, so I could execute the Query with the formated date?
LimorIf you are Trying what I think you are trying ...
Here is something you could try
Create a Dataset for your Date Values
I named mine DATE
in the Query type
--
SELECT
LABEL = LEFT([DATEFIELDNAME],11), --WRITES AUGUST 30 2005
VALUE = [DATEFIELDNAME] --WRITES 2005-08-30 00:00:00.000
FROM dbo.SALES
GROUP BY [DATEFIELDNAME]
ORDER BY [DATEFIELDNAME] DESC
--
Then Create a Parameter for your date
I named mine BDATE
DATATYPE = STRING
AVAILABLE VALUES = FROM QUERY
DATASET = DATE (NAME I CREATED)
VALUE = VALUE (THIS IS WHAT THE QUERY IN THE DATASET WILL SEE)
LABEL = LABEL ( THIS IS WHAT THE CLIENT WILL SEE)
--OPTIONAL
DEFAULT QUERY = FROM QUERY
DATASET = DATE
VALUE = VALUE
--
In the dataset that runs the Query for the report you would then have your
date field = @.BDATE (cap sensitive)
I hope this helps and is what you were looking for :)
"Limor Bellison" wrote:
> Hi,
> Can I get a Date Parameter that the user filled and format it to
> 'yyyy-mm-dd' format, so I could execute the Query with the formated date?
> Limor|||Hi,
Thank you for your prompt answer.
I cannot use your solution for 2 reasons:
1-The dates combo box will be too long
2-I get the dates by Url and there is a chance that the date in the combo
will not include the date in the Url query
Thanx,
Limor
"EsWallace" wrote:
> If you are Trying what I think you are trying ...
> Here is something you could try
> Create a Dataset for your Date Values
> I named mine DATE
> in the Query type
> --
> SELECT
> LABEL = LEFT([DATEFIELDNAME],11), --WRITES AUGUST 30 2005
> VALUE = [DATEFIELDNAME] --WRITES 2005-08-30 00:00:00.000
> FROM dbo.SALES
> GROUP BY [DATEFIELDNAME]
> ORDER BY [DATEFIELDNAME] DESC
> --
> Then Create a Parameter for your date
> I named mine BDATE
> DATATYPE = STRING
> AVAILABLE VALUES = FROM QUERY
> DATASET = DATE (NAME I CREATED)
> VALUE = VALUE (THIS IS WHAT THE QUERY IN THE DATASET WILL SEE)
> LABEL = LABEL ( THIS IS WHAT THE CLIENT WILL SEE)
> --OPTIONAL
> DEFAULT QUERY = FROM QUERY
> DATASET = DATE
> VALUE = VALUE
> --
> In the dataset that runs the Query for the report you would then have your
> date field = @.BDATE (cap sensitive)
>
> I hope this helps and is what you were looking for :)
> "Limor Bellison" wrote:
> > Hi,
> >
> > Can I get a Date Parameter that the user filled and format it to
> > 'yyyy-mm-dd' format, so I could execute the Query with the formated date?
> >
> > Limor

Format Date in Reports

Dear friends

I have a problem with the format date, because my database has the format yyyy/mm/dd and in the computer user's the default configuration of date is dd/mm/yyyy, the problem exists with the reports that have the parameter of date type, the users input dd/mm/yyyy and the report request error in the format.

My question is, how do i make the any format input the users, i change in the report for this recieve the format yyyy/mm/dd, where is the parameter i have to change, the easy request is i have change the configuration of computer users, but i dont have this.

Thank you

Dear ,

U can try it like this

If u have a mtrix set report

=Day(First(Fields!OrderDate.Value, "MatrixSource"))& "/" & month(First(Fields!OrderDate.Value, "MatrixSource"))& "/" & year(First(Fields!OrderDate.Value, "MatrixSource"))

If not then remove the matrixsource text from the foruma

|||

Thank you for your response, but how use this code in the parameters, because my problem is whit the parameter, the users input dd/mm/yyyy, but my database is configurate in yyyy/mm/dd, i don't know how i make this change from parameters to store procedure who is that recive the parameters.

Thank you

|||You can do the conversion in the stored procedure using the CAST or CONVERT functions (or in the command text, depending on the command text). Or you could convert it before hand in the expression for the query parameter value using the DateTime.Parse method along with ToString("yyyy\mm\dd"). For example,

=DateTime.Parse(ReportParameters!DateParam.Value, New CultureInfo("fr-FR", False)).ToString("yyyy/mm/dd")

For more information:
http://msdn2.microsoft.com/en-us/library/kc8s65zs.aspx

For more information on CAST and CONVERT:
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Ian