Query color change

Archived Topics
1

I have a two part question. I beleive part two has already been covered here before.

Part one - Have a report that is running a query, the data from the query is populating the chart. In the query there is a section that polls 2 tables, if the data it is looking for is in table 1 then that is all there is to it, if it is not then it obtains what it can from table 2. To do this I am using the following code:

ISNULL(a.prod_std, b.prod_std) as prod_std

These two columns are as you can guess from different tables. The data type is BIGINT. What i want to do is have it be one color if the a. table is used and a different color if the b. table is used. I have tried the following but it comes up with ERROR CONVERTING VARCHAR TO BIGINT.

ISNULL('<html><font color = blue>' + a.prod_std, '<html><font color = red' + b.prod_std) as prod_std.

So which part of this did I do wrong or is it something that can not be accomplished?

Now for part 2 - This is the one that I am sure has been asked here. When doing a report within the designer, I know you can group like items and then each group will show on its own page, but can you group the items together and have them sequentially displayed on the same page?

Example:
Inserting
86 1234567890
90 90843573245

page 1

Bindery
56 384756392
66 963843592

page 2

Inking and Printing
1 993388475
4 9018327643

page 3

The above is how it is currently printed

This is what I am refering to:

Inserting
86 1234567890
90 90843573245
Bindery
56 384756392
66 963843592
Inking and Printing
1 993388475
4 9018327643

Page 1

I think the correct term is pagination or something like that.

Thanks and have a great day.

2

Part1. You’re trying to use an HTML FONT tag to color a chart pen? This doesn’t make any sense. Did you mean a table? If not, then no, you can’t have a single line on a chart change colors mid-way through

Part2. Not sure what the problem is. Just put them on the same page, don’t group anything.

3

Sorry about the confusion. You are correct carl it is not a chart. What it is is the reporting plugin. The ISNULL part of query first polls table one, if it finds data then that is what it used, however if it does not find the data in table one then it pulls data from table 2. The query itself is working fine. But one of the managers wants some easy way to know which table the query pulled from. That is why I tried changing the color. If the ISNULL is true it pulls from table 1 and the color would be blue, if it pulls from table 2 the color would be red.

That is why I showed how I changed the query to:

ISNULL(’’ + a.prod_std, ‘’ + b.prod_std) as prod_std

then in the reporting plug in I use @prod_std@

However when I try it this way I get the error message that it can not convert the varchar to bigint. And this leads me to beleive that because the values in the column in question are BIGINT you can not do this without first converting the value to a VARCHAR. But I am unsure.

Hope this clears things up better.

As for the reporting plug in grouping, dont I need to group in order to get the headers like I showed in the origional post? I know somewhere in here another user was talking about I think the term was pagination and grouping. I am going to look for it a little more in detail if I find it I will post the info here as well.

Thanks again for the quick reply.

[quote=“Carl.Gould”]Part1. You’re trying to use an HTML FONT tag to color a chart pen? This doesn’t make any sense. Did you mean a table? If not, then no, you can’t have a single line on a chart change colors mid-way through

Part2. Not sure what the problem is. Just put them on the same page, don’t group anything.[/quote]

4

I’ll take a whack at the first part…

There are 2 main problems, the first of which you’re on to: With one string and one number, it doesn’t know if the “+” means “convert the string to a number and add it” or “convert the number to a string and concat”. Looks like it’s picking the first. So yes, you could cast the int to a string first…

…HOWEVER, you’re still going to have a problem- your “IsNull” isn’t going to work as you’d like, because you’re concat’ing something to the values, so the first is never null. Instead, you’ll want to use a “CASE WHEN” statement:

SELECT CASE WHEN a.prod_std IS NULL THEN '<html><font color = red>' + cast(b.prod_std as varchar(255)) ELSE '<html><font color = blue>' + cast(a.prod_std as varchar(255)) END from table a, table2 b

Unfortunately I can’t really help with part 2, (surprisingly/shamefully) I don’t have much knowledge of the reporting plugin…

Regards,