MDX Measure Expression Syntax Divide By A Number
May 29, 2008
One of my measures is in seconds and I would like to convert it to hours.
It is simply a number in decimal format
the expression I tried is this
[Measures].[OPEN DURATION] /60
and I ge this message
Error 1 Errors in the metadata manager. The measure expression of the OPEN DURATION measure contains the [(60)] operand , which could not be resolved. 0 0
All help is appreciated, thank you in advance. I have tried a couple different syntax's with no luck.
View 5 Replies
ADVERTISEMENT
Jun 3, 2015
I am new to SQL. I added a calculated field to my dataset with and expression to calculate efficiency.
My expression is as follows: =Fields!EstTotLbrHrs.Value/Fields!ActTotLbrHrs.Value
In some cases my value for ActTotLbrHrs is 0, so it returns #Error
How do I return "N/A" for fields with ActTotLbrHrs = 0
View 1 Replies
View Related
May 7, 2007
I'm trying to help out a beginning DBA and come up with a SQL query. Here is the information I've been given so far.
"I have a SQL database (TEST1_new) with several tables. I need to have some values updated in one of the tables HARR2APP.CUSTOMER_ORDER_LINE_TAB1). I need the value that exists in the QTY_SHIPPED field to be divided by 250. I also need to include a WHERE statement for the PLANNED_SHIP_DATE greater than 11/15/2004 and a CATALOG_NO =18053185292 or 18053185285.
Basically, what I need to do is take the value for QTY_SHIPPED for Catalog_NO 18053185285 & 18053185282 and divide the result by 250"
After reading through her statement above about 10 times and guessing a bit, here's what I was able to come up with. I'm sure the syntax is not correct but maybe it's close to being what's needed?
INSERT INTO HARR2APP.CUSTOMER_ORDER_LINE_TAB1()
(SELECT dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB.QTY_SHIPPED LIMIT 1) /250),PLANNED_SHIP_DATE from dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB
WHERE CATALOG_NO = ‘18053185292’
OR CATALOG_NO= ‘18053185285’
AND PLANNED_SHIP_DATE > ‘11/15/2004’;
Any help is greatly appreciated.
Chris.
View 4 Replies
View Related
Jul 7, 2015
I have this rows as result of my query:
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15
a 100 7-7-15
a 100 8-7-15
b 200 7-7-15
b 200 8-7-15
b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15 50
a 100 7-7-15 50
a 100 8-7-15 100
b 200 7-7-15 200
b 200 8-7-15 100
b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) :
SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3,
CASE WHEN TRAPR3 IS NULL THEN
(CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
[Code] .....
View 8 Replies
View Related
Jun 22, 2015
my table includes detailed records with total Rate repeated in each record:
CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,
[code]....
View 1 Replies
View Related
Aug 4, 2015
We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
View 6 Replies
View Related
Jul 20, 2015
I have a table with date in the following format:
orderID | dateOrdered | customerID
101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3
I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today.
So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.
For example if we say 60 days my table would look like the following:
Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".
I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.
View 12 Replies
View Related
Apr 26, 2007
Hi,
This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).
What I WANTED to do is a straight character check:
@[User::xml_output] == "ABC"
but that wasn't working... so I deceided to try the len function.
However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.
len(trim(DT_WSTR,18,1252)@[User::xml_output])) > 17
len(trim(@[User::xml_output])) > 17
(DT_WSTR,18)@[User::xml_output] > 17
I just want to trim @[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.
Thanks,
Phil
View 5 Replies
View Related
Apr 26, 2007
Hi,
I'm having some trouble with this syntax:
@[User::xml_output] != "<ROOT></ROOT>"
I have a variable that I KNOW is = '<ROOT></ROOT>' and I've set up a precendence constraint to only go to the next step if the above not true, correct?
Do I need to convert the right side of the equation to a string or something first? Does anybody know that syntax off hand?
Thanks,
Phil
View 6 Replies
View Related
Jun 28, 2007
How would I do a select from a container using the previous container's starttime as a condition in the variable?
select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @[System::ContainerStartTime] + "
Thanks,
Phil
View 11 Replies
View Related
Sep 11, 2007
what is the syntax for IN in Expression?
I am tryin to do @ID IN (1,2,3,4) then return 1 else 0...
View 3 Replies
View Related
Feb 27, 2007
What would be the correct syntax if I wanted to add the following lines into a variable using an expression? The lines should be the first two rows before my XML.
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
+@[User::xml_output]
Thanks,
Phil
View 5 Replies
View Related
May 9, 2007
I have a variable that I'm storing in a database. The variable is a server name "servernamepub". When I watch this variable in the locals it becomes "servername\pub". Is there something going on in the expression/variable syntax which is changing this?
Thanks,
Phil
View 8 Replies
View Related
May 13, 2015
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
View 4 Replies
View Related
Sep 12, 2007
Hi,
I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.
I am trying to code :
IIF(Value_A = 0, <NULL>, Value_A)
It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.
I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.
Thanks.
regards,
Kenny
View 4 Replies
View Related
Sep 25, 2007
Hi,
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :
2007-04
2007-05
2007-06
Amount Sales
1000
2000
3000
Unit Sales
10
20
30
Total
1010
2020
3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
Thanks
best regards,
Tanipar
View 1 Replies
View Related
Jan 22, 2008
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
View 1 Replies
View Related
Jan 31, 2008
I'm getting the error listed above. To create this I used the wizard in VS2005 for the datagrid. The delete works but not the updating. I can't seem to find what's wrong. In the update parameters I've removed eventID and added it, neither works. Delete does work. Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>
View 4 Replies
View Related
Feb 8, 2008
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
View 9 Replies
View Related
Sep 15, 2015
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
View 4 Replies
View Related
May 3, 2015
I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
[Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
ON
COLUMNS,
[code]....
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
View 4 Replies
View Related
Apr 17, 2008
Hi All
Lets say i have that table:
Month Sales
1 30
1 10
1 10
1 20
2 30
2 30
2 15
3 1
How can i get the sum of the sales where month=1?
I need to do it in the Expression of a text box
View 7 Replies
View Related
Feb 6, 2008
Hi everyone,
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
Thanks for all your help,
regards,
Chris
View 12 Replies
View Related
Jul 2, 2007
I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!
Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.
Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.
I'm just getting started with SQL 2005 so I don't know how to do this using an expression.
Any help would be greatly appreciated.
Thanks,
Gray
View 3 Replies
View Related
Apr 21, 2015
So if my Parameter value is...
MCCL, MCMA, MCPL, MCPR, MCRE, MCSE, MCSN, MCVA, MCCL, MCEP, MCMA, MCRE out of 43 possible choices,
What is the expression syntax to hide my tablix column based on my parameter value?
=iif(instr(Join(Parameters!ColumnVisibility.Value,","),"MCCL","MCMA","MCPL"...)>0,false,true)
Just not sure of the syntax...or do I have to qualify each one???
= IIf(Parameter!PF1.Value = "MCCL" OR Parameter!PF1.Value = "MCMA"..., TRUE, FALSE)
View 6 Replies
View Related
Feb 6, 2008
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
View 1 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
May 17, 2001
So far we are happy with MS OLAP 2000 service.
In the past we did cubes with aggregation method using summary.
MS OLAP server allows aggregate functions (Sum, Min, Max, Count, and Distinct Count).
But now the customer wants to know percentage of SUM(measure A) /SUM (measure B) in different levels and dimension combination.
Any idea?
View 1 Replies
View Related
Apr 3, 2015
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh',
@SearchString VARCHAR(10)= '_',
@OccuranceNumber SMALLINT = 1
DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0
WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
View 9 Replies
View Related
Jun 3, 2008
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59.
[SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code>
Here is my button click sub in its entirety:
1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs)
2 'Variable declarations...
3 Dim articleid As Integer
4 articleid = Request.QueryString("aid")
5 Dim strSelectQuery, strInsertQuery As String
6 Dim strCon As String
7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection()
8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand()
9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader
10 Dim MyHttpAppObject As System.Web.HttpContext = _
11 System.Web.HttpContext.Current
12 Dim strRemoteAddress As String
13 Dim intSelectedRating, intCount As Integer
14 Dim Ratingvalues As Decimal
15 Dim Ratingnums As Decimal
16 Dim Stars As Decimal
17 Dim Comments As String
18 Dim active As Boolean = False
19 Me.lblRating.Text = ""
20 'Get the user's ip address and cast its type to string...
21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress)
22 'Build the query string. This time check to see if IP address has already rated this ID.
23 strSelectQuery = "SELECT COUNT(*) As RatingCount "
24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'"
26 'Open the connection, and execute the query...
27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString
28 conMyConnection.ConnectionString = strCon
29 conMyConnection.Open()
30 cmdMyCommand.Connection = conMyConnection
31 cmdMyCommand.CommandType = System.Data.CommandType.Text
32 cmdMyCommand.CommandText = strSelectQuery
33 intCount = cmdMyCommand.ExecuteScalar()
34 intSelectedRating = Int(Me.rbRating.Text)
35 conMyConnection.Close()
36 'Close the connection to release these resources...
37
38 If intCount = 0 Then 'The user hasn't rated the article
39 'before, so perform the insert...
40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) "
41 strInsertQuery += "VALUES ("
42 strInsertQuery += intSelectedRating & ", '"
43 strInsertQuery += strRemoteAddress & "', "
44 strInsertQuery += articleid & ", '"
45 strInsertQuery += comment.Text & "', '"
46 strInsertQuery += active & "'); "
47 cmdMyCommand.CommandText = strInsertQuery
48 conMyConnection.Open()
49 cmdMyCommand.ExecuteNonQuery()
50 conMyConnection.Close()
51 Me.lblRating.Text = "Thanks for your vote!"
52 Comments = comment.Text.ToString
53
54 If Len(Comments) > 0 Then
55 emailadmin(comment.Text, articleid)
56 End If
57 'now update the article db for the two values but first get the correct ratings for the article
58 strSelectQuery = _
59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
61 conMyConnection.Open()
62 cmdMyCommand.CommandText = strSelectQuery
63 dtrMyDataReader = cmdMyCommand.ExecuteReader()
64 dtrMyDataReader.Read()
65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
67 Stars = Ratingvalues / Ratingnums
68 conMyConnection.Close()
69 'Response.Write("Values: " & Ratingvalues)
70 'Response.Write("Votes: " & Ratingnums)
71
72 UpdateRating(articleid, Stars, Ratingnums)
73 Else 'The user has rated the article before, so display a message...
74 Me.lblRating.Text = "You've already rated this article"
75 End If
76 strSelectQuery = _
77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
79 conMyConnection.Open()
80 cmdMyCommand.CommandText = strSelectQuery
81 dtrMyDataReader = cmdMyCommand.ExecuteReader()
82 dtrMyDataReader.Read()
83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
85 Stars = Ratingvalues / Ratingnums
86 If (Ratingnums = 1) And (Stars <= 1) Then
87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
88 ElseIf (Ratingnums = 1) And (Stars > 1) Then
89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then
91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
92 ElseIf (Ratingnums > 1) And (Stars > 1) Then
93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
94 End If
95
96 'Response.Write(String.Format("{0:f2}", Stars))
97 'Response.Write("Values: " & Ratingvalues)
98 'Response.Write("Votes: " & Ratingnums)
99 If (Stars > 0) And (Stars <= 0.5) Then
100 Me.Rating.ImageUrl ="./images/rating/05star.gif"
101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then
102 Me.Rating.ImageUrl = "./images/rating/05star.gif"
103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then
104 Me.Rating.ImageUrl = "./images/rating/1star.gif"
105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then
106 Me.Rating.ImageUrl = "./images/rating/15star.gif"
107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then
108 Me.Rating.ImageUrl = "./images/rating/2star.gif"
109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then
110 Me.Rating.ImageUrl = "./images/rating/25star.gif"
111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then
112 Me.Rating.ImageUrl = "./images/rating/3star.gif"
113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then
114 Me.Rating.ImageUrl = "./images/rating/35star.gif"
115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then
116 Me.Rating.ImageUrl = "./images/rating/4star.gif"
117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then
118 Me.Rating.ImageUrl = "./images/rating/45star.gif"
119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then
120 Me.Rating.ImageUrl = "./images/rating/5star.gif"
121 End If
122 dtrMyDataReader.Close()
123 conMyConnection.Close()
124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating:
http://www.link-exchangers.com/view_full_article.aspx?aid=51
Thanks for helping me figure this out.
View 4 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Jul 27, 2001
I have a field that divides one field by the sum of two others. However, when both of the two latter fields are 0's, I get a divide by zero error and the output halts. How do I get this so that for these records I put a NULL or UNDEFINED or something in there at least so I can see the rest of the output. Thanks.
View 1 Replies
View Related
