Hi Guest,

Looking at your report, it is hard to tell how exactly your date fields are configured (whether they are a part of a date hierarchy etc). There are a couple of methods I can suggest

1. Use an individual day date field (01/01/2015) and format it in MM yyyy format. Under this scenario you would only need to use a single field in the master query or sub query.

Here is an example of formatting an individual date field to appear in MM yyyy format:

Before





After





2. Use the text advanced function 'Concatenation' to combine both fields. Please keep in mind that when you use this function it doesn't respect field formatting so you would not be able to add a 0 prefix to your month field and then concatenate it to the year field.

Here is an example of the Concatenation advanced function:

Fields Before Concat





New Concatenated Field









Anyway hopefully this information is helpful for you. Please let me know if you have any questions or concerns.

Kind Regards,

Dustin

Thanks a lot Dustin,

I tried the second way concatenate columns. The concatenate works perfect in the master query, but didn't work for the append query.(shown in the image below)








And I tried to apply the concatenation on month name with year, on month with year. But as long as it's in append sub query, it didn't give me anything.

Much appreciate!

Hi Guest,

Hmm that's odd that concatenation isn't working within your sub query...

Do you think that you could you send in a screenshot of your report output after trying to concatenate 'Append:Year' and 'Append:Month Name'?

Also, could you let me know which version of Yellowfin you are using so that I can test this in your build? I would like to test if this is a bug that has since been fixed.

Anyway, I look forward to your response and hopefully we can have a work around for you soon.

Kind Regards,

Dustin

Yeah it's weird. I copied the SQL to freehand SQL report, and it reported as error
"Invalid state, the Connection object is closed" I think our version is 6.3.

SELECT
T0.C0,
T0.C1,
T0.C2,
T0.C3,
T0.C4,
T0.C5,
T1.C4,
T1.C2,
T1.C3
FROM (
SELECT DISTINCT
datepart(year, "ClientVisit"."rev_timein") AS C0,
datename(month, "ClientVisit"."rev_timein") AS C1,
"Team"."team_name" AS C2,
"ClientVisit"."emp_name" AS C3,
CASE
WHEN 1 = 0 THEN "Planner"."visit_status"
WHEN "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD BY PROV', N'CNCLD>24hr') THEN N'Cancellations'
WHEN "Planner"."visit_status" IN (N'COMPLETED') THEN N'Completed'
WHEN "Planner"."visit_status" IN (N'NOSHOW', N'NOTPRESENT') THEN N'No Shows'
ELSE "Planner"."visit_status"
END AS C4,
COUNT("Planner"."visit_status") AS C5
FROM "dbo"."ClientVisit"
INNER JOIN "dbo"."Team"
ON (
"ClientVisit"."team_id" = "Team"."team_id"
)
INNER JOIN "dbo"."Planner"
ON (
"ClientVisit"."plan_id" = "Planner"."plan_id"
)
WHERE (
datepart(year, "ClientVisit"."rev_timein") = 2014
AND "Team"."team_name" IN (N'Med Clinic')
AND datepart(month, "ClientVisit"."rev_timein") IN (7)
AND "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD>24hr')
)
GROUP BY
datepart(year, "ClientVisit"."rev_timein"),
datename(month, "ClientVisit"."rev_timein"),
"ClientVisit"."emp_name",
"Team"."team_name",
CASE
WHEN 1 = 0 THEN "Planner"."visit_status"
WHEN "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD BY PROV', N'CNCLD>24hr') THEN N'Cancellations'
WHEN "Planner"."visit_status" IN (N'COMPLETED') THEN N'Completed'
WHEN "Planner"."visit_status" IN (N'NOSHOW', N'NOTPRESENT') THEN N'No Shows'
ELSE "Planner"."visit_status"
END

) T0
LEFT OUTER JOIN (
SELECT DISTINCT
"Team"."team_name" AS C0,
"ClientVisit"."emp_name" AS C1,
datepart(year, "ClientVisit"."rev_timein") AS C2,
datename(month, "ClientVisit"."rev_timein") AS C3,
COUNT("Planner"."visit_status") AS C4
FROM "dbo"."ClientVisit"
INNER JOIN "dbo"."Team"
ON (
"ClientVisit"."team_id" = "Team"."team_id"
)
INNER JOIN "dbo"."Planner"
ON (
"ClientVisit"."plan_id" = "Planner"."plan_id"
)
WHERE (
datepart(year, "ClientVisit"."rev_timein") = 2015
AND "Team"."team_name" = N'Med Clinic'
AND datepart(month, "ClientVisit"."rev_timein") = 7
AND "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD>24hr')
)
GROUP BY
datepart(year, "ClientVisit"."rev_timein"),
"ClientVisit"."emp_name",
datename(month, "ClientVisit"."rev_timein"),
"Team"."team_name"

) T1
ON T0.C2 = T1.C0
AND T0.C3 = T1.C1

Sorry I forgot to screenshot the report output.

Report master query



Append query




filter




Output

Hi Guest,

Apologies for the delay in responding. I have good news and bad news regarding this case...

First, I was able to replicate what you are experiencing in 6.3. There appears to be a defect that exists surrounding the use of the concatenation advanced function within sub queries.

The good news is, this defect has been resolved in 7.1.

I think you have two options...

1. Upgrade Yellowfin to 7.1 and use the concatenate advanced function or a calculated field to combine your two date fields

2. Rethink your date formatting in your current report

I think #2 is likely the more reasonable of the two options in this case, as it doesn't seem to make much sense to have multiple date fields in your report when you could just reference an individual date field (i.e a specific day date) and format it into YYYY MMM format. Do you think that this is feasible?

Could you let me know if either work arounds will work for you? I look forward to hearing back.

Kind Regards,

Dustin

Hi Dustin,

Thanks for your help. But I don't think concerting the date to month and year will work in my current report since I need it to group by month. I tried to convert date to mm/yyyy, but the result of the report is grouped by date. I also tried to convert the format of month. But it doesn't work.

I need to solve the problem in a new and similar cross tab report. But I cannot even apply any advanced function in the columns and rows. The graph below sum up the same month in 2014 and 2015, giving me a wrong result.









So Now I'm thinking about doing the concatenation using freehand sql in the view level. I haven't figured the syntax though. Is there any easier to do this?

Thanks a lot again!