|
hello
SSRS - web service interface generated pdf's with slightly different font/color compared to those pdf when access via "Direct URL access" (point browser to http://localhost/reports/SomeReport/SomeReport - then export by pdf).
Any idea why? Perhaps it has to do with DeviceInfo in Render method? What do I need to do so two are same?
Thanks
dev
|
|
|
|
|
i have a crystal report which is bind with oledb database. tommorrow its working but now it show error that "Table could not found". i googled my problem and can't find any satisfactory solution if anyone knows it solution plz share with me.
advance thnx 4 help
|
|
|
|
|
|
Hi i have 2 tables..,
one is master another one is child...,
ex:
Master=>Pid is primary key
===========
Pid Name
1 m1
2 m2
3 m3
4 m4
5 m5
6 m6
Child=>Cid is primary key
==========
Cid Pid Name
1 2 nm1
2 2 nm2
3 1 nm3
4 5 nm4
5 6 nm5
Now i need the lastest records...,
O/p:
======
Pid Cid Name
6 5 nm5
5 4 nm4
4 null m4
3 null m3
2 2 nm2 //in child table have 2 records for pid...,in tht latest is Cid=2
1 3 nm3
Thanks & Regards,
Member 3879881,
please don't forget to vote on the post
|
|
|
|
|
Try this
declare @tblMaster table(pid int primary key,name varchar(20))
insert into @tblMaster
select 1,'m1' union all select 2,'m2' union all
select 3,'m3' union all select 4,'m4' union all
select 5,'m5' union all select 6,'m6'
declare @tblChild table(cid int primary key,pid int,name varchar(20))
insert into @tblChild
select 1,2,'nm1' union all select 2,2,'nm2' union all
select 3,1,'nm3' union all select 4,5,'nm4' union all
select 5,6,'nm5'
select pid,cid,name from
(
select
m.pid
,c.cid
,case when c.name IS null then m.name
else c.name
end name
,row_number() over(partition by m.pid order by c.name desc) as rn
from @tblChild c
full join @tblMaster m
on c.pid = m.pid
)x(pid,cid,name,rn)
where rn = 1
order by pid desc
Niladri Biswas
|
|
|
|
|
whts tht row_number function?
Thanks & Regards,
Member 3879881,
please don't forget to vote on the post
|
|
|
|
|
|
This should do it
select m.pid,c.cid,c.name
from Master m
join Child c on c.pid = m.pid
and c.cid = (select max(cid) from Child c1 where c1.pid = m.pid)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Okay, someone straighten me out. I have two tables: [Budget] is the monthly budget for a bunch of accounts and [Account List] is (duh) the master list of accounts. I need to find the account names, [Twin Pack], that do not have any budget, ie no record exists, for a specified date, [Date].
Here's what I wrote:
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));
When I run it as is, it returns nothing. To troubleshoot, I put the subquery in a query by itself and got the desired results: the list of accounts that DO have budget in the specified month. Also, when I reversed the EXISTS statement by taking out the "Not", I also got the list of accounts that DO have budget in the specified month.
What am I missing?
|
|
|
|
|
You seem to have 2 disassociated queries here, one on
Account List and another on Budget with no logic to join them.
[rant] Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date) [/rant]
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE something NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));
also make sure your internal select returns the expected records (what is DateValue)
|
|
|
|
|
Mycroft Holmes wrote: Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date)
Yeah, I'm right up there on that soap box with ya. It's a linked table that someone else created. I could write some code to go change the field names, but this was supposed to be a quick and easy DB.
I found out what I was missing. I scrapped the first approach and did a Left Join on the two tables.
|
|
|
|
|
Try this
declare @tblAccountList table(TwinPack int)
insert into @tblAccountList
select 111 union all select 222 union all
select 333 union all select 444 union all
select 555 union all select 666 union all
select 777 union all select 888 union all select 1100
declare @tblBudget table(TwinPack int,Mnth varchar(20))
insert into @tblBudget
select 111,'Jan' union all select 222,'Feb' union all
select 333,'Mar' union all select 444,'Apr' union all
select 555,'May' union all select 666, 'Jun' union all
select 777,'Jul' union all select 888 ,'Aug' union all
select 999,'Sep' union all select 1000 ,'Oct' union all
select 1100,'Nov' union all select 1200 ,'Dec'
SELECT a.TwinPack
FROM @tblAccountList a
except
SELECT b.TwinPack
FROM @tblBudget b
WHERE b.Mnth = CONVERT(varchar(3),DATENAME(mm,GETDATE()))
Basically I am finding out the TwinPack from @tblAccountList where the budget is not in November. I am using Except here
Note- I have done the schema by myself which I hope is as per ur requirement.Else you please correct and I will again give a shot.
Hope this helps
Niladri Biswas
|
|
|
|
|
|
Try this
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT 1 from Budget
WHERE Budget.[Twin Pack] = [Account List].[Twin Pack]
and Budget.Month=DateValue([Date])
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
So I have a table Invoices with ID type Guid. But the user wants to have a unique, human-friendly code, to go along w/ each row. Any good suggestions on how to implement this?
Should I have the client app check->generate->update, or should I have some sort of scalar function generate this (auto-compute column value). Maybe a trigger? Thoughts appreciated.
(running mssql'08)
|
|
|
|
|
Keep you guid as the primary key (I hate guids) and add a CustomerCode field, populate the initial set of data according to the clients needs and let the operator manage the code from then on.
NEVER and I mean not once do you use this field for anything but human UI searches, continue to use the guid as the key constraint for you data.
|
|
|
|
|
|
hii ,
i have question and i wont ask this question in form of the example.
if we have table named CUSTOMER:
CUSTOMER TABLE:
CustomerID : Integer (Not a primary Key)
CustomerName: TEXT
And This is the table's data:
CustomerId | CustomerName
12 | Jone
25 | Mark
25 | Marc
10 | AJ we note that CustomerId number 25 has deffrint spiling of the CustomerName for the same Customer!
i need to return the unique CustomerId with the first CustomerName.
i write this querey :
select DISTINCT CustomerID , CustomerName
From Customer
but it got me the four records !! the 25 is dublicated twies becuse the spiling of the name are deffrint for the same Customer
how to write querey return the unique CustomerId with the first CustomerName?
Many Thanks
|
|
|
|
|
Maybe it ins't the best solution but you can use a group by (CustomerId), then you have to choose wich CustomerName is the choosen (min(CustomerName), max(CustomerName...). Sql:
>select CustomerId, max(CustomerName) from Customer group by CustomerId.
Visit my blog at http://dotnetforeveryone.blogspot.com
|
|
|
|
|
Ohhh...Yes it is work !! many thanks
|
|
|
|
|
SELECT
@customerId as customerId,
(SELECT TOP 1 customerName FROM Customer WHERE customerId=@customerID ORDER BY customerName) AS customerName
Whenever your data looks this bad, its a sure-fire signal to clean your data, and ensure your apps only insert clean data.
|
|
|
|
|
hii ,sorry what do you mean by sure-fire signal ?
yes i agree with you that the data is look bad , but i work on a huge data that which is extracted by the Customers them self so i can not edit any thing.
many thanks
|
|
|
|
|
Member 3018046 wrote: hii ,sorry what do you mean by sure-fire signal
It means that it is time to examine why you have 2 customer records with the same Id, but different names.
|
|
|
|
|
You can even try this
declare @tblcustomer table(customerid int,customername varchar(50))
insert into @tblcustomer
select 12,'Jone' union all select 25,'Mark' union all
select 25,'Marc' union all select 10,'AJ'
select x.customerid
,x.customername
from
(
select
ROW_NUMBER() over (partition by customerid order by customerid) as rn
,customerid
,customername
from @tblcustomer)X(rn,customerid,customername)
where rn = 1
Output:
customerid customername
10 AJ
12 Jone
25 Mark
Niladri Biswas
|
|
|
|
|
How do you know which is the first CustomerName? Is the 'first' name always the correct one?
|
|
|
|