Interesting SQLProblem using joins

Using Join when 2 columns in one table point to 1 column in another table

I have spent hours on this problem and no research has turned in my favour. Does anyone have any examples they can put forward for me.

I am really desperate to sort this out, and any help will be greatly appreciated.

Thanks
ShaunAre you meaning col1.value + col2.value from table1 is the equal to colx.value of table2
P

Interactive Sort on date field

I have an RS 2005 June CTP report where the data source is an MDX query against AS 2005 June CTP. I'm using Interactive Sort on several columns and it's working fine for the numeric and string fields, but it's sorting the date columns alphabetically instead of chronologically. Is there a way I can specify that these fields are date fields and should be sorted chronologically?
Thanks,
Dirk

Most likely the date field is returned as string through the provider. Try to explicitly convert it with the CDate(...) function in the sort expression. E.g. =CDate(Fields!OrderDate.Value)
-- Robert

|||That did the trick, thanks for the help.|||

I'm also using Interactive Sort on several columns in MSRS 2005,
my problem is with sorting the currency columns (columns with $ sign) - it doesn't work, dallor ammounts are not sorted in accending/decending order. Is there a way I can specify that these fields be sorted as numbers?

Thanks!

|||

It sounds like you are sorting on strings (because the underlying dataset field contains a string rather than a numeric value).

You should sort on a numeric value of the dollar amount - e.g. by changing the dataset query to return the numeric value instead of the string. You can then still apply the currency formatting in the report by using the Format property on textboxes and set it to e.g. C0 (for currency formatting with zero decimals)

-- Robert

Interactive Sort on date field

I have an RS 2005 June CTP report where the data source is an MDX query against AS 2005 June CTP. I'm using Interactive Sort on several columns and it's working fine for the numeric and string fields, but it's sorting the date columns alphabetically instead of chronologically. Is there a way I can specify that these fields are date fields and should be sorted chronologically?
Thanks,
Dirk

Most likely the date field is returned as string through the provider. Try to explicitly convert it with the CDate(...) function in the sort expression. E.g. =CDate(Fields!OrderDate.Value)
-- Robert

|||That did the trick, thanks for the help.|||

I'm also using Interactive Sort on several columns in MSRS 2005,
my problem is with sorting the currency columns (columns with $ sign) - it doesn't work, dallor ammounts are not sorted in accending/decending order. Is there a way I can specify that these fields be sorted as numbers?

Thanks!

|||

It sounds like you are sorting on strings (because the underlying dataset field contains a string rather than a numeric value).

You should sort on a numeric value of the dollar amount - e.g. by changing the dataset query to return the numeric value instead of the string. You can then still apply the currency formatting in the report by using the Format property on textboxes and set it to e.g. C0 (for currency formatting with zero decimals)

-- Robert

Interactive sort not working in Tabular View

I am just starting with Sql Server reporting, but I can't get interactive sort to work for columns with aggregate fields.

I am using the following query for DataSet:

SELECT
Store.ID as StoreID,
Store.Name as StoreName,
COUNT(*) as NumReservations,
SUM(Appointment.TotalBeforeTaxes) as Revenue
FROM Store LEFT JOIN Appointment ON Store.ID=Appointment.StoreID
GROUP BY ALL Store.ID, Store.Name
ORDER BY Store.Name ASC

For report, I am using tabular data view. Interactive sorting works great for StoreID, StoreName, but doesn't work for NumReservations and Revenue fields. I turned it on for all 4 columns.

What could be causing this problem?
Figured out the problem... It just doesn't work in FireFox. Seems to work fine in IE.

interactive sort in group

hi,

i created a report using table with added group header and footer. i set a few columns at the table header for interactive sort. however, the sort doesn't work for thhe field that i place in the group header.

Is interactive sort supported in group header?

Thanks.

Yes, interactive sort is supported in group header. When you click on the sort in the group header, it just doesn't do anything? Can you attach your rdl so that I can see why it's not working for you?

- Fang

|||

thanks Fang for your reply. sorry if this seems silly. how do i attach the rdl file here?

thanks again

|||Looks like there is no way to attach a file here. Can you just post the XML in the thread?|||

hi Fang.. sorie... probably i should rephrase my question.

actually i have fields in my group header and place the labels for the respective fields at the table header. can i sort the fields in group header using the label in table header?

sorie for asking the wrong question.. thanks!

Interactive drill-through bug

Hi,
I've got a report with 3 columns, each one contains a "Y" which links
to a different report. Two of the columns work 100% but for some reason
the 3rd column won't link through once the report has been deployed.
All 3 links work fine when I'm using the "preview" tab in VS.NET
I've tried through report manager & our custom web application and I
get the same problem - It's very weird as I'm not doing anything fancy
and like I said it works fine through VS.NET
currently running SP2.
Any help would be much appreciated.
Thanks,
David SandellDavid,
What error message are you getting?
This sort of problem often indicates a permission issue or a pathname
issue.
Chris
sandelld wrote:
> Hi,
> I've got a report with 3 columns, each one contains a "Y" which links
> to a different report. Two of the columns work 100% but for some
> reason the 3rd column won't link through once the report has been
> deployed. All 3 links work fine when I'm using the "preview" tab in
> VS.NET
> I've tried through report manager & our custom web application and I
> get the same problem - It's very weird as I'm not doing anything fancy
> and like I said it works fine through VS.NET
> currently running SP2.
> Any help would be much appreciated.
> Thanks,
> David Sandell

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Integrity Constraint Error on SQL Server column with no constraints

I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.

I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?

I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2

Thanks,

Chris

Try setting the extended property IMEX to 1 in the Excel Source properties. Search this forum for IMEX and you'll get plenty of answers.|||And make sure you don't have any empty values in the excel source. Your table is set to not accept NULLs, so make sure there are none going into it.|||

Ok, so I did a search of this forum and found the recommendation to add "IMEX=1; MAXROWSTOSCAN=0" to the Excel Connection manager connection string. So I hunted and pecked and found that if I displayed the properties on my excel connection manager, there was a property called Expressions with three dots next to it that if you clicked on would display a Property and an Expression column. I chose connection string for the Property and pasted the recommended string into the Expression. I tried with and without quotes, eliminated the ; MAXROWSTOSCAN=0 part

(IMEX=1), also tried IMEX==1 because that's what the ensuing error message recommended - All to no avail.

what am I missing ?

Thanks,

Chris

|||Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

Your connection string would have to be similar to the above. Look at your existing ConnectionString property, copy it, and add IMEX=1 to the Extended Properties as I have done above.|||

Sorry to drag this out, but I had to add a Connection String Property to my Excel Connection Manager properties. When I bring up the Expressions dialog as I described above, all I see is a blank dialog box. Should I be using an Excel Connection manager or a modified OLE DB connection manager ? I have a text book that recommends using the latter for an excel file if you need finer control.

|||

You may use either one connection if you plan to set it only once. You do not need to use Expressions, change the ConnectionString property directly.

If you plan to edit/change the connection settings, than you might be better with pure OLE DB connection because the Excel wrapper might override the settings you manually put into the connection string.

Thanks,

Bob

|||

I directly edited the Connection string for my Excel Connection manager, adding IMEX=1; to what was there. The complete string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

I am unable to use the connection manager like that - I'm getting another error "Could not find Installable ISAM"

I assume HDR=YES means there is a header row in the spreadsheet, which there is in this case.

Thanks,

Chris

|||

Christohperrobin wrote:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

IMEX goes *INSIDE* the Extended Properties property as I've shown earlier.

....Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";|||

I put the quote (") in the wrong place. I corrected that so my connection string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

IT'S WORKING FINE NOW!

Thanks for all your help,

Chris

|||

Actually I had to run it a few more times before I got all the rows to load. The package was still skipping (Redirecting) some of the rows due to the SAME error on another column that had mixed format data just like the one that originally caused the problem. This other column also had both straight numeric and combination strings (ex: 84001, 57B42).

From what I gathered reading about this problem, it looks like Integration Services makes certain decisions about the metadata associated with the contents of Excel files based upon the first few rows of the input spreadsheet. With this in mind I moved the row containing the 57B42 value up closer to the top, so now instead of the first 10 rows having all numeric values in that column, there was a non-numeric value appearing early on in the mix.

When I tried to run the package after this change, It threw an error right at the beginning indicating there was a problem with the Excel Data Flow source. The error indicated that there was mismatched metadata between the Excel Source and the SQL Server OLE DB Destination. The error message also offered to fix the problem without my intervention. I did choose to allow this to happen. After that the package ran to completion and all 2034 rows in the spreadsheet loaded into the DB table.

I am just wondering how I should deal with this in the future. Do I actually have to be concerned with the order of the rows in my spreadsheet ? Why should it be a problem to load character data and apparent numeric data into an nvarchar field ?