Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with dates in Dataset Select statement

I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.

The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:

strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"

I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.

The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?

I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to.

BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.

Diane

You have to put the Datediff portion in the Filter column of the Query Designer. Then SQL will use it in the WHERE clause.|||

Please show us your code. That would be easier for us to figure out why it does not owrk for you.

Also, please take a look the following link, it shows how to use SqlDataSource with parameters.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/sqldatasource.aspx

Here is an example from the above link:

<asp:DropDownList ID="DropDownList1" ... runat="server"/>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Pubs %>"
SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">
<SelectParameters>
<asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

Hope this helps

|||

Yes, I tried that. I get an error, so I must be doing something wrong. I tried

(@.lowAge < DATEDIFF(yyyy, [DATE OF BIRTH], date))

And I get an error: 'date' in expression not part of the query

Diane

|||

I can't use the SqlDataSource control this way. I have a huge select statement and many of the parameters can be empty. For example. users can select to search by state or not. So in my code behind I check if a state has been selected. If so, I add it to the select string. I have to check first, or I'll have a string that looks for records with no state in them, which is not what I want. So I build the select string in the code behind. If strSQL is the string I've built, I set SqlDataSource1.SelectCommand = strSQL. This works great for the first page, but the grid disappears if I try to do any paging. Google and forum searches turned up others with the same problem, but no answers. I suspect there's an answer for this, but that it's much more complicated than using the dataset which I'd rather use anyway.

I'd hate to say it, but at this point, faster is better. I'm at the end of a long project. My customer would like this done so he can move on to the next phase, and I'm ready for something new.

Diane

|||

I've narrowed down my problem.If I want to do this: (DATEDIFF(yyyy, [DATE OF BIRTH], Date) <= @.rUpperAge), how do I specify the current date in the dataset designer? It doesn't recognize Date, Date() Now, Now() or DateTime.now. I even tried to make today's date a parameter (DATEDIFF(yyyy, [DATE OF BIRTH], @.thisDate) <= @.rUpperAge). Didn't accept it. So how do I specify that I'm looking for a date difference between the field value and today?

Diane

|||

I never used the query designer. So I cannot give you any suggestion on that.

Regarding get the today's date, since it is used to build the query, so you shoudl use the sql function: GetDate().

|||

I second that

You should use getdate() to get the current date since you are generating sql query.

Now() is a VB keyword and would be used within VB code.

|||

Thanks everyone, GetDate() as part of the select string did it. I had tried to use it this way:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & GetDate() & "') <= " & Request.Form("UpperAge") & ")"

But this worked:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], GetDate()) <= " & Request.Form("UpperAge") & ")"

Diane

Problem with Dataset.Merge

Hello guyz,
I am using SQL server and I want to merge two datasets. I have two tables which are similar in structure-(same number of columns). I want to display these two tables in one datagrid. I have 5 rows in the first table and 3 on the other. So in the datagrid it should have 8 rows. Part of my code is below…

ConnectToDatabaseServer()
Dim querystr As String = ("Select username, password from PublicAccounts")
Dim da As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds1 As DataSet = new DataSet()
da.Fill(ds1, "firsttable")

querystr = ("Select username, password from EmployeeAccounts")
Dim da2 As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds2 As DataSet = new DataSet()
da2.Fill(ds2, "secondtable")

ds1.Merge(ds2,False, System.Data.MissingSchemaAction.Add)
MergeGrid.DataSource = ds1
MergeGrid.DataBind()
myConnection.Close()

This code only displays the contents of ds1 (5 rows).
My table structure are as follows:
PublicAccounts (username Char(50), password char(50), pid integer primary key)
EmployeeAccounts (username Char(50), password char(50), eid integer primary key)

I really would appreciate any help.
Thank you..couple of ideas. nothing jumps out at me immediately. try setting preservechanges to true (the second parameter of the ds1.Merge) just for grins.

also, try wrapping the merge in a try-catch block and see if any errors are being thrown. that might help narrow it down.

cs